Solved

Use Database Engine Tuning Advisor on SQL 2008

Posted on 2014-01-16
3
516 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retention Policy for Backups 1 25
Usage Scenarios for Extended Events? 1 25
email the result out from a T-SQL queries 29 62
Managing Columnstore Indexes 2 15
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now