CRM SQL Query go faster trick – SQL Server trace flag 8780
A developer was looking at some reports to try and improve performance and they were advised by Microsoft to use a trick which I had never heard off setting SQL Server trace flag 8780
I know what you are thinking
“Hosk, you didn’t even know about turning on SQL Server trace flag 8780, amateur”
Everyday is a school, so prepare yourself for today’s lesson
What difference did turning on the flag
The performance was dramatically improved, changing the report speed from 14 seconds down to 1 second!
BOOM
I’m interested tell me more
The report was an SQL report and to turn on the flag you simply put the this line at the end of the SQL statement, after the where clause.
option (querytraceon 8780)
e.g.
select name, accountid, telephone1
from account
where name = ‘hosk’
option (querytraceon 8780)
The good thing to note is you can turn this flag on for individual queries.
What does the flag do
The developer called it the “make it faster” setting on SQL. The query in the report was joining three different tables and one of them was contact.
warning – the description below is my understanding, it could well be wrong but it will give you an idea of what I think is happening, more research by yourself would need to be done if you are thinking of using it.
When SQL queries run on SQL Server the SQL server creates an execution plan. SQL Server calculates the most efficient way to retrieve the data you want. When the query is compiled if the execution plan can’t find a quick it will time out and then go with the best one it had found so far.
There could be other more effective execution plans for your SQL statement and if you gave the execution plan a bit more time it could find them.
When I mention time, I’m talking milliseconds (which is a long time for SQL Server)
In the report we were joining the contact table but using a contact guid but the execution plan was timing out because the execution plan was doing a full table scan of the contact table (2 million rows).
Whilst it was trying to scan the whole contact table it timed out and gave up (who can blame it)
SQL Server trace flag – 8780 to the rescue. This flag gives the sql optimizer more time to find the most effective (e.g. quickest) way to retrieve the data.
You set the flag (which can be set server side or for each individual query) this increases the time limit before the optimizer timeout occurs.
The result in our reports meant the execution plan found a better way to optimize the query and the reports ran a lot of faster.
What are the downsides
Traceflags in SQL Server are undocumented and unsupported by Microsoft (unless they advise you to use it :-)
I view traceflags as developer tools written by developers but not fully tested in all scenarios. This means Microsoft doesn’t know how the queries will react in all scenarios.
The obvious danger by turning off the time limit for optimizing a query I guess is it could keep on optimizing for a long time. The problem is no one really knows what it’s going to do
When to use it
If you have some reports which are taking a long time to run and you have been asked to optimize them, turning on the this flag for individual queries (e.g. reports) could save you a lot of time.
It’s unsupported so I would suggest you only use it when you really need to
Further reading
I couldn’t find a lot of information on this mysterious trace flag but these three articles will help you learn more
Optimizer Timeout or Optimizer memory abort
Is it possible to give the optimizer more or all the time it needs
Filed under: CRM 2011, CRM 2013, CRM 2015, SQL Server
*This post is locked for comments