Questions[stored-procedures](server)

Using MSSQL 2005

Today I was called in to look at a stored procedure that started executing slowly when used from our program. Around the order of a couple seconds where it used it execute instantly. I ran SQL Server Profiler on it and it was using 1000+ in CPU and over 400,000 Reads. I copied the Exec line from SQL Server Profiler to SQL Management Studio to look at the Execution plan. The stored procedure executed instantly and returned the correct results. Execution plan looked correct, no glaring errors. I tried running it multiple times from our program to see if maybe it wasn't cached and I was just seeing a first run slowdown as the execution plan was determined, but it was consistent at 2-3 seconds per run.

For kicks I then ran DBCC FreeProcCache just to see if that would make my runs from SQL Management Studio run slower. Ran the stored procedure from Management Studio and it still ran instantly. I then ran it from the program again with profiler running, and it too ran instantly. The profiler showed the CPU had dropped to 0 and the Reads had dropped to 40. It seems to be staying consistently fast now.

Why would running DBCC FreeProcCache speed up a stored procedure so drastically?

Server: SQL Server 2005 SP2 64 bit, 32 gigs of memory. 2 instances of SQL server running. Main instance I'm using has 20 gigs visible.

We have a situation where it appears every so often our entire procedure cache is cleared which in turn is forcing stored procedure (sp) recompiles. Once the sp is in the cache everything runs fast for a little while. After a couple hours, it's cleared from the cache and has to be recompiled causing things to run slow briefly.

I'm watching the cache using:

SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
GO

DBCC FREEPROCCACHE is never called.

If I run DBCC MEMORYSTATUS I can see the TotalPages of the procedure cache as being around 500k pages. This comes out to be 3.9 Gigs allocated to the cache. Referencing: Plan Caching in SQL Server 2008 (The section on caching includes 2005 SP 2). It indicates the pressure limit should be 4.6 gigs. (75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB... 3gigs + 1.6 gigs = 4.6 gigs)

This seems to indicate that we shouldn't be under cache pressure for another 700 megs. If the statistics were changing then the stored procedure should still be in the cache and recompiled when it's next run and it checks the statistics. If this was the case I would expect the cache to stay at almost constant size.

Any ideas what might be causing the procedure cache to empty or what else I should keep an eye on to try to find the cause?

Is it possible to create a MySQL stored procedure that can execute a given insert or update and then also have it connect to a 2nd server and run the same commands? I want to basically call one server and in real-time have it backup to the second server. Just a yah or ney would suffice unless you have a link or two handy or another solution.

Thank you for your time. Aaron

I am stumped, and was hoping someone here would have a quick and easy answer. I did a fresh install of MySQL 5.5 and am trying to pass parameters into a stored procedure. The parameter values aren't being read by the stored procedure. MySQL doesn't throw an error and processes the code with null parameters. Here is the code:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `testing`(IN parameter INTEGER)
BEGIN
    UPDATE table_name SET valueToChange = 'Test' WHERE mainID = @parameter;
END

And here is the query I'm using to call it:

USE database_name; CALL testing(72);

Can anyone help?

I was executing the script I found on this site to mark all my tables and stored procedures for recompilation.
(I changed it to also mark the stored procedures)

I have a Windows Forms (.net 3.5) application which uses a lot of data tables and after executing this script the time it took to open a data heavy form went from 8 seconds to just 4 seconds.

Do I have to run such a script regularily? I thought SQL Server would take care of compiling procedures and such.

BTW: Should this question be on SO? I thought the DBAs would be here on SF.