Changing the table pagecount

When SQLserver needs to retrieve data from a table, it checks if it should use an index or a tablescan. The threshold for this choise is around 30% of the number of 8k pages of the table, not the number of rows.
The statistics concerning the number of pages and rows can be changed with the little known clause of the update statistics command:

UPDATE STATISTICS WITH pagecount = 1000000

I recently used this in a case where a vendor application had a bad SQL statement in it that didn’t use the index. The table was deemed too small for index usage by SQLservers optimizer, but the subsequent tablescan led to extensive blocking in a splitsecond environment. Being a vendor application I couldn’t change the SQL statements and after examining some alternatives a planguide was considered the appropriate way. So i changed the table statistics to have a large pagecount and the optimizer was convinced that an index was best now. I captured the new executionplan with the indexusage into a planguide (the howto of that can be found here )

To reset the pagenumber use the command

DBCC UPDATEUSAGE ( 0, <tablename>)

This command can help you to find the threshold of plan changes ( called ‘tipping point’ ) See Kimberly Tripps post for an intro:

Leave a Reply

Your email address will not be published.