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 «««56789»»

Index Management Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 3:31 PM
Points: 2, Visits: 2,343
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
Post #861984
Posted Thursday, April 1, 2010 10:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:00 AM
Points: 268, Visits: 279
I haven't reviewed this article in a while. Is the original article updated with all of the changes mentioned in this discussion?


Post #895001
Posted Monday, April 19, 2010 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 28, 2012 2:19 AM
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
Post #905897
Posted Friday, April 23, 2010 1:59 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, June 12, 2014 7:54 AM
Points: 1,475, Visits: 1,630
am working on automating this across all servers and databases. It may take some time.

Regards,
Sqlfrenzy

Post #909240
Posted Wednesday, July 21, 2010 8:02 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 16, Visits: 126
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
Post #956362
Posted Friday, June 10, 2011 7:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:43 AM
Points: 27, 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
Post #1123333
Posted Friday, June 10, 2011 10:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:14 AM
Points: 199, Visits: 593
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
Post #1123509
Posted Friday, June 10, 2011 11:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 8, 2014 10:40 PM
Points: 28, Visits: 180
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.
Post #1123530
Posted Friday, June 10, 2011 11:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Post #1123542
Posted Friday, June 10, 2011 11:50 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 7:43 AM
Points: 63, 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.



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!
Post #1123543
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse