| Author |
Topic |
|
gregoryagu
Yak Posting Veteran
80 Posts |
 Posted - 2008-07-11 : 17:28:57
|
| When running the DB Tuning Advisor against a database, it is not actually replaying the workload, correct? I just want to make sure that if I run it against a production database, that it is not going to be making changes to the data.Greg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-11 : 17:49:43
|
| It does not replay the workload. But I would play it safe and do a backup/restore to a test environment and run the DTA there.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-11 : 18:14:36
|
quote:
Originally posted by gregoryagu
When running the DB Tuning Advisor against a database, it is not actually replaying the workload, correct? I just want to make sure that if I run it against a production database, that it is not going to be making changes to the data.Greg
No Unless you choose Apply Recommendation.It depends on how big is workload. |
|
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2008-07-14 : 11:42:30
|
| Thank you, I didn't realize that option existed.Greg |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-07-14 : 11:45:39
|
apply reccomendation does not replay the workload. what that does is create the indexes and statistics that DTA recommends. still I would not run DTA against a production database. sometimes the recommendations DTA makes are pretty silly, so you should have someone with experience review them before applying them blindly.create a backup as Tara suggests and apply the recommendations in a test system. then in test, see if the new indexes improve things.
elsasoft.org |
|
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2008-07-14 : 13:21:38
|
| Hi Jezemine,Thanks for the info. Can you give me an example of a recommendation that should not be accepted?Greg |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-14 : 13:31:50
|
| Often times the DTA will recommend that duplicate indexes be added. This affects DML performance.Here's an example of a duplicate:Index1 - Column1, Column2, Column3Index2 - Column1You wouldn't need to add Index2 as Index1 already covers it as the first column in the index. But if Column1 (and not all 3) is going to be the clustered index, then it's better to add both indexes as you wouldn't want Index1 to be the clustered index due to its size.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-07-14 : 14:01:00
|
once I had this table: col1, col2, col3, col4, etc... where col1 was the pk clustered asc.DTA suggested I add a non clustered index on col1 asc, with all the other columns added to the index as included columns. completely useless.
elsasoft.org |
|
|
|
gregoryagu
Yak Posting Veteran
80 Posts |
Posted - 2008-07-14 : 14:05:58
|
| Thanks very much Tara and Jezemine.Greg |
|
|
|
|
Database Tuning Advisor