Do you advise to apply Recomendations given by DTA?

Question

Do you advise to apply Recomendations given by DTA?

SqlServerLover

Ten Centuries

Points: 1348
More actions
August 20, 2010 at 5:03 pm

#226483

Hello Guys,
I have a question.
When i analyze the workload captured from profiler, DTA shows me bunch of recomendations and sometimes estimated improvement is almost 90%.
Most of the recomendations are to create statistics and few of them are to create non-clustered indexes.
Do you advise to create those additional statistics and indexes?
Are there any drawbacks of applying the recomendations given by DTA ?
How should i proceed further ? I have captured data in profiler using standard tuning template.
Thanks

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SqlServerLover (8/20/2010)
Do you advise to create those additional statistics and indexes?

I advice people to test those recommendations carefully, and then apply only the ones that make a noticeable improvement to the performance. DTA is far too generous in its recommendations, often recommending way more indexes and stats than are actually needed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

Ditto on Gails recommendation. It will many times recommend indexes that don't do anything for performance at all. Test for performance after any index addition/change.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SqlServerLover Ten Centuries Points: 1348 More actions · Answer 3

Thanks Gail and Jeff,

I have one more question now...

So do i have to apply those indexes in test and find out if performance has improved or not by running those specific query or stored proc and checking their time to complete before i apply those in production ?

Is there any other way to see the percentage of improvement after aditional indexes ?

WayneS SSC Guru Points: 95460 More actions · Answer 4

I thing that I've noticed from the recommended indexes, is that there will be some very similar indexes.

i.e. it might recommend both of the following indexes

CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[MyTable] (Col1 ASC) INCLUDE (Col2, Col3);

CREATE NONCLUSTERED INDEX [IX2] ON [dbo].[MyTable] (Col1 ASC, Col2 ASC) INCLUDE (Col3);

Obviously, the second one will handle the first one also.

Another example:

CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[MyTable] (Col1 ASC) INCLUDE (Col2, Col3);

CREATE NONCLUSTERED INDEX [IX2] ON [dbo].[MyTable] (Col1 ASC) INCLUDE (Col3, Col4);

In this case, it might be better to create a third one altogether that combines both:

CREATE NONCLUSTERED INDEX [IX3] ON [dbo].[MyTable] (Col1 ASC) INCLUDE (Col2, Col3, Col4);

What I like to do is to take the output, dump it into a spreadsheet, sort by the commands, and then look for the dupes. It's a pretty good bet that if it finds several based on the same column and with minor differences like shown above, that there are several queries out there that would benefit from adding it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SqlServerLover Ten Centuries Points: 1348 More actions · Answer 5

Thank you. Your explanation is really good. I understand it. I really appreciate your help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

SqlServerLover (8/21/2010)
So do i have to apply those indexes in test and find out if performance has improved or not by running those specific query or stored proc and checking their time to complete before i apply those in production ?

How else would you tell if performance has improved if you don't run the queries and compare the before and after performance?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

GilaMonster (8/22/2010)
SqlServerLover (8/21/2010)
So do i have to apply those indexes in test and find out if performance has improved or not by running those specific query or stored proc and checking their time to complete before i apply those in production ?
How else would you tell if performance has improved if you don't run the queries and compare the before and after performance?

I agree with what Gail said above. Some will tell you that you can do a before'n'after on execution plan costs using estimated execution plans. Sometimes that works but sometimes it doesn't and, until you run the code for real, you just won't be able to tell for sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (8/22/2010)
Some will tell you that you can do a before'n'after on execution plan costs using estimated execution plans. Sometimes that works but sometimes it doesn't and, until you run the code for real, you just won't be able to tell for sure.

Because the costs in the plans are estimates (even on the actual plans) and if any of the estimations that the optimiser's using are wrong, so will those costs be. Hence you can use them, but shouldn't rely on them.

I've seen cases where a plan that's cheaper according to the optimiser had a higher execution time than a plan that was more expensive (and I'm not talking about cases where there was parameter sniffing or stale statistics)

Edit: I really shouldn't try typing on a Sunday evening

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

So if you can't trust the plans, can you trusts the ms, reads, writes, cpu... from the profiler 'cause that's pretty much the data I ended up trusting after a while?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Ninja's_RGR'us (8/22/2010)
So if you can't trust the plans, can you trusts the ms, reads, writes, cpu... from the profiler 'cause that's pretty much the data I ended up trusting after a while?

Yes, because those aren't estimates. They are actual measured results. The query ran, it did so many reads, so much CPU time, so many ms to run. Actual, hard data based on an actual execution of the query.

The costs in the execution plans aren't measured data. They are estimated costs, showing how expensive the optimiser thinks, based on the data available to it at optimisation time and based on the algorithms it uses, the query will be. The key work here is estimated.

It's the difference between me saying that I think it will take me x minutes to run a kilometre vs running the kilometre and measuring the time taken.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Thanks Gail, now 1 follow up question : Since I can't seem to find the option to get the details per table for reads in profiler (please point it out if I'm missing something), I'd like a confirmation that set statistics IO is also based in real data rather than estimates.

TIA.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Sure they are. They're the actual reads done on per table. You won't get exactly the same values as profiler, can't remember clearly why, but differences are only small.

The distinction is when the values are generated. Exec plan costs - generated at optimisation time, i.e. before the query is run. Profiler, statistics IO, extended events, sys.dm_exec_query_stats, others that I've forgotten - generated during/after the query was run.

Again, distinction between me estimating before a run how long it will take and measuring during and after the run exactly how long it took.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 13

I'm pretty sure that statistics TIME and IO don't include SQLCLR and I believe they sometimes miss what's happening in UDF's that a query may contain. I'll have to double check and see if I can come up with an actual example, but I wanted folks to know that they do seem to miss things on occasion (for me, anyway).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Jeff Moden (8/22/2010)
I'm pretty sure that statistics TIME and IO don't include SQLCLR and I believe they sometimes miss what's happening in UDF's that a query may contain. I'll have to double check and see if I can come up with an actual example, but I wanted folks to know that they do seem to miss things on occasion (for me, anyway).

Ya there's definitly something missing (certainly in the plans) for functions.

The only thing I have in mind to test this is to run profiler and include statment completed events for both sql and rpc. That will show all the function calls and they seem to have the reads info as well (never had this occur with function that reads from a base table... but that's an easy test to setup if you feel like it. Then you simply have to compare both read totals from statistics IO and profiler).