SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Management


Index Management

Author
Message
SQLCrazy
SQLCrazy
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 2343
I have been working through this article and have found very instructional and also useful. I have not noticed anyone having problems with proc_InsertMostUsedIndexes. I am stuck. Whenever I run it I get the following error:

Msg 512, Level 16, State 1, Procedure proc_InsertMostUsedIndexes, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I am at a deadend so if anyone could give me any help it would be greatly appriciated.

Thanks in advance.

Post #861979
pnewhart
pnewhart
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 386
I haven't reviewed this article in a while. Is the original article updated with all of the changes mentioned in this discussion?



matthew-550133
matthew-550133
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
It doesn't look like it has been updated...

SQLFrenzy - can you update the original article with the fixed code please?

Thanks much :-)
Ahmad Osama
Ahmad Osama
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2739 Visits: 1660
am working on automating this across all servers and databases. It may take some time.

Regards,
Sqlfrenzy
Sean Woehrle
Sean Woehrle
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 155
Hey SQLFrenzy, great work.

We all should expect articles to be a good starting point and not a perfect solution. Your thought process is sound to me.

Sean

Cheers,

Sean :-D

alexms_2001
alexms_2001
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 108
Good dtuff, thank you, but I was getting "division by zero" so I wrapped relevant code of proc_FilltblIndexUsageInfo in a CASE:

CASE WHEN dbo.Uf_GetindexSize(si.index_id, so.object_id) != 0 THEN
CAST((user_seeks + user_scans + user_lookups + user_updates)
/ dbo.Uf_GetindexSize(si.index_id, so.object_id) AS DECIMAL(10,2))
ELSE 0 END AS IndexUsagetoSizeRatio
Daryl AZ
Daryl AZ
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 698
Nice article SqlFrenzy!!

I had an in-database index sproc but I like the additional functionality of needed and unused indexes.

I am updating the code to run from a Maintenance type database and to run for a specific database. The independent database allows me to update indexes for third party apps and Share Point databases. I am a bit leery of the automatic drop and create of indexes but that looks like a scheduled job on the weekends.

THANKS!
Daryl
James Stephens
James Stephens
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 210
A couple of comments:

1. I appreciate the offer of these utilities and the benefit of the work put into this.
2. However, I get the "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= ..."
3. The code appears to be written in a form to purposefully obfuscate it's meaning.
4. If the forum software could make the "code windows" on the site more "copy/paste friendly" that would also be a great help--when I copy/paste it spits everything out in one huge line and seems to strip whitespace, linefeeds, etc, so I have to spend tedious time finding the linebreaks, etc.

5. I don't mean to "look a gift horse in the mouth" but I would greatly appreciate it if an updated version could be posted--without the errors, etc.
6. Bottom line--this does not work at all--and again--I appreciate free code but if it doesn't work it actually costs me time trying to get it to work so it's not free anymore.

7. If I'm out of line, please let me know. I just can't get this bit of code to work when I copy it as posted.

Thank you.
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4800 Visits: 2907
This is a great article, and well thought out. I have been in the process of writing something that I can use here - but not to this extent.

I am going to implement it to a point and run tests.

I am unwilling to automatically drop or create indexes, but will use it to a point. I would, as some have said here, want to evaluate the data which is conveniently stored in a table and decide. But, to me, the theory is sound. I look forward to following this thread further.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
wchaster
wchaster
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 109
Not sure what your pasting into Grasshopper, code block I copy, pastes well formed for me in NOTEPAD, NOTEPAD2 and into a query pane.

:-D
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
Hehe
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