Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Reap the Benefits of SQL Server's New Database Tuning Advisor : Page 3

The Database Tuning Advisor (DTA) is an improvement over the old Index Tuning Wizard. Learn how to access the advanced features of DTA from the command line with a custom configuration.


advertisement

Configuration and Enabling Remote Connections

As an aside, under the old model, a user inherited permissions implicitly, whereas under new model requires explicit actions before allowing remote connections and grants. Figure 3displays the set up screen for server configuration, followed by the client set up. Books Online explains: "In Microsoft SQL Server 2005 Express Edition, Developer Edition, and Evaluation Edition, remote connections are disabled by default. In other editions, remote connections are enabled by default."

Click to enlarge

Figure 3. Enabling TCP/IP on the Server



On the server side of the communication, the Net-Libraries—DLLs that enable communication between the OS and the database—are part of the database engine. On the client side, the Net-Libraries are part of the SQL Native Client. Net-Libraries are not directly configured. Instead, the server and the SQL Native Client are configured to use a network protocol. Then, the database engine and the SQL Native Client automatically use the appropriate Net-Library settings. This state is established using the configuration manager or surface area manager (see Figure 4).

Click to enlarge

Figure 4. Enabling TCP/IP on the Client

Now I have a test plan, a data set, and a configuration ready to go. All I need is a work file against that data set for DTA to evaluate. When evaluating different query formulations, it does not hurt to include the following block at the top of your code:

Set statistics profile on -- gives you a show plan text Set statistics time on -- gives you server and client elapsed time in ms Dbcc freeproccache -- blows away any compilation in cache for your tests Issue your query -- works the gears… Dbcc show_statistics "your_table_name," "your_index_name"

When you issue dbcc show_statistics "your_table_name," "your_index_name", you can see the selectivity (or density) of the index recommendations. This measures the index usefulness and the number of boundary rows and tied values. It also gives a great view of the dispersion of values through the data. It is a kind of canned data analysis, but it is all done for free.

I created a work file from the sample data to exercise a singleton select, a range select, and an aggregate. Here are the examples from the work file:

use adventureworks go --an aggregate select cast(sum(actualcost) as decimal(9,2)) as sumcost, sum(quantity) as sumqty, b.[name] from production.transactionhistory a join production.product b on a.productid = b.productid join sales.salesorderheader c on c.salesorderid = a.referenceorderid where transactiondate between '2003-10-01 00:00:00.000' and '2003-10-31 00:00:00.000' group by month(transactiondate), year(transactiondate), b.[name] order by 1 desc,2 desc --a range select TransactionID, a.ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, b.name from production.transactionhistory a join production.product b on a.productid = b.productid join sales.salesorderheader c on c.salesorderid = a.referenceorderid where transactiondate between '2003-10-01 00:00:00.000' and '2004-04-30 00:00:00.000' order by TransactionDate, TransactionID, ReferenceOrderID, ReferenceOrderLineID, ProductID --a singleton select TransactionID, a.ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, b.name from production.transactionhistory a join production.product b on a.productid = b.productid join sales.salesorderheader c on c.salesorderid = a.referenceorderid where transactionid = 119639 order by TransactionDate, TransactionID, ReferenceOrderID, ReferenceOrderLineID, ProductID

Using the workload and entering through the GUI, I got the result shown in Figure 5.

Click to enlarge

Figure 5. Feed the File Name for the Work File into the Dropdown

If you select Export from the File menu, you save the session you created into an XML file. I did, and used that to enhance its definition. MSDN has published a usable cheat sheet for this operation, as well as the template to work from.

To work out an example that will help carry this idea along, invoke DTA from the command line with the bare minimum of arguments and specify an XML input file for "what if?" analysis. After copying this sample into your editing tool, replace the values specified for the Server, Database, Schema, Table, Workload, TuningOptions, and Configuration elements with those for your specific tuning session. (For more information about all of the attributes and child elements you can use with these elements, see the XML Input File Reference (DTA).)

This XML Input File Samplefrom MSDN uses only a subset of available attribute and child element options. The command line equivalent would look something like this:

C:\Documents and Settings\drew.DEVBOX>dta -S devbox -s 20060503DrewB -D Adventureworks
-if DTAWorkload2.sql -ix MyAlternatives.xml

In the Final Analysis: DTA Is Valuable

DTA provides two significant benefits. First, it enables you to know which of several alternate index formulations will perform best, because it runs analysis on a test machine using a production server's metadata. Secondly, you can entertain lists of related nested attributes to test in the same session, whether that list consists of more than one database or more than one variety of indexes (only non-clustered, all indexes, or all indexes and indexed views).



Drew Georgopulos is a Technical Lead for Collaborative Consulting in Woburn, Massachusetts. He has more than 20 years' experience in database programming and design. Using SQL Server and its related technologies, Drew helps organizations meet their goals by creating ETL solutions that build data quality into their business intelligence processes.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap