Error message 'Could not find stored procedure 'dbo.IndexOptimize'

Question

Error message 'Could not find stored procedure 'dbo.IndexOptimize'

ashevis

Mr or Mrs. 500

Points: 510
More actions
April 16, 2011 at 6:05 pm

#249055

I'm trying to run the query below in MS SMS, and get the error 'Could not find stored procedure 'dbo.IndexOptimize'...anyone know why? When I parse it, it completes successfully, but when I execute it gets the error.
Here is the query:
EXECUTE dbo.IndexOptimize
@databases = 'ALL_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE_STATISTICS_UPDATE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 100,
@MaxDOP = 3,
@FillFactor = 97,
@PartitionLevel = 'Y'

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Are you in the same database when you parse and when you run otherwise. Would recommend that you use a two part identifier, for example EXECUTE.Dbname.dbo.IndexOptimize

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ashevis Mr or Mrs. 500 Points: 510 More actions · Answer 2

I'm running the query in the same DB that I parse. I tried adding in the db name, again, I get 'completed successfully' when I parse, and get the same error (with dbname) when I run. Any other ideas?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Parsing doesn't check that the object is there. It just checks that the SQL syntax is fine.

EXEC dbo.ProcThatDoesNotExist

Parse that and you get "Command(s) completed successfully."

So short answer, the procedure does not exist. Question is, where is it really. Should it exist in that DB? Maybe it's in a different schema? Maybe a slightly different spelling?

SELECT Schema_name(schema_id) as SchemaName, name as ProcedureName FROM sys.procedures

That'll give you all the procedures in the DB, should help in finding this one

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ashevis Mr or Mrs. 500 Points: 510 More actions · Answer 4

Thanks GilaMonster,

so I ran the command you posted, and it turns out that dbo doesn't in fact exist in that database...so now I'm wondering, how can I optimize the database if the procedure to do so isn't there? The database was created by my companies software vendor, so I'm not too familiar with how it was set up, etc. As you can probably tell, I'm not too experienced with database maintenance either.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

That's not a standard procedure, not something that is automatically part of a database (and I assume you mean the proc doesn't exist rather than dbo not existing).

Ok, let's go back a couple steps. Why are you trying to run it and where did you get that command from?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ashevis Mr or Mrs. 500 Points: 510 More actions · Answer 6

Yes, I meant the procedure, not dbo...I got the command from another website (http://codetempest.blogspot.com/2010/08/ola-hallengens-maintenance-stored.html). I'm trying to defrag the index on the database. I had a query that worked for SQL2000, but it doesn't work on 2005. I think I need to defrag it because we're getting hourglasses on client machines when running look ups etc.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Well then, first you need to create the stored procedures listed in that maintenance article.

Honestly though, defragmenting may not help that much. Depends what's wrong. 90% of cases I see poor performance is due to bad code and/or bad indexing and no amount of defragging is going to fix that.

If it's important, get a specialist in to help you out. Way cheaper in the long run.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ashevis Mr or Mrs. 500 Points: 510 More actions · Answer 8

Thanks (again). Based on what you said, is there any queries etc that would clean up the indexes?, that was kind of what I was hoping to do...make it more efficient, less cluttered.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

That's what an index rebuild/defrag does.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ashevis Mr or Mrs. 500 Points: 510 More actions · Answer 10

ashevis

Mr or Mrs. 500

Points: 510

April 17, 2011 at 4:59 pm

#1312564

In other words, that query possibly might help?

WayneS SSC Guru Points: 95410 More actions · Answer 11

It might. But as Gail mentioned, it is more likely that the problem is poorly written code, and possibly a poorly designed database.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ashevis Mr or Mrs. 500 Points: 510 More actions · Answer 12

Thanks. I think your tag sums it up well...

"If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!"