| Author |
Topic |
|
Rauken
Posting Yak Master
108 Posts |
 Posted - 2008-04-01 : 04:28:48
|
| Hi!I'm not a dba at all, just a simple developer but I have got the assignment to help out with performance problems on a db. I noticed that almost all indexes are fragmented 75-99%. Can I rebuild them when the database is in use? Can I reorganize indexes when the db is used?/MagnusJesus saves. But Gretzky slaps in the rebound. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-01 : 05:13:35
|
| What version of SQL Server are you running? You can rebuild them in ONLINE mode using ALTER INDEX if you are running Enterprise version.Otherwise, do this outside of normal working hours (whatever they may be for your database). |
 |
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2008-04-01 : 15:58:29
|
| Yes it's Enterprise Edition. I've setup a maintenance plan to run at 3 a.m. every Saturday.Jesus saves. But Gretzky slaps in the rebound. |
|
|
|
Ola Hallengren
Starting Member
33 Posts |
Posted - 2008-04-02 : 04:54:19
|
You may run into a problem with the Maintenance Plan failing if you're rebuilding with the ONLINE option and if your tables contains LOB columns (text, image, xml, varchar(max), nvarchar(max), varbinary(max)).Another thing is that with the Maintenance Plans you will rebuild all indexes regardless of the fragmentation.I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.htmlOla Hallengrenhttp://ola.hallengren.comquote:
Originally posted by Rauken
Yes it's Enterprise Edition. I've setup a maintenance plan to run at 3 a.m. every Saturday.Jesus saves. But Gretzky slaps in the rebound.
|
|
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2008-04-03 : 11:48:30
|
| Thanks for your help. After rebuilding indexes etc performance is really good. There is no LOB columns in that database.Jesus saves. But Gretzky slaps in the rebound. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-16 : 03:24:42
|
| Tara, your comment about only rebuilding at 75% (from Microsoft engineers) is interesting , would you be able to elaborate slightly more on this comment, what are the reasons?Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-16 : 04:44:57
|
| We Rebuild if the tables are small, and Defrag above that.We don't do either unless the Fragmentation is above our threshold (I've forgotten what that is unless looking it up) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-16 : 13:01:47
|
quote:
Originally posted by jackv
Tara, your comment about only rebuilding at 75% (from Microsoft engineers) is interesting , would you be able to elaborate slightly more on this comment, what are the reasons?
I can't expand on it too much, since it's coming from Microsoft engineers that I've spoken to on the phone and email. Both engineers suggested that fragmentation doesn't impact performance until around 75% fragmentation. Anything below that shouldn't impact the system. I assume they did testing to come up with this information.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
When to rebuild indexes