November 11, 2003 at 10:13 pm
Hi all,
We are testing a new application that executes a series of stored procedures from ASP pages. Now I don't know anything about the ASP side of things, (I'm the DBA) but I am noticing that I have multiple plans stored in the procedure cache for the same stored procedures. Out testing involves 60/120/240 user connections executing the same number of these stored procedures all within one second. I thought there should only be one executable plan and one compiled plan per stored procedure saved in cache. Not multiple plans for the same thing. I am not seeing any Recompiles in profiler except when I clear the cache, which I’d expect. I am also seeing a lot of CacheMiss in profiler. Some of the plans may only have a ‘usecount’ of 1 or 2 while another plan for the exact same procedure may have 40 or more. I have another stored procedure that has 10, 16, 6, 22 ‘usecount’ for different plans. Does anyone know what is going on?
Thanks in advance
Angela
November 12, 2003 at 1:11 am
SQL Server stores a plan for a stored procedure based on the 'SET' options (e.g. CONCAT_NULL_YIELDS_NULL or ANSI_NULLS etc etc) in force at the time of execution.
If your connections have different set options at the time the procedure is executed, you will end up generating multiple plans. I'm sure I also read somewhere that on multi cpu boxes, SQL Server will create parallel and non-parallel execution plans for the same procedure.
November 19, 2003 at 11:28 pm
All the connections have the same set options.
The number of executable plans is never consistent either.
After numerouse SQL profile traces and queries against the syscacheobjects table I have given up. As long as the queries run well, I'm not going to worry about it. It would've been nice to know why I'm seeing this behaviour but no one else seems to have any ideas and I haven't found much info on the Net either.
Thanks for the suggestions though
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply