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 |
|
beav1013
Starting Member
3 Posts |
 Posted - 2008-03-13 : 13:36:08
|
| Hello. I'm kind of new to the admin arena and my company doesn't have a dedicated dba, so I've kind of taken it upon myself to clean up our indexes. I've gone through help and ran DBCC SHOWCONTIG on the tables and indexes to see which indexes need to be defragged. My question is more geared towards impact on the system: when you run DBCC DBREINDEX, does that get captured in the transaction logs? Our main customer order table has roughly 8.1 million records in it, and the logical fragmentation of the table's clustered index is 23%. There are 16 non-clustered indexes also on this table, with log frags ranging from 1 - 43%. This table is our core table in the database, so this one will have the most impact on the system when we re-index. We're doing full backups on Sundays, Differentials every night, and backing up the transaction logs every 15 minutes. Our programming team is mainly concerned with the impact this will have on the trans logs. Any tips/advice you could offer would be greatly appreciated. Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-13 : 13:41:27
|
| You should run in off hour time like sunday morning so that you don't affect production hours |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-13 : 23:18:14
|
| Table with 8.1 m rows is not that big. I have a table with 100gb indexes, uses 50gb log to rebuild indexes. You can reduce log usage by sorting in tempdb. |
|
|
|
beav1013
Starting Member
3 Posts |
Posted - 2008-03-17 : 10:27:04
|
| Thanks for the help. I'm reading a white paper on the process that says a step you should take is to run a trace on the worst performing stored procs that run during heavy production times. I can do that, that but I'm not well versed enough to know how to dump the results from the trace into a table to be queried against. No one else in my office knows how to do that either. Does anyone know how to do that? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-17 : 10:45:02
|
| This will helphttp://technet.microsoft.com/en-us/library/ms191276.aspx |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-17 : 11:14:30
|
| http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1159433,00.html# |
|
|
|
beav1013
Starting Member
3 Posts |
Posted - 2008-03-17 : 12:07:58
|
| Beautiful. Thanks. |
|
|
|
|
|
|
|
|
Index Cleanup