SQl server 2008R2 Performance issue after SP3 update

Question

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.

Answer 1

Its difficult to tell now because SQL Server 2008 R2 Sp3 is not supported. Did you restarted your server after SP/CU upgrade then most likely old plan is flushed and SQL Server made all together different plan for these stored procedures and new plan is not that optimal. This has happened to me sometimes, I updated the stats and things got fixed but took time. But in your case you would have to look at the wait types coming while executing this SP. What is the wait_type you see in sys.dm_exec_requests ? I also hope execution is not blocked.

Answer 2

Thanks Shanky.
I am kind of agreed with your thoughts that it might be flushed out the after SP update.
So do you think that i should run the update stat for all the databases?
We have Ola Maintenance plan running so it should be running update stats.
it's a production server and There are multiple stored procedures and tables are involved from the different databases and also it's truncation table and reloading so afraid to run during business hours as it will take another 5 hrs.
What i should look into for LAst_Wait_Type?
Is it i can go back and see any thing?

Answer 3

Thanks Shanky once again.
There are no blocking sessions.

Answer 4

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.