Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question related to missing index DMV's ? Expand / Collapse
Author
Message
Posted Monday, September 02, 2013 12:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:09 PM
Points: 220, Visits: 536
This formula is given by microsoft to find out the index performance impact "user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )".
But what is the unit to measure it. And if there is no unit to measure it then how can we say that index needs to be created. Like suppose if this column returns more then 100 then index needs to be created or if this value exceeds 1000 then this index needs to be created?
Post #1490520
Posted Monday, September 02, 2013 2:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
It's not a unit and you cannot tell just from the missing index DMV that an index should or should not be created. It's just a place to start.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1490549
Posted Monday, September 02, 2013 9:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:09 PM
Points: 220, Visits: 536
Hmmm.......ok..........So you are telling me that this missing index dmv's are useless .
Post #1490736
Posted Monday, September 02, 2013 11:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:49 AM
Points: 187, Visits: 699
I would not call it useless. I personally like to have a look at the columns "user_seeks" and "last_user_seek". High numbers are an indicator that I should have a look at the queries that use this table, these queries might run slower than they could.
Post #1490753
Posted Tuesday, September 03, 2013 12:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:47 AM
Points: 41,525, Visits: 34,442
Shadab Shah (9/2/2013)
So you are telling me that this missing index dmv's are useless .


No. I didn't say they're useless. I said they're a place to start.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1490762
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse