Microsoft SQL - Query Blocking Report

One of the most common problems encountered with databases is blocking. What matters to a database administrator is identifying when blocking is a problem and how to deal with it effectively. When blocking is bad enough, users will notice slowdowns and complain about it. With many users, it is common for tens or hundreds of processes to be blocked when slowdowns are noticed. When you have lots of blocking that is not resolved in a reasonable amount of time, you need to identify the root blocker, or the process at the top of the tree of blocked processes. To historically analyze blocking and the root blockers responsible for it, administrators can use the Query Blocking Report. This report helps administrators study past trends in blocking. The trend analysis reveals whether/not the count of blocking processes spiked every time the count of root blockers did. The report also pinpoints the exact day on which the root blockers and blocked processes were at their peak. Additionally, the report enables administrators review the queries that are blocking other queries, so that they can figure out the reason on why the queries are blocking and optimize the query line so that it no longer blocks.

To generate this report, do the following:

  1. Select the Query Blocking option by following the menu sequence: REPORTS BY FUNCTION -> Domain-specific Reports -> Database Technologies -> Microsoft SQL / Cluster. Figure 1 will then appear.

    Figure 1 : Setting the criteria for generating the Query Blocking report

  2. When Figure 1 appears, select a criterion for analysis from the Analyze By list box. Using this report, you can analyze the queries that are blocking other queries that are executing on one/more independent database servers, or those that are part of a segment, service, or a zone. This way, you can assess the impact of the desktops on a particular service/segment/zone, and accordingly take decisions. The options provided by the Analyze By list box are discussed hereunder:

    • Component:Select this option to choose the component(s) from across all the managed components in the environment.
    • Service: Select this option if the component for which a report is to be generated are involved in the delivery of a business service. Then, select a Service.
    • Segment: Choose this option if the components to be evaluated are part of a segment. Then, pick a Segment for analysis.
    • Zone: To generate a report for one/more components that are included in a zone, pick the Zone option. A Zone drop-down list will then appear, from which you would have to select the zone to which the components of interest to you belong. A Sub zone flag also appears. Indicate whether the components present within the sub-zones of the chosen zone are also to be to be considered for report generation, by setting the Sub zone flag to Yes.
  3. Then, select a component type for which you wish to generate the report from the Component Type list. By default, Microsoft SQL is chosen from this list.
  4. The Components list will now be populated with all the components that are managed in your environment for the chosen component type. Pick a component of your choice from this list.

  5. Then, specify the Timeline for the graph. You can either provide a fixed time line such as 1 hour, 2 days, etc., or select the Any option from the list to provide a From and To date/time for report generation.

    Note:

    For every user registered with the eG Enterprise system, the administrator can indicate the maximum timeline for which that user can generate a report. Once the maximum timeline is set for a user, then, whenever that user logs into eG Reporter and attempts to generate a report, the Timeline list box in the report page will display options according to the maximum timeline setting of that user. For instance, if a user can generate a report for a maximum period of 3 days only, then 3 days will be the highest option displayed in the Timeline list - i.e., 3 days will be the last option in the fixed Timeline list. Similarly, if the user chooses the Any option from the Timeline list and proceeds to provide a start date and end date for report generation using the From and To specifications, eG Enterprise will first check if the user's Timeline specification conforms to his/her maximum timeline setting. If not, report generation will fail. For instance, for a user who is allowed to generate reports spanning over a maximum period of 3 days only, the difference between the From and To dates should never be over 3 days. If it is, then, upon clicking the Run Report button a message box will appear, prompting the user to change the From and To specification.

  6. In addition to the settings discussed above, this report comes with a set of default specifications. These settings are hidden by default. If you do not want to disturb these default settings, then you can proceed to generate the report by clicking the Run Report button. However, if you want to view and then alter these settings (if required), click on the icon. The default settings will then appear in the More Options drop down window (See Figure 2). The steps below discuss each of these settings and how they can be customized.

    Figure 2 : The default settings for generating the Query Blocking Report

  7. If the timeline specified for the report needs to exclude the data collected during the Weekends, then set Exclude weekends to Yes. By default, this parameter is set to No.

    Note:

    You can configure the days of the week that need to be considered as a ‘weekend’ using the Days parameter in the [EXCLUDE_WEEKEND] section in the eg_report.ini file (in the <eG_INSTALL_DIR>\manager\config directory).  The Days parameter is set to Saturday,Sunday by default. To change this weekend specification, enter two other days of the week against the Days parameter.

  8. Next, indicate the report Time Period.

    Note:

    By default, the Time Period is set to 24 hours. Accordingly, the From and To parameters in the [timeframe] section of the eg_report.ini file (in the <eG_INSTALL_DIR>\manager\config directory) are set to 00:00 and 24:00 respectively. If need be, you can override this default setting by configuring a different timeframe against the From and/or To parameters. 

  9. In large environments, reports generated using months of data can take a long time to complete. Administrators now have the option of generating reports on-line or in the background. When a report is scheduled for background generation, administrators can proceed with their other monitoring, diagnosis, and reporting tasks, while the eG manager is processing the report. This saves the administrator valuable time. To schedule background processing of a report, you can either select the Background Save - PDF option or the Background Save - CSV option from the Report Generation list. In this case, a Report Name text box will appear, where you would have to provide the name with which the report is to be saved in the background. To process reports in the foreground, select the Foreground Generation - HTML option from this list.

    Note:

    • The Report Generation list will appear only if the EnableBackgroundReport flag in the [BACKGROUND_PROCESS] section of the eg_report.ini file (in the <eG_INSTALL_DIR>\manager\config directory) is set to Yes.
    • The default selection in the Report Generation list will change according to the Timeline specified for the report. If the Timeline set is greater than or equal to the number of days specified against the MinDurationForReport parameter in the [BACKGROUND_PROCESS] section of the eg_report.ini file, then the default selection in the Report Generation list will be Background Save - PDF. On the other hand, if the Timeline set for the report is lesser than the value of the MinDurationForReport parameter, then the default selection in the Report Generation list will be Foreground. This is because, the MinDurationForReport setting governs when reports are to be processed in the background. By default, this parameter is set to 2 weeks - this indicates that by default, reports with a timeline of 2 weeks and above will be processed in the background.
  10. Click the Done button if any changes were made to the More Options drop down window.
  11. Finally, click the Run Report button.

  12. Figure 3 will then appear.

    Figure 3 : The generated Query Blocking report

    From Figure 3, administrators can figure out the following:

    • The Root Blockers line chart reveals at a single glance the number of root blocker queries encountered in the target database server over a period of time. Using this graph administrators can figure out the exact date/time on which maximum number of root blockers were encountered in the target environment over the chosen period of time.
    • The Blocked Processes graph reveals at a single glance the number of blocked processes encountered in the target database server over a period of time. Using this graph administrators can figure out the exact date/time on which maximum number of blocked processes were noticed in the target environment over the chosen period of time.
    • The Observation section reveals the number of root blockers detected over a chosen period of time and also reveals the exact date and time at which maximum number of root blockers were detected.

    • The Top Queries Blocking Other Queries section (see Figure 4) reveals the top commands/queries that were blocking other queries in the chosen period of time.

      Figure 4 : The top queries blocking other queries listed in the generated report

  13. On the other hand, if the Background Save - PDF option is chosen from the Report Generation list, then clicking on the Run Report button will not generate the report and display it in this page for your benefit. Instead, a message indicating that the report is being processed in the background will appear. This will be accompanied by a link that will lead you to the page that lists all the reports that are being processed in the background, and their current status. If background report generation fails for a report, you can regenerate that report using this page, or can even delete that report if need be. On the other hand, if background processing successfully completes for your report, then, you can view a PDF of the report by clicking on the icon in that page.