sp_executesql: How can I tell its working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp_executesql: How can I tell its working

Author

Topic

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-24 : 17:17:38

I have a stored procedure that dynamically builds a query and is then executed by the stored procedure sp_executesql. I did this so i could have the benefit of sql server re-using execution plans where possible. In the books online it states


sp_executesql can be used instead of stored procedures to execute a
Transact-SQL statement a number of times when the change in parameter
values to the statement is the only variation. Because the 
Transact-SQL statement itself remains constant and only the parameter 
values change, the Microsoft® SQL Server™ query optimizer is likely to
reuse the execution plan it generates for the first execution

Which sounds great. but then if you read down further it says in sortof a batteries not included kind of way


Note  If object names in the statement string are not fully qualified,
the execution plan is not reused.

Ive fully qualified all the fields in my statement ( i think ).. and I now would like to test if its reusing the execution plan.. Is that possible?

Kristen
Test

22859 Posts

Posted - 2004-09-25 : 06:15:51

Check the Query Plan and then do so again having replaced sp_executeSQL with EXEC(@MySQLString) ??

I preume you will have to run both tests a couple or three times to get the query plan into the cache in the first place! Probably best to alter the "parmaeters" on each run too (if they are identical on each run even EXEC(@MySQLString) will be cached!)

There is a command to empty the query plan cache (sorry, can't remember it, but I expect it will turn up here in a second, otherwise its a trip to BoL I'm afraid!)

I'd be interested to know what you find out ...

Kristen

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-25 : 11:16:23

This is the coolest thing I think i have ever found. Thank you for
pointing me in the right direction. Check this out... I did a search
on the web and found

DBCC FREEPROCCACHE
which completely free's the procedure cache, including prepared statements.

While browsing the books online looking for the keyword cache I found
that all procedure caches, prepared statements are stored in a table
called syscacheobjects. In that table, the sql statement is stored
along with a whole bunch of fields. The one I was interested in was
called usecount :). So i freed the cache, located the prepared
statement I was interested in and reran it after calling it a couple of
times.. indeed usecount did increment every time it was ran. So I
know for a fact... the execution plan is being reused. Cool stuff!

Kristen
Test

22859 Posts

Posted - 2004-09-25 : 11:37:04

"DBCC FREEPROCCACHE " - yeah, that's the one. As I said: "I expect it will turn up here in a second" [:-D]

Useful info about usecount, Thanks

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources