我们遇到了数据库性能方面的问题,我对.NET Profilers有很多经验,并且总是在应用程序上执行分析,但很多开发人员现在都在等待,直到很晚(当它出现问题时)开始分析并尝试收集有关如何解决问题的数据.
这可能不是一个单一的答案帖子更多的是“帮助我是一个数据库IdioT”帖子,并寻找任何方向个人建议,建议和如何追踪问题的经验.
至于我们使用sql 2005的设置,我在生产中的访问权限非常有限,只能通过门户界面运行sql数据库引擎优化顾问和sql事件探查器,我可以复制和粘贴,但这就是它.我想要做的一件事是获得生产查询和调用的真实快照,以便我可以在较低的环境中将它们加载到调优引擎中,我可以尝试修改数据库,以便我可以从引擎调整中获取建议顾问.
解决方法
此脚本可用于确定您是否选择了正确的索引.您需要查看索引用于搜索的频率,并将其与索引更新的频率进行比较.寻求性能是以更新性能为代价的.更糟糕的是,当索引频繁更新时,您会导致索引碎片化并且统计信息过时.
您还应该将range_scan_count与singleton_lookup_count进行比较.单例查找之前首选范围扫描.单例查找可能是索引查找和键查找操作的原因.也就是说,对于在索引查找中找到的每一行,sql将在聚集索引中查找数据页,这对于让我们说成千上万,但不是数百万行是可以的.
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) 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
