July 22, 2009 at 10:24 pm
Hi,
for increasing performance of a set of queries we are creating indexes in our database ,
for one db we had data in tables after tat we created indexes,statiscal info is ter for it ,n we created another copy of db,we have indexes in this db,but no statiscal info(date ,density etc) null,do i need to update statis for all tables in new db?wil these indexes be used by queries in new db,
n one more thing we have around 20 queries,is there any method to check if indexes is used by these queries other then the method by lookin at the execution plan becoz generating execution plan n searching for indexes wil b difficult
July 23, 2009 at 8:09 am
If I understand the question, you do need to make sure that the statistics are updated on the second database. usually just leaving the auto update statistics set to on, the default, is enough.
You can query the plan cache, sys.dm_cached_plans and the plan itself, sys.dm_query_plan, to see if indexes are used within an execution plan, rather than running the queries and trying to capture the plan. Also, you can query the index usage stats, sys.dm_db_index_usage_stats, to see if the indexes are being used.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2009 at 8:43 am
All indexes have statistics associated with them and those stats cannot be dropped. If you're manually creating stats, it's unlikely you'll get any benefit creating stats on the same column as an index is created on.
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply