Solved

Use Database Engine Tuning Advisor on SQL 2008

Posted on 2014-01-16
3
525 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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