| Author |
Topic |
|
AnAgSh
Starting Member
18 Posts |
 Posted - 2007-10-03 : 23:55:06
|
| All, My employer is concerned that the Rebuild/Reorganize indexes will slow down the server,will take more time and our online application users will experience slow responses. And they don't want to do off line defrag either. So I am going to suggest to spread out the Rebuild/Reorganize indexes in such a way that rebuild/reorganize is done in small chunks rather than doing it all at once. What do you guys think of this approach?Thanks, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-04 : 02:02:06
|
| Offline defrag won't help with the indexes anyway. Taking the SQL Server down to defragment the files provides very little performance help and is typically not worth it. It is recommended to focus on index fragmentation instead.How big is your database? Have you ever tested how long the rebuild or reorgnanize takes on the database? It is much faster now than what it was in SQL Server 2000. What version of SQL Server 2005 are you using?How fragmented are your indexes?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
AnAgSh
Starting Member
18 Posts |
Posted - 2007-10-21 : 14:45:31
|
| Tara, The one of biggest db has 80GB data. It is defragmented more than 95% for many indexes. No, we have not tested to see how long it takes to defragment. We are pretty sure, it will take longer. Moreover, Management will not approve the slow down in performance while defragmentation happens. We are using MS SQL SERVER Enterprise Edition SP2 64bit Version. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-21 : 15:35:51
|
| "Management will not approve the slow down in performance while defragmentation happens"Well in that case one presumes they are happy that the user queries run slow, and annoy the users. Weird!I ran a full Reindex of ALL tables ALL indexes on a 10GB database today. 5 minutes it took ...We had the database down because of disaster recovery on the Web server, normally we run Index Defrag only, and I thought I would take the opportunity to do an Index Rebuild instead of Index Defrag.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-21 : 16:53:22
|
| And you can rebuild index on one table at a time during off hours. |
|
|
|
AnAgSh
Starting Member
18 Posts |
Posted - 2007-10-21 : 23:15:46
|
| We don't have any off hours. So I am going to pick up the indexes in chunks and defragment them. It will be an automated process. For example, defragment the indexes that are fragmented above 95% on the 1st of the month, 90 to 95 on the 2nd, 85 to 90 on the third and so forth. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-22 : 04:05:46
|
| So what if a file that you defrag on the 1st is already above the 95% threshold on the second? That's going to get worse and worse for a month isn't it?We just defragment anything above the threshold we have set every night. Some nights there is nothing to do, but you could time-limit the process if you are worried that it might get abused one night - e.g. after lots of data import, or deletes. Index defrag has very small impact on server performance (but it does impact on TLogs, we increase TLog backup to every 2 minutes during the Defrag and Update Stats process)Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-22 : 12:23:44
|
quote:
Originally posted by AnAgSh
while defragmentation happens.
You'll be pleasantly surprised how fast it is compared to 2000 reindex. With Enterprise Edition, you'll also be able to do it online. I'd suggest restoring your database to a test server and seeing how long it'll take to do the defragment.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
ColdFusion
Starting Member
3 Posts |
Posted - 2007-11-06 : 08:31:37
|
quote:
Originally posted by AnAgSh
For example, defragment the indexes that are fragmented above 95%
wow!  I defrag any indexes which fragmented above 20%... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-06 : 22:24:01
|
| You got performance penalty. |
|
|
|
AnAgSh
Starting Member
18 Posts |
Posted - 2007-11-08 : 02:30:20
|
| Yes, the indexes are very badly fragmented. But we are very conservative and don't want to create any kind of issues by running defragmentation routine. The routine has been running in production server for 3 weeks now. To reduce the time it takes to do the defragmentation and avoid blocking, i am only doing 10 indexes per day. Still, we are getting "Timeout Expired" error messages from our online websites. We have very busy websites. To avoid the logs getting filled up, I am running the TxLog backup every 5 mins. instead of our normal 15min. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-08 : 13:59:40
|
Sounds nuts to me ...Your site is timing out, but you don't want to rebuild more than 10 indexes a day ??You can DEFRAG rather than REINDEX and that will have no impact on online users (it will increase logs, and it will use CPU, but it will run at low priority). You can abort that and any work-done will be retained (i.e. NOT rolled back).You just need to get on and do it ...Do it manually, index-by-index, and watch the Tlog growth, and any user-slowdown problems (I'm not expecting any, but it would be prudent to be prepared of course )Abort the process if it is causing any problems.Use DBCC SHOWCONTIG to work out the most fragmented indexes, and start with those. Or start with the tables that you know are involved in timeouts.Or get a consultant in - if your website is timing out, and its making money / annoying customers, then that's probably your best bet. Coming in here once a fortnight to read forum advice won;t be a good substitute for a good consultant!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-08 : 14:07:30
|
| AnAgSh,What edition of SQL Server 2005 are you running?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-08 : 14:15:40
|
| Tara: Did I read somewhere that Index Rebuild can be prep'd on a database copy, and then "applied" for speed? Or maybe it was something to do with DBCC CHECKDB.Darn'd old age ... but there might be something in an Enterprise version that would help "farm-out" some of the work.Just doing a test-run on another server to see how long the rebuild takes, and how much it jacks-up the Tlog filesize would be useful in having more confidence to run it on the Live system.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-08 : 14:21:01
|
| If you run it on a database copy, then those changes will only get applied to the copy. So the database copy is only for DBCC CHECKDB since it doesn't make modifications that you need.Enterprise Edition allows for online reindexing, which is why I'm wondering what edition he is using. But SQL Server 2005 reindexing is surprisingly fast. I am able to run the rebuild (dbreindex) every night and don't even bother with reorganize (indexdefrag). I was unable to do this in SQL Server 2000.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-08 : 14:24:54
|
| I should mention that what can be run on a database copy/snapshot for a defrag is the query against the DMV that checks for which indexes are fragmented. There is information in my blog about this. The actual ALTER INDEX command needs to run against the actual database and not a copy.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-08 : 15:38:34
|
| "I should mention that what can be run on a database copy/snapshot for a defrag is the query against the DMV that checks for which indexes are fragmented."That's the one I was thinking of!! Thanks. |
|
|
|
|
Rebuild/Reorganize Schedule