March 7, 2007 at 11:23 am
I'm seeing some performance issues since moving to SQL 2005, and I'm hoping that someone else out there has seen something similar. I'm running 2005 SP1 on Windows 2003 server. I have several hundred customer databases and in the course of a day we may add or remove databases depending on customer status. After moving to 2005, we noticed that when we dropped a database, the CPU would spike tremendously and we would see blocking on our crystal report calls and it would take several minutes for the blocking to clear up. The waitresource on the blocked spids showed TAB: 32767:840884877:0 [COMPILE]. The blocking was not on the stored procedure call for the report itself, but rather on the sp_procedure_params_rowset and sp_procedures_rowset calls. I believe these system procedures get called on a Parameters.Refresh call from ADO or ADO.net.
I have duplicated this behavior on a test system by creating an empty stored procedure with ten bogus parameters.
Create Procedure dbo.testTenParameters ( @test-2 int, @Test2 int, @Test3 int, .... )
as set nocount on
return
GO
I call sp_procedure_params_rowset on this procedure (exec sp_procedure_params_rowset 'testTenParameters') repeatedly with a 0.25 second delay from seven different connections. Then I drop 100 test databases in succession. (The high volume is to simulate a load.) This produces the blocking on the procedure calls with waitresource TAB: 32767:840884877:0 [COMPILE].
On the test system I've applied SP2 and this does not solve the problem. I can get around the issue temporarily by dropping databases after hours, but this is not ideal for my current operation. I also assume that the report(s) could be rewritten without a parameters.refresh, but I need a quicker solution.
By the way, this behavior can be produced by doing other things as well, including DBCC CheckDB, restores, and database renames. Creating new databases does not produce this behavior.
Any help or insight on solving the issue or debugging it further would be appreciated.
Regards,
Rubes
March 12, 2007 at 8:00 am
This was removed by the editor as SPAM
March 13, 2007 at 11:27 am
Well, it seems that we've found the cause, and Microsoft says it's "by design" - http://support.microsoft.com/kb/917828. Apparently, running checkdb, dropping databases, and quite a few other things clears the procedure cache. I'm not too happy about it and don't understand the reasoning, but we've already committed to 2005, so we're working around it.
Regards,
Rubes
April 1, 2007 at 6:34 pm
Doing a parameter refresh like this is suboptimal coding. You should explicitly declare/populate all parameters when accessing sprocs.
I noted in the referenced support article that DBCC CHECKDB only flushes the proc cache if you have the database set to autoclose. You should use this setting carefully.
I must admit that I am STUNNED that flushing the ENTIRE proc cache would be standard opperating procedure for all of the listed activities/configurations!!! Why they don't just flush RELEVANT items from the proc cache is beyond my understanding. Can anyone offer enlightenment??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply