Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

Use Database Engine Tuning Advisor on SQL 2008

To use DTA in SQL 2008, I need to generate a workload using SQL Server Profiler. Normally, what kinds of event should I capture on the file. How long should I take on the production server and will it affect the performance ?

Is it enough to capture "SQL:Batch Completed" & "RPC:Completed" ?  Or I need to include much more detail "sp:StmCompleted" and "sp:StmStarting" as well.

After capturing the file, if I load the file on other testing sql server and run the DTA, will the result reflect the real situation on the production sql server ?

Tks
0
AXISHK
Asked:
AXISHK
  • 2
2 Solutions
 
David ToddSenior DBACommented:
Hi,

I can't really answer your questions directly, but I will say this about the tuning adviser: Take its recommendations with a grain of salt, and verify them.

Not knowing any better, I didn't run it just as and put too much into the parameters, and it stripped out many indexes, and performance degraded instead of improved.

Regards
  David
0
 
ValentinoVBI ConsultantCommented:
As David said: be careful with the recommendations. It's an Advisor, not a Magician.  So you need to understand its recommendations and use your own judgement to decide if it's a good idea or not.

Now, as for your question I believe you'll find the following a good read: Fundamentals: Creating a Workload by Using a Trace Log

Basically there are two ways to run the trace: using the built-in Tuning template or building your own.  The Tuning template has following events checked:

SP - RPC:Completed
SP - SP:StmtCompleted
TSQL - SQL:BatchCompleted

The lightest trace you can use is by just checking one event:

TSQL - SQL:BatchCompleted

Once you've got your trace file you're ready for the next step: Fundamentals: Running Database Engine Tuning Advisor and Selecting Indexes
0
 
ValentinoVBI ConsultantCommented:
After capturing the file, if I load the file on other testing sql server and run the DTA, will the result reflect the real situation on the production sql server ?

It is indeed a good idea to use a test server because DTA will put a huge load on the production server otherwise.  In any case: don't run the analysis during business hours.

The following article contains details on how DTA uses a test server and how you can run an analysis that way: Reducing the Production Server Tuning Load

Through that method it will create a shell database on the test server and run the analysis on that server while the recommendations are applied to the production server.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now