October 11, 2013 at 11:20 am
Guys,
One of the other project's guys in my company asked me to improve the performance of their databases. I saw that the index rebuilding was not changing the fragmentation. It remained same even for the larger tables. Any solution for this ??? I am using SQL 2008 R2.
October 11, 2013 at 1:14 pm
sandeep singh-337370 (10/11/2013)
Guys,One of the other project's guys in my company asked me to improve the performance of their databases.
Keeping your indexes un-fragmented will help performance. That said, I would suggest that you don't get too caught up on index fragmentation. There are dozens of things that you can check & change to improve performance. Some random thoughts (in no particular order)... Are the log files and data files on their own hard drives? Are there missing indexes? Are there too many indexes? Are you experiencing excessive locking? blocking? deadlocks? Using (max) data types when a smaller data type would do? Have you partitioned the DB correctly? Are we indexing the right stuff?
These examples of things that will also impact performance.
I saw that the index rebuilding was not changing the fragmentation. It remained same even for the larger tables. Any solution for this ??? I am using SQL 2008 R2.
How fragmented is it? If it's still 97% fragmented after a rebuild or re-org then that would be a problem; if it's still 1% fragmented, then not a big deal...
I have experienced this when my index fill factor was poorly configured. Usually the default (0) is fine but there are times when it isn't. Here's a great article[/url] by Kendra Little on the subject. This is a good article about index fragmentation by Brent Ozar: Index Fragmentation Findings: Part 1, The Basics[/url]
-- Itzik Ben-Gan 2001
October 11, 2013 at 3:27 pm
sandeep singh-337370 (10/11/2013)
It remained same even for the larger tables.
What do you condsider to be a "larger table"?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2013 at 4:02 pm
Most likely either the table is a heap or it's too small to bother about defragmenting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2013 at 11:12 am
Alan.B (10/11/2013)
sandeep singh-337370 (10/11/2013)
Guys,One of the other project's guys in my company asked me to improve the performance of their databases.
Keeping your indexes un-fragmented will help performance. That said, I would suggest that you don't get too caught up on index fragmentation. There are dozens of things that you can check & change to improve performance. Some random thoughts (in no particular order)... Are the log files and data files on their own hard drives? Are there missing indexes? Are there too many indexes? Are you experiencing excessive locking? blocking? deadlocks? Using (max) data types when a smaller data type would do? Have you partitioned the DB correctly? Are we indexing the right stuff?
These examples of things that will also impact performance.
I saw that the index rebuilding was not changing the fragmentation. It remained same even for the larger tables. Any solution for this ??? I am using SQL 2008 R2.
How fragmented is it? If it's still 97% fragmented after a rebuild or re-org then that would be a problem; if it's still 1% fragmented, then not a big deal...
I have experienced this when my index fill factor was poorly configured. Usually the default (0) is fine but there are times when it isn't. Here's a great article[/url] by Kendra Little on the subject. This is a good article about index fragmentation by Brent Ozar: Index Fragmentation Findings: Part 1, The Basics[/url]
Another problem may be based on a misperception of what REORGANIZE does on an index. If you use the DETAILED mode of sys.dm_db_index_physical_stats to determine what the fragmentation is, it lists the fragmentation of both the B-Tree Levels and the Leaf Level of the index (which is the actual data for a clustered index, of course). When you use REORGANIZE, it only defrags the Leaf Level and doesn't actually defrag the B-Tree. If you run sys.dm_db_index_physical_stats again after the REORGANIZE, the previously noted fragmentation in the B-Tree will remain.
In order to defrag the B-Tree, you have to do a rebuild.
Another thing that a log of people forget about is that, although the fragmentation of the B-TREE may qualify as fragmentation of a "small" table and might not affect performance that much, it can mess with fragmentation levels at the Leaf Level. I haven't done a deep dive to determine the exact mechanism that causes the problem but we had a large table that had 11% fragmentation at Level 1 (The B-Tree level nearest the Leaf Level) and, every week, the fragmetation of the indexes would rise to the point where all of the indexes would hit the REORGANIZE threshold and be reorganized. This caused the LDF file to blow out to a whopping 32GB (normal sysops never get over about 6GB) because, every once in a while, the auto-reindex job (3rd party POS that someone got off the internet that I'm in the process of rewriting) would cross paths with the backup job. Since determinination of whether to rebuild or reorg the index was being done only at the leaf level, the system never decided to do a rebuild of the indexes to defrag the B-TREE and the problem occurred every week.
I manually (by script, actually) did a rebuild of all the indexes on that large table about 6 weeks ago and monitored the LDF file size and the B-Tree fragmentation of the indexes. Everything ran smooth for the re-index job after that until B-Tree fragmentation once again hit 11% (about a month after th total rebuilds) and the log file blew out to 32GB again.
My point here is that just reorganizing the indexes on a table based on the traditional 10% rule can sometimes be misleading and sometimes not be aggressive enough especially on large tables. Although it may not actually be affecting application or batch performance, you may sometimes have to rebuild the indexes to cause defragmentation of the B-Tree because it apparently can and does somehow affect what SQL Server caclulates.
[EDIT] Almost forgot... the B-Tree can also have a FILL FACTOR. Lookup "PAD_INDEX" under "ALTER INDEX" in Books Online. It can be usefull especially on those indexes that take a lot of insert/updates and the order of insertion doesn't match the column order of the index keys.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2013 at 4:15 am
Alan.B (10/11/2013)
sandeep singh-337370 (10/11/2013)
Guys,One of the other project's guys in my company asked me to improve the performance of their databases.
Keeping your indexes un-fragmented will help performance. That said, I would suggest that you don't get too caught up on index fragmentation. There are dozens of things that you can check & change to improve performance. Some random thoughts (in no particular order)... Are the log files and data files on their own hard drives? Are there missing indexes? Are there too many indexes? Are you experiencing excessive locking? blocking? deadlocks? Using (max) data types when a smaller data type would do? Have you partitioned the DB correctly? Are we indexing the right stuff?
These examples of things that will also impact performance.
I saw that the index rebuilding was not changing the fragmentation. It remained same even for the larger tables. Any solution for this ??? I am using SQL 2008 R2.
How fragmented is it? If it's still 97% fragmented after a rebuild or re-org then that would be a problem; if it's still 1% fragmented, then not a big deal...
I have experienced this when my index fill factor was poorly configured. Usually the default (0) is fine but there are times when it isn't. Here's a great article[/url] by Kendra Little on the subject. This is a good article about index fragmentation by Brent Ozar: Index Fragmentation Findings: Part 1, The Basics[/url]
At best and as an addition to the answer provided here, check the amount of pages against the level of fragmentation. 25% fragmentation when you only have 4 pages is nothing, but when you have the same level of fragmentation against 10000 pages then you need to think about performance issues and resolution.
October 14, 2013 at 7:39 am
Check the page count on the indexes that are not defragmenting. SQL Server will not defrag anything 8 pages or less.
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
October 16, 2013 at 8:35 am
I would add Alan.B tips, check unused indexes and statistics update is on
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply