Prepared query - plan cache
in [Server Configuration]
|
Prev: Failed to pause full-text catalog for backup. Backup was aborted
Next: How to create a new instance in SQL 2005 Server Standard?
From: Abba on 13 Oct 2009 02:21 Hello, [SQL2005] EXEC sp_executesql @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', @params = N'@InvID int', @InvID = 100320 - Is the above query called a Prepared query? - Is the plan for the query be cached and prepared for use for all subsequent executions of this query? - In terms of plan caching, how is this query different from a ad-hoc query like SELECT * FROM Inven.InventoryDetails WHERE InventID =100320? tia, AbbA
From: Uri Dimant on 13 Oct 2009 03:28 Abba Yes you get parameterization See the below select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text] from sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql join sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle See Tony's great post http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/07/procedure-cache-tuning-sizing-from-1gbyte-to-768kbytes-increase-the-size-of-usable-data-cache.aspx "Abba" <sql_help(a)aaa.com> wrote in message news:OsAu$18SKHA.5052(a)TK2MSFTNGP06.phx.gbl... > Hello, > [SQL2005] > > EXEC sp_executesql > @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', > @params = N'@InvID int', @InvID = 100320 > > - Is the above query called a Prepared query? > - Is the plan for the query be cached and prepared for use for all > subsequent executions of this query? > - In terms of plan caching, how is this query different from a ad-hoc > query like SELECT * FROM Inven.InventoryDetails WHERE InventID =100320? > > > tia, > AbbA >
From: Erland Sommarskog on 13 Oct 2009 03:47 Abba (sql_help(a)aaa.com) writes: > Hello, > [SQL2005] > > EXEC sp_executesql > @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', > @params = N'@InvID int', @InvID = 100320 > > - Is the above query called a Prepared query? No. With a prepared query, the API sends in the query text with sp_prepare, and SQL Server returns a handle. To run the query, the API calls sp_prepexec and passes the handle and the parameters, but the query text is only passed once. > - Is the plan for the query be cached and prepared for use for all > subsequent executions of this query? Yes. The one thing you win with sp_prepare/prepexec is that the query text only travels the network once, and maybe the cache lookup is somewhat faster as well. > - In terms of plan caching, how is this query different from a ad-hoc > query like SELECT * FROM Inven.InventoryDetails WHERE InventID =100320? An ad-hoc query is also cached, but the plan is only reused if you send in the exact same query text again. (Although a simple query like the one you gave as example, is likely to be auto-parameterised, and will in fact behave as a regular parameterised query.) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Abba on 14 Oct 2009 01:49 Thank you Erland. >> - Is the above query called a Prepared query? > > No. Then, is this also an ad-hoc query? >> An ad-hoc query is also cached, but the plan is only reused if you send >> in >> the exact same query text again. EXEC sp_executesql @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', @params = N'@InvID int', @InvID = 100320 So, does this mean the plan is reused only if I send the same query as above. After the one above, if I send a query like this one below, wouldn't the query plan be reused? EXEC sp_executesql @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', @params = N'@InvID int', @InvID = 100397 tia, AbbA "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CA3639BCC30CYazorman(a)127.0.0.1... > Abba (sql_help(a)aaa.com) writes: >> Hello, >> [SQL2005] >> >> EXEC sp_executesql >> @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', >> @params = N'@InvID int', @InvID = 100320 >> >> - Is the above query called a Prepared query? > > No. With a prepared query, the API sends in the query text with > sp_prepare, > and SQL Server returns a handle. To run the query, the API calls > sp_prepexec > and passes the handle and the parameters, but the query text is only > passed > once. > >> - Is the plan for the query be cached and prepared for use for all >> subsequent executions of this query? > > Yes. The one thing you win with sp_prepare/prepexec is that the query text > only travels the network once, and maybe the cache lookup is somewhat > faster > as well. > >> - In terms of plan caching, how is this query different from a ad-hoc >> query like SELECT * FROM Inven.InventoryDetails WHERE InventID =100320? > > An ad-hoc query is also cached, but the plan is only reused if you send in > the exact same query text again. > > (Although a simple query like the one you gave as example, is likely to be > auto-parameterised, and will in fact behave as a regular parameterised > query.) > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 14 Oct 2009 04:22
Abba (sql_help(a)aaa.com) writes: > Thank you Erland. > >>> - Is the above query called a Prepared query? >> >> No. > Then, is this also an ad-hoc query? I would call it parameterised query. An "ad-hoc query" really refers to something that you run from outside an application, but there is not really any strict definition. > EXEC sp_executesql > @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', > @params = N'@InvID int', @InvID = 100320 > So, does this mean the plan is reused only if I send the same query as > above. After the one above, if I send a query like this one below, > wouldn't the query plan be reused? > > EXEC sp_executesql > @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', > @params = N'@InvID int', @InvID = 100397 No, the plan would be reused (assuming that it has not been flushed from the cache for some reason.) On the other hand, this query would not reuse the plan: EXEC sp_executesql @stmt = N'SELECT * FROM Inven.InventoryDetails WHERE InventID = @InvID', @params = N'@InvID int', @InvID = 100397 If you look closely, you will see that I've added a space in the parameter list, and that's enough to get a cache miss. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |