CODEWITHSUNDEEP
sql-serversql-server-2005ado.netsqlclient
Suraj
Suraj

Reputation: 36587

Does Sql Server cached compiled queries and execution plan across transactions?

Folks,

I'm using the best-practice of prepared sql statements to execute many Inserts/Updates that vary by the same parameters. I have two choices in my design: 1. all of the work gets done in a single transaction. 2. break-up the work into a number of transactions (not one per statement, but something that suits the concurrency of my environment). If I opt for #2, will SQL take advantage of the cached compiled query/execution plan across transactions? Or, because the query was made within a transaction, the life of the cache will be limited to the transaction?

Upvotes: 2

Views: 430

Answers (2)

gbn
gbn

Reputation: 432511

Plans are unrelated to transactions. Or connections for that matter

That is, a plan can be shared by many txns and/or users and/or connections. And at different times if the plan is valid and still in cache

Upvotes: 6

TToni
TToni

Reputation: 9391

The query cache is independent of transactions, so your queries will get cached regardless which option you choose.

Upvotes: 5

Related Questions