SQL Statistics on multiple columns | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Statistics on multiple columns

Has anyone a good description or rule of thumb how or when to create an additional statistic on multiple columns. SQL Server already generates statistics for each field (_WA….). The Index Tuning Wizard suggests new indexes and also new statistics, sometimes on multiple columns. But I have no ideas where he finds out such combinations… Does there exist a documentation which explains how the query optimizer works? Thanks,
Patrick Simons, MCP
May search under INSIDE SQL Server book by Kalen Delaney.
(Meanwhile I will update the post) _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Creating statistics manually allows you to create statistics that contain multiple column densities (average number of duplicates for the combination of columns). For example, a query contains the clause: WHERE a = 7 and b = 9 Creating manual statistics on both columns together (a, b) can allow SQL Server to make a better estimate for the query because the statistics also contain the average number of distinct values for the combination of columns a and b. And I guess you might know how to create stats manually. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
My experience:
Statisctics find by Index Tuning are usefull.
After Index Tunning I only apply statistics (even if there is index suggestion too) and run Index Tunning again, sometimes there is no longer index suggestion so statistics was what SQL need. After any statistics via Index Tuning, I check for hyphotetical index and delete it. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Thanks for your replies. But there’s no right answer. Assuming that I don’t want to use the Index Tuning Wizard, should I create a statistic for all fields in the WHERE-clause … what about AND and OR expressions? I really need some rule of thumb. Patrick Simons, MCP
For a reference review information from thishttp://www.sql-server-performance.com/statistics.asp link. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I got a tip and read the paper (Automating Statistics Management for Query Optimizers ftp://ftp.research.microsoft.com/users/AutoAdmin/stats.pdf). Very **very** complex! Has anyone already read it? Opinions? Patrick Simons, MCP
Thanks, nice article … require some time to understand the content. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Yes, and good Math too. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
]]>