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 2, 2013 12:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:40 PM
Points: 248, Visits: 569
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 2, 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: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2, 2013 9:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:40 PM
Points: 248, Visits: 569
Hmmm.......ok..........So you are telling me that this missing index dmv's are useless .
Post #1490736
Posted Monday, September 2, 2013 11:46 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:31 AM
Points: 194, Visits: 717
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 3, 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: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
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