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.
| Author |
Topic |
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
 Posted - 2012-10-23 : 06:43:00
|
| Hi All,While i was reading about the compile locks from the below link:<http://support.microsoft.com/kb/263889>i came across below info.[For example, if user "dbo" owns object dbo.mystoredproc and another user, "Harry," runs this stored procedure by using the command "exec mystoredproc," the initial cache lookup by object name fails because the object is not owner-qualified. (It is not yet known whether another stored procedure named Harry.mystoredproc exists. Therefore, SQL Server cannot be sure that the cached plan for dbo.mystoredproc is the correct one to execute.]My doubts are:1.what is initial cache look up:is it plan cache memory allocated for user "Harry"? 2.we have an user "dbo" and another user "Harry" thendo we have separated memory areas for plan cache for each user?3.If cache look up fails at "Harry" Why sql server reads "dbo" cache palns.Thanks...M.MURALI kRISHNA |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-23 : 12:00:09
|
| Plan Cache is not segregated by user. Harry.MyProc would have a different Plan Handle than DBO.MyProc, if Harry doesn't qualify the owner, then SQL assumes he is talking about Harry.MyProc and will search the Plan Cache for an object named Harry.MyProc. -Chad |
 |
|
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
Posted - 2012-10-25 : 02:51:23
|
| Thanks a lot chadmat,for clarification.M.MURALI kRISHNA |
|
|
|
|
|
|
Plan Cache--Compiler locks