SQL Tuninig recommendation page

Question

SQL Tuninig recommendation page

zi

SSCrazy

Points: 2387
More actions
February 17, 2013 at 2:41 am

#270238

Dear Experts
How to read the recommendation page in sql tuning,
I mean what should I do
ex : the attached picture
Thanks lot

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Test the indexes out, one by one. If they help performance keep them. If they don't, drop the index and ignore the recommendation.

Never run DTA on a production server.

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

zi SSCrazy Points: 2387 More actions · Answer 2

Thanks for replying

what do you mean by test out index and how?

How to know that this index inhance performance or not

why never run Database Tuning Advisor on a production server

Thanks lot

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

Create the index, run your benchmark (the one you set up starting any tuning work), see if there's an improvement in performance.

As for why not on a production server, load, impact, side effects. Production servers are for production usage, for users. Not for dev and testing, that's what dev and testing servers are for.

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

zi SSCrazy Points: 2387 More actions · Answer 4

Still dont understand how to measure performance after creatiing index

and if the index is already exist, should I drop it to see the difference

Thanks

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

Measure query performance before you create the index. Record the stats (and you should have some idea what queries you're tuning)

Add the index

Measure query performance again.

Compare.

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

zi SSCrazy Points: 2387 More actions · Answer 6

Do you mean to see how much time the query has spent befor and after the index

Thanks

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

Not just time, all performance characteristics. You need to have a baseline before you start tuning, or how do you know whether your work has been effective or not?

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