1

We have CRM NAV on premise. We have lot of performance issue. We do get 40-50 messages per day regarding waring for query Execution times in application log.

The warning message that we get in application log which is like

Warning 1: Query execution time of 46.3 seconds exceeded the threshold of 30 seconds. Thread: 148; Database: MSCRM; And query itself.

But when we run that query it takes no time in SSMS OR may be 1 or 2 seconds.

Why there is a big difference in terms of time running the query from CRM and directly on SSMS. ?

Do you think we should rebuild or reorganize indexes anyway to improve performance ?

Thanks. P.

user2040021
  • 309
  • 3
  • 15
  • The folks on [Stack DBA](http://dba.stackexchange.com/search?q=query+slow) might be able to help. But they'll need a lot more information. Anything you can capture from [SQL Server Profiler](https://msdn.microsoft.com/en-us/library/ms181091.aspx?f=255&MSPPError=-2147217396) or scrape from log files will help. Be sure to include the execution plans. – David Rushton Aug 16 '16 at 11:56
  • Well we ran few queries into profiler and got suggestions of creating and dropping indexes but how safe it is to do that? – user2040021 Aug 16 '16 at 12:02
  • Try to add `-T4136` for startup parameters and check about `Parameter Sniffing` – Devart Aug 16 '16 at 13:39
  • Sorry can you explain in detail, please or give me some reference to it. Thanks. – user2040021 Aug 16 '16 at 14:26
  • 1
    http://www.sommarskog.se/query-plan-mysteries.html – Devart Aug 16 '16 at 14:42

1 Answers1

0

The most likely difference between running the report in your CRM system and running from SSMS would be the user who is executing the reports. Most reports are done against the filtered views which include a lot of overhead enforcing security. Executing a query with system administrator rights significantly reduces the overhead of queries against the filtered views. Are you using a system administrator to execute from SSMS and a non administrator in CRM?

Note that while unsupported, writing report queries against the regular views works perfectly fine and is MUCH faster. Of course you lose the functionality included in the filtered views including security checks.

Indexes: Yes, you should be sure to index your CRM databases on a regular basis and defragmenting your indexes daily or weekly is even more important.

Creating and de-fragmenting indexes on the CRM database are supported operations. Microsoft recommends that indexes with a fragmentation level greater than 30% be rebuilt, while those with greater than 10% fragmentation should be reorganized.

Defragmenting your production indexes is perfectly safe but you should run the operation overnight to minimize any affect on system performance.

Zach Mast
  • 1,698
  • 1
  • 10
  • 17