How to find history of Blocking?

Article
09/10/2017

Question

_{Sunday, September 10, 2017 4:52 AM}

Hello,

In one server, There were two blockings , now it becomes one blocking.

I want to find from what time , it becomes one blocking from two.

Can you advise.

Thanks,

All replies (8)

_{Sunday, September 10, 2017 5:27 AM ✅Answered}

You can refer the below pdf to log the blocking information. This is very useful.

http://sqlmag.com/site-files/sqlmag.com/files/archive/sqlmag.com/content/content/142603/wpd-sql-extevtandnotif-us-sw-01112012_1.pdf

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
[Blog]

_{Sunday, September 10, 2017 6:29 AM ✅Answered}

https://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server/

>>>How to apply above things in SSMS

Open a new query and run

sp_configure 'show advanced options', 1 ; GO RECONFIGURE ; GO sp_configure 'blocked process threshold', 20 ; GO RECONFIGURE ; GO

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/blocked-process-threshold-server-configuration-option

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Sunday, September 10, 2017 9:19 AM ✅Answered}

I have gone through it, so Currently our server has blocking process threshold is 0. when I set it to some other value, then blocked process reports are generated ? but How will get those reports? Do I need to create any events in Prod server ?

Will I get to know time and date when session blocking got changed from 2 to 1?

If you collect the correct information, yes. And you are able to interpret the information correctly.

If you configure "Blocked process report", SQL Server will generate internal events when then it detects that a process has been blocked longer than the threshold. Then you need to collect this event somehow. This can be done by

1) SQL Trace.
2) Extended events.
3) Event notifications.

Whichever method you select, you will need to analyse the information. Each report is an XML document, and you will need to write XQuery to wrestle out information from it.

People have been kind to post links to various article about this. They may go over your head, if you are fresh to SQL Server, but you are not exactly asking a trivial question. So if you want to pursue the answer, it will take you to corners of the product you have not seen before.

_{Sunday, September 10, 2017 4:56 AM}

You should capture blocking probably by using blocked process threshold

For example, a 200-second blocked process threshold can
be configured in SQL Server Management Studio as follows:

1.
Execute Sp_configure ‘blocked process threshold’, 200

2.
RECONFIGURE WITH OVERRIDE;

Once the blocked process threshold is established, the next step is
to capture the trace event. The trace events of blocking events that
exceed the user configured threshold can be captured with SQL Trace or Profiler.

3.
If using SQL Trace, use sp_trace_setevent and event_id=137.

4.
If using SQL Server Profiler, select the Blocked Process Report event
class (under the Errors and Warnings object).

Blocking per object with sys.dm_db_index_operational_stats
The new SQL Server 2005 DMV Sys.dm_db_index_operational_stats

select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc

Also read https://www.mssqltips.com/sqlservertip/2927/identify-the-cause-of-sql-server-blocking/

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

_{Sunday, September 10, 2017 5:44 AM}

Thank for reply.

I am actually new to SQL server, I am not able to apply these things in SSMS

could you please advise How to apply above things in SSMS , so i will get to know

Thanks

_{Sunday, September 10, 2017 6:17 AM}

Rakpa88, The link I provided, you need to go to blockedprocess page and just follow the steps Jason explained. He has done a wonderful job in explaining how to implement the same.

Try and let us know if you find any issues, we would be able to help you.

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
[Blog]

_{Sunday, September 10, 2017 7:46 AM}

Thank for suggestion

I have gone through it, so Currently our server has blocking process threshold is 0. when I set it to some other value, then blocked process reports are generated ? but How will get those reports? Do I need to create any events in Prod server ?

Will I get to know time and date when session blocking got changed from 2 to 1?

please advise.

thanks,

_{Sunday, September 10, 2017 8:13 AM}

Please see here (step by step)

https://www.sqlskills.com/blogs/erin/capture-blocking-information-with-extended-events-and-the-blocked-process-report/

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

Share via

How to find history of Blocking?

Question

All replies (8)

Additional resources