Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Why9999
Starting Member
10 Posts |
 Posted - 2011-05-24 : 12:16:10
|
| We have been using sqlfool's defrag/reindex/reorg script. However, it has bogged down in speed as the amount of schema has grown.This is for a large server and with lots of tables/indexes in several databases and what I have found is that it takes forever just for the script to gather the stats for deciding which tables to defrag based on fragmentation percentage, etc. Anybody have any recommendations for some good scripts out there? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Why9999
Starting Member
10 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
purdyra
Starting Member
7 Posts |
Posted - 2011-05-26 : 13:19:13
|
| I am running something very similar I found somewhere? It gets the fragmentation of each index so I can then choose which index needs to be executed. It takes 6-12 minutes. Lately we've been getting some weird errors, like other jobs timing out or server is not responding. I've noticed it's when I happen to be running this script at the same time. Could this be the reason why or is it just a fluke and something else I need to look into? SELECT left(o.name, 30) as tblName, left(b.name, 20) as IndexName, left(avg_fragmentation_in_percent, 10) as avg_fragm_pct, left(page_count, 10) as PageCount, left(index_type_desc, 20) as Index_Type_Desc, left(fragment_count, 10) as fragm_count, avg_fragment_size_in_pages, left(a.index_id, 5) as IndexID FROM sys.dm_db_index_physical_stats (DB_ID(N'i-wireless'), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id inner join sys.objects o on b.OBJECT_ID = O.OBJECT_ID where o.type <> 's' and index_type_desc <> 'heap' and page_count > 100 order by avg_fragmentation_in_percent desc |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-05-26 : 13:52:43
|
| The reason why it's so fast is that it's using LIMITED for the DMF. LIMITED is the default. You can choose to specify LIMITED if you run my code, and it too will be fast. But be warned that LIMITED doesn't provide as good of information as SAMPLED and SAMPLED doesn't provide as good of information as DETAILED. LIMITED is the fasted, then SAMPLED, then DETAILED. So the better information you collect, the slower it'll be. That's just the way it works.So there's nothing special about the code you found. My code and sqlfool's code are querying the same DMF.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Reorg/Defrag Script