Troubleshooting Performance in CRM: Data Tier Part 3–Analysis

So, by this point we have captured data and have imported it into SQL Nexus.  With that down, we come to the interesting and productive part, which is going through and analyzing the data that has been captured.  There are a lot of great articles out there on how to tune SQL for performance. We are here to focus on what we look for in relation to CRM.  That said, lets get started. 

When you select SQL Perf Main on the left hand side of the screen, you get to a screen of reports based on that data that was collected.  From support, we will often use this to review some of the environmental information and the error logs.  In the environment, there are a couple of key items that we look at:

Environment:

1) Number of databases – This is mostly used as a way to gauge how much the server is doing

2) Max degree of parallelism – For CRM, we would prefer to see this set at a value of 1

3) Max Server memory (MB)  - Generally, this should be slightly less than the maximum memory for the actual server itself

While there is a lot of other good settings returned, those three are key for CRM. 

From there, we start looking into some of the specific reports. 

ReadTrace Reports:

Select Unique Batches at the top and we will get 4 graphs that let us see a combination of the top 10 queries for each of CPU, Duration, Reads and Writes.  What we are looking for on this is the outlying queries, something taking a much larger amount in any of the four categories.  You can click on the query from the chart and it will drop you down into the list below.  Once we look at the query itself there are two primary things we can do.  First, is to grab the query itself and run it within Database Tuning Advisor (DTA).  Second, we can look at the query itself and identify where it is coming from within the system.  The biggest queries that we will see as performance culprits, historically, are things like views and quick finds.  Quick finds can be identified by the multiple Like statements being used.  In a later post we will take a deeper look into views and quick finds, what are the performance impacts and what we can do to optimize performance while giving users the best experience. 

Blocking and Resource Wait Statistics:

This area will highlight the specific items that cause delays.  The biggest things we look at in here are going to be for any blocking that was captured as well as CXPacket. 

CXPacket, its comparison to other stats and its overall number in total milliseconds.  If you are seeing CXPacket this indicates that the max degree of parallelism is not set in a way that is the most advantageous. 

The Blocking, section then can be drilled into specific chains, see what string of queries were colliding and then further investigation can be done. 

Missing Indexes:

This section will give a quick view of the top ten indexes that were identified by SQL as something that would improve performance.  These will be across all databases on the SQL server.  While the report displays the top 10, there is a potential for many more to identify.  One way to pull out a larger list is to go to the SQL database itself that Nexus utilizes.  The SQL statement below can be used to pull out the indexes identified, either for a specific database or for all CRM Organization databases. 

select * from tbl_MissingIndexes where create_index_statement like '%_MSCRM%' and improvement_measure > 100000

Missing indexes and best practices is something that can be driven into a lot further but most often we will put in a list of the top 10-20 indexes and then go back through and do a new capture to see how we are comparing.  The improvement measure is something that I get questions about, and is really more of a comparison guide.  Normally I suggest putting in indexes with a value over 100,000.

Virtual File Stats:

Virtual File stats is a display of some aggregated performance monitor information.  It breaks down by database and then by file type whether it is primary data file, secondary file or log and what is the full file path.  The primary column we are looking at here is the Avg IOTime.  For this average value we are looking for 15 ms.  This will give us the best overall performance.  Depending on hardware flexibility the best performance is going to be seen by breaking out files to three sets of physical drives.  One drive for the mdfs, one for ldfs, and then a third specifically for tempdb.