July 2, 2015 at 1:50 pm
Hi,
We have a vendor supplied SQL2014 DB. This DB creates blocking but only when running a stored proc the first time with the parameters.
So, for example when my end user closes a claim, it creates a block and she (and everyone else) has to exit out of the program. When she goes back into the front end, and runs the same exact claim again it works without creating this blocking. I can emulate this same thing from the stored procedure.
I am running a complete index rebuild tonight, hoping that will help.
I don't know the next steps I need to do to stop this blocking.
July 2, 2015 at 10:56 pm
krypto69 (7/2/2015)
Hi,We have a vendor supplied SQL2014 DB. This DB creates blocking but only when running a stored proc the first time with the parameters.
So, for example when my end user closes a claim, it creates a block and she (and everyone else) has to exit out of the program. When she goes back into the front end, and runs the same exact claim again it works without creating this blocking. I can emulate this same thing from the stored procedure.
I am running a complete index rebuild tonight, hoping that will help.
I don't know the next steps I need to do to stop this blocking.
First thing is to find out what exactly is causing the blocking before trying to fix it, suggest you grab a monitoring tool (such as DBMonitor3) which can collect the relevant informatio.
😎
July 3, 2015 at 3:10 am
USE PROFILER .. TO FIND OUT WHAT HAPPEN S AT FIRST TIME THEN ANALYIZE THAT
July 3, 2015 at 4:02 am
It could be compile time on the query is very high which can cause some blocking, or that it's loading a lot of data into memory, again, possibly causing blocking. Heck, it could be taking locks on stuff or who knows what. I'd suggest looking at the procedure to see what it's doing.
Capture the wait statistics when the procedure is run in order to understand what is causing it run slow. I'd also suggest setting up your blocked process report within extended events. Here's a couple[/url] of articles on that. Check the execution plans for this procedure to understand what it's doing within your system. In short, gather knowledge about what it's doing and how it's doing it. I'm not sure that an index rebuild is likely to help, although that will give you a full can update on your statistics, which, if this is a bad parameter sniffing issue, that could help.
If you can, post the query here along with the execution plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2015 at 4:06 am
Almighty (7/3/2015)
USE PROFILER .. TO FIND OUT WHAT HAPPEN S AT FIRST TIME THEN ANALYIZE THAT
This is actually really bad advice on two levels. First, running the Profiler GUI against a production server is a very bad practice. It uses a buffering mechanism that can seriously negatively impact the system. If you must use trace events, set them up using T-SQL and output them to a file. You can then use the Profiler GUI to look at the file if needed.
Second point, we're on SQL Server 2014 (or at least this is being posted in a 2014 forum). With the introduction of the GUI for managing extended events in 2012, there are no really strong cases to be made for using trace events any more, let alone profiler. Extended events are less intrusive, easier to manage, better at filtering, and offer a much wider range of possible events for gathering data about the system. Trace events, and profiler, are on the deprecation track and shouldn't be used any more except in legacy systems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2015 at 4:40 am
but we can use profiler remotely or as u said by using TSQL right ? extended events are good but for earlier version profiler was better idea
July 3, 2015 at 4:46 am
Thank you so much Grant. You are always helpful and informative. Looking into your suggestions now.
Capture the wait statistics when the procedure is run in order to understand what is causing it run slow.
What do you recommend to capture the wait stats?
July 3, 2015 at 4:50 am
Almighty (7/3/2015)
but we can use profiler remotely or as u said by using TSQL right ? extended events are good but for earlier version profiler was better idea
Server side trace if you really want to, but as mentioned before SQL 2014 = Extended Events is the way forward.
July 3, 2015 at 5:45 am
To quickly grab the blocker's information you can use a tool like SQL Performance Monitor - v3.1, very handy to have on your USB stick
😎
July 3, 2015 at 6:02 am
Almighty (7/3/2015)
but we can use profiler remotely or as u said by using TSQL right ? extended events are good but for earlier version profiler was better idea
Nope. Running Profiler remotely doesn't change how it uses memory on the server to capture events. Even on old systems, the Profiler GUI shouldn't be run against production (and if you're really trying to measure performance on a test system, it shouldn't be used there either). Use trace events to create the event monitoring and only use Profiler to consume the output from the trace events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2015 at 6:05 am
krypto69 (7/3/2015)
Thank you so much Grant. You are always helpful and informative. Looking into your suggestions now.Capture the wait statistics when the procedure is run in order to understand what is causing it run slow.
What do you recommend to capture the wait stats?
You can use the really simple, if not entirely accurate, approach of querying sys.dm_os_wait_stats before and after the query. Or, better, to get a really accurate measure of the waits for just the execution of your query, you can use extended events. Paul Randal has a good blog post[/url] on how to do it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply