THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? Statistics vs Indexes

 

What is the difference between statistics and indexes? Do auto created statistics indicate that you should build an index?

I will be posting answers to some of the interesting questions I get from my students and my readers (these are not two disjoint sets, of course) on the new SQLCommunity site.

Check out this the answer to this question at:

http://www.sqlcommunity.com/Articles/SQLServerArticlesCategorized/tabid/153/language/en-US/Default.aspx

The article has some geeky stuff in it too. For example, it explains how to interpret the strangely named statistics, such as _WA_Sys_00000010_2EDAF651.

 

Have fun!

~Kalen

Published Monday, February 04, 2008 12:44 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

Ah, the _WA indexes :)

I once had a client insisting that they should be deleted and were wasting space...and he was the senior DBA for that company!

Didn't know about the sqlcommunity website, is that new? Would they accept longer, non-blog like articles from the likes of a lowly me?

February 5, 2008 9:46 AM
 

Kalen Delaney said:

Hi James

Yes, I continue to see lots of confusions about the automatically built stats, and column stats in general. I have a script I run in class that shows a query running much worse when auto create stats is turned off.,

I blogged about SQLCommunity just a few weeks ago:

http://sqlblog.com/blogs/kalen_delaney/archive/2008/01/19/did-you-know-a-terrific-new-community-website.aspx

We'd love to have your contributions!

Thanks

Kalen

February 5, 2008 11:18 AM
 

em81 said:

Kalen,

When the statistics are generated on a index doesn't it choose a sample of 200 rows initailly or is it a different number?  What ever sample number it uses how does it come about choosing those rows?

February 5, 2008 2:07 PM
 

Kalen Delaney said:

Hi em81

When statistics are being generated, SQL Server will select up to 200 values. How it chooses them is part of the internal algorthm which MS does not make public.

You can read this Whitepaper for more details:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx    

February 6, 2008 6:34 PM
 

Jonathan McMullan said:

Hi Kalen,

I'd really like to read this article, What is the difference between statistics and indexes? Do auto created statistics indicate that you should build an index?, but the link to SQL Community doesn't work and searching for it doesn't bring up any results :o(

Do you still have a copy of it handy?

Thanks in advance,

Jonny

April 19, 2013 7:15 AM
 

Kalen Delaney said:

Hi Jonny

I don't have it on my current computer, it might be on an old backup somewhere. But I am out of the office for 5 weeks, so I won't be able to look around until I get back.

Thanks

Kalen

April 21, 2013 5:11 PM
 

Jonathan McMullan said:

Hi Kalen,

Thanks for coming back to me so quickly! No worries, if it's gonna be a hassle to find it don't stress it! I'll just have to Google a bit harder ;o)

Kind regards,

Jonny

April 22, 2013 3:25 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement