Hosk Top CRM Articles of the week – 27th Feb

Article of the week

Diving into the Product Structure of Microsoft Dynamics CRM 2015 (and meet ET)
A detailed post and video on the new product structure

Best of the Rest

CRM SQL Query go faster trick – SQL Server trace flag 8780

Training & Adoption Kit for Microsoft Dynamics CRM
A good bunch of links for new CRM users.

9 reasons to replace excel spreadsheet with CRM
How CRM can replace a business excel

XRMtoolbox updated
A couple of interesting things. Field Level Security bulk updater has been updated and XRMToolbox has been moved to github

Why the advanced find is a CRM Developers best friend

Why CRM Advanced find is soo useful to CRM developers

Tip #329: Resolve Missing Record Dependencies
resolving missing dependencies

Why your CRM code and customizations should be simple
The benefits of writing simple customizations

SYSTEM-WIDE ALERTS IN DYNAMICS CRM 2015
You can do system-wide alerts in CRM 2015

Using “Roslyn” to Catch CRM Coding Issues
A detailed and interesting blog post looking at the new features and how to use them with CRM

Custom State Model Transitions in Spring Release for Dynamics CRM 2013
This is a really cool piece of functionality, managing what statuses a record can move to. I still haven’t come close to using it yet.

What are the hidden costs of excluding user adoption in your Microsoft Dynamics CRM implementation plan?
A good article looking at the hidden costs

Unit Testing Plugins using Microsoft Fakes
A great post on testing Microsoft plugins with Microsoft Fakes

 

Advertisements

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

SL Server Trace flag – 8780