axinthefield: Capture Database Blocking with Performance Analyzer for Microsoft Dynamics

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...-dynamics.aspx
==============

Performance Analyzer 1.0 for Microsoft Dynamics is the tool used by Microsoft Dynamics support, Premier Field Engineers and product team members to diagnose performance issues with Dynamics products. The following is a step-by-step guide to capturing blocking information with this toolset on your SQL Server environment. This tool will capture blocking information for any SQL Server product



This tool is delivered as a SQL Server solution file and can be downloaded from http://code.msdn.com/dynamicsperf. Once the file is unzipped the solution can be opened from within SQL Server Management Studio.

What is Database Blocking?

Before going into setting up and capturing blocking information, I thought I would give a bit of background on database blocking and the challenges in determining root cause. Blocks occur when two users try to access the same records with incompatible lock types. Information about lock compatibility can be found here http://msdn.microsoft.com/en-us/library/ms186396.aspx. There are many reasons for blocks but the top 3 are users are updating the same records at the same time, queries scanning tables thus locking records not expected to be locked, and long running transactions.

Users locking the same records is a natural activity in a database. This is a natural activity in an ERP system that occurs all the time without the users even knowing that it occurs. Where this can become a problem is when there is a natural set of data based upon the business needs that will cause more of this activity than expected. Some business examples of this are where you may do 80% of your sales on 5 inventory items, or 90% of your purchase orders are from 3 vendors, or 70% of your sales are to 1 customer. In all of these situations, there will be a lot of natural database blocking occurring because of the business needs.

Queries scanning tables is a second major source of blocking that occurs in a database. An example of this is where a user might be requesting the information on a single sales order but the way the query is written is causing SQL to scan through all of the sales orders thus leaving record locks on the other sales orders blocking other users. With Performance Analyzer, we can do query analysis and find those situations relatively quickly. In the execution plan of the query we will have an index scan.















The third situation that occurs frequently is what we call long running transactions. This situation looks as follows in the database:

User1 User 2

Begin Tran

Update Table A

Select Table B

Update Table C Select Table A

Commit Tran

In this case, User 2 would get blocked because user 1 has not yet committed the transaction to the database. This is the hardest situation to determine root cause of with any blocking tools or scripts. The reason for this is because SQL Server will only show the currently executing SQL statement for User 1 which in this case is the Update to Table C. All of the tools will show the update to Table C blocking the Select from Table A. If you have this condition where the 2 SQL statements are not accessing the same tables, this is the tell-tale sign that the root cause is a long running transaction. The longer the transaction is the harder it is to find the root cause.

Capturing Blocking with Performance Analyzer

To capture blocking with Performance Analyzer, we have provided two different methods. (Please see http://blogs.msdn.com/b/axinthefield...-dynamics.aspx for setting up Performance Analyzer).

Option 1: SQL Trace

For our preferred method of capturing blocking, we have provided a method that uses SP_TRACE to setup a trace that tracks blocking events and a few other events which can lead up to blocking. SP_TRACE is similar to using SQL Profiler but with less stress on SQL Server. The trace is captured on the SQL Server side with no client involvement as there is with SQL Profiler. The events we capture are:

• 55 - Hash Warning
• 60 - Lock Escalation
• 67 - Execution Warnings
• 79 - Missing Column Statistics
• 80 - Missing Join Predicate
• 92 - Data File Growth
• 93 - Log File Growth
• 137 - Blocked Process Report
• 148 - Deadlock Graph

To enable capturing of blocking data with this method, do the following:

1) Create a folder on the C Drive of the SQL Server called SQLTRACE (This is the default location of the TRC files that the SQL Job creates)

NOTE: The job is configured so that it will not create more than 1 GB of files so there only needs to be 1 GB of free space on the C drive.

2) Start the DYNPERF_Log_Blocks_Option1_Tracing_Start SQL Job.











NOTE: This job is setup to only run for 25 hours so a schedule will have to be added if you want to run this job for more than 1 day.

The data will be loaded into the DynamicsPerf database by the SQL Job: DYNPERF_Log_Blocks_Option1_Load_Blocked_Data. This job is automatically configured to run every 10 minutes once you install Performance Analyzer for Microsoft Dynamics.

To begin analysis of the blocking data run the following query in the DynamicsPerf database:

SELECT * FROM [BLOCKED_PROCESS_INFO_VW]

To stop the tracing before the 25 hours is up, you can run the DYNPERF_Log_Blocks_Option1_Tracing_Stop job.











Option 2: Polling

This method of capturing data runs a SQL script that looks at the sys.processes system table to determine if blocking is happening. We have pre-configured the script to analyze every 2 seconds for blocking data. This is not our preferred method for collecting blocking data because if the server is under heavy load or heavy blocking conditions this script can add as much as 10% CPU load to the server. The one advantage this option has over using the tracing method is in the situation where API Cursors are being used such as our Dynamics AX product in particular. When analyzing the data from Option 1, you see sp_cursorfetch as the SQL statement, then you might want to consider using Option 2. Option 2 because of the way it polls the system can actually capture the SQL statement of the cursor so that you can see what the actual SQL statement is versus seeing the sp_cursorfetch statement.

To collect data using Option 2 Polling:

Start the DYNPERF_Log_Blocks_Option2_Polling job



NOTE: This job will not stop executing to just close the dialog







To begin analysis of this blocking data run the following query in the DynamicsPerf database:

SELECT * FROM [BLOCKS]

To stop this script, right click DYNPERF_Log_Blocks_Option2_Polling and select stop job.













If you have any comments or feedback, drop us a line at PFEDynamics@microsoft.com.




Источник: http://blogs.msdn.com/b/axinthefield...-dynamics.aspx