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 12»»

Reindexing requires recompile of Stored procedures Expand / Collapse
Author
Message
Posted Wednesday, May 21, 2008 6:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:46 AM
Points: 67, Visits: 301
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...



Post #504420
Posted Wednesday, May 21, 2008 7:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:23 AM
Points: 318, Visits: 351
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.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://www.infoworld.com/blogs/sean-mccown
DBA Rant – http://dbarant.blogspot.com
Post #504450
Posted Wednesday, May 21, 2008 7:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:46 AM
Points: 67, Visits: 301
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.



Post #504461
Posted Wednesday, May 21, 2008 8:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:46 AM
Points: 67, Visits: 301
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



Post #504515
Posted Wednesday, May 21, 2008 12:48 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 7, 2014 8:23 AM
Points: 318, Visits: 351
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.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://www.infoworld.com/blogs/sean-mccown
DBA Rant – http://dbarant.blogspot.com
Post #504757
Posted Thursday, May 22, 2008 3:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:46 AM
Points: 67, Visits: 301
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????




Post #505506
Posted Friday, May 23, 2008 1:56 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284
if thats was the problem then the procedure will generate an error even before the built index.

..>>..

MobashA
Post #505662
Posted Friday, May 23, 2008 8:45 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 4,359, Visits: 6,195
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
Post #505919
Posted Friday, May 23, 2008 10:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:46 AM
Points: 67, Visits: 301
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)...



Post #506012
Posted Friday, May 23, 2008 12:21 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 4,359, Visits: 6,195
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. :D


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #506080
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse