Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2010-09-14 : 18:31:06
|
| Just looking for some clarification on what "database for workload analysis" means. I used SQL Profiler to capture a pretty substantial trace on our production database. In the database engine tuning advisor I selected that trace and because I don't want to run the analysis against production directly I created a backup and restored it to a database called "PerfAnalysis". Do I pick "PerfAnalysis" for both "database for workload analysis" and "select databases and tables to tune"? I did this and it told me to delete almost every index I have (about 50) and to only create 4. This struck me as very strange. Is it because I am running it against a backup and not the actual production database? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-09-14 : 18:51:12
|
| That's correct that you point it to your restored database. Do not point it at production as it'll impact production.The DTA is insane for what it recommends. I never use it as it makes bad decisions in my opinion. It recommends way too many duplicates or near duplicates and only makes the recommendation based upon the workload, so you have to ensure that your workload is comprehensive of every aspect of your system. Instead I use the DMVs to decide what indexes I need and which one I don't. See this: http://sqlserverpedia.com/wiki/Index_Related_DMV_QueriesBe very careful with the results. You need to do thorough analysis on it and ensure that SQL Server has been up for a while when the report is run. The DMV reports provide similar information to DTA, however I prefer the control of the DMV data.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Database for workload analysis