碎片后如何检查数据库性能 [英] How to check DB performance after fragmentation

问题描述

我通过网络来提高我们需要在特定数据库中处理碎片的性能



我才知道我们可以通过双向REBUILD改变索引（碎片时）大于30％）并重新组织（当碎片少于30％时）



所以我根据碎片百分比进行了重建和重组。我也注意到了如果页面数量高于1000，那么碎片百分比会越来越低，就像特定索引一样，我也会针对CLUSTERED INDEX类型进行碎片分析





但毕竟这个我检查数据库大小仍然相同..你能帮我，我怎么能从前端检查数据库的性能

解决方案

到减少数据库大小，使用： SHRINKDATABASE [ ^ ]或/和 SHRINKFILE [ ^ ]



MS SQL Server性能是非常具有挑战性的主题。我根本不知道如何从前端检查它，但是......

一种方法是测量查询执行的时间，使用：设备统计时间 [ ^ ]命令。

第二种方法是使用 SET STATISTICS IO [ ^ ]。



有关SQL Server性能调优的更多信息，请在此处找到：http://blog.sqlauthority.com/sql-server-performance-tuning/ [ ^ ]。



SQL Server性能取决于查询/查询性能。如果要查找运行时间最长的查询，请阅读： http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/ [ ^ ]

要检查性能，请使用此查询

  SELECT  avg_fragmentation_in_percent ，fragment_count 
  FROM  sys.dm_db_index_physical_stats（ DB_ID （）， OBJECT_ID （'  SecondTable'）， NULL ， NULL ，'  LIMITED '）
  GO  

如需更多说明，请查看此链接： http：// blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/ [ ^ ]

I gone through in net to improve performance we need to treat fragmentation in particular database

I came to know we can alter index in two way REBUILD(when fragmentation s greater than 30 % ) and reorganize (when fragmentation is less then 30 %)

So i did rebuild and reorganize according to the fragmentation percentages .and also i noticed while doing fragmentation percentage is getting lower when page count is above 1000 like that for particular index and also i targeted fragmentation for CLUSTERED INDEX type


But after all this i checked DB size its same still.. Could you please help me how could i can able to check performance of the DB from front end

解决方案

To reduce database size, use: SHRINKDATABASE[^] or/and SHRINKFILE[^]

MS SQL Server performance is very challenging subject. I don''t know how to check it from front-end at all, but...
One way is to measure the time of query execution, using: SET STATISTICS TIME[^] command.
Second way is to check disk activity, using SET STATISTICS IO[^].

More about SQL Server Performance Tuning, you''ll find here: http://blog.sqlauthority.com/sql-server-performance-tuning/[^].

SQL Server performance depends of query/queries performance. If you want to find the longest running query, read this: http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/[^]

To check performance use this query

SELECT avg_fragmentation_in_percent, fragment_count
 FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
 GO

For more clarification check this link:http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/[^]

这篇关于碎片后如何检查数据库性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！