SQl server 2008R2 Performance issue after SP3 update

SQL Server Administration
1

Hello,
We have recently applied SP3 KB2979597 and Security Update Patch KB4057113 on Sql server 2008R2 but after that our couple stored procedures runs slow, it's taking almost 4 to 5 hours, but normally it was running under 5 minutes.
Any one has idea that SP update causing any issue?
We have applied earlier same SP and security patch on test server and we didn't have any issue.

Any quick guidance really apprecitae.

2

That is a very unusual problem, I don't see how that could happen either.

Are you sure the patch fully applied, didn't error out on anything?

Do you see any blocking going on during the stored proc execution?

3

Thanks Scot.
When Job was running, i checked Blocking or locking and couldn't see, only i can see those procedures as a long running query.

4

Can you check WAITS

5

Thanks Ahmed.
I have also checked Waits and it's mostly bunch of CXPACKET with one session id.

6

Make sure you threshold for parallelism is not too low, like 5, rather than 40 or 50 or whatever.

7

Thanks Scott, I have already set up Cost threshold for parallelism to 50 and MAXDOP = 8.
Any other suggestions as It's running fine in Test, it was running fine in production also but after Service PAck, it;s changed the behaviour.

8

Maybe go higher on threshold?! If you're seeing a lot of CXPACKET waits, something's going on with parallelism. You don't have an anti-virus or something (3rd party backups?) chewing up your CPU do you?

9

Thanks Scott.
When i was troubleshooting, i didn't see much CPU but i can see more Network I/O and Disk I/O on the server PerfMonitor.
In, Activity Monitor, it was multiple CXPACKET Wait and Batch/Request.

10

Thanks everyone, i have resolved the issue as it was parameter sniffing so assigned parameters to local variable and it took care of drastic poor performance.