Kalen Delaney : statistics

Geek City: More statistics info available!

Kalen Delaney — Wed, 10 Apr 2013 01:39:00 GMT

I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives us more info about distribution statistics. It returns info about the last statistics update date (which is also available with a function STATS_DATE())....(read more)

Geek City: Accessing Distribution Statistics

Kalen Delaney — Fri, 18 Jan 2013 20:08:00 GMT

Distribution statistics are one of the most important sources of information that the Query Optimizer uses to determine a good query plan. In this post, I’m not going to tell you everything about distribution statistics. I’m just going to show you a few...(read more)

Geek City: String Statistics

Kalen Delaney — Wed, 13 Feb 2008 22:18:00 GMT

If you’ve ever run DBCC SHOW_STATISTICS, you know you get 3 sections of information back. The first section is basic information about the last time the statistics were updated, the number of rows, the number of steps, etc. The second section is density information for each left-based subset of columns. The third section is the histogram for the first column in the statistics. I won't be going into detail on what any of these things mean (i.e. steps, density, histogram), but you can get lots more information from this whitepaper:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

What you might not know is that you can get each of the three sections independently by adding an option to the DBCC command:

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH STAT_HEADER;

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH DENSITY_VECTOR;

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH HISTOGRAM;

These options are documented as part of the DBCC SHOW_STATISTICS command for SQL Server 2005. These options were actually available in SQL Server 2000, but they just aren’t documented.

In SQL Server 2005, the first section contains a column of output called "String Index", which I like to call "String Statistics". I just wrote an article for www.SQLCommunity.com about what these string statistics do for you.

http://www.sqlcommunity.com/default.aspx?tabid=77&id=178

Have fun!

~Kalen

Did You Know? Statistics vs Indexes

Kalen Delaney — Mon, 04 Feb 2008 19:44:00 GMT

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

Did you know? Cloning is Legal (and Available in SQL Server 2005)

Kalen Delaney — Wed, 21 Nov 2007 18:50:00 GMT

I first heard about the possibility of database cloning way back in version 7.0 and thought it sounded like a great idea. Who wouldn't love to have a bunch of identical little databases running around, of smaller size and easier manageability?

Cloning a database means creating a statistics only copy of it, so that you can examine execution plans for poorly performing queries in a test environment that takes hardly any space at all. A clone database has all the metadata for all your objects, including function and stored procedure definitions. It has all the statistics for all the objects including the histograms and density information returned by DBCC SHOW_STATISTICS. Looking at space usage information shows you only the pages used, but not the pages allocated but unused.

So how can you get a little clone of your own? The original plan was to have a toolbar option to just click and presto, there would be your clone. That never happened. Later, one of the optimizer engineers at Microsoft created a stored procedure, called something like sp_clone_db that took a database name as a parameter, and created a new database with all the metadata and statistics, but none of the data, of the original. That procedure was never included with any version of the product.

Instead, what we have in SQL Server 2005 is the ability to script a database, and include the statistics and histogram information.

In SQL Server Management Studio's Object Explorer, right-click your database name and choose Tasks, and then Generate Scripts....

Choose the name of the database to script, and select the box to "Script all objects in the selected database". Next you'll see a "Choose Script Options" dialog with several dozen options to choose from, mostly indicating whether your want to include certain properties or objects in your database. Most of these you can leave at the default, or deselect the ones you're not interested in. I usually select the option to create the database, so it will include the CREATE DATABASE statement and any necessary filegroups. If you don't do this, and your database has objects created on filegroups other than the primary, your script will need a lot more manual editing.

There are two options that you must change in this "Choose Script Options" dialog. In the General section is the one called "Script Statistics". When you click in the list of options on the right side, you'll see three different options. The default is "Do not script statistics". Choose the option to "Script statistics and histograms." When you make this selection, you'll get a warning like the following:

If you want a clone, you'll have to use this option, so don't let this warning scare you.

In the Table/View Options section (you'll have to scroll down) change "Script Indexes" to True.

Click Ok, and after you click Next to leave this "Choose Script Options" dialog, the "Output Options" dialog opens. (If you forget to check the box for scripting all objects in the database in the very first dialog, you'll get about 10 different dialogs asking you to choose which objects you want to copy, before you get to the "Output Options" dialog. Don't say I didn't warn you.)

I usually just choose to copy to a New Query Window and click Next. I then get a chance to confirm my choices, and then I click Finish. The copy process begins.

WARNING: If you notice in the confirmation list that you forgot something, or want to go back and make any changes, the option to Script Indexes will be set back to false. You'll need to make sure to reset it to True before going forward again.

Once I get the script in the new window, I need to make sure I change all references to the original database name to a new name. (If you are creating the clone on a different server that doesn't have the original database, then you won't have to do this step.) Make sure you check the filenames and make sure they don't duplicate existing names. Execute when ready.

Or, you might want to spend a few minutes examining the script. You'll see UPDATE STATISTICS statements like you've never seen before, looking something like the following. (My clone was created on the AdventureWorks database):

UPDATE STATISTICS [Sales].[CreditCard]([AK_CreditCard_CardNumber]) WITH STATS_STREAM = 0x0100000002000144 <huge big long hex value> 000000000000510D0000000000, ROWCOUNT = 19118, PAGECOUNT = 93

So the UPDATE STATISTICS statements force a particular histogram into the statistics, as well as the page count and row count.

Once you've created the new database, you should be able run queries to look at sizing information.

EXEC sp_spaceused 'Sales.SalesOrderDetail'

returns the following:

name rows reserved data index_size unused
---------------- ------ -------- ------- ---------- ------
SalesOrderDetail 121317 0 KB 9880 KB 0 KB 0 KB

However,

SELECT count(*) FROM Sales.SalesOrderDetail

returns 0.

You can look at execution plans for queries involving any of the cloned tables and pre-existing indexes. You can create new indexes, but be careful. Any new indexes will have statistics created based on the real data in the clone database, which is 0 rows. So the statistics on your new indexes will not be very accurate.

Of course, your clone database cannot be used for timing tests, or troubleshooting concurrency issues, since there is no data to lock, but for examining how plans change as you rewrite queries, it's a great idea.

Have fun!

~Kalen