Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reindexing requires recompile of Stored procedures


Reindexing requires recompile of Stored procedures

Author
Message
Reg_Mayfield
Reg_Mayfield
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 337
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...



KenpoDBA
KenpoDBA
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 617
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/DBARant

Minion Maintenance is FREE:


Reg_Mayfield
Reg_Mayfield
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 337
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.



Reg_Mayfield
Reg_Mayfield
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 337
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



KenpoDBA
KenpoDBA
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 617
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/DBARant

Minion Maintenance is FREE:


Reg_Mayfield
Reg_Mayfield
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 337
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????



mobasha
mobasha
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 1284
if thats was the problem then the procedure will generate an error even before the built index.

..>>..

MobashA
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7256 Visits: 8404
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 at GMail
Reg_Mayfield
Reg_Mayfield
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 337
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)...



TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7256 Visits: 8404
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. BigGrin

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
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