Solved

Use Database Engine Tuning Advisor on SQL 2008

Posted on 2014-01-16
3
522 Views
Last Modified: 2014-01-17
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
Comment
Question by:AXISHK
  • 2
3 Comments
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 200 total points
ID: 39787574
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39787752
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 300 total points
ID: 39787769
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Addition to SQL for dynamic fields 6 56
ms sql + get number in list out of total 7 44
(sql serv16)ssis 2016 question/check 1 101
Need return values from a stored procedure 8 40
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question