4

I have a SQL Server 2008R2 OLTP database which is experiencing performance issues during work hours. I would like to run Database Engine Tuning Advisor on this instance but I don't know how it will impact the instance itself.

When can I use this tool and are there any risks involved (Is it possible to overload the DB or significantly impact the environment)?

EDIT: I have identified a few issues with the environment and came up with a few ideas to test.

I would like to further the question here: If it is generally unadvisable to use DETA in production, when can and should it be used? Microsoft created this tool for a reason. If it may help DBAs and developers, how, when and why should we use it?

Improve this question

2 Answers 2

Reset to default
4

Don't go to DTA. It can have severe performance impacts in production and can really do more harm than good (I learned that lesson the hard way when I started out as a DBA).

The first step is to find out what exactly is causing your performance issues. Is it a CPU issue? Memory? Improper indexes? Also, how do you know you are experiencing a performance issue? Are users complaining of slowness? Are you seeing CPU spike high and stay there? What about memory? Is Page Life Expectancy dropping often and you can't explain why? These are the questions you need to ask. You need to find out where it hurts first, then go from there.

So, to answer your question, no you shouldn't use DTA [in production, during business hours].

EDIT:

This answer may help you as well: Should I rely on Database Engine Tuning Advisor for creating indexes?

The important thing to realize is that just because DTA recommends something, there is still ALOT to be said for using your knowledge of the DBs and data in conjunction with it. Same goes for the wonderful Missing Index Warnings. Can DTA get you started? Absolutely, but there's much more to consider when making changes that it recommends and the performance impact of running it in production.

I, personally, have used it less than 5 times in my career. I try to use DMVs/queries in conjunction with our 2 monitoring solutions to troubleshoot performance issues.

Improve this answer
1
0

Don't do it.

First eliminate the obvious - it's not bad drive on the SAN, there isn't filegrowth happening, indexes aren't fragemented badly... etc.

This is a last gasp effort from a desperate man... you're not that guy :)

Edit (append): Fair enough comment: Elaborate :)

As DBA I mistrust running processes on an already labouring server that are intensive, and not 100% certain to provide telling information solving the situation.

You describe scale symptoms: when in use it's slow; it's fast in light load. Have you considered WHAT is slowing it down: Are your workloads consuming more IO your disks supply; are they using more CPU your server presents to the instance?

I am happy to see you asked "Should I run" this in prod; and learnt from others' experience.

Check this for assessing which queries hurt most (CPU, IO) http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

See if any optimisation is possible for those hurting most; see if you need to spread the IO load across multiple files / drives, or add indexes to reduce IO (scans)...

Our community is stronger for those innovators sharing their solutions (Adam, Brent, Ola). Standing on the shoulders of giants, we reach higher.

Improve this answer
1
  • Perhaps you could explain a little more on why you don't recommend the Tuning Advisor. 'Don't do it' is low on reasons. (It is true that I have not found the Tuning Advisor to be very useful, but this is your answer.)
    – RLF
    Commented Dec 16, 2014 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.