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 |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-04-09 : 09:40:59
|
| If you have ideas or links:1. I write a simple query that certainly has a missing index. It does not appear in sys.dm_db_missing_index_group_stats. I read somewhere that simple queries are overlooked in these DMVs. Any idea what constitutes a simple/ignored query in this situation? Any idea why simple queries would be overlooked in the first place?2. Anyone with an opinion on whether or not these DMVs are helpful?3. Isn't it possible these DMVs would make performance suffer (ie. the time it takes SQL Server to analyse this information every time a query is run)? |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-04-09 : 18:18:24
|
quote:
Originally posted by denis_the_thief
If you have ideas or links:1. I write a simple query that certainly has a missing index. It does not appear in sys.dm_db_missing_index_group_stats. I read somewhere that simple queries are overlooked in these DMVs. Any idea what constitutes a simple/ignored query in this situation? Any idea why simple queries would be overlooked in the first place?
Just a single query does not necessarily make it to the missing indexes DMV list. The query has to have run a few times with considerable IO/CPU cycles consumed relative to the system resources. quote:
2. Anyone with an opinion on whether or not these DMVs are helpful?
These DMVs are definetely helpful. But dont just go by this list. The DMVs get refreshed everytime the server is bounced. Also, if you are really trying to capture missing indexes I would recommend collecting server side traces at different times depending on your workload and use the Index Tuning Advisor along with DMVs to create the necessary indexes. As much tempting it can be to just create indxes on as many columns they can soon become an overhead and tax the INSERT/UPDATE queries since the stistics need to be calculated when the data changes. quote:
3. Isn't it possible these DMVs would make performance suffer (ie. the time it takes SQL Server to analyse this information every time a query is run)?
The DMVs have slight overhead depending on the hardware, system usage. There isnt anyway to disable them. I have not heard of anyone complaining about DMVs bringing the system down or consuming too many CPU cycles. Their benefits certainly outweigh their cost.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
|
|
|
A few questions regarding Missing Index DMVs