Reindexing requires recompile of Stored procedures
-
I have a database that was recently created to interface with an in house web application. The developer has written a number of Stored Procedures to handle data manipulation. After a few weeks the queries started to run rather slow. I suggested we setup a maintenance plan to reindex all the tables during the weekend when there is no one using the application. After reindexing the Web application failed ("500 internal server error"). In order to get things working I had to recompile all of his Stored Procedures. The reindexing did improve performance but this is the first time I have run across this issue. I reindex all my other databases with no problems and no recompiles. Is this something that I should do as a normal course of business or is there something I can do to find out why a recompile is required for SP in this particular Database.
I am running Sql Server 2005 Enterprise Edition, SP2, compatibility level 90.
Thanks in advance...
-
No, you're right... recompiling and reindexing are completely autonomous ops and shouldn't have anything to do with each other.
Have you done a checkDB on it?
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Just ran one and there were no errors...
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MRv4_Reg'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-
Additional Information...
Here is the actual error from the Web Application..
Inner Exception
---------------
Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : Incorrect syntax near 'CA'.
Source : .Net SqlClient Data Provider
Help link :
Errors : System.Data.SqlClient.SqlErrorCollection
Class : 15
LineNumber : 29
Number : 102
Procedure : uspDALC_mrCourtListByApplication
Here is the Code for the SP where it appears the error is occurring...
Again I only see this after I reindex and the error goes away when I recompile the SP...
/****** Object: StoredProcedure [dbo].[uspDALC_mrCourtListByApplication] Script Date: 05/21/2008 09:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/*
uspDALC_mrCourtListByApplication 'BKECF'
uspDALC_mrCourtListByApplication '4'
uspDALC_mrCourtListByApplication '%','tracking.exists'
exec uspDALC_mrCourtListByApplication @strApplicationId=N'__SELECT_ONE__',@strOption=N'tracking.exists'
*/
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[uspDALC_mrCourtListByApplication]
@strApplicationId VARCHAR(50) = '%',
@strOption VARCHAR(20) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @intApplicationKey INTEGER
BEGIN TRY
SET @intApplicationKey = CAST(ISNULL(@strApplicationId,0) AS INTEGER)
END TRY
BEGIN CATCH
SET @intApplicationKey = 0
IF @strApplicationId <> '%'
SELECT @intApplicationKey = applicationKey FROM [Application] WHERE applicationId = @strApplicationId
END CATCH
SELECT DISTINCT
Court.courtKey,
courtId,
courtName,
courtId + ' ' + courtName as courtDisplayName
FROM RequestDetail BASE
JOIN ReportedBy rby ON BASE.requestKey = rby.requestKey
AND rby.reportedByKey = (SELECT MAX(t.reportedByKey) FROM ReportedBy t WHERE t.requestKey = BASE.requestKey)
JOIN Contact ON rby.contactKey = Contact.contactKey
JOIN Court
ON Contact.courtKey = Court.courtKey
--ON BASE.courtKeyReportedIn = Court.courtKey
AND (applicationKeyAssignedTo = @intApplicationKey OR @intApplicationKey = 0)
AND
((@strOption IS NULL)
OR (@strOption = 'tracking.exists'
AND EXISTS (SELECT * FROM requesttracking RT WHERE RT.requestKey = BASE.requestKey))
OR (@strOption = 'mr.exists'
AND EXISTS (SELECT * FROM MR mr WHERE mr.requestKey = BASE.requestKey))
)
ORDER BY courtId
RETURN @@ROWCOUNT
END
-
You know what... I completely misread your original problem. I thought you were saying that you were unable to reindex the DB until you recompiled this SP, which was just impossible.
OK, I'm on track now... the SP fails after you reindex and won't run again until you recompile, right... ok, I get it now.
It's not a big SP, why don't you just save it with the recompile option and recompile it every time.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
I was looking at the stored procedure and was wondering based on the error I got "Message : Incorrect syntax near 'CA'."
Could it be that the TRY/CATCH is not meant for any error but only errors involving a transaction. The code will never cause an event like an update/insert or delete...
BEGIN TRY
SET @intApplicationKey = CAST(ISNULL(@strApplicationId,0) AS INTEGER)
END TRY
BEGIN CATCH
SET @intApplicationKey = 0
IF @strApplicationId <> '%'
SELECT @intApplicationKey = applicationKey
FROM [Application]
WHERE applicationId = @strApplicationId
END CATCH
So my thought was the Try/Catch block may not be interpreted correctly and therefore an error is thrown...
Any thoughts????
-
if thats was the problem then the procedure will generate an error even before the built index.
..>>..
MobashA
-
Are you reBUILDING the indexes, or reORGing them? a rebuild will redo the stats and that WILL force all associated sprocs to compile their first execution. IIRC a reorg doesn't automatically redo the stats.
Could be wrong about that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I have created a MP that runs on the weekend, I have two procedures that run, one to Rebuild Indexes and the second is Update Statistics...
Each Monday morning we have the same Stored proccedures failing as stated above. The three Sp have a Try/Catch block at the begining of the procedure. When we recompile these SP everything works fine till we run the MP on the weekend.
I am going to turn off the MP this weekend and see what happens??? Will update next Tuesday (Monday is Memorial day)...
-
Reg_Mayfield (5/23/2008)
I have created a MP that runs on the weekend, I have two procedures that run, one to Rebuild Indexes and the second is Update Statistics...This is a VERY common Worst Practice I see at the majority of my new clients. A REBUILD of the index is forced to read 100% of the pages, obviously, in order to completely REBUILD the index. Thus the statistics that it builds after index creation have a 100%-page-read set of stats. Then when you do the update stats action next - it reads only a PORTION of the pages in the just-REBUILT index . . . and you wind up with LESS EFFECTIVE statistics because of that. More work and 'worse' data. Double hit!
Everyone who reads this - write it down in your document of what NOT to do. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.
BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?
From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffrey Williams (5/23/2008)
Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?
From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...
Jeff
Jeffrey Williams (5/23/2008)
Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?
From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...
Jeff
1) Are you sure that sp_updatestats only updates stats that need it? a) if autoupdate is on this would be pointless since they would already be done presumably and b) I looked at the code for sp_updatestats and it seems to do everything. There was use of stats_ver_current(@table_id, @ind_id), but I can't find code for that anywhere. Perhaps that is what you were referring to?
NOTE: per BOL here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b86a88ba-4f7c-4e19-9fbd-2f8bcd3be14a.htm, "If you prefer not to update all statistics by running sp_updatestats". That implies that sp_updatestats does all stats.
and yet here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/919158f2-38d0-4f68-82ab-e1633bd0d308.htm, " This updates statistics only when they are required. " Not very clear!! 🙂
2) I am sure there is an algorythm somewhere that details out the logic on what percentage of rows/data is sampled for update stats call, but I can't find it at the moment.
Oh, while checking around for some clarification for this topic I stumbled across this: http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx. Quite amazing that you can 'falsify' the stats stuff and thus force the optimizer into a particular type of plan!! Gosh can I think of some uses for that. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
1) Are you sure that sp_updatestats only updates stats that need it? a) if autoupdate is on this would be pointless since they would already be done presumably and b) I looked at the code for sp_updatestats and it seems to do everything. There was use of stats_ver_current(@table_id, @ind_id), but I can't find code for that anywhere. Perhaps that is what you were referring to?
NOTE: per BOL here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b86a88ba-4f7c-4e19-9fbd-2f8bcd3be14a.htm, "If you prefer not to update all statistics by running sp_updatestats". That implies that sp_updatestats does all stats.
and yet here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/919158f2-38d0-4f68-82ab-e1633bd0d308.htm, " This updates statistics only when they are required. " Not very clear!! 🙂
2) I am sure there is an algorythm somewhere that details out the logic on what percentage of rows/data is sampled for update stats call, but I can't find it at the moment.
Oh, while checking around for some clarification for this topic I stumbled across this: http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx. Quite amazing that you can 'falsify' the stats stuff and thus force the optimizer into a particular type of plan!! Gosh can I think of some uses for that. :w00t:
Yep, I am with you - it is a bit confusing. When I reviewed 'sp_updatestats' I found that it checks whether or not there have been rows modified (rowmodctr). If so, it updates the statistics for that table, if not - it gets skipped. I tested this and it does indeed skip tables that have not had any activity.
Now - from everything I have been able to find, the only way to insure that statistics are rebuilt using a full scan is to issue an UPDATE STATISTICS WITH FULLSCAN (or rebuild the index) and turn off auto update stats. If auto update stats kicks in - it rebuilds the statistics using the default sampling rate (which I still don't know what that value is).
But, if you have not hit an auto update stats situation for a table - and you run sp_updatestats with the RESAMPLE option or UPDATE STATISTICS WITH RESAMPLE, then those statistics will be rebuilt with the previous sampling rate. That means that statistics built when an index was created/rebuilt will be updated with a full scan - and any statistics that were created manually with a full sampling rate would also be built with a full scan.
Basically - it does not look like you can insure that stats are updated with a full scan all the time.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Did you figure what caused the 'CA' error? I see that this thread changes track a bit and the original 'CA' error issue is not discussed further. I have the same error reported for our product. When my process is run in parallel with index rebuild online, then I get the 'CA' error.
I am eager to know if you had overcome the problem.If so, please let us know how.It will be of immense help. Thanks again
-
My memory is a little foggy and we have upgraded to Sql 2008. But from what I remember I had to break the MP up into individual Databases and that solved our issue. Originally I had set the MP to do all our User databases. Everything worked OK and then I had a database that was removed from production so I went back into the MP and removed the database from there. But every time the MP ran it continued to look for the deleted DB. I decided it was easier to do a MP for each individual DB and from that point on I no longer saw the errors I had encountered before. Sorry I can't be of more help but as I said we upgraded our servers and I have not had any problems since I changed the Maintenance Plan...
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply