SQL Server Performance Forum – Threads Archive

Prepared Statement Plan reuse in SQL 2000

Hello again, I’m posting this in case someone has had some experience with Prepared Statements on SQL Server 2000. I have an application that uses connection pooling and prepared statements. Typically, the application will open a connection, execute one simple query and then close the connection. The trace looks like the following: declare @P1 int
set @P1=-1
exec sp_prepexec @P1 output, N’@par0 varchar(13),@par1 bigint’, N’SELECT TOP 1 SubscriptionID, SubscriptionNotes FROM Subscriptions WHERE SubscriptionMSISDN = @par0 AND SubscriptionServiceID = @par1 ‘, @par0 = ‘+447751234567’, @par1 = 10
select @P1 sql_unprepare 34
sql_reset_connection Each query is followed by an unprepare and reset_connection which makes me think the prepared execution plan is being thrown away. Does anyone know how to make Sql Server not throw away the prepared statement when the connection closes (or in this case is returned to the pool)? I’m using ADO.NET. Thanks. PS: Note that I do not see any sql_prepare statements in the trace.
PS2: Any links to documentation on sp_prepare/sp_unprepare would be appreciated. There’s almost nothing out there.

If the application closes the connection then the sp_reset_connection is expected, also if you are using queries across servers then it is expected, it is basically SQL tidying up the connection for someone hing else to use. It has no bearing on whether the plan will be reused by another user, as this is part of SQLs normal caching algorithms Cheers
Twan

Cool, so I can be confident that the statement will be reused across connections on SQL server, regardless of the sp_unprepare statement that follows every statement? Thanks.

Yes that’s right the unprepare just means that the connection which called the unprepare needs to prepare the statement before trying to execute it. When it is passed to SQL for preparation, SQL will already have an execution plan for this statement and will use it (unless it has deemed it too simple to bother caching, in which case it won’t) Cheers
Twan

Thanks Twan, that’s good news. PS: is there a way to check the list of execution plans/statements SQL Server has cached?

I can only think of DBCC PROCCACHE but this does not show you proc names, etc. Cheers
Twan

PROFILER is the tool to use with details. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

FYI, The above was slightly incorrect. SQL Server was indeed throwing away the prepared statements each time. There isn’t much documentation on this, but turns out you need to make sure you keep the Command object alive at the client for prepared statements to be reused. So you need to prepare once, cache the Command object and reuse it in the future. For more details:http://support.microsoft.com/default.aspx?kbid=243588

The above KBA refers …When you are resolving performance issues, it is extremely valuable to view SQL Profiler data. You do not have to review all the data that you captured; be selective. … Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes sorry, I wasn’t talking about your post (see above).

Never mind, on a moment I was mystified. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>