SQL Profiler and Tuning Advisor

Question

We are having problems with database performance, and I have a lot of experience with .NET Profilers and always perform the analysis on the app, but much like a lot of developers I am now waiting until really late (when its a problem) to start analyzing and trying to gather the data on how to fix the issue.

This is probably not going to be a one answer post just more of a "HELP I AM A DB IDIOT" post and looking for any direction personal advise, recommendations, and experience on how to track down issues.

As far as the setup we use SQL 2005, I have very limited access in production to only run SQL Database Engine Tuning Advisor, and SQL Profiler through a portal interface, I can copy and paste but that's about it. One key thing I would like to do is get a true snap shot of production queries and calls so I can load those into the tuning engine in a lower environment that I can try to nail the DB so I can get the recommendations from the Engine Tuning Advisor.

Answer 1

Here are some links that should get you started in your quest for performance.

http://stackoverflow.com/questions/761204/what-resources-exist-for-database-performance-tuning

http://stackoverflow.com/questions/18783/sql-what-are-your-favorite-performance-tricks

http://stackoverflow.com/questions/39331/what-generic-techniques-can-be-applied-to-optimize-sql-queries

http://stackoverflow.com/questions/1311181/best-way-to-improve-performance-and-include-somehow-failover

Answer 2

If you can use the profiler to store the events to a table, then it is no problem to use the Database Tuning advisor (DTA) to optimize the database from the log table, but personally i do not use the DTA at all. It takes a lot of time to use the DTA and I want more control over what is happening.

If you can convince the owner of the server to create a new database called something like "SQLToolkit" and give you execute rights to procedures, then I have a couple of procedures that will help you choose the right indexes.

    CREATE PROCEDURE [ADMIN].[spMissingIndexes]
AS
SELECT
      mid.statement,
      mid.equality_columns,
      mid.inequality_columns,
      mid.included_columns,
      migs.user_seeks,
      migs.user_scans,
      migs.last_user_seek,
      migs.avg_user_impact,
      user_scans,
      avg_total_user_cost,
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight]--, migs.*--, mid.*
   FROM
      sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig
         ON (migs.group_handle = mig.index_group_handle)
      INNER JOIN sys.dm_db_missing_index_details AS mid
         ON (mig.index_handle = mid.index_handle)
   ORDER BY
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;

GO

Answer 3

Upon request i send another useful script to determine how often and how long any index is blocked due to the locking mechanism in SQL:

CREATE PROCEDURE [ADMIN].[spIndexContention]
    @dbname sysname
WITH EXECUTE AS CALLER
AS
declare @dbid int
select @dbid = DB_ID(@dbname)
declare @sql nvarchar(1000)
SET @sql = N'SELECT dbname=DB_NAME(database_id), tablename=object_name(s.object_id, s.database_id)
    , indexname=i.name, i.index_id
    , row_lock_count, row_lock_wait_count
    , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
    , row_lock_wait_in_ms
    , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
FROM sys.dm_db_index_operational_stats (' + convert(nvarchar(5),@dbid) + ', NULL, NULL, NULL) s
    INNER JOIN ' + @dbname + N'.sys.indexes i 
     ON i.object_id = s.object_id
     AND i.index_id = s.index_id
ORDER BY row_lock_wait_count desc'
print @sql
exec sp_executesql @sql


GO

Answer 4

This script can be used to determine if you have choosen the right indexes. You need to look at how often the index is used for seek and compare it to how often the index is updated. Seek performance comes at the cost of update performance. And what is worse, when index is frequently updated you causes the index to be fragmented and the statistics to be out of date.

You should also compare the range_scan_count to singleton_lookup_count. Range scan is preferred before singleton lookup. A singleton lookup may be the cause of and index seek and a key lookup operation. That is, for every row found in the index seek, sql will lookup the datapage in the clustered index, and that is okay for lets say a couple of thousands, but not for millions of rows.

CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
    @dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))

set @sql=N'select ''object'' = object_name(iu.object_id, iu.database_id)
     , i.name
     ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
     ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
     ,''user writes'' = iu.user_updates
     ,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
where 
    iu.database_id = ' + @dbid + '
    and iu.index_id=i.index_id
    and iu.object_id=i.object_id
    and (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'

exec sp_executesql @sql

set @sql=N'SELECT
   ''object'' = object_name(o.object_id, o.database_id),
   o.index_id,
   ''usage_reads'' = user_seeks + user_scans + user_lookups,
   ''operational_reads'' = range_scan_count + singleton_lookup_count,
   range_scan_count,
   singleton_lookup_count,
   ''usage writes'' = user_updates,
   ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
   leaf_insert_count,
   leaf_update_count,
   leaf_delete_count,
   ''operational_leaf_page_splits'' = leaf_allocation_count,
   ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
   ''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
   ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
   ' + @dbname + '.sys.dm_db_index_usage_stats u
WHERE
   u.object_id = o.object_id
   AND u.index_id = o.index_id
ORDER BY
   operational_reads DESC,
   operational_leaf_writes,
   operational_nonleaf_writes'

exec sp_executesql @sql

GO