Sdílet prostřednictvím

SQL Server Performance Tuning: Creating a Shell Database to tune your Production queries

  • Článek

On so many occasions have we encountered situations wherein during a performance issue on Production , we struggled to mimic or reproduce the issue on another environment. Some of the usual steps we follow, to debug these type of issues are :

 

  1. Request for a Read Access with permissions to access the DMVs on Production.
  2. Request for a Production Backup and restore it on a different environment to analyze.
  3. Run Extended Events / SQL Profiler on the Production Databases.

 

These methods work and are quite fruitful too. But they can sometimes be time consuming (specially if we end up asking for a backup of the database) or could be less flexible to test out new things (if we want to create a new index) . Are there any quick ways? Sure is.

 

One of the very interesting yet a less known feature SQL Server offers is that you can script out all the necessary metadata, statistics that a SQL Optimizer uses (to optimize a query) , and load it in another server to do the analysis. Some benefits are :

 

  1. The DBA is happy that you will not run excess load on production servers to tune the queries
  2. There is no need to copy backup of huge databases across networks from production to test environments, thereby saving valuable time.
  3. There is no need to figure out as big a disk space on your local environment as it is in Production.
  4. In event of databases having sensitive information, there is no compromise as there is no movement or copying of data to another environment.
  5. New indexes created on the shell database also have their statistics updated as per the stats available on other indexes in the table.

 

Just to note that this is absolutely not a panacea for creating an environment for doing all forms of performance tuning. It serves a purpose where we want to tune queries based on the statistics currently available on Production. 

 

Let's see how this can be done.

 

Step - 1 : Right Click on the Database , go to Tasks -> Generate Scripts

 

 

Step - 2 : On the Generate and Publish Script Window, select the objects you want to script out.

 

Step - 3 : On the Set Scripting Options Tab of the Window, provide a location to where the file can be generated and then  click on the Advanced Button 

 

 

Step - 4 : On the Advanced Scripting Objects, change the default values of the given properties to values mentioned below

 

Property

Value

Script Statistics

Script Statistics and Histograms

Script Indexes

True

 

Once Done, click on Finish to generate the script. This script may now be used to re-create the objects on a different environment.

 

Since I am performing the activities on the same machine, I have edited the script to change the database name from AdventureWorks2012 to AdventureWorks2012Shell.

 

Now, assuming a query (like the one below) is causing some performance challenge on production.  This can now be run on this newly created database for analysis. One can observe that although there are no records returned, it uses the statistics from our source Database (Look at the Estimated Number of Rows Property), like so.

 

 

 

Now as part of the analysis, we would like to validate if creating a new index will help remove the clustered index scan. Let's go create one on column ModifiedDate.

 

Running the same query again, shows that the SQL Optimizer chose to use the newly created index. This way, we can validate usage of new indexes based on the statistics on our Production Server.

 

 

Thanks