SQL 探查器和调优顾问 [英] SQL Profiler and Tuning Advisor

问题描述

我们在数据库性能方面遇到了问题，我在 .NET Profilers 方面有很多经验，并且总是在应用程序上执行分析，但就像很多开发人员一样，我现在等到很晚(当它出现问题时)) 开始分析并尝试收集有关如何解决问题的数据.

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.

这可能不是一个单一的答案帖子，只是更多的帮助我是一个 DB IDIOT"帖子并寻找任何方向的个人建议、建议和如何追踪问题的经验.

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.

就我们使用 SQL 2005 的设置而言，我在生产中的访问权限非常有限，只能通过门户界面运行 SQL 数据库引擎优化顾问和 SQL Profiler，我可以复制和粘贴，但仅此而已.我想做的一件关键事情是获得生产查询和调用的真实快照，以便我可以在较低的环境中将它们加载到调整引擎中，我可以尝试固定数据库，以便我可以从引擎调整中获得建议顾问.

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.

推荐答案

此脚本可用于确定您是否选择了正确的索引.您需要查看索引用于查找的频率，并将其与索引的更新频率进行比较.寻求性能是以更新性能为代价的.更糟糕的是，当索引频繁更新时，会导致索引碎片化和统计数据过时.

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.

您还应该将 range_scan_count 与 singleton_lookup_count 进行比较.在单例查找之前首选范围扫描.单例查找可能是索引查找和键查找操作的原因.也就是说，对于索引查找中找到的每一行，sql 都会在聚集索引中查找数据页，这对于几千行是可以的，但对于数百万行则不行.

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

这篇关于SQL 探查器和调优顾问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！