Search results matching tag '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()). It also provides the number of rows sampled when the statistics were last updated. This is available through DBCC SHOW_STATISTICS, and in a blog post a couple of months ago, I showed you how to get that output into a table for your own querying. One of the coolest things this new DMV shows is the row modification counter, which used to be available in sysindexes as a count of rows changed, but in SQL 2005 and later, SQL Server keeps track of changes to each column that has statistics on it. These values were not visible before, but now they are! This new object also reports any filter definition and includes rows for all statistics, whether index statistics or column statistics.

As a table valued function, sys.dm_db_stats_properties can be used with the CROSS APPLY operator to give information for all statistics in a database, or you can filter to just return user objects and not any system objects, as shown in the code here:

SELECT
sp.object_id, object_name(sp.object_id) as object_name,sp.stats_id, name as stats_name,
filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.object_id > 100;

The new DMV was easy to miss because it wasn’t added until SQL Server 2008R2 Service Pack 2 and SQL Server 2012 Service Pack1. But since you should always be running with the latest service pack, you should have this great DMV if you’re any version after SQL Server 2008.

Let me know if you find this DMV useful, and what you mainly use it for.

Have fun!

~Kalen

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 tricks for getting access to the statistics.

If you want a deeper understanding of what the statistics keep track of, and you don’t have any of my SQL Server Internals books handy, check out this whitepaper: Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

Microsoft does provide us a tool called DBCC SHOW_STATISTICS for examining the distribution statistics.

Microsoft has gradually been making more of the more of the old DBCC commands available as DMVs, even some undocumented ones. For example, one of my favorites, DBCC IND, has now been replaced in SQL Server 2012 by sys.dm_db_database_page_allocations.

I have been wishing for several versions that Microsoft would make the DBCC SHOW_STATISTICS information available as a DMV. But it hasn’t happened yet, and I’m tired of waiting, so I decided to do something about it.

My solution is not quite as easy to use as a DMV might be, but it allows you to get the information that DBCC SHOW_STATISTICS provides into a set of three tables that can then be saved into a more permanent location of your choice, and/or queried as desired.

DBCC SHOW_STATISTICS returns three sets of information, with different columns in the output, so three different tables are needed. The DBCC SHOW_STATISTICS command can be called with an argument that specifies that you just want one of the three sets returned. The options are

WITH STAT_HEADER – returns basic info such as last update date, and number of rows in the table/index. Also reports number of steps returned for HISTOGRAM section.

WITH DENSITY_VECTOR – returns density info for each left-based subset of columns in the index. For example, an index on (lastname, firstname, city) would have a density value for (lastname), for (lastname, firstname), and for (lastname, firstname, city). Each density value is a single number representing the average number of occurrences and depends on the number of distinct values. For example, if there are only 2 possible values in the column, the density would be 0.5. Multiplying density by the number of rows in the STAT_HEADER section would give the average expected rowcount if a query was executed looking for an equality on the specified column(s).

WITH HISTOGRAM – returns a set of ordered values from the first column of the index, creating a histogram. This histogram provides the optimizer with selectivity information for specific values or ranges of values in the first column of the index.

To collect this info, I will use one of my favorite tricks, which is to create a table in the master database with a name starting with sp_. (I’ve written about this trick several times, including in this earlier blog post.) Once I have the table(s) created, I can access them from any database. So here are the three tables:

USE Master;
GO

IF (SELECT object_id('sp_stat_header')) IS NOT NULL
DROP TABLE sp_statsheader;
GO
CREATE TABLE sp_stat_header
(   Name sysname,
    Updated datetime,
    Rows bigint,
    Rows_sampled bigint,
    Steps smallint,
    Density numeric (10,9),
    Average_key_length smallint,
    String_index char(3),
    Filter_expression nvarchar(1000),
    Unfiltered_rows bigint);
GO

IF (SELECT object_id('sp_density_vector')) IS NOT NULL
DROP TABLE sp_density_vector;
GO
CREATE TABLE sp_density_vector
( all_density numeric(10,8),
   average_length smallint,
   columns nvarchar(2126) );
GO

IF (SELECT object_id('sp_histogram')) IS NOT NULL
DROP TABLE sp_histogram;
GO
CREATE TABLE sp_histogram
(   RANGE_HI_KEY sql_variant,
    RANGE_ROWS bigint,
    EQ_ROWS bigint,
    DISTINCT_RANGE_ROWS bigint,
    AVG_RANGE_ROWS bigint);
GO

The second trick is to use INSERT … EXEC to execute a DBCC statement and populate the tables. I will build the DBCC command dynamically, after capturing the schema, table and index names in variables. You of course could take this code and turn it into a stored procedure, for which the schema, table and index names are passed as parameters. I’ll use as an example a table in the AdventureWorks2008 sample database, just so you can try running the code, and I can verify that it actually works!

I will use the table Sales.SalesOrderDetail and the index IX_SalesOrderDetail_ProductID. So the object name (@oname) is SalesOrderDetail, the schema name (@sname) is Sales, and the index name (@iname) is IX_SalesOrderDetail_ProductID.

SET NOCOUNT ON;
USE AdventureWorks2008;
GO
DECLARE @oname sysname, @iname sysname, @sname sysname

SELECT @oname = 'SalesOrderDetail', @sname = 'Sales', @iname = 'IX_SalesOrderDetail_ProductID';

-- Update the object name to include the schema name, because that is the format the DBCC command expects
SELECT @oname = @sname +'.' + @oname;
TRUNCATE TABLE sp_stat_header;
INSERT INTO sp_stat_header
    EXEC ('DBCC SHOW_STATISTICS(['+ @oname + '],' + @iname +') WITH STAT_HEADER');
TRUNCATE TABLE sp_density_vector;
INSERT INTO sp_density_vector
    EXEC ('DBCC SHOW_STATISTICS(['+ @oname + '],' + @iname +') WITH DENSITY_VECTOR');

TRUNCATE TABLE sp_histogram;
INSERT INTO sp_histogram
    EXEC ('DBCC SHOW_STATISTICS(['+ @oname + '],' + @iname +') WITH HISTOGRAM');

So now you can look at the values collected and filter or query in any way, or use SELECT INTO to save them into another table, so the sp_ tables can be used the next time you want to capture distribution statistics information.

SELECT * FROM sp_stat_header;

SELECT * FROM sp_density_vector;

SELECT * FROM sp_histogram;

Let me know if you find this useful, and especially if you embellish it to create a procedure or an automated process of your own!

Thanks!

~Kalen

Decoding STATS_STREAM

jchang — Sat, 05 May 2012 10:16:00 GMT

Data distribution statistics is one of the foundations of the cost-based query optimizer in all modern database engines including SQL Server. From SQL Server 2005 on, most of the information displayed by DBCC SHOW_STATISTICS is kept in a binary field accessible with the STATS_STREAM clause. Back in SQL Server 2000, it was possible to modify system tables directly, including the sysindexes stat_blob field. At the time, I described a decode of the stat_blob field with the purpose of influence the execution plan, presumably on a development system and not a production system.

Starting with SQL Server 2005, it was nolonger possible to directly modify system tables. An API was provided to access data distribution statistics to allow cloning the statistics from one database to another. The presumed usage is to clone statistics from a large production database to a small development database. In other database engines, I had heard of the idea of updating statistics on a backup system to be applied to the production system. While it was still possible to decode most of the 2005 stats_stream binary, it appears that a checksum was added so it was not possible to apply an externally generated statistics binary unless the "checksum" value could be correctly calculated.

Around this time, I was working on other SQL Server tools, most prominently SQL System for performance monitoring, Exec Stats for execution plan analysis and TraceAnalysis for trace processing. Work on the SQL Server data distribution cloning tool was discontinued, and I could not continue further research into the decoding of SQL Server data distribution statistics.

Since several people have asked about the data distribution statisics decode, I am making what I know about stat_stream available. It would be helpful is other people would contribute to the missing pieces.

Note that organization of stats_stream changed from SQL Server version 2000 (then sysindexes stat_blob) to 2005 and again to 2008? It is quite possible there are also changes in version 2012? Most of what I discuss here applies to version 2008 R2.

Decoding Stats Stream for SQL Server 2008R2

Here I am using 1 based reference. Byte index 1 is the first byte.
In C# and most other programming languages use zero based index.

Position	Length	Value/Type	Purpose
1	4?	1	unkown
5	4?		number of vectors
9	4	0	zero
13	4	0	zero
17	4		checksum
21	4	0	zero
25	4		stats stream length
29	4	0	zero
33	4		stats stream length - minus vector variable length The difference [25]-[33] is 64 for 1 vector (defined as off1). Each additional vector adds 24 bytes starting at byte position 41
37	4	0	zero
Start of vector information, 24-bytes per vector
41	1		system type id
42	1		unkown
43	2		unkown
45	4		user type id
49	2		length
51	1		Prec
52	1		Scale
53	4		unknown
57	4		unknown
61	2		unknown
63	2		unknown
			Some of the unknown fields should be for nullable, collation, etc
Addition vectors if present
off1+1^*	9		Updated?, 9 byte datetime2?
off1+10	3		unknown
off1+13	8		Rows
off1+21	8		Rows sampled
off1+29	4	4 byte real	Density - Header
off1+33	4x33=132	4 byte real	Density - vector, upto 33 values
off1+165	4	4 byte int	Steps (first copy)
off1+169	4	4 byte int	Steps (second copy)
off1+173	4	4 byte int	number of vectors
off1+177	4	4 byte int	Step size (in bytes)
off1+181	4	4 byte real	Average Key length - header
off1+185	4	4 byte real	Unfiltered rows
off1+189	4	4 byte int	unknown
off1+193	4x33=132	4 byte real	Average key length - vector
			Some fields may represent string index (bool), or filter expression
off1+325	8	8 byte int	unkown, values 0x11, 0x13 and 0x19 observed, may determine the # of post histogram 8 bytes values starting at off1+341?
off1+333	8	0	8 byte 0?
off1+341	8	0	offset for value after histogram?
off1+349	8	0	another offset
off1+357	8	0	another offset if value of [off1+25] is 19 or more?
more offsets if value of [off1+25] is 25 or more?
Eventually, this sequence appears: 0x10001100 followed by three 4-byte real, a value in native type of the stat, and then ending with 0x040000
off2	2	0x10 - 16	length of core columns determines the organization of histogram structures?-->
off2+2	2	17 or higher	size of step, excluding 3 byte trailer
off2+4	4	4 byte real	Eq Rows
off2+8	4	4 byte real	Range Rows
off2+12	4	4 byte real	Avg Range Rows
off2+16	native length	native type	Range Hi Key
off2+16+x	3 byte	0x040000	step terminator?, x is the size of the type
off3	?	?	additional info

^*off1 = value of 4(8) byte int at position [25] - value of [33]

^**off2 = off1 + 341 + 16 if value of [off1+325] is 0x11, or 24 if 0x13 or 0x19

^***off3 = off1 + 341 + value of 4(8) byte int at [off1+341]

So far, for SQL Sever 2008 R2, I have only looked at fixed length not nullable statistics. Variable length statistics has different organization, particularly in the histogram part. String statistics may have extended information after the histogram, per new feature of SQL Server 2008?

Umachandar provides a SQL function for converting 4-byte binary to real or 8-byte binary float, and vice versa.

Supporting SQL functions and procedures:

The updated tools now has a stored procedure that accepts table and index (or column stat) as input parameters, in addition to the original procedure that has the stats stream binary.

Updates on QDPMA Stats Stream - Updated

decoding stats stream - Updated

decoding stats stream

ps
An interesting fact is that it is not necessary for statistics to be highly accurate to be effective. Normally we are interested in distribution differences to shift the execution plan from one to another. The boundaries for this can be very wide. False statistics in certain circumstances might guard against catastrophically bad execution plans, example in out of bounds situations. Another is in skewed distributions, but this should be handled by other means, to ensure high and low distributions get different execution plans.

Connect Digest : 2011-09-22

AaronBertrand — Thu, 22 Sep 2011 11:31:00 GMT

Where is SYSDATE()?

I've talked about this one before, but due to a recent conversation about building a datetime value from separate date/time columns or variables, it may be worth bringing up again. When SQL Server 2008 introduced several new date/time types, they also added new built-in functions, like SYSDATETIME() and SYSDATETIMEOFFSET(). But they forgot about the same functions they forgot about back when GETDATE() and GETUTCDATE() were introduced: functions that return only date or only time. So we're still stuck with using implicit conversion to assign CURRENT_TIMESTAMP or SYSDATETIME() to a DATE or TIME variable, only to throw away the irrelevant part.

Connect #293333 : SQL 2008: Add sysdate()

Where is my built-in sequence?

In Denali we've seen the addition of SEQUENCE, an easy and more efficient way to centralize IDENTITY-like identifier values. But often we want to generate these numbers without incrementing a counter or running a bunch of inserts. To generate numbers on the fly in cases where I haven't been able to create my own numbers table, I often use master..spt_values.number, convoluted CTEs, or ROW_NUMBER() OVER () a cross join against arbitrary system tables. Why can't there be an in-memory table of numbers that we could reference, without all this extra scaffolding?

Connect #258733 : Add a built-in table of numbers

Why is CLR still so hard?

For ages I've wished it were easier to implement CLR solutions. RegEx is still not in SQL Server, and CLR has proven much more efficient than traditional XML, numbers table and looping methods in dealing with string splitting operations as well. And probably dozens of use cases that I haven't had the necessity to research myself. But it is often hard to help folks implement this on their own, never mind deal with strict DBA/management environments where CLR is still off-limits. Wouldn't it be great if it were far easier to implement C# code directly within a stored procedure or function, without all the overhead of deploying a DLL, creating an assembly, etc.

Connect #265266 : Add server-side compilation ability to SQL CLR

Will CASE always evaluate in left-to-right order?

The documentation for CASE states that the WHEN expressions are evaluated in textual order. Most people infer from this that any subsequent expression will not be evaluated, but there have been several cases where this has not been true. While I didn't find this particular manifestation of the bug (that was Itzik), and while I do hope they correct this scenario, I'm much more interested in clarity in the documentation so that people know whether or not they can rely on this behavior to always be true.

Connect #690017 : CASE / COALESCE won't always evaluate in textual order

Why can't I have partition-level stats?

Partitioning is great, but if you have disproportionate partition sizes, or a lot of partitions, statistics can become a real challenge. Consider a table with 100 million rows, but only 1 million rows in the currently active partition. If you want to update the statistics on the active partition, your probable approach today is to update statistics on the whole table. (The same type of issue exists with filtered indexes - auto statistics updates kicks in based on the row count in the table, not the subset in the index.)

Connect #468517 : Update Statistics at the partition level

Connect #328093 : Statistics are saved on table level and not on partition level

Connect #457024 : Update statistics, top 100 percent and Sort warnings

How to Find the Statistics Used to Compile an Execution Plan

Paul White — Tue, 20 Sep 2011 15:22:15 GMT

In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan.

Trace Flags

We will need three undocumented trace flags. The first one (3604) is well-known – it redirects trace output to the client so it appears in the SSMS messages tab.

The second trace flag is 9292. With this enabled, we get a report of statistics objects which are considered ‘interesting’ by the query optimizer when compiling, or recompiling the query in question. For potentially useful statistics, just the header is loaded.

The third trace flag is 9204. With this enabled, we see the ‘interesting’ statistics which end up being fully loaded and used to produce cardinality and distribution estimates for some plan alternative or other. Again, this only happens when a plan is compiled or recompiled – not when a plan is retrieved from cache.

You can enable and disable these flags with the usual DBCC TRACEON and TRACEOFF commands, but it is also possible to enable them just for a particular statement using the undocumented QUERYTRACEON query hint (demonstrated below).

Sample Query

     DBCC FREEPROCCACHE

    SELECT 

        p.Name,

        total_quantity = SUM(th.Quantity)

    FROM AdventureWorks.Production.Product AS p

    JOIN AdventureWorks.Production.TransactionHistory AS th ON

        th.ProductID = p.ProductID

    WHERE

        th.ActualCost >= $5.00

        AND p.Color = N'Red'

    GROUP BY

        p.Name

    ORDER BY

        p.Name

    OPTION

    (

        QUERYTRACEON 3604,

        QUERYTRACEON 9292,

        QUERYTRACEON 9204

    )

The DBCC FREEPROCCACHE is just there to empty the plan cache so we get a compilation. You can also evict the current plan from cache if you know its handle (SQL Server 2008) or use a RECOMPILE query hint. Using RECOMPILE is often convenient, but you may get a different plan compared to that obtained without the hint. Note that compiling the query is enough – we do not need to execute the query; simply requesting an ‘estimated plan’ will do. It doesn’t hurt to run it either though, just to be clear.

Sample Output

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 1, 

        ColumnName: ProductID, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 1, 

        ColumnName: ProductID, 

        EmptyTable: FALSE

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 3, 

        ColumnName: Name, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 3, 

        ColumnName: Name, 

        EmptyTable: FALSE

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 11, 

        ColumnName: Color, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.Product, 

        IndexId: 11, 

        ColumnName: Color, 

        EmptyTable: FALSE

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.TransactionHistory, 

        IndexId: 2, 

        ColumnName: ProductID, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.TransactionHistory, 

        IndexId: 2, 

        ColumnName: ProductID, 

        EmptyTable: FALSE

    Stats header loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.TransactionHistory, 

        IndexId: 5, 

        ColumnName: ActualCost, 

        EmptyTable: FALSE

    Stats loaded: 

        DbName: AdventureWorks, 

        ObjName: AdventureWorks.Production.TransactionHistory, 

        IndexId: 5, 

        ColumnName: ActualCost, 

        EmptyTable: FALSE

There’s no sign of an official way to get this very useful information in Denali, despite it being requested many times over the years. Trace flag 9204 works at least as far back as SQL Server 2005. Both 92xx flags work in 2008, R2, and Denali CTP 3.

Enjoy!

SQLkiwi@gmail.com
@SQL_Kiwi

Ola Hallengren adds STATISTICS support to his solution

AaronBertrand — Mon, 13 Dec 2010 19:56:00 GMT

Last week, Ola published a very useful update to his Backup, Integrity Check and Index Optimization scripts: the solution now supports updating statistics. There are several options, such as only updating when the data has been modified and using the RESAMPLE and NORECOMPUTE options. An example call:

EXEC dbo.IndexOptimize
    @Databases                  = 'USER_DATABASES',
    @FragmentationHigh_LOB      = 'INDEX_REBUILD_OFFLINE',
    @FragmentationHigh_NonLOB   = 'INDEX_REBUILD_ONLINE',
    @FragmentationMedium_LOB    = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
    @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
    @FragmentationLow_LOB       = 'STATISTICS_UPDATE',
    @FragmentationLow_NonLOB    = 'STATISTICS_UPDATE',
    @UpdateColumnStatistics     = 'Y',
    @OnlyModifiedStatistics     = 'Y';

If you haven't already tried Ola's maintenance solution, I highly suggest you check it out.

Fooling the Query Optimizer

Ben Nevarez — Thu, 14 Jan 2010 04:57:00 GMT

Did you ever wanted to know which execution plans the Query Optimizer would generate for your queries should your tables have millions of records? You can actually generate those plans by using the undocumented ROWCOUNT and PAGECOUNT options of the UPDATE STATISTICS statement. These options can be used on small or empty tables and can be helpful for testing in some scenarios where you may not want to spent time or disk space creating big tables.

By using this method you are tricking the Query Optimizer as it will generate execution plans using cardinality estimations as if the table really had millions of records. Note that this option, available since SQL Server 2005, only helps in creating the execution plan for your queries. Actually running the query will use the real data and of course will execute faster than a table with millions of records.

UPDATE STATISTICS WITH ROWCOUNT, PAGECOUNT does not change the table statistics, only the counter of number of rows and pages of a table. But the Query Optimizer uses this information to estimate the cardinality of queries as I will show later. Also keep in mind that these are undocumented and unsupported options and should not be used in any production environment.

Let us see an example. Run the following query to create a new table on the AdventureWorks database

select * into dbo.Address

from Person.Address

Inspect the number of rows by running the following queries. It must show 19,614 rows.

select * from sys.partitions

where object_id = object_id('dbo.Address')

select * from sys.dm_db_partition_stats

where object_id = object_id('dbo.Address')

Run the following query

select * from dbo.Address

where city = 'London'

Running this query will create new statistics for the city column and will show the following plan. Note that the estimated number of rows is 434 and it is using a simple Table Scan operator

We can see where the Query Optimizer is getting the estimated number of rows by inspecting the statistics object. Run this query to see the name of the statistics object

select * from sys.stats

where object_id = object_id('dbo.Address')

Then use the displayed statistics object name in the following statement (the name may be different in your case)

dbcc show_statistics('dbo.Address', _WA_Sys_00000004_46136164)

By looking at the histogram you can find the value 434 on EQ_ROWS for the RANGE_HI_KEY value ‘London’ (Statistics and histograms are explained on previous posts in this blog)

Now run the UPDATE STATISTICS WITH ROWCOUNT, PAGECOUNT (you can specify any other value for rowcount and pagecount)

update statistics dbo.Address with rowcount = 1000000, pagecount = 100000

If you inspect the number of rows again from sys.partitions or sys.dm_db_partition_stats, as shown previously, it will now show 1,000,000 rows. sys.dm_db_partition_stats also shows the new number of pages. Clear the plan cache and run the query again

dbcc freeproccache

select * from dbo.Address

where city = 'London'

Note that the estimated number of rows has changed from 434 to 22,127.1 and that a different plan was generated using this new cardinality estimation. The Query Optimizer decided to parallelize this plan. But this is a very simple query, more dramatic plan changes can happen with more complex queries.

After execution the actual number of rows obviously is still is 434 but the Query Optimizer is not able to see this value.

If you look at the statistics object again, using DBCC SHOW_STATISTICS as shown before, the histogram has not changed. One way to obtain the estimated number of rows shown in the new execution plan is calculating the percentage or fraction of rows for the value ‘London’ from the statistics sample, which in this case is 19,614, as shown on the header of the statistics object. So the fraction is 434 / 19,614 or 022127052. Then obtain the same percentage from the new “current” number of rows which is 1,000,000 calculated as 1,000,000 * 0.022127052 and we get 22,127.1 which is the estimated number of rows displayed in the plan.

Finally, drop the table you just created

drop table dbo.Address

Lies, Damned Lies and Statistics – Part III (SQL Server 2008)

Elisabeth Redei — Thu, 17 Dec 2009 11:14:00 GMT

In previous posts (http://sqlblog.com/blogs/elisabeth_redei/archive/2009/08/10/lies-damned-lies-and-statistics-part-i.aspx and http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-ii.aspx)I have talked about the performance problems that may surface because of low statistics sample rate on large tables.

One of the things I wrote about are issues with naturally skewed data in an indexed column. In this context, skewed data means that some values are represented more than others. One example is a "Country" column in an Address table where an international company is more likely to have more customers or employees in the U.K then let’s say Finland. A “Lastname" column in a Customers table would typically have skewed data as well – there are a lot more Smiths around than Rédei for example . Your SalesOrder table is bound to have more entries for some customers than for others as well.

The solutions I listed - that have been listed by many before me - were never really appealing to me and consequently I was never good at persuading anyone to use them and this has always annoyed me.

SQL Server 2008 offers a great solution to this problem - filtered statistics. As you probably know, SQL Server 2008 offers you the ability to create filtered indexes, i.e. the index is created on a horizontal partition of the column. But did you know you can create filtered statistics on an unfiltered index?

This solution is much more appealing simply because you implement it at the index level rather than individual query level. In addition, your data will change and this solution can easily adopt to those changes. Another attractive perk is that it would be quite difficult to mess things up with this approach – unless you make a serious effort to!

Jason Massie has touched on the subject on SQLServerpedia, http://sqlserverpedia.com/blog/sql-server-bloggers/filtered-stats-to-counter-data-skew-issues/, but I wanted to see how it worked if you filtered on ranges of data covering all values in the index.

Again, I have a table, test1, with with values in the c2 column that ranges from 1 to 500.I have created a non-clustered index Idx1 on column c2.

In each bucket, I have about 2 400 rows, but c2 = 45 returns 0 rows:

SELECT c2, COUNT(*) as NumRows
FROM test1
group by c2
order by c2

DBCC SHOW_STATISTICS ('test1','idx1') WITH HISTOGRAM reveals that the value 45 has fallen between the chairs, even after an UPDATE STATISTICS test1 WITH FULLSCAN:

DBCC SHOW_STATISTICS ('test1','idx1') WITH HISTOGRAM:

DBCC SHOW_STATISTICS ('test1','idx1') WITH STAT_HEADER shows that all 1 190 402 rows are represented by 200 “buckets” or ranges (the Steps column):

Consequently, the following query gives me a table scan because the statistics that the optimizer have at hand, implies that 2 400 rows will be returned:

SELECT c1, c2 FROM test1 WHERE c2 = 45

So the problem is not the sample rate; SQL Server has looked at all values in the index when it was building the statistics – it’s just that the histogram in this case is to coarse for the data it represents.

How can I solve this problem?

The trick is to increase the number of ranges that represent the data so rather than having 200 to represent 1 200 000 rows you can have 600 or a 1000.

To start with, I need to know my domain of values:

SELECT MIN(c2) AS minVal, AVG(c2) avgVal, MAX(c2) maxVal FROM test1

I have only some 1 200 000 rows in this table, so I am going to go ahead and try with 3 ranges (ideally you should have an idea how many rows are in each range):

CREATE STATISTICS testStatLow
ON test1 (c2)
WHERE c2 < 200
GO

CREATE STATISTICS testStatMed
ON test1 (c2)
WHERE c2 > 200 AND c2 < 400
GO

CREATE STATISTICS testStatHi
ON test1 (c2)
WHERE c2 > 400
GO

I am then going to update the statistics with some sample rate to see if I - in addition to getting better query plans – I can save time on my maintenance job that updates the statistics for all tables:

UPDATE STATISTICS test1 WITH SAMPLE 20 percent

And then look at the statistics:

dbcc show_statistics ('test1','testStatLow') with STAT_HEADER

Rows sampled vs. Rows shows that a little bit more than half of the rows were scanned (remember that SQL Server can decide to increase the sample rate if it decides that it is necessary to get a good representation of the data). The data in the column has been divided into yet another 194 buckets/ranges, drastically reducing the risk for problems with skewed data.

Let’s run the query again to see if my new filtered statistics makes a difference:

SELECT c1, c2 FROM test1 WHERE c2 = 45

There we go, the optimizer chose to do an index seek to find my non-existing row. Much better!

What about maintenance for the statistics?

My idea was that perhaps I can get away with a lower sample rate now that the index has been logically divided into several histograms. And as you just saw, I can for this particular scenario. However, after an update, I ran:

UPDATE STATISTICS test1 WITH SAMPLE 20 PERCENT

… which took about 6 seconds whereas

UPDATE STATISTICS test1 WITH FULLSCAN

.. took about 1 second.

So in my somewhat constructed scenario, my gain is with the performance of the query but I am not saving on the precious time I have available for doing index maintenance.

Hm… I can sense an upcoming blog on the subject of the Long and Winding Shortcut!

For want of a nail

mz1313 — Tue, 08 Dec 2009 00:55:55 GMT

For want of a nail the shoe was lost.
For want of a shoe the horse was lost.
For want of a horse the rider was lost.
For want of a rider the battle was lost.
For want of a battle the kingdom was lost.
And all for the want of a horseshoe nail.

I'm now in the middle of severity A case that is best depicted by this rhyme. While Microsoft engineers look for a root cause, I'll describe here how small bug can "kill" strong server. The story begins when for some unknown reason "rows" column in sys.partitions DMV begins to show 4.6 billion rows for clustered index of one of the entities tables in my datawarehouse database. COUNT(*) on the table returns 1 million rows.

Second step - daily job that executes sp_updatestats stored procedure. This procedure updates statistics in the database using sample data . Let's say, I randomly took 5% of table's data and calculated number of unique values for Column1 - n values. So total unique values of Column1 in my table is 20*n. But how do I know, how many rows per unique value of Column1 are there in the table? Very simple: SQL Server just takes rows column from sys.partitions and divides by our 20*n number.

Third step - query: it joins between our Table and another one WHERE Column1 = x. Optimizer compiles the query and builds execution plan based on existing statistics (e.g. 4 billion rows in the table). Expected number of rows from our table is millions - so Optimizer chooses HASH JOIN as the best way to perform JOIN operation.

Now that Optimizer made a decision about execution plan, it asks for a memory quota for query execution. Based on the estimation about millions of rows. Remember that memory quota for query execution isn't taken from data buffer cache, so on 32 bit systems we can't utilize too much memory. For example, I executed single query as described earlier and while it was running checked memory usage of my session.

So we have 252Mb granted while less than 1Mb used. Total available memory is 1.6Gb, so you can calculate yourself how many concurrent queries system supports at its current state. It is exactly the situation we had: 5 sessions were running and 65 were waiting for memory grant (if you monitor wait types - there will be a lot of RESOURCE_SEMAPHORE).

P.S. DBCC UPDATEUSAGE fixes the initial problem. So while Microsoft engineers look for the reason why sys.partitions went wild, I monitor it in order to fix the problem before it "kills" the server.

Rebuilding Indexes vs. Updating Statistics

Ben Nevarez — Tue, 06 Oct 2009 07:04:00 GMT

One of the questions I was asked recently while speaking at user groups, was regarding the order that jobs like rebuilding indexes or updating statistics should be performed as part of the database maintenance activities. Then I started writing this post about this topic on the weekend but was interrupted several times, including one of them to watch the premiere on VH1 of the movie Anvil: The Story of Anvil.

In general, the order should not matter, at least if you carefully consider these important points:

1) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

2) By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

3) Rebuilding an index, for example by using ALTER INDEX … REBUILD, will also update index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update column statistics.

4) Reorganizing an index, for example using ALTER INDEX … REORGANIZE, does not update any statistics.

So depending on your maintenance jobs and scripts several scenarios can exist.

The simplest scenario is if you want to rebuild all the indexes and update all the statistics. As mentioned before, if you rebuild all your indexes then all the index statistics will also be updated by scanning all the rows on the table. Then you just need to update your column statistics by running UPDATE STATISTICS WITH FULLSCAN, COLUMNS. Since the first job only updates index statistics and the second one only updates column statistics, it does not matter which one you execute first.

Some other more complicated scenarios include when you have a job which rebuilds your indexes depending on their fragmentation level. In these cases perhaps you want to update only those index statistics that were not touched by the index rebuild job, plus all the column statistics.

Of course, the worst case scenario would be if you first rebuild your indexes, which also updates the index statistics by scanning the entire table, and later you run UPDATE STATISTICS using the default values, which again updates the index statistics but this time with a default sample. Not only are you updating your index statistics twice but you are overwriting the better of the two choices.

Let me show you how these commands work with some examples using the AdventureWorks database. Create a new table dbo.SalesOrderDetail

select * into dbo.SalesOrderDetail

from sales.SalesOrderDetail

The next query uses the sys.stats catalog view and shows that there are no statistics objects for the new table.

select name, auto_created, stats_date(object_id, stats_id) as update_date from sys.stats

where object_id = object_id('dbo.SalesOrderDetail')

Use this query again to inspect the status of the statistics after each of the following commands. Now run the following query

select * from dbo.SalesOrderDetail

where SalesOrderID = 43670 and OrderQty = 1

Use the previous sys.stats query to verify that two statistics objects were created, one for the SalesOrderID column and another one for the OrderQty column (they both have names starting with _WA_Sys as shown in the next figure). Now create the following index and again run the query to verify that a new statistics object for the ProductID column has been created. Notice the value of the auto_created column which tells if the statistics were created by the query optimizer.

create index ix_ProductID on dbo.SalesOrderDetail(ProductID)

Run the next command to update the column statistics only. You can validate that only the column statistics were updated by looking at the update_date column which uses the STATS_DATE function to display the last date the statistics were updated.

update statistics dbo.SalesOrderDetail with fullscan, columns

This command will do the same for the index statistics

update statistics dbo.SalesOrderDetail with fullscan, index

These commands will update both index and column statistics

update statistics dbo.SalesOrderDetail with fullscan

update statistics dbo.SalesOrderDetail with fullscan, all

See how an index rebuild only updates index statistics

alter index ix_ProductID on dbo.SalesOrderDetail rebuild

Here you can verify that reorganizing an index does not update statistics

alter index ix_ProductID on dbo.SalesOrderDetail reorganize

Finally, remove the table you have just created

drop table dbo.SalesOrderDetail