SQL Server Statistics Only Database

Hi Geeks,

First of all I would like to thank Amit Sir and SQLServerGeeks.com for giving me this opportunity to blog.

This is my 1st blog on SQL Server Statistics Only Database, so I am pretty much excited about it, hope you all will like it.

In this blog, I will show you a technique of re-producing a production server execution plan on dev or test environment without replicating real data. Let me explain: On your production box, you have millions or perhaps billions of records in your table and you are running dozens of queries against that table. Of course, for each query you have an execution plan. You want to generate the same execution plan for tuning or query performance investigation purposes, but on your dev or test box. You don’t needs to import the entire table or millions of rows into your dev/test box. You can only create a statistics-only database and mimic the same query plan behavior. Yes, no user/real data is required.

What is Statistics-Only Database?

As the name suggest it’s a database that contain statistics of the database but no real user data. Yes you read it right “No Real User Data”. This is how you can create a statistics-only database:

How to create a statistics only database?

1. Open SQL Server Management Studio.
2. In the Object Explorer, expand Databases, and then locate the database that you want to script.
3. Right-click the database, point to Tasks, and then click Generate Scripts.
4. In the Script Wizard, verify that the correct database is selected. Click to select the Script entire database and all objects to script radio button, and then click Next.
5. In Output Type select Save scripts to a specific location radio button.
6. Click on Advanced and Choose Advanced Scripting Options dialog box, change the following settings from the default value to the value that is listed in the following table.

Scripting option                                           Value to select
Ansi Padding                                                                     True
Continue Scripting on Error                                        True
Generate Script for Dependent Objects                  True
Include System Constraint Names                           True
Script Collation                                                                 True
Script Database Create                                                   True
Script Logins                                                                      True
Script Object Level Permissions                                True
Script Statistics                                                               Script Statistics and histograms
Script Indexes                                                                   True
Script Triggers                                                                   True

7. Note The Script Logins option and the Script Object Level Permissions option may not be required unless the schema contains objects that are owned by logins other than dbo.
8. Select Save to file radio buttonand thenselect Single file radio button. In File name select the location where the output will be generated and Save as will be Unicode.
9. Click Next.
10. Click Next.
11. Click Finish.

Note: Generation of scripts of huge database with complex subsystems will created load on the system so do this in non-peak hours.

Demo:

I have created a simple database with one table having 15 records.

use [master]
go
CREATEDATABASE [PWI_TESTDB]
 CONTAINMENT =NONE
ONPRIMARY
( NAME =N'PWI_TESTDB',FILENAME=N'E:\temp\PWI_TESTDB.mdf', SIZE = 10240KB , FILEGROWTH = 1024KB )
LOGON
( NAME =N'PWI_TESTDB_log',FILENAME=N'E:\temp\PWI_TESTDB_log.ldf', SIZE = 1024KB , FILEGROWTH = 10%)
GO
 
--Create table testtab
 
use [PWI_TESTDB]
go
createtable testtab(id int, name varchar(50), dept varchar(10), sal int);
go
	 
	 
--Insert data into the testtab
 
insertinto testtab values(1,'debjeet','acc',10000);
go
insertinto testtab values(2,'abhisek','acc',20000);
go
insertinto testtab values(3,'arup','it',30000);
go
insertinto testtab values(4,'sumit','hr',10000);
go
insertinto testtab values(5,'anil','it',50000);
go
insertinto testtab values(6,'rahul','it',80000);
go
insertinto testtab values(7,'suhasini','acc',60000);
go
insertinto testtab values(8,'ramol','it',70000);
go
insertinto testtab values(9,'sudesh','it',20000);
go
insertinto testtab values(10,'debjeet','hr',30000);
go
insertinto testtab values(11,'mahek','it',23000);
go
insertinto testtab values(12,'amol','it',90000);
go
insertinto testtab values(13,'debjeet','it',50000);
go
insertinto testtab values(14,'susmit','it',13000);
go
insertinto testtab values(15,'komal','it',44000);
go

When I run the following query (actual execution plan turned on), observe the cardinality estimation:

SELECT name FROM testtab 
WHERE name ='debjeet';
GO

Note: Actual Number of Rows = 3 and Estimated Number of Rows = 3

Now, I have scripted out this database (using the technique mentioned above) and created it with another name pwi_statdb. This database only contains the objects, metadata & statistics but no user data. Here is what I executed to create this new stats-only database:

USE [master]
GO
CREATEDATABASE [PWI_STATDB]
 CONTAINMENT =NONE
ONPRIMARY
( NAME =N'PWI_STATDB',FILENAME=N'E:\temp\PWI_STATDB.mdf', SIZE = 10240KB , MAXSIZE =UNLIMITED, FILEGROWTH = 1024KB )
LOGON
( NAME =N'PWI_STATDB_log',FILENAME=N'E:\temp\PWI_STATDB_log.ldf', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTERDATABASE [PWI_STATDB] SETAUTO_CREATE_STATISTICSOFF
GO
ALTERDATABASE [PWI_STATDB] SETAUTO_UPDATE_STATISTICSOFF
GO
 
--Create table testtab with no records in it
 
USE [PWI_STATDB]
GO
CREATETABLE [dbo].[testtab](
    [id] [int] NULL,