On this article I will be covering mainly optimization around indexes and how DMV's can help to monitor and optimize indexes.
Data management views (DMV's)
Dynamic management views store SQL transactional information which can be used to monitor health, performance and diagnose problems. DMV's are extremely helpful in particular around performance-tuning. The information is stored until you restart SQL server, after a restart all is reset to 0.
There are a number of DMV's to retrieve a different number of metrics for different purposes; we will be looking in particular at the Index related and Execution DMV's:
Indexes Overview
Imagine walking to a library with 1 million books stored across 5 floors, and ask for a book. If the library does not keep a record where each book is stored, which floor, shelf, categories etc... how long to you think staff would take to find your book? a very long time. The same concept applies to databases, indexes are needed to improve response time when users request specific data.
We will be mainly looking at optimization with indexes so before we do that I want to explain very briefly what they are and how they work. There are two types of indexes, clustered index and non-clustered index.
Both type of indexes have positive and negative areas, the clustered index has a higher cost when you need to re-index the column 'Amount' because all the rows need to shift into the correct place, however the data it's immediately available when data is found. The non-clustered indexes are quicker to re-index but require an extra lookup to gather the data on the table. Also note that the clustered indexes only one index can exist per table. The non-clustered indexes can be created for multiple columns on the same table.
Missing Indexes
DMV's store missing indexes when queries are optimized by the SQL query optimizer; if you have a customized CRM environment you should monitor these type of DMV's on a regular basis. Below a nice script you could use with references to missing indexes DMV's.
Index Usage
It's important you check for Index usage because indexes consume memory and disk I/O resources when they need to be rebuild to keep the most up to date data.
To check for index usage run the following query:
The output of this query would be something like:
From the above screenshot is clear that _DMV_09 has not been used since it was created and therefore should be deleted.
Fragmentation
CRM ships with built-in maintenance jobs which are configured during installation and are designed to optimize the database and reduce fragmentation. It's good practice to re-configure these jobs to run out-of-hours.
To configure the built-in sql maintenance jobs download the following tool:
http://crmjobeditor.codeplex.com/
Place the tool under C:\Program Files\Microsoft Dynamics CRM\Tools\
The tool provides a flexible and easy way to configure the maintenance jobs; note that the job will run once every day( every 1440 minutes), you can configure it to run more times during the day.
Normally the CRM built-in maintenance jobs are designed to keep the system healthy and do a very good job, to understand if you need these jobs to run more often you can monitor fragmentation with the following queries:
Or you can be more specific at the table level:
You can check if the maintenance tasks have run successful by looking at the last time the indexes have been updated:
Check for expensive queries
With a better understanding around indexes and DMV's we could start looking at top 20 most expensive queries in your CRM environment using sys.dm_exec_query_stats to give you a better picture what is going on under the hood and analyse whether tuning is needed for those queries.
Database Maintenance
However CRM maintenance jobs do a really nice job in keeping the system healthy, I would still recommend creating SQL jobs to do regular out-of-hours database maintenance:
Summary
There are a number of scripts and documentation on the internet around DMV's, is good practice monitor these metrics and optimize the system accordingly. Note that with each SQL version DMV's may change and new features added etc...
We have covered basic SQL optimization around indexes and an overview on how indexes work and how they can help. I hope you enjoyed the article, please leave your feedback.
References
Data management views (DMV's)
Dynamic management views store SQL transactional information which can be used to monitor health, performance and diagnose problems. DMV's are extremely helpful in particular around performance-tuning. The information is stored until you restart SQL server, after a restart all is reset to 0.
There are a number of DMV's to retrieve a different number of metrics for different purposes; we will be looking in particular at the Index related and Execution DMV's:
- Index
- sys.dm_db_index_operational_stats
- sys.dm_db_index_physical_stats
- sys.dm_db_index_usage_stats
- sys.dm_db_missing_index_columns
- sys.dm_db_missing_index_details
- sys.dm_db_missing_index_group_stats
- sys.dm_db_missing_index_groups
- Execution
- sys.dm_exec_query_stats
- sys.dm_exec_requests
- http://msdn.microsoft.com/en-us/library/ms187974(v=sql.105).aspx
- http://msdn.microsoft.com/en-us/library/ms188068(v=sql.105).asp
Indexes Overview
Imagine walking to a library with 1 million books stored across 5 floors, and ask for a book. If the library does not keep a record where each book is stored, which floor, shelf, categories etc... how long to you think staff would take to find your book? a very long time. The same concept applies to databases, indexes are needed to improve response time when users request specific data.
We will be mainly looking at optimization with indexes so before we do that I want to explain very briefly what they are and how they work. There are two types of indexes, clustered index and non-clustered index.
- Non-Clustered IndexesThis type of index, indexes a particular column and hold a reference to where on the table this data can be found. The example below, shows if you want to find an order with an amount of 210 you would need to scan the entire table to find all orders with an amount of 210. If we create an index for the column 'Amount', the index it's ordered descendent and the query would stop at the last 210 and there would be no need to continue the execution.
- Clustered IndexesClustered indexes have the same concept but, they sort the column 'Amount' descendent on the table itself, when orders with the amount 210 are found SQL does not need to lookup the reference to find the details of that order, the data is accessible immediately.
Both type of indexes have positive and negative areas, the clustered index has a higher cost when you need to re-index the column 'Amount' because all the rows need to shift into the correct place, however the data it's immediately available when data is found. The non-clustered indexes are quicker to re-index but require an extra lookup to gather the data on the table. Also note that the clustered indexes only one index can exist per table. The non-clustered indexes can be created for multiple columns on the same table.
Missing Indexes
DMV's store missing indexes when queries are optimized by the SQL query optimizer; if you have a customized CRM environment you should monitor these type of DMV's on a regular basis. Below a nice script you could use with references to missing indexes DMV's.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SELECT migs.avg_total_user_cost
* (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS
improvement_measure, 'CREATE
INDEX [myIndex_'
+ CONVERT
( varchar ,
mig.index_group_handle) + '_'
+ CONVERT
( varchar ,
mid.index_handle) +
'_'
+ LEFT
(PARSENAME(mid.statement, 1), 32) + ']' +
'
ON '
+ mid.statement +
'
('
+ ISNULL
(mid.equality_columns, '' )
+
CASE
WHEN
mid.equality_columns IS
NOT
NULL
AND
mid.inequality_columns IS
NOT
NULL
THEN
','
ELSE
''
END
+
ISNULL
(mid.inequality_columns, '' ) +
')' +
ISNULL
( '
INCLUDE ('
+ mid.included_columns + ')' ,
'' )
AS
create_index_statement, migs.*,
mid.database_id, mid.[object_id] FROM
sys.dm_db_missing_index_groups mig INNER
JOIN
sys.dm_db_missing_index_group_stats migs ON
migs.group_handle = mig.index_group_handle INNER
JOIN
sys.dm_db_missing_index_details mid ON
mig.index_handle = mid.index_handle WHERE
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 ORDER
BY
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
DESC |
Index Usage
It's important you check for Index usage because indexes consume memory and disk I/O resources when they need to be rebuild to keep the most up to date data.
To check for index usage run the following query:
1
2
3
4
5
6
7
8
9
|
select
OBJECT_NAME(a.object_id),b. name ,a.user_seeks,a.user_scans,a.* from
sys.dm_db_index_usage_stats a join
ORG_MSCRM.sys.indexes b on a.object_id
= b.object_id and
a.index_id = b.index_id where a.database_id
= DB_ID( 'ORG_MSCRM' ) and
b. name
like
'myIndex%' order
by b. name
asc |
and
b.
From the above screenshot is clear that _DMV_09 has not been used since it was created and therefore should be deleted.
Fragmentation
CRM ships with built-in maintenance jobs which are configured during installation and are designed to optimize the database and reduce fragmentation. It's good practice to re-configure these jobs to run out-of-hours.
To configure the built-in sql maintenance jobs download the following tool:
http://crmjobeditor.codeplex.com/
Place the tool under C:\Program Files\Microsoft Dynamics CRM\Tools\
The tool provides a flexible and easy way to configure the maintenance jobs; note that the job will run once every day( every 1440 minutes), you can configure it to run more times during the day.
Normally the CRM built-in maintenance jobs are designed to keep the system healthy and do a very good job, to understand if you need these jobs to run more often you can monitor fragmentation with the following queries:
1
2
3
4
5
|
SELECT
OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level, avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM
sys.dm_db_index_physical_stats (DB_ID(N 'ORG_MSCRM' ),
NULL ,
NULL ,
NULL
, 'SAMPLED' ) ORDER
BY
avg_fragmentation_in_percent DESC |
Or you can be more specific at the table level:
1
2
3
|
SELECT
avg_fragmentation_in_percent,* FROM sys.dm_db_index_physical_stats
( db_id( 'ORG_MSCRM' ), OBJECT_ID( 'PrincipalObjectAccess' ),
NULL ,
NULL
|
1
< class="sql plain">,
'DETAILED' )
|
You can check if the maintenance tasks have run successful by looking at the last time the indexes have been updated:
1
2
3
4
|
SELECT
name
AS
index_name, STATS_DATE(OBJECT_ID,
index_id) AS
StatsUpdated FROM
sys.indexes WHERE
OBJECT_ID = OBJECT_ID( 'PrincipalObjectAccess' ) |
With a better understanding around indexes and DMV's we could start looking at top 20 most expensive queries in your CRM environment using sys.dm_exec_query_stats to give you a better picture what is going on under the hood and analyse whether tuning is needed for those queries.
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT
DISTINCT
TOP
20 t.TEXT
QueryName, s.execution_count
AS
ExecutionCount, s.max_elapsed_time
AS
MaxElapsedTime, ISNULL (s.total_elapsed_time
/ NULLIF (s.execution_count,0),
0) AS
AvgElapsedTime, s.creation_time
AS
LogCreatedOn, ISNULL (s.execution_count
/ NULLIF (DATEDIFF(s,
s.creation_time, GETDATE()),0), 0) AS
FrequencyPerSec FROM
sys.dm_exec_query_stats s CROSS
APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER
BY s.max_elapsed_time
DESC |
Database Maintenance
However CRM maintenance jobs do a really nice job in keeping the system healthy, I would still recommend creating SQL jobs to do regular out-of-hours database maintenance:
- Shrink the database
- DBCC SHRINKFILE (1, TRUNCATEONLY);
- DBCC SHRINKFILE (2, TRUNCATEONLY);
- Rebuild indexes
- EXEC sp_msforeachtable 'DBCC DBREINDEX("?"," ",80)'
- Update statistics
- EXEC sp_updatestats;
Summary
There are a number of scripts and documentation on the internet around DMV's, is good practice monitor these metrics and optimize the system accordingly. Note that with each SQL version DMV's may change and new features added etc...
We have covered basic SQL optimization around indexes and an overview on how indexes work and how they can help. I hope you enjoyed the article, please leave your feedback.
References