One of the most interesting and perhaps not well known features of the Database Engine Tuning Advisor (DTA) is that you can use it with a test server to tune the workload of a production server. As I mentioned on the first part of this post, the DTA relies on the Query Optimizer to make its tuning recommendations and you can use it to make these optimizer calls to a test server instance without impacting the performance of the production server.
Information Required by the Query Optimizer
To better understand how this works let us first review what kind of information the Query Optimizer needs to tune a workload. Basically the most important information it needs to perform an optimization is:
1) The database metadata (i.e. table and column definitions, indexes, constraints, etc.)
2) Optimizer statistics (index and column statistics)
3) Table size (number of rows and pages)
4) Available memory and number of processors
The DTA can gather the database metadata and statistics from the production server and use it to create a similar database, with no data, on a different server. This is called a shell database. The DTA can also obtain the available memory and number of processors on the production server, by using the extended stored procedure xp_msver, and use this information for the optimization process. It is important to remind that no data is needed for the optimization process. This process is summarized in the following figure taken from Books Online:
This process provides the following benefits:
1) There is no need to do an expensive optimization on the production server which can impact its resources usage. Production server is only used to gather initial metadata and the required statistics.
2) No need to copy the entire database to a test server either, which is especially important for big databases, saving disk space and time to copy the database
3) No problems where test servers are not as powerful as production server as the DTA tuning session will consider the available memory and number of processors of the production server.
Running a Tuning Session
Now I am going to show an example of how to run a tuning session. First of all, the use of a test server is not supported by the DTA graphical user interface so the use of the dta utility, the command prompt version of DTA, is required. Configuring a test server also requires an XML input file containing the dta input information. I am using the following input file for this example
<?xml version="1.0" encoding="utf-16" ?> <DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/dta"> <DTAInput> <Server> <Name>production_instance</Name> <Database> <Name>AdventureWorks</Name> </Database> </Server> <Workload> <File>workload.sql</File> </Workload> <TuningOptions> <TestServer>test_instance</TestServer> <FeatureSet>IDX</FeatureSet> <Partitioning>NONE</Partitioning> <KeepExisting>NONE</KeepExisting> </TuningOptions> </DTAInput> </DTAXML>
The Server and Database elements of the XML file include the production SQL Server instance and database. The Workload element includes the definition of a script containing the workload to tune. TuningOptions includes the TestServer subelement which is used to include the name of the test SQL Server instance.
Create the workload.sql file containing a simple query like this
SELECT * FROM AdventureWorks.Sales.SalesOrderDetail WHERE ProductID = 898
Run the following command
dta -ix input.xml -S production_instance -s session1
A successful execution will show an output similar to this
Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor com mand line utility Version 9.00.5000.00 Copyright (c) Microsoft Corporation. All rights reserved. Tuning session successfully created. Session ID is 26. Total time used: 00:00:03 Workload consumed: 100%, Estimated improvement: 96% Tuning process finished.
This example creates an entire copy of AdventureWorks (with no data) and performs the requested optimization. The shell database is automatically deleted after the tuning session is completed. Optionally you can keep the shell database, for example if you want to use it again on another tuning exercise, by using the RetainShellDB in the TuningOptions element like in the following XML fragment.
<TuningOptions> <TestServer>test_instance</TestServer> <FeatureSet>IDX</FeatureSet> <Partitioning>NONE</Partitioning> <KeepExisting>NONE</KeepExisting> <RetainShellDB>1</RetainShellDB> </TuningOptions>
If the shell database already exists when you request a tuning session, the database creation process will be skipped. However, you will have to manually delete this database when it is no longer needed.
Once the tuning session is completed you can use the DTA graphical user interface as usual to see the recommendations. To do this open the DTA, open the session you used by double-clicking its session name (session1 in our example) and chose the Recommendations tab if it is not already selected.
Although the DTA automatically gathers the metadata and statistics to build the shell database, I am going to show you how to script the required objects and statistics to tune a simple query. This can be helpful in cases where you don’t want to script the entire database. Scripting database objects is a fairly simple process well known by SQL Server professionals. Something that may be new for many though, is how to script the statistics. Created scripts make use of the undocumented STATS_STREAM, ROWCOUNT and PAGECOUNT options of the CREATE/UPDATE STATISTICS statement.
As an example to optimize the simple query shown previously try the following on Management Studio: Select Databases, right-click the AdventureWorks database, select Tasks, Generate Scripts …, click Next, select “Select specific database objects”, expand Tables, select Sales.SalesOrderDetail, click Next, click Advanced, look for the “Script Statistics” choice and select “Script statistics and histograms”. Finally chose True on “Script Indexes”. Your Advanced Scripting Options window should look similar to this:
Click Ok and finish the wizard to generate the scripts. You will get a script with a few UPDATE STATISTICS statements similar to this (with the STAT_STREAM value shortened to fit in this page).
UPDATE STATISTICS [Sales].[SalesOrderDetail]([IX_SalesOrderDetail_ProductID]) WITH STATS_STREAM = 0x010000000300000000000000000000004036000 …, ROWCOUNT = 121317, PAGECOUNT = 227
These UPDATE STATISTICS statements are used to update the statistics of existing indexes (obviously the related CREATE INDEX statements were scripted as well). If the table also has column statistics it will include CREATE STATISTICS statements instead.
Testing Scripted Statistics
Finally, I will show you an example of how to use the scripted statistics to obtain plans and cost estimates on an empty table. Running the following query on the regular AdventureWorks database creates the following plan with an estimated number of rows of 9 and a cost of 0.0296835.
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 898
Let us produce the same plan on an empty database. Following the procedure described before you can script the Sales.SalesOrderDetail table. You will end with multiple statements including the following (again shortened to fit on this post).
CREATE TABLE [Sales].[SalesOrderDetail]( [SalesOrderID] [int] NOT NULL, … ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ( [ProductID] ASC ) GO UPDATE STATISTICS [Sales].[SalesOrderDetail]([IX_SalesOrderDetail_ProductID]) WITH STATS_STREAM = 0x010000000300000000000…, ROWCOUNT = 121317, PAGECOUNT = 227 GO UPDATE STATISTICS [Sales].[SalesOrderDetail] ([PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]) WITH STATS_STREAM = 0x010000000200000000000000000000003C2F68F6…, ROWCOUNT = 121317, PAGECOUNT = 1237
Create a new database and run at least the previous four statements using the scripts you got on the previous step (or you can use the attached script on this post containing the statements needed to reproduce the example). After implementing the script on an empty database and running the sample query, you will get again the plan with cost 0.0296835 and estimated number of rows of 9.