Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Missing Indexes By Index Advantage


Missing Indexes By Index Advantage

Author
Message
SQL Scripts Manager dev team
SQL Scripts Manager dev team
Forum Newbie
Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)Forum Newbie (0 reputation)

Group: General Forum Members
Points: 0 Visits: 3
Comments posted to this topic are about the item Missing Indexes By Index Advantage
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Right there with Babe
Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)

Group: General Forum Members
Points: 724 Visits: 736
If this is SQL Script Manager compatible, where is the .rgtool file to download?

Edit: I found it, but boy that was unclear.
ez4321
ez4321
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 26
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.
mikaeru
mikaeru
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 221
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 (:coolSmile, so i'm reviewing some server and I see some number very high =>>> 11409912,6372924--- 5496054,50928026, .... Crazy

thanks.
ez4321
ez4321
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 26
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!
mikaeru
mikaeru
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 221
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.
agiangone
agiangone
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
logicinside22
logicinside22
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1391
very helpful..
great effort..keep it up

Aim to inspire rather than to teach.
SQL Server DBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search