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

Missing Indexes By Index Advantage Expand / Collapse
Author
Message
Posted Thursday, December 23, 2010 6:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 24, 2010 2:17 AM
Points: 0, Visits: 3
Comments posted to this topic are about the item Missing Indexes By Index Advantage
Post #1038719
Posted Thursday, December 23, 2010 11:14 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:27 AM
Points: 717, Visits: 689
If this is SQL Script Manager compatible, where is the .rgtool file to download?

Edit: I found it, but boy that was unclear.
Post #1038900
Posted Friday, April 8, 2011 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 16, 2013 4:17 PM
Points: 8, Visits: 25
From all appearances this is Glen Berry's missing index query, with the helpful rgtools download capability.
Thank you for making it available! Glen is also known as Dr. DMV. If you want other great DMV queries, check out his SQL Server 2008 and R2 Diagnostic Information Queries.
Post #1090630
Posted Wednesday, June 22, 2011 3:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:23 AM
Points: 17, Visits: 148
Hi,
Small question, at which point should we considere that the [index_advantage] is high, and that it need urgently a index .

I just arrived a DBA, and they never had a real DBA (), so i'm reviewing some server and I see some number very high =>>> 11409912,6372924--- 5496054,50928026, ....

thanks.
Post #1129528
Posted Wednesday, June 22, 2011 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 16, 2013 4:17 PM
Points: 8, Visits: 25
The number one factor is frequency. If a query or stored proc that would use this index is run hundreds or even thousands of times a day, an index that will give even a small amount of speed advantage, is worthwhile.
Another factor is amount of advantage. If you have a query that's run just once a month, like for an end of month report, that takes 12 hours to complete and runs into the next workday, I've seen the right index change that to a few seconds, and is worthwhile.
Think size - a small table of a few hundred rows that won't be growing doesn't need as much indexing attention as compared to a table with millions of records. Small frequently accessed tables might already be loaded in memory and an index won't help at all.
Yet another factor, toward the other direction, is that you don't want to over-index a table - it slows performance when performing inserts, updates and deletes, requires maintenance and takes up space. In general, even a highly used table shouldn't have more than 10 indexes. Unless it's a data warehouse table or strictly used for reporting (i.e., there's very little addition and no change to the data) - then you might find it beneficial to have lots of indexes.
These are some of the major factors - enough for a start. Give that one you found a try!
Post #1129635
Posted Thursday, June 23, 2011 2:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:23 AM
Points: 17, Visits: 148
Thanks for the info,
Does it exist a table/query that cann tell us if a table is more used for select or insert/update/delete ??

my small contribution for this great query, a collumn which will generate the create index statement


 'CREATE NONCLUSTERED INDEX IX_'+replace((right (mid.[statement],len (mid.[statement])-
charindex (']',mid.[statement],charindex (']',mid.[statement],0)+3)-2)) ,']','')
+isnull(replace (replace (replace (mid.equality_columns,'[','_'),']',''),', ',''),'')
+isnull(replace (replace (replace (mid.inequality_columns,'[','_'),']',''),', ',''),'')
+isnull(replace (replace (replace (mid.included_columns,'[','_'),']',''),', ',''),'')
+ ' ON ' + mid.[statement]
+ replace (' (' +isnull(mid.equality_columns,'')+isnull(+mid.inequality_columns,''),'][','],[')+ ')'
+isnull ('INCLUDE ('+included_columns +');',';' )

I guess it can be improved, if so, please let me know your point of view.

Thanks again.
Post #1130212
Posted Tuesday, November 15, 2011 4:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 22, 2014 1:12 PM
Points: 19, Visits: 48
Hi there,

Just two quick questions.
1) Some indexes reported missing actually exist. Why would that happen?

2) The second one is about partitioned tables. Would it be correct for this query to suggest adding the partitioned column and if so, if the index is constructed aligned, would it be correct to omit such column from the key or the include list since it would be automatically part of it anyway?

Thank you for your time.
Post #1206364
Posted Wednesday, February 8, 2012 7:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
very helpful..
great effort..keep it up


Aim to inspire rather than to teach.
SQL Server DBA
Post #1248997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse