What is DBCC SHOW_STATISTICS Telling Me About My Data?

Per Books Online, DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view.  Basically it shows you the statistics, or a summary of the data, that SQL Server will use to help generate an execution plan.

In the example below, we'll be looking at the statistics for the index IX_Person_LastName_FirstName_MiddleName, which is a non-clustered index on the Person.Person table in the AdventureWorks2012 database.  We'll cover several queries you can run against your data to help you visualize what the statistics are telling you about your data.

Before we dive into the example, we need to update the statistics with a full scan on this index.  This will make sure the details in the example match our queries exactly.

UPDATE STATISTICS Person.Person IX_Person_LastName_FirstName_MiddleName WITH FULLSCAN;

DBCC SHOW_STATISTICS returns three sections of information: the header, the density vector, and the histogram.  The first section is the statistics header.

DBCC SHOW_STATISTICS('Person.Person','IX_Person_LastName_FirstName_MiddleName') WITH STAT_HEADER;

The statistics header returns meta data about the statistic.  For example, the date it was created or last updated, number of rows in the table or indexed view, the number rows used to calculate the statistic, etc.

The second section is the density vector.

DBCC SHOW_STATISTICS('Person.Person','IX_Person_LastName_FirstName_MiddleName') WITH DENSITY_VECTOR;

The density vector is representation of how many unique values are present within a column or columns of the statistic.  Simply put it's 1/# of distinct values.  Our example has 4 levels for the density vector; one for each of the three key columns, plus one that includes the clustered index.  To see how SQL Server calculates the values for each of these four levels, we can use the following queries.

--Level 1

SELECT CONVERT(DECIMAL(15,12),1.0/COUNT(DISTINCT LastName)) AS 'Level1'

    FROM Person.Person;

GO

--Level 2

SELECT CONVERT(DECIMAL(15,12),1.0/COUNT(*)) AS 'Level2'

    FROM (SELECT DISTINCT

                 LastName

                ,FirstName

            FROM Person.Person) AS DistinctRows;

GO

--Level 3

SELECT CONVERT(DECIMAL(15,12),1.0/COUNT(*)) AS 'Level3'

    FROM (SELECT DISTINCT

                 LastName

                ,FirstName

                ,MiddleName

            FROM Person.Person) AS DistinctRows;

GO

--Level 4

SELECT CONVERT(DECIMAL(15,12),1.0/COUNT(*)) AS 'Level4'

    FROM (SELECT DISTINCT

                 LastName

                ,FirstName

                ,MiddleName

                ,BusinessEntityID

            FROM Person.Person) AS DistinctRows;

GO

Our values are formatted to display the entire number, but DBCC SHOW_STATISTICS will use the E notation to shorten number to 5.124001E-05.  This notation just means take 5.124001 * 10^-5, or an easier explanation would be to move the decimal place 5 spaces to the left.  As you can see, our numbers nearly match what is returned by DBCC SHOW_STATISTICS.

The third section of information is the histogram.

DBCC SHOW_STATISTICS('Person.Person','IX_Person_LastName_FirstName_MiddleName') WITH HISTOGRAM;

The histogram returns information about the frequency of data within the first key column of the statistic.  In our example, we have a composite index of LastName, FirstName, and MiddleName, so the histogram only contains information about the first column, LastName.

1. RANGE_HI_KEY - is the upper bound value of the key.
2. RANGE_ROWS - is the number of rows who's value falls within the step, but does not equal the upper bound (RANGE_HI_KEY).
3. EQ_ROWS - is the number of rows equal to the upper bound.
4. DISTINCT_RANGE_ROWS - is the number of distinct values within the histogram step, but does not equal the upper bound (RANGE_HI_KEY).
5. AVG_RANGE_ROWS - is the average number of duplicate values within the step, but does not equal the upper bound (RANGE_HI_KEY); calculated as RANGE_ROWS/DISTINCT_RANGE_ROWS.

If we examine the RANGE_HI_KEY value of Adams, we can figure out the exact data rows that fall into this histogram step by looking at these queries.

For the RANGE_ROWS, we need to find all the rows that are greater than the previous RANGE_HI_KEY value, but less than the RANGE_HI_KEY of 'Adams'.  This will return the 10 rows that were in the histogram RANGE_ROWS column.

--RANGE_ROWS

SELECT * FROM Person.Person

WHERE LastName > 'Abbas'

AND LastName < 'Adams';

GO

For the EQ_ROWS, we just need to find all the rows that are equal to the RANGE_HI_KEY  'Adams'.  This will return the 86 rows that were in the histogram EQ_ROWS column.

--EQ_ROWS

SELECT * FROM Person.Person

WHERE LastName = 'Adams';

GO

For the DISTINCT_RANGE_ROWS, we need to find all the distinct values that are greater than the previous RANGE_HI_KEY  but less than the RANGE_HI_KEY of 'Adams'.  This will return the 6 rows that were in the DISTINCT_RANGE_ROWS column.

--DISTINCT_RANGE_ROWS

SELECT DISTINCT LastName FROM Person.Person

WHERE LastName > 'Abbas'

AND LastName < 'Adams';

GO

For the AVG_RANGE_ROWS, we need to find the values that are greater than the previous RANGE_HI_KEY but less than the RANGE_HI_KEY of 'Adams', and then divide that by the number of distinct values within the same range.  This will return the average value of 1.666667 that was in the AVG_RANGE_ROWS column.

--AVG_RANGE_ROWS

DECLARE

     @x DECIMAL(20,6)

    ,@y DECIMAL(20,6);

SELECT @x = COUNT(LastName) FROM Person.Person

WHERE LastName > 'Abbas'

AND LastName < 'Adams';

SELECT @y = COUNT(DISTINCT LastName) FROM Person.Person

WHERE LastName > 'Abbas'

AND LastName < 'Adams';

IF @y > 0

    SELECT CONVERT(DECIMAL(20,6),@x/@y) AS 'AVG_RANGE_ROWS';

GO

As you can see, it's not that hard to see how the statistical information is derived within DBCC SHOW_STATISTICS.

If you would like more detailed information on how statistics are generated and how they help the query optimizer, check out Grant Fritchey's books and blog.  He covers a lot of good in-depth information about statistics.  And of course Books Online has plenty more information about DBCC SHOW_STATISTICS.

How to Remove (Undo) Table Partitioning

I have seen plenty of articles and blog posts out there for how to setup and implement table partitioning, but very few for removing or undoing it.  So I thought I would cover a few ways to accomplish this while still preserving the data.

There could be many reasons for removing partitioning: no longer needed, need to change partitioned tables, etc.  But for our example, we just want to completely remove it from all tables.  It would be nice if we could just drop the partition function and partition schema and SQL Server would handle all the rest, but it just isn't that simple and that's why DBAs were created.

The Problem - We have two partitioned tables (PartitionTable1 & PartitionTable2) split across four filegroups.  We need to remove partitioning from the tables, remove the four files and filegroups, and then move all data to the PRIMARY filegroup without losing any data.

Sample Database - Start by creating a test database with a few filegroups and add some data files to those filegroups.

USE master;

GO

-- Create a test database.

CREATE DATABASE PartitionTest

    ON PRIMARY (

         NAME = N'PartitionTest'

        ,FILENAME = N'D:\MSSQL11.TEST1\MSSQL\DATA\PartitionTest.mdf'

        ,SIZE = 25MB, FILEGROWTH = 25MB)

    LOG ON (

         NAME = N'PartitionTest_log'

        ,FILENAME = N'D:\MSSQL11.TEST1\MSSQL\DATA\PartitionTest_log.ldf'

        ,SIZE = 25MB, FILEGROWTH = 25MB);

GO

USE PartitionTest;

GO

-- Add four new filegroups to the PartitionTest database.

ALTER DATABASE PartitionTest ADD FILEGROUP PartitionFG1;

GO

ALTER DATABASE PartitionTest ADD FILEGROUP PartitionFG2;

GO

ALTER DATABASE PartitionTest ADD FILEGROUP PartitionFG3;

GO

ALTER DATABASE PartitionTest ADD FILEGROUP PartitionFG4;

GO

-- Adds one file for each filegroup.

ALTER DATABASE PartitionTest

    ADD FILE

    (

        NAME = PartitionFile1,

        FILENAME = 'D:\MSSQL11.TEST1\MSSQL\DATA\PartitionFile1.ndf',

        SIZE = 25MB, MAXSIZE = 100MB, FILEGROWTH = 5MB

    )

    TO FILEGROUP PartitionFG1;

GO

ALTER DATABASE PartitionTest

    ADD FILE

    (

        NAME = PartitionFile2,

        FILENAME = 'D:\MSSQL11.TEST1\MSSQL\DATA\PartitionFile2.ndf',

        SIZE = 25MB, MAXSIZE = 100MB, FILEGROWTH = 5MB

    )

    TO FILEGROUP PartitionFG2;

GO

ALTER DATABASE PartitionTest

    ADD FILE

    (

        NAME = PartitionFile3,

        FILENAME = 'D:\MSSQL11.TEST1\MSSQL\DATA\PartitionFile3.ndf',

        SIZE = 25MB, MAXSIZE = 100MB, FILEGROWTH = 5MB

    )

    TO FILEGROUP PartitionFG3;

GO

ALTER DATABASE PartitionTest

    ADD FILE

    (

        NAME = PartitionFile4,

        FILENAME = 'D:\MSSQL11.TEST1\MSSQL\DATA\PartitionFile4.ndf',

        SIZE = 25MB, MAXSIZE = 100MB, FILEGROWTH = 5MB

    )

    TO FILEGROUP PartitionFG4;

GO

Create our partition function and then our partition scheme.

-- Creates a partition function called myRangePF1 that will partition a table into four partitions

CREATE PARTITION FUNCTION myRangePF1 (int)

    AS RANGE LEFT FOR VALUES (500, 1000, 1500);

GO

-- Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above

CREATE PARTITION SCHEME myRangePS1

    AS PARTITION myRangePF1

    TO (PartitionFG1, PartitionFG2, PartitionFG3, PartitionFG4);

GO

Create the partitioned tables on the partition scheme; one (PartitionTable1) with a clustered index and one (PartitionTable2) with a non-clustered index.

-- Creates a partitioned table called PartitionTable1 with a clustered index

CREATE TABLE PartitionTable1 (col1 int IDENTITY(1,1), col2 datetime, col3 char(8000))

    ON myRangePS1 (col1);

GO

CREATE CLUSTERED INDEX [PK_col1] ON [dbo].[PartitionTable1]

    ([col1] ASC) ON [myRangePS1]([col1]);

GO

-- Creates a partitioned table called PartitionTable2 with a nonclustered index

CREATE TABLE PartitionTable2 (col1 int IDENTITY(1,1), col2 datetime, col3 char(8000))

    ON myRangePS1 (col1);

GO

CREATE NONCLUSTERED INDEX [IX_col2] ON [dbo].[PartitionTable2]

    ([col1],[col2] ASC) ON [myRangePS1]([col1]);

GO

Now add 2000 rows of dummy data to each table.  The random date generator code is courtesy of Latif Khan.

-- Insert dummy data.

INSERT PartitionTable1(col2,col3)

SELECT  CAST(CAST(GETDATE() AS INT) -2000 * RAND(CAST(CAST(NEWID() AS BINARY(8)) AS INT))AS DATETIME), REPLICATE('1',8000);

GO 2000

INSERT PartitionTable2(col2,col3)

SELECT  CAST(CAST(GETDATE() AS INT) -2000 * RAND(CAST(CAST(NEWID() AS BINARY(8)) AS INT))AS DATETIME), REPLICATE('2',8000);

GO 2000

Let's query the sys.partitions table and see what we have created.

-- Get partition information.

SELECT

     SCHEMA_NAME(t.schema_id) AS SchemaName

    ,OBJECT_NAME(i.object_id) AS ObjectName

    ,p.partition_number AS PartitionNumber

    ,fg.name AS Filegroup_Name

    ,rows AS 'Rows'

    ,au.total_pages AS 'TotalDataPages'

    ,CASE boundary_value_on_right

        WHEN 1 THEN 'less than'

        ELSE 'less than or equal to'

     END AS 'Comparison'

    ,value AS 'ComparisonValue'

    ,p.data_compression_desc AS 'DataCompression'

    ,p.partition_id

FROM sys.partitions p

    JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

    JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id

    JOIN sys.partition_functions f ON f.function_id = ps.function_id

    LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id

    JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number

    JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id

    JOIN (SELECT container_id, sum(total_pages) as total_pages

            FROM sys.allocation_units

            GROUP BY container_id) AS au ON au.container_id = p.partition_id 

    JOIN sys.tables t ON p.object_id = t.object_id

WHERE i.index_id < 2

ORDER BY ObjectName,p.partition_number;

GO

Here we can see both PartitionTable1 and PartitionTable2 are evenly split with 500 rows in each of the four partitions and each in a separate filegroup. 

Within SSMS, you can also see each table is showing the partition scheme and the four partitions.

Solution for PartitionTable1 - This table has a clustered index which makes our solution pretty easy.

Since we have a partitioned clustered index, we can remove partitioning from this table by simply executing a single statement; CREATE INDEX using the DROP_EXISTING option and specifying a different filegroup.  This will drop the current partitioned index (which includes the data) and recreate it on the PRIMARY filegroup all within a single command.

-- Quick and easy way to unpartition and move it.

CREATE CLUSTERED INDEX [PK_col1]

    ON [dbo].[PartitionTable1]([col1])

    WITH (DROP_EXISTING = ON)

    ON [PRIMARY];

GO

Now query the sys.partitions DMV again and you will see PartitionTable1 no longer shows up and only PartitionTable2 is remaining.

Once again in SSMS, you can will see PartitionTable1 now resides on the PRIMARY filegroup and its data still remains intact.

Solution for PartitionTable2 - We can't use the previous index trick on the this table because it doesn't have a clustered index.  For this solution, we'll need to use a few ALTER commands such as MERGE RANGE, NEXT USED, SPLIT RANGE, and SWITCH.

First we need to use the ALTER PARTITION FUNCTION MERGE command to combine all of the four partitions into a single partition.  The MERGE RANGE command removes the boundary point between the specified partitions.

-- Merge all partitions into a single partition.

ALTER PARTITION FUNCTION myRangePF1() MERGE RANGE (500);

GO

ALTER PARTITION FUNCTION myRangePF1() MERGE RANGE (1000);

GO

ALTER PARTITION FUNCTION myRangePF1() MERGE RANGE (1500);

GO

Query the sys.partitions DMV again, and you will see that all 2000 rows have been combined, or merged, into a single partition and now reside on the PartitionFG4 filegroup.

Next, we need to use ALTER PARTITION SCHEME NEXT USED to specify the PRIMARY filegroup as the next partition.

-- Create next partition as PRIMARY.

ALTER PARTITION SCHEME myRangePS1 NEXT USED [PRIMARY];

GO

Then we need to use ALTER PARTITION FUNCTION SPLIT RANGE using a partition value that is larger than the maximum value of your partition column.  In our example, since we're doing a RANGE LEFT partition then specifying any value greater than or equal to 2000 will do the trick.  The SPLIT RANGE command will create a new boundary in the partitioned table.

-- Split the single partition into 2 separates ones to push all data to the PRIMARY FG.

ALTER PARTITION FUNCTION myRangePF1() SPLIT RANGE (2000);

GO

Query the sys.partitions DMV once again.  You can see that PartitionTable2 is still partitioned into two partitions, but all 2000 rows now reside in the PRIMARY filegroup.

At this point we're only half way done.  Now we need to create a non-partitioned table in the PRIMARY filegroup that matches the PartitionTable2 in every way, including any data types, constraints, etc.  This new table will only be used as a temporary holding location for the data.

-- Create a new temporary non-partitioned table.

CREATE TABLE NonPartitionTable (col1 int IDENTITY(1,1), col2 datetime, col3 char(8000))

    ON [PRIMARY];

GO

CREATE NONCLUSTERED INDEX [IX_col2] ON [dbo].[NonPartitionTable]

    ([col1],[col2] ASC) ON [PRIMARY];

GO

Next we'll use the ALTER TABLE SWITCH command to move the 2000 rows of data into the NonPartitionTable.

-- Switch the partitioned data into the temporary table.

ALTER TABLE PartitionTable2 SWITCH PARTITION 1 TO NonPartitionTable;

GO

Query the sys.partitions DMV again to see there are now zero rows in the PartitionTable2.

The SWITCH command is very efficient because it's just making a metadata change.  Under the covers, no data is actually being moved; it's just reassigning the partition_id of PartitionTable2 to the the NonPartitionTable object_id.  If you want to really see the undercover action, then you can run this script before and after the SWITCH command to see the 2000 rows of data never leave the same partition_ids. Our data has never left partition_id 72057594040156160.

SELECT

     o.name

    ,o.object_id

    ,p.index_id

    ,p.partition_id

    ,p.partition_number

    ,p.rows

FROM sys.objects o

    JOIN sys.partitions p ON o.object_id = p.object_id

WHERE o.name IN ('PartitionTable2','NonPartitionTable')

ORDER BY o.name,p.partition_number;

GO

Before:

After:

Now that all the data has been moved to the temporary table, we can drop PartitionTable2 and rename the temporary table back to the original name.

-- Drop the partitioned table.

DROP TABLE PartitionTable2;

GO

-- Rename the temporary table to the original name.

EXEC sp_rename 'dbo.NonPartitionTable', 'PartitionTable2', 'OBJECT';

GO

At this point the PartitionTable2 is no longer partitioned.

Partitioning has now been completely removed from both PartitionTable1 and PartitionTable2.  We can drop the remaining parts (partition schema, partition function,files, and filegroups) of partitioning to complete the clean up.

-- Remove the partition scheme, function, files, and filegroups.

DROP PARTITION SCHEME myRangePS1;

GO

DROP PARTITION FUNCTION myRangePF1;

GO

ALTER DATABASE [PartitionTest] REMOVE FILE PartitionFile1;

ALTER DATABASE [PartitionTest] REMOVE FILE PartitionFile2;

ALTER DATABASE [PartitionTest] REMOVE FILE PartitionFile3;

ALTER DATABASE [PartitionTest] REMOVE FILE PartitionFile4;

GO

ALTER DATABASE [PartitionTest] REMOVE FILEGROUP PartitionFG1;

ALTER DATABASE [PartitionTest] REMOVE FILEGROUP PartitionFG2;

ALTER DATABASE [PartitionTest] REMOVE FILEGROUP PartitionFG3;

ALTER DATABASE [PartitionTest] REMOVE FILEGROUP PartitionFG4;

GO

What we're left with is a completely un-partitioned database, and all rows of data in each table completely intact. 

For further reading on table partitioning, see Books Online.