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.
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, [name] [varchar](50)NULL, [dept] [varchar](10)NULL, [sal] [int] NULL )ON [PRIMARY] GO --Update the stats for empty table testtab UPDATESTATISTICS [dbo].[testtab] WITHROWCOUNT= 15,PAGECOUNT= 1 GO
Following things to observe in the above code:
1. I have turned off auto create stats and auto update stats
2. No user data is entered in to the table
3. I have updated the statistics of the table with Update STATS command (notice ROWCOUNT & PAGE COUNT) (this is automatically generate by Generate Scripts Wizard)
4. Note that there is not stats for column ‘name.’
Now, I execute the same query on the new stats-only db:
USE [PWI_STATDB] GO SELECT name FROM testtab WHERE name ='debjeet'; GO
And, you can see the following execution plan:
Note: Actual Number of Rows = 0(Because no user data is there in the table) and Estimated Number of Rows = 7.62199
This is the default selectivity of the optimizer. But what happed to the previous estimated number of rows = 3? We will get that only when we create statistics stream for column ‘name’ that was scripted out by the wizard. Here it is:
Now, if you run the same query again, this is the execution plan:
So, here it is: A statistics only database that contains the objects, metadata, statistics but no user data. And you can mimic the same query plan for tuning. Monitoring and/or any other query plan performance investigation purposes.
I recently learnt this technique and wanted to share with the SQL community. Hope you enjoyed.