sys.syscacheobjects and sys.objects

Question

sys.syscacheobjects and sys.objects

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
December 8, 2010 at 2:58 am

#144327

Hi
I was trying to get execution plans for all procs and corresponding proc name.
In the process, I was using this query:
select object_name(objid),* from sys.syscacheobjects a inner join sys.sysobjects b on a.objid=b.id
where objtype ='proc'
I put a join on a.objid=b.id since I read from sys.syscacheobjects help of SQL 2008 that:
SQL Server 2008 Books Online
sys.syscacheobjects (Transact-SQL)
objid
int
One of the main keys used for looking up an object in the cache. This is the object ID stored in sysobjects for database objects (procedures, views, triggers, and so on). For cache objects such as ad hoc or prepared SQL, objid is an internally generated value.
But when I manually checked, I found that none of the id is same as objid.
Am I missing something???
Thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

syscacheobjects is deprecated, will be removed in a future version of SQL and should not be used. Besides, you can't get the exec plan from it. sysobjects is also deprecated. Use sys.objects.

Query sys.dm_exec_query_stats or sys.dm_exec_cached_plans.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

I knew that gail but since it was having objid, i thought of using it. but even the DMV's you mentioned wont give me the name of the proc corresponding to which the plan is there in sys.dm_exec_cached_plans.

Will appreciate your help on this.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Cross apply to sys.dm_exec_sql_text. If the plan is from a procedure, the object-id column in there will give the object_id of that procedure. Join to sys.procedures for the name.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 4

That is the catch here Gail.

If I run below query:

select * from sys.dm_exec_cached_plans a cross apply sys.dm_exec_sql_text(plan_handle)

I can see from text column that the plan is for a proc. I also get an Objectid column.

But none of the objectid matches the object_id from sys.procedures or sys.objects for that matter.There the objectId's are different.

Where as Online help says that it is same object id which is present in sys.sysobjects. you may try by yourself and see.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I've tried that many times, and if the plan is for an object (as opposed to ad-hoc SQL), it works fine.

Are you not maybe in the context of the wrong database? The exec cached plans and exec sql text are server-wide, but sys.objects is database-scoped. Check the DBid column (also in sys.dm_exec_sql_text) and make sure that you're in that database. Otherwise use the 2-parameter form of ObjectName()

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass