January 22, 2011 at 7:26 pm
i have bunch of procedures running from management studio and i have also enabled actual execution. I do see part which is taking most cost however i am not able to find which object is it. I do see the sql( which is a select statement) but i do not which object is that. I looked up in sql_modules but couldnt find it. I also tried using trace with SQL XML trace object no success. Can someone please help me in finding it?
January 22, 2011 at 10:27 pm
i was able to generate execution plan using Show XML Statistics Profile,however the execution plan from management studio is little from the execution plan captured in trace. Any idea?
January 23, 2011 at 1:51 am
Are you checking the object_id in the right database? Object_id is just to identify the procedure and, if you have the text you already know what the procedure is. It's not to get table ids. The table names will be given in the execution plan.
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
January 23, 2011 at 2:34 am
GilaMonster (1/23/2011)
Are you checking the object_id in the right database? Object_id is just to identify the procedure and, if you have the text you already know what the procedure is. It's not to get table ids. The table names will be given in the execution plan.
execution plan from management studio does not give object_id, and SHOW XML Statistics from profiler does give object_id.However exec plan from management studio is different and the one from profiler does not really spread the cost in well defined way like the one from mgmnt studio. I know the sql but not sure which procedure has that sql.?Thanks
January 23, 2011 at 3:02 am
Again, are you sure you're checking in the right database? ObjectID are database specific, profiler will pick up plans for the entire instance.
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
January 23, 2011 at 4:20 am
GilaMonster (1/23/2011)
Again, are you sure you're checking in the right database? ObjectID are database specific, profiler will pick up plans for the entire instance.
I have filter on trace for a specific database.I think i didnt make my question clear. I will try once again. I have bunch of procedures running from management studio from a single query window with actual execution plan enabled on MyDB and i also have SHOW XML statistics running same time for MyDB. The exec plan from mgmt studio and exec plan from trace are different. If you test with a simple query it will look same but in my case they are different. Objectid is not shown in exection plan from mgmnt studio and object_id is seen on exec plan from trace. However i am not relying on exec plan from trace but total cost is not clear. Exec plan from mgmt studio looks to be more reliable but i cant see the object id. I individually ran each proc and compared with the original to get the object id but this would be cumbersome for a big process. Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply