February 3, 2014 at 2:52 pm
Hi, y'all
I'm using the following recompile script. I wish I could give credit to the originator of the code but it was something new that was being implemented when I was first brought on. The script works great... However, when an error does occur, I can't see what it says because it's marking each db as 'was successfully marked for recompilation' in the resulting error message. I've rem'd out all of the print statements, but it still insists on putting too much info in the error message.
CODE:
/***************************************************
Database Level Recompiler Script
point this script at a database, and it will
loop through the entire list of tables and
mark them for recompile (affecting all views
and procedures using the table.
****************************************************/
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT table_catalog + '.' + table_schema + '.' + table_name as tableName
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
SET @cmd = ''
OPEN TableCursor
--PRINT 'Starting table recompile...'
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Marking table: ' + @Table
-- SQL 2005 command
SET @cmd = 'EXEC sp_recompile ''' + @Table + ''''
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
--PRINT 'Table recompile complete'
CLOSE TableCursor
DEALLOCATE TableCursor
RESULTING ERROR:
Job_name = RecompileDB
--------------------------------------
Step name= Recompile: SomeDB
DB Name = SomeDB
Run Date = Feb 2 2014 11:33PM
Severity = 16
Error = Executed as user: XXX. ... recompilation. [SQLSTATE 01000] (Message 15070) Object 'SomeDB.dbo.SomeTable' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) Object 'SomeDB.dbo.SomeTable' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) Object 'SomeDB.dbo.SomeTable' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) Object 'SomeDB.dbo.SomeTable' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) Object 'SomeDB.dbo.SomeTable' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) Object 'SomeDB.dbo.SomeTable' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) Object 'SomeDB.dbo.SomeTable' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) Object 'SomeDB.dbo.So
Any idea how I could get all of the 'for info purposes only' statements out of the error?
Typically, when I get this error, it's due to a table with special characters in the name...but lately, it's happening to dbs that have tables that all look fine to me, namewise, -- so I need more error info.
TIA for any suggestions! 😀
February 3, 2014 at 3:35 pm
I wander why do you run that script? What do you think you will achieve by running it?
Igor Micev,My blog: www.igormicev.com
February 3, 2014 at 4:08 pm
Save yourself some time and just run dbcc freeproccache (just kidding), but what is the reason for the script? Why do you want to have every store procedure, trigger, etc. recompile for the tables in the list?
February 4, 2014 at 5:55 am
Why not modify the code to use brackets. That will narrow down the issues around naming & special characters.
And I'm with the others. What is the purpose of this code?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 4, 2014 at 7:33 am
Hi, all. No ideas on how to view the full error? Bummer. 😉
Grant, I'll see what I could do with the brackets, although I'm pretty green with TSQL syntax and such.
As a company that develops our own webapps, table structure may change with production code pushes without our knowledge, we run this weekly to force new optimized query plans. It seems to be working quite well.
February 4, 2014 at 7:39 am
lisa.randles (2/4/2014)
Hi, all. No ideas on how to view the full error? Bummer. 😉Grant, I'll see what I could do with the brackets, although I'm pretty green with TSQL syntax and such.
As a company that develops our own webapps, table structure may change with production code pushes without our knowledge, we run this weekly to force new optimized query plans. It seems to be working quite well.
Suggestion: Get a good update of statistics and the plans will get updated all on their own.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 4, 2014 at 7:43 am
lisa.randles (2/4/2014)
Hi, all. No ideas on how to view the full error? Bummer. 😉Grant, I'll see what I could do with the brackets, although I'm pretty green with TSQL syntax and such.
As a company that develops our own webapps, table structure may change with production code pushes without our knowledge, we run this weekly to force new optimized query plans. It seems to be working quite well.
If there are any execution plans in the procedure cache that reference the table, ALTER TABLE marks them to be recompiled on their next execution - http://msdn.microsoft.com/en-us/library/ms190273.aspx
Also the maintenance can cover that.
Igor Micev,My blog: www.igormicev.com
February 4, 2014 at 7:43 am
If you are altering your table structures you shouldn't have to mark them for recompile. Here is a quote from BOL article on ALTER TABLE:
If there are any execution plans in the procedure cache that reference the table, ALTER TABLE marks them to be recompiled on their next execution
February 4, 2014 at 7:48 am
We also do that nightly (except for once a week when we run the recompile). We use:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_updatestats'
I was under the impression, tho, that msforeachdb (from what I've read on forums) may have a tendancy to skip databases -- and with the code I'm using (in a SQL Job), I wouldn't know which ones it may or may not have skipped. I also thought that it may be decommissioned and/or unsupported. For those reasons, I didn't want to rely soley on it.
Thanks! Your feedback is greatly appreciated!
February 4, 2014 at 7:51 am
I'm not aware of hitting issues with ms_foreachdb skipping databases. But, you're right, it's technically unsupported by Microsoft, so could go away at any time. It's just a cursor, so you can write your own. I'm just lazy and take advantage of the one they provided for me.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 4, 2014 at 7:56 am
Hi, Keith
Right you are. I was under a mis-conception that structure changes did not cause an automatic recompile. However, we also frequently add new indexes.... Also from BOL:
There are times when procedure recompilation must be forced and other times when it occurs automatically. Automatic recompiling occurs whenever SQL Server is restarted. It also occurs if an underlying table referenced by the procedure has undergone physical design changes. But adding a new index from which the procedure might benefit does not cause automatic recompilation because the table itself has not changed. Automatic recompilation does not occur until the next time that the procedure is executed after SQL Server is restarted. In this situation, it can be useful to force the procedure to recompile the next time that it executes instead of waiting for a SQL Server restart.
Thanks for clearing that up!
February 4, 2014 at 7:58 am
Grant Fritchey (2/4/2014)
I'm not aware of hitting issues with ms_foreachdb skipping databases. But, you're right, it's technically unsupported by Microsoft, so could go away at any time. It's just a cursor, so you can write your own. I'm just lazy and take advantage of the one they provided for me.
I had also read somewhere that ms_foreachdb can make skipping, but didn't remember the cause.
However, their replacement with cursor is easy.
Igor Micev,My blog: www.igormicev.com
February 4, 2014 at 8:06 am
lisa.randles (2/4/2014)
Hi, KeithRight you are. I was under a mis-conception that structure changes did not cause an automatic recompile. However, we also frequently add new indexes....
I still wouldn't forcefully invalidate the plans for every single procedure in that DB (and if you really want to, just use DBCC FLUSHPROCINDB). Stats updates will invalidate plans, table changes will invalidate plans, plans will be aged out of cache on their own
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply