[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

Create Statistics - how to decide what to create

I raised another thread yesterday (still ongoing) where angell gave me the idea of using filtered indexing in sql 2008. Thats going well.

I would like to get some understanding of how you decide which statistics to create. Here's some thoughts on possible strategies:

1. Just blindly run database engine tuning advisor and create the stats it suggests. When I previously followed this approach with indexes I ended up with mostly fast queries but sql was sometimes using the wrong indexes and the index size was abou 12GB. Now my index size is about 3.5GB as I created all the indexes manually - now all my queries are subsecond but I distrust database engine tuning advisor for those reasons.


2. Create statistics covering every column mentioned in each index - so the stats become a copy of the indexes effectively

3. Create statistics covering every column mentioned in each query - so the stats list off the columns in each sql statement in your "workfile"

4. Some other strategy not listed above ?

I just need some understanding of how you guys approach the problem of which stats to create. How do you decide ??

thanks
Paul

0
plq
Asked:
plq
  • 3
  • 3
  • 2
2 Solutions
 
AnujCommented:
The better option is, Let SQL Server create your statistics automatically Check this article from Kimberly L.Tripp.

Dont ALWAYS trust the DTA, It may suggest you the duplicate indexes, before creating the indexes check whether suggested index keys already exists in the table.

0
 
lcohanDatabase AnalystCommented:
0
 
lcohanDatabase AnalystCommented:
Besides all the above, indexes needs maintenance(REBUILD is an ON-LINE operation) and stats needs refresh so you can schedule either a
                 exec sp_updatestats;
at db level or
                 update statistics table_name;
at table level issue

http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
plqAuthor Commented:
These indexes are complex, supporting multiple column accesses and very complex query plans.

So would it therefore not be prudent to create statictics to help sql server ensure that it selects the correct index for each query ?

The only stats which sql creates for itself are single column stats - not useful when you have multiple indices where the first columns are identical ?

I appreciate your help but i feel that the above does not completely answer the question.

What I really need is to understand how and when a dba decides to specifically create stats and what the criteria they use are for supporting that decision.

thanks
0
 
AnujCommented:
I have auto create statistics on in all my database, Also i relay on Actual execution plan and used to check the Actual rows returned and the estimated rows returned, If they differ I used to update the statistics or create statistics on the filtered\Join columns. Also most of the time Actual execution plan gives you a warning when a useful statistics were not found.

Preferably you should have statistics on the aggregate columns and group by columns, Join columns and Filtered columns.

Remember Statistics are not used efficiently if you use function on your columns.

0
 
AnujCommented:
Determining When to Create Statistics from msdn will definitely helps you.
0
 
lcohanDatabase AnalystCommented:
I can tell that:

" For most queries, the query optimizer already generates the necessary statistics for a high quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results." - unless you disabled the default AUTOSTATS in SQL (which I hope you didn't) you don't realy need to "create statistics" and I believe you should focus more on the indexes and the refresh instead for both:

indexes (REBUILD is an ON-LINE operation) and
update statistics or spupdatestats.

http://msdn.microsoft.com/en-us/library/ms190397.aspx

This is confirmed from my 12+ years working with SQL alone where I think I had to create 1(one!) statistic myself because the query plan wasn't using the index and I couldn't force an index hint.


Good luck!
0
 
plqAuthor Commented:
thanks for helping. Turns out sql creates multi column stats for columns covered by an index, so in my case with the right indexes in place additional stats were not necessary.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now