December 21, 2010 at 9:32 am
I stumbled across an interesting script at SQLFOOL[/url] for Estimating the number of Rows Per Page. The article mentioned that 'if you have a low number of Rows Per Page compared to a large number of Rows for a table, you might want to think about a redesign of the table.'
What exactly would be considered an acceptable value, or better yet, a value that would make you consider redesigning a table? Would the examples below be considered extremely low in terms of Rows Per page?
Rows Pages Rows per Page
TableA 5282 881 5
TableB 8979 1297 6
TableC 1457 1678 8
December 21, 2010 at 9:44 am
upstart (12/21/2010)
I stumbled across an interesting script at SQLFOOL[/url] for Estimating the number of Rows Per Page. The article mentioned that 'if you have a low number of Rows Per Page compared to a large number of Rows for a table, you might want to think about a redesign of the table.'What exactly would be considered an acceptable value, or better yet, a value that would make you consider redesigning a table? Would the examples below be considered extremely low in terms of Rows Per page?
Rows Pages Rows per Page
TableA 5282 881 5
TableB 8979 1297 6
TableC 1457 1678 8
You need to take into consideration the size of a page is 8K. If you only have 5 rows on an 8K page and you have 5282 rows in the table - you probably have some large data types in that table. You would probably want to consider a redesign. Especially if this database is going to grow very much.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2010 at 12:04 pm
As Jason says, low rows per page really means wide rows. In my old mainframe days we could manage the size of the page, this allowed us to calculate an optimal page size for row length, giving the "best" number of records per page with the least wasted space. Unfortunately in server based databases you can't do this, so the only way to control rows per page is via the table design.
If you have 5 rows on a page, that means 40 rows per extent (since SQL reads extents at a time this is more relevant really). So each logical IO gets you 40 records. If your data usage is such that a single IO gets you your required record most of the time this may not be an issue, but if you regularly do batch processes that process a few thousand rows at a time, then you want to reduce the row size so a logical IO returns more records if possible.
You need to balance this with the increased IOs if you split the table since you may now need to do a second read or write to access the other half of the table, unless there is a way to reduce the row size without splitting off some of the rows. e.g if you have CHAR(255) columns that could be changed to VARCHAR(255).
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply