patternsqlMinor
continuously extending a table in SQL Server
Viewed 0 times
sqlcontinuouslyservertableextending
Problem
We have a large data warehouse database where we continuously get new rows inserted in 5 different tables, at the left-hand side of the b-tree (=at end of the table)
This cause a lot of page splits.
Paul Randall calls these "good" page splits as in they do not cost much when inserting the data.
But when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages
just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments.
Not very "good" page splits anymore!
The only way I know we can bring these extends together (in order to get better read-ahead), is to REBUILD the table (or table partition).
Question 1)
Are there other options?
We have one table that in addition to the new inserts get a lot of updates to the newer rows. I.E we get 6 rows every hour, and once a week all rows for the entire week are recalculated (=updated). Fillfactor only works for index REBUILDs
Question 2)
Is there any way I can get SQL Server to use a FillFactor look-alike when inserting rows on the left-most page in the B-Tree?
This cause a lot of page splits.
Paul Randall calls these "good" page splits as in they do not cost much when inserting the data.
But when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages
just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments.
Not very "good" page splits anymore!
The only way I know we can bring these extends together (in order to get better read-ahead), is to REBUILD the table (or table partition).
Question 1)
Are there other options?
We have one table that in addition to the new inserts get a lot of updates to the newer rows. I.E we get 6 rows every hour, and once a week all rows for the entire week are recalculated (=updated). Fillfactor only works for index REBUILDs
Question 2)
Is there any way I can get SQL Server to use a FillFactor look-alike when inserting rows on the left-most page in the B-Tree?
Solution
when we query sys.dm_db_index_physical_stats we see an avg_fragment_size_in_pages just under 8, namely the 8 8kB pages that can fit into one 64kB extend. A table with 50000 pages has 6300 fragments. Not very "good" page splits anymore!
If your server has a RAID disk system, or is using SAN-based storage, or has SSD storage, index fragmentation will have no appreciable effect, either way. If you are storing the database on a single hard drive, then table fragmentation will have an effect that is most noticeable when scanning an index sequentially that spans multiple 64KB extents. If your system is not querying large portions of the given table(s) sequentially, index fragmentation will have a low impact.
You are much more likely to see gains in performance by ensuring you have the most efficient indexes for the actual work load present on the system. Oh, and for less than 1/5th the cost of the typical SQL Server Enterprise license you can have the highest performing PCI-attached SSD cards commonly available that can pump hundreds of thousands of I/Os per second.
Look at http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ for more information about the index fragmentation issue.
If your server has a RAID disk system, or is using SAN-based storage, or has SSD storage, index fragmentation will have no appreciable effect, either way. If you are storing the database on a single hard drive, then table fragmentation will have an effect that is most noticeable when scanning an index sequentially that spans multiple 64KB extents. If your system is not querying large portions of the given table(s) sequentially, index fragmentation will have a low impact.
You are much more likely to see gains in performance by ensuring you have the most efficient indexes for the actual work load present on the system. Oh, and for less than 1/5th the cost of the typical SQL Server Enterprise license you can have the highest performing PCI-attached SSD cards commonly available that can pump hundreds of thousands of I/Os per second.
Look at http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ for more information about the index fragmentation issue.
Context
StackExchange Database Administrators Q#51911, answer score: 2
Revisions (0)
No revisions yet.