June 27, 2012 at 8:20 am
Hi
We are observing the blocks in our database.
The blocking process in having the sp_cursorclose;1 in the session when we do the dbcc inputbuffer(blockingsessionid).But we could not able to find what are the queries which are executed prior to this statement.
One of our DBA has suggested to start the blocked process report in the profiler,to get the statement prior to this.
Is it can be done using only blocked process report or should we have to enable any thing else.
I want to run the profiler other than the production database server.Is this have any performance impact on the production server even though we are running profiler on some other server.
Please provide your suggestion ,how best I can solve the issue using profiler.
June 27, 2012 at 8:27 am
I've used the blocked processes trace in the past. You can use it without other events being traced. The load is dependent upon how many events you are capturing, but in general, I think if this as pretty safe. You need to configure an appropriate "blocked processes threshold". If you set this to 1 second, you are likely to get a lot of blocked processes and therefore more load. Start with a high threshold- 30 seconds or something, and run it for a short period of time. gradually reduce the threshold until you start getting results.
June 27, 2012 at 8:30 am
NJ-DBA (6/27/2012)
I've used the blocked processes trace in the past. You can use it without other events being traced. The load is dependent upon how many events you are capturing, but in general, I think if this as pretty safe. You need to configure an appropriate "blocked processes threshold". If you set this to 1 second, you are likely to get a lot of blocked processes and therefore more load. Start with a high threshold- 30 seconds or something, and run it for a short period of time. gradually reduce the threshold until you start getting results.
Just re-read your question... yes there is some impact- anything that touches the server is going to have some impact. I think of the impact as being quite low.
Yes- you need to set the blocked processes threshold (via sp_configure) in order for events to be returned.
think of it this way- if your threshold is 10 secnods, profiler is going to return info about what is blocking and what is being blocked for longer than 10 seconds. The benefit of returning this information is usually much lower than the cost of collecting it.
June 27, 2012 at 11:31 am
Just understand that the Profiler GUI actually pulls data using a different mechanism than trace events. So, yes, pointing the Profiler GUI at your server will put additional load on it. I'd suggest just using the GUI to create a script that outputs to a file and use that. Then the load on the server is extremely low (just not zero).
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply