December 23, 2010 at 6:02 am
Comments posted to this topic are about the item Missing Indexes By Index Advantage
December 23, 2010 at 11:14 am
If this is SQL Script Manager compatible, where is the .rgtool file to download?
Edit: I found it, but boy that was unclear.
April 8, 2011 at 7:37 am
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.
June 22, 2011 at 3:23 am
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 (:cool:), so i'm reviewing some server and I see some number very high =>>> 11409912,6372924--- 5496054,50928026, .... :crazy:
thanks.
June 22, 2011 at 7:28 am
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!
June 23, 2011 at 2:18 am
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.
November 15, 2011 at 4:01 pm
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.
February 8, 2012 at 7:59 am
very helpful..
great effort..keep it up
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply