UPDATE STATISTICS undocumented options

re: UPDATE STATISTICS undocumented options

Wesley Backelant — Tue, 25 Jul 2006 12:00:33 GMT

These kind of posts are exactly why I love blogs. This could save us hours of data generation effort. Most of the DBA's here tend to 'forget' testing with a realistic amount of data.

DTA (ITW) also allows you to fool SQL Server into having more rows, is this based on the same principle?

re: UPDATE STATISTICS undocumented options

QueryOptTeam — Wed, 26 Jul 2006 07:30:45 GMT

Hi Wesley - thank you for the interest and feedback.

I believe that DTA uses these same interfaces, but am not 100% sure.

About testing with realistic amounts of data: i want to be perfectly clear about the fact that UPDATE STATISTICS WITH ROWCOUNT and PAGECOUNT will only fool the Optimizer into believing that the table is actually bigger (or smaller for that matter) than in reality. However, the content of the actual tables and indexes will remain perfectly intact. So while it is true that these commands will let you see what kind of query plans the Optimizer would generate against larger amounts of data, actually running these plans would not provide particularly interesting information. All query plans will complete extremely quickly when run against empty or very small tables.

What you can do, however, is to look at the progression of the estimated query cost based on the size of the tables. If the cost grows significantly with the size – i.e. the query performances do not seem to scale with the amount of data - it might be a hint that maybe some tuning is required.

Stefano

re: UPDATE STATISTICS undocumented options

Wesley Backelant — Wed, 26 Jul 2006 09:29:45 GMT

Thanks for the extra explanation.

I do understand that nothing can compete with realistic data. But I'd rather see people try some first optimizations with artificial statistics than not optimizing at all because they have no time to generate test data, especially since I'm typically the one who has to solve it once it goes to production :-)

I still love the black art of query tuning though ;-)

Wesley

re: UPDATE STATISTICS undocumented options

mz1313 — Mon, 31 Jul 2006 04:24:36 GMT

Thank you for this post! It came exectly at time, while I was breaking my head trying to simulate behavior of a table with 70 Gb of data without actually loading it.
My question is - is there any way to fool Full-Text index? When I created Full-Text index on my table and executed query with CONTAINS(...), the "Estimated Row Count" in the execution plan was again 1 instead of 3M estimated row count in the query without CONTAINS(...).

re: UPDATE STATISTICS undocumented options

QueryOptTeam — Mon, 31 Jul 2006 21:02:10 GMT

Unfortunately, at the moment there is no way to change the statistics for internal hidden tables like those used for Full Text Search. This is a limitation we should definitely attempt to remove in some future release.

Thanks you for the great feedback!
Stefano

dosql :: Used update statistics on DEV env to get the PRD execution plan

dosql :: Used update statistics on DEV env to get the PRD execution plan — Fri, 01 Dec 2006 00:45:15 GMT

PingBack from http://www.dosql.com/blog/?p=87

update statistics undocumented

roy ashbrook — Fri, 09 Mar 2007 16:59:04 GMT

these are undocumented for a reason as they will affect performance and you will break your sql production

How to create fake statistics for testing query plans

SQL Server Storage Engine — Tue, 13 Mar 2007 04:49:53 GMT

This is very cool - an undocumented option to UPDATE STATISTICS that allows you to fool the server into

Online Indexing operations enhancement in SQL Server 2005

SQL Server tools — Sat, 17 Mar 2007 21:35:18 GMT

In SQL Server designing efficient indexes is paramount to achieving good database and application performance.

Tips Tricks and Advice from the SQL Server Query Optimization Team | Quick Diets

Tips Tricks and Advice from the SQL Server Query Optimization Team | Quick Diets — Wed, 10 Jun 2009 06:13:19 GMT

PingBack from http://quickdietsite.info/story.php?id=4048

Tips Tricks and Advice from the SQL Server Query Optimization Team | debt settlement program

Mon, 15 Jun 2009 20:02:22 GMT

PingBack from http://edebtsettlementprogram.info/story.php?id=24748

Tips Tricks and Advice from the SQL Server Query Optimization Team | storage bench

Fri, 19 Jun 2009 11:03:26 GMT

PingBack from http://thestoragebench.info/story.php?id=9514