Stored proc interesting performance issue with inserts into table variables

Question

Post reply

Stored proc interesting performance issue with inserts into table variables

VIJAYRANGA KONDAPALLY

SSC-Addicted

Points: 451
More actions
March 2, 2010 at 8:26 pm

#211792

Hi,
I am hoping someone has seen this interesting performance issue with stored proc and offer some insight.
1.I am running a stored procedure with exactly the same parameter value under 2 diferent user contexts.
2.The results are exactly the same.
3.When the user1 runs the procedure it runs in 2 seconds. When user2 runs it , takes 3 minutes.
4.I verified thee execution plan genrated for both executions by user1 & user2. They are exactly the same.
5.I verified the IO activity using the SET STATISTICS IO . The reads and writes are exactly the same.
6.I profiled the executions for user1 and user 2 . Here , i found a major difference. During inserts into table variables,
the duration taken for user1 per insert was 1 ms whereas it was taking 315 ms for the user2.
7.I changed the table variables to temp tables to verify but the performance issue remains the same.
8.I recompiled the procedure without any success. In fact, i even created a new procedure
and ran it.No dice.
As i specified above , the parmeter value is the same hence it could not be parameter sniffing issue.
Result sets , execution plan and IO activity are exactly the same. The only difference is the duration
of inserts into table variables/temp table between user1 & user2.
Has anyone seen this issue before. Any advice or insights into the procedure behaviour?? Thanks much in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

please post the exact code you are using to call the sps.

...if one calls the sp like this exec usp_whatever

while the second one uses any variation dbo.usp.. Usp_

that will give a chance to use a different execution plan

Also can you risk running freeproccache in case you have 1 bad plan and 1 good plan (cause nothing else seems to explain this).

VIJAYRANGA KONDAPALLY SSC-Addicted Points: 451 More actions · Answer 2

Thanks for the quick reply . It is a production server hence i cannot risk running the dbcc freeproccache.

However , the recompile should have taken care of that. I have also created the procedure with a new name and ran it under user1 & user2 contexts without any success. The procedure code , name , parameter and the execution plans are exactly the same. I can provide the execution plans but you you will not see the difference. Apart from the difference in duration of inserts , i cannot find any other difference.

one another clarification..user1 is 'sa' while user2 is not . I temprorily added user2 to sysadmin role for verification but did not see any change in behaviour.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I don't know what else to tell you... same plan should give same results and pathway to results.

I've never seen that outside of having 2 plans cached.

Anyway you can reproduce the problem on a test server?

Long shot but have you tried updating the stats and doing index maintenance (yes I said LONG shot).