SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by SQL Scripts Manager dev team / Missing Indexes By Index Advantage / Latest Posts

RE: Missing Indexes By Index Advantage

logicinside22 — Wed, 08 Feb 2012 07:59:46 GMT

very helpful..great effort..keep it up

RE: Missing Indexes By Index Advantage

agiangone — Tue, 15 Nov 2011 16:01:43 GMT

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.

RE: Missing Indexes By Index Advantage

mi_ka_ze — Thu, 23 Jun 2011 02:18:50 GMT

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 [code="sql"] '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 +');',';' )[/code]I guess it can be improved, if so, please let me know your point of view.Thanks again.

RE: Missing Indexes By Index Advantage

ez4321 — Wed, 22 Jun 2011 07:28:14 GMT

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!

RE: Missing Indexes By Index Advantage

mi_ka_ze — Wed, 22 Jun 2011 03:23:52 GMT

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.

RE: Missing Indexes By Index Advantage

ez4321 — Fri, 08 Apr 2011 07:37:32 GMT

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.

RE: Missing Indexes By Index Advantage

Dan Guzman - Not the MVP — Thu, 23 Dec 2010 11:14:12 GMT

If this is SQL Script Manager compatible, where is the .rgtool file to download?Edit: I found it, but boy that was unclear.

Missing Indexes By Index Advantage

SQL Scripts Manager dev team — Thu, 23 Dec 2010 06:02:03 GMT

Comments posted to this topic are about the item [B]Missing Indexes By Index Advantage[/B]