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
Patrick SIMONS, MCP
Patrick SIMONS, MCP
-
quote:
Does there exist a documentation which explains how the query optimizer works?Can you spare some $40 for 'Inside SQL Server'?
This is the technical reference on SQL Server.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
are there something in english? couldn't read it..........
-
Do you mean my homepages?
Sorry, I am from Germany, but the SQL Server FAQ on InsideSQL.de should work also in english
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Probably more than you want to know, but there's a paper (Automating Statistics Management for Query Optimizers) on this from the developers of the Index Tuning Wizard.
--Jonathan
--Jonathan -
Thanks Jonathan,
I read the paper (Automating Statistics Management for Query Optimizers ftp://ftp.research.microsoft.com/users/AutoAdmin/stats.pdf). Very **very** complex!
If I understand well, there's no simple rule of thumb. You have to try and to test (heuristic approach) and so on ...
Am I right?
Patrick SIMONS, MCP
Patrick SIMONS, MCP
-
quote:
Thanks Jonathan,I read the paper (Automating Statistics Management for Query Optimizers ftp://ftp.research.microsoft.com/users/AutoAdmin/stats.pdf). Very **very** complex!
If I understand well, there's no simple rule of thumb. You have to try and to test (heuristic approach) and so on ...
Am I right?
Patrick SIMONS, MCP
Yes, that paper is quite technical...
You can guess at some cases where creating statistics on multiple columns (which auto-stats does not do) might help; e.g. with multiple equalities in the join predicate, these heuristics could help the query optimizer better decide which table is probed in a hash join.
--Jonathan
--Jonathan -
Jonathan,
the paper is about SQL7. I thought the query optimizer hase greatly improved between these versions. Is it still valid for SQL2k?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Jonathan,the paper is about SQL7. I thought the query optimizer hase greatly improved between these versions. Is it still valid for SQL2k?
Frank
They "implemented and experimentally
evaluated [the] approach on Microsoft SQL Server 7.0." The paper was presented more than three years ago. Its authors wrote the MSDN White paper on the SQL Server 2000 Index Tuning Wizard. Draw your own conclusions.
--Jonathan
--Jonathan -
quote:
They "implemented and experimentallyevaluated [the] approach on Microsoft SQL Server 7.0." The paper was presented more than three years ago. Its authors wrote the MSDN White paper on the SQL Server 2000 Index Tuning Wizard. Draw your own conclusions.
hey, have you ever told your clients that you are about to "implement and experimentally evaluate [an] approach" on their systems and charge money for it?
Where did you find this original paper?
Are there more of these preciousnesses?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply