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 |
|
vbwrangler
Starting Member
9 Posts |
 Posted - 2011-05-17 : 17:06:26
|
| Checking a client’s index and table fragmentation the numbers are very high. But I have been told that you don't have to defrag a SAN because the storage method is so different. Is there anything to that or would I see a performance boost by defragging these indexes. Please only respond if you know the answer to this question.vbwranglerDGrahamvbwrangler@yahoo.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vbwrangler
Starting Member
9 Posts |
Posted - 2011-05-17 : 18:04:34
|
| Still trying to understand this a little more:They have a database that is strictly for reporting and only loaded monthly. No other insert throughout the month. And the code is little more than SELECT * INTO tblMonthlyTotals FROM tblDailySales. {Not my code don't go into a tangent on the "*".} These tables show indexes with heavy fragmentation, as high as 99%. How is that possible?I anticipate you next question below is the code I have used to identify index fragmentation:EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');Thanks vbwranglerDGrahamvbwrangler@yahoo.com |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-17 : 21:56:09
|
| Check to see if the table has a clustered index. If it does not, then this is called a HEAP and index fragmentation does not apply. To eliminate fragmentation, add a clustered index to the table.As for defragging a SAN - this is a common misunderstanding. First, indexes are creating inside the data file of a database - and this has no bearing on file fragmentation. Index fragmentation has to do with how the data on the pages inside the file are arranged.File level fragmentation - that is, fragmentation of the actual data file as seen from Windows does not have any relationship with the actual data in the file. If the data file is fragmented - you should schedule a downtime and perform a defrag on the file. There is this myth that because a SAN will spread a file across multiple disks - that fragmentation of the file has no meaning. This is not true, because Windows doesn't know anything about the SAN - and if it sees a fragmented file it will perform additional IO operations to read each file fragment.Jeff |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-05-18 : 13:30:43
|
| To add to what Jeff said,A heap can still have fragmented non-clustered indexes, that need rebuilt as well. |
|
|
|
|
|
|
|
|
Does a San need to be defraged?