| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-03-11 : 13:48:58
|
| I ran a TSQL query on sys.dm_db_index_physical_stats.We have one table with clustered index:Rows: approx. 5,000,000Fragment count: 794945Page count: 794955Fragmentation: 99.2%Is this really bad? How could an index become that fragmented? Could query performance be suffering because of this? Significantly?(Just want to note that at least this is on the Development Server.) |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-03-11 : 14:10:18
|
| 1) Yes, this is bad.2) Indexes can get fragmented from inserts, updates and deletions.3) Fragmentation causes perfromance issues4) Significantly so...We run a job that looks for and corrects fragmentation by using the ALTER INDEX command to either REORGANIZE or REBUILD the index. We run this daily but your needs might vary. Since this is a development server, there should be some quiet time (nightly? weekends?) when the job can run.=======================================There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980) |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-03-11 : 14:24:31
|
| Thanks. I am about to do the Alter Index - Rebuild. We have a job that does this but it has been erroring out (for quite some time now).We definitely have 'quiet time' for the job to run. Which makes me wonder what someone would do in situations when there is no down time? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-11 : 14:30:21
|
quote:
Originally posted by denis_the_thief
Which makes me wonder what someone would do in situations when there is no down time?
Indexes can be rebuilt "online" for 24/7 systems. The "offline" method makes a fresh copy of the index, so is probably quicker and somewhat more efficient - but the "online" method works fine too.Suggest you put a procedure in place so that when a maintenance task "has been erroring out for quite some time" that it gets escalated to the point where it gets attention and is fixed!! If maintenance is failing do you also have no backups?? |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-03-11 : 14:39:20
|
quote:
Indexes can be rebuilt "online" for 24/7 systems. The "offline" method makes a fresh copy of the index, so is probably quicker and somewhat more efficient - but the "online" method works fine too.
Thankyou. Can you post an example of the "online" method? I've also seen deadlocks involving Alter Index - Rebuild while the database is operational. Is that avoidable? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-11 : 15:13:25
|
quote:
Originally posted by denis_the_thiefCan you post an example of the "online" method?
See SQL Documentationquote:
I've also seen deadlocks involving Alter Index - Rebuild while the database is operational. Is that avoidable?
Sounds like you are using the "offline" method - I assume it has to lock the whole table in order to perform its rebuild. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-03-11 : 16:11:09
|
| You can use the WITH (ONLINE = ON) option to perform this but it does impose a load on the system (No surprise). There are limitations, also. I believe that you can't use the online mode if there are blob columns in the table (text, varchar(max), etc.). There may be other limitations but, as Kristen rightly pointed out, its in the documentation.=======================================There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980) |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-03-17 : 15:31:40
|
| Update:Thanks for helping me understand this some.After the index rebuilds, query was way faster when comparing to clean buffer caches. Time time went down, physical reads went way down and read-ahead reads went way up.So if I understand, with the fragmented indexes, SQL Server must go all around the Disk to find the pages it needs. With unfragmented indexes, the pages are contiguous, allowing the Read-Ahead mechanism which loads in contiguous chuncks which is much faster? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 15:42:01
|
| Yup, that's the top-and-bottom of it.We rebuild (well, "defrag" mostly) indexes every night if they are fragmented - specifically we do not wait for a "weekend job" to do that. Seems to me that if an index goes out of shape it should be sorted out as soon as possible, otherwise the users are disadvantaged.It does depend, somewhat, on how much slack time you have each day - for sites that are used heavily, and evenly, by users all around the world this can be a challenge, but for sites that have some quieter time (or even "very quiet time") during the night it is worth doing maintenance on all tables that need it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-17 : 16:38:06
|
| You may be interested in my custom code which intelligently runs ALTER INDEX with the best settings. The maintenance plan that does this type of thing does not do a good job at the best settings, hence the reason why so many people's jobs error. Here's my custom code: http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspxIt supports SQL Server 2005 and 2008. It will run ONLINE=ON wherever possible. You can choose to REBUILD or REORGANIZE by passing it in as an input parameter. I only rebuild starting at 50% fragmentation.Using the ONLINE=ON option doesn't mean that users won't be impacted when the job runs. It just means that the index isn't offline and can be used in execution plans. Defragmenting indexes is a very heavy job when your database is big and active, so make sure it is done during the slowest peaks and always with the best settings. The offline method does not lock the table. It just means the index is offline and can't be used for queries.We have a very large database that has no quiet time. We are unable to defragment indexes on that database as it pretty much seizes the application. There is heavy fragmentation in the database, however no performance degradation is occurring. We heard from a SQL Server expert at Microsoft that fragmentation doesn't hinder performance all that much, and that he's never even seen a performance issue where defragmenting the indexes corrected the problem.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." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 18:47:39
|
| "It just means the index is offline and can't be used for queries."I didn't know that, thanks Tara. Makes sense now I read it!Does this apply to Clustered index too? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 19:00:01
|
Thanks Tara. I was thinking that a Rebuild of a Clustered Index is a pretty big deal to do "offline" - if the data is being shifted around to optimise it then even a Table Scan might be iffy ... but anything can be programmed around of course  , so maybe that is exactly what MS have done. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-17 : 19:04:30
|
| I think it is a big deal to do it offline, which is a huge reason to use Enterprise Edition. I would assume that having the clustered index offline would have a huge performance impact on all queries that access the table due to the clustered index being used in many queries where a covering index doesn't exist. The clustered index would be used for lookups where indexes don't cover the query.I am only making assumptions though as we ALWAYS use Enterprise Edition and almost never have LOB columns. I know we have some tables with them, but either that database is not mission critical with huge performance requirements or the table is small.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." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 19:10:08
|
Off topic, but we have tended to put LOBs in a 1:1 "parallel" table, and I'm not worried about fragmentation on those - the only index is the clustered PK.I might put a bit more effort into putting all our LOB columnss in 1:1 tables in future - we don't have many, and they tend to be TEXT [as was, VARCHAR(MAX) now] columns called "Notes" or "Description" - not the highlight of my Query Optimisation concerns!  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-17 : 19:15:05
|
| The "parallel" table sounds like a good idea. You can rebuild the index on the main table ONLINE, and OFFLINE the "parallel" table. Hopefully the main table is used most often, so the "parallel" table design would have a good performance improvement during index rebuilds. If fragmentation isn't a concern on the "parallel" table, then skip it for the index rebuilds. Brilliant! I'll keep this idea in mind if I'm ever asked to help out with a database design in the future. Unfortunately, the DBAs have very little say about the database design where I work. The developers use tools to build their model, which is just frustrating when the DBA is finally involved in a performance issue.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." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 19:18:02
|
| Works for me - I get called in to sort it out at consultancy rates! |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-03-24 : 09:10:07
|
| Thankyou. That is a good idea about re-building only indexes that need them. Last night re-building everything took 6 hours. Then again the CheckDB all Databases took 2 hours. |
|
|
|
|
Fragmentation question