We are working on ERP application with a SQL server 2008 R2 database in compatibility level 80. I’m working as SQL server DBA I want to make performance tuning against our database but I’m facing many obstacles because our application may not be compatible with higher compatibility level so I cant use DMVs which may help me to find the most expensive queries which is running frequently against our production database.
I tried to run SQL server profiler to extract workload file and run this trc file on database tuning advisor to explore it’s recommendation concerning our database, including index creation and SQL server statistics. I found many opinions said that do not blindly execute DTA recommendation.
I tried to run SQL server activity monitor to discover the most expensive queries and displayed it’s execution plan and I found also recommendations to execute non-clustered indexes.
My questions are:
How can I depend on DTA or execution plan to tune performance?
If I execute these recommendations (indexes) and I face regression on performance, could I drop it easily without any threats and will it be created automatically while Index rebuild operation or rebuild indexes operation drop and create the only existed indexes?
What are the best practices to make new indexes?