Default Trace–Performance Issues


There are multiple events that a default trace in SQL Server 2005 and above tracks which can be significantly useful for finding out areas of improvement. The events that I will be concentrating on are:

1. Missing Column Statistics – This event class indicates that column statistics that could have been useful for the optimizer are not available due to which an incorrect cardinality estimation could occur. This can cause the optimizer to choose a less efficient query plan than expected. You will not see this event produced unless the option to auto-create statistics is turned off.

2. Missing Join Predicate – This event class indicates that a query is being executed that has no join predicate. (A join predicate is the ON search condition for a joined table in a FROM clause.) This could result in a long-running query. This event is produced only if both sides of the join return more than one row.

3. Sort Warnings – This event class indicates that sort operations do not fit into memory. This does not include sort operations involving the creation of indexes, only sort operations within a query (such as an ORDER BY clause used in a SELECT statement). The EventSubClass field in this event shows whether this was a single pass or a multiple pass. A single pass (EventSubClass = 1) is when the sort table was written to disk, only a single additional pass over the data was required to obtain sorted output. A multiple pass (EventSubClass = 2) is when the sort table was written to disk, multiple passes over the data were required to obtain sorted output. A multiple pass is an enemy of query performance.

4. Hash Warnings – This event class can be used to monitor when a hash recursion or cessation of hashing (hash bailout) has occurred during a hashing operation.  Hash recursion (EventSubClass = 0) occurs when the build input does not fit into available memory, resulting in the split of input into multiple partitions that are processed separately. Hash bailout (EventSubClass = 1) occurs when a hashing operation reaches its maximum recursion level and shifts to an alternate plan to process the remaining partitioned data. Hash bailout usually occurs because of skewed data. Another enemy of performance!

5. Server Memory Change – This event class occurs when Microsoft SQL Server memory usage has increased or decreased. You can even determine what is the current memory usage after the increase or decrease.

6. Log File Auto Grow – This event class indicates that the log file grew automatically. This event is not triggered if the log file is grown explicitly through ALTER DATABASE. Frequent log file growths are not food for performance.

7. Data File Auto Grow – This event class indicates that the data file grew automatically. This event is not triggered if the data file is grown explicitly by using the ALTER DATABASE statement.

Since this information is already available in the default trace, I decided to use my Default Trace Statistics Power View Excel sheet to track this information graphically. And this is what I got (see screenshot 1)!

DefaultTrace_PerfIssues

So what is the above Excel sheet displaying?

1. The information available in the first column chart will show the Data and Log file grow events per database.

2. The first matrix in the middle of the Excel sheet shows the number of Sort Warnings and Hash Warnings with drill-down capabilities for each database to see the EventSubClass fields.

3. The second matrix shows the Missing Column Statistics and the Missing Join Predicate events for each database. The drill-down capability gives the name of the column statistics that was missing.

4. The line graph shows the change in memory for the SQL Server database engine.

Happy monitoring!

Previous posts in this series:

Schema Changes History Report

Advertisements

Report Server Analytics with PowerPivot


I recently needed to query report execution statistics for a SQL Server Reporting Services instance where the number of rows were quite large. So I decided to use Excel 2013 and PowerPivot to analyze the data. This would allow me to directly pull all of the data into an Excel sheet and then perform analysis on the extracted data without having to query the Report Server database repeatedly.

The first thing to do is to create a connection to your Report Server database table. That can be done using the PowerPivot tab in Excel 2013. Click on the Manage button (Screenshot 1).

image

Once you are PowerPivot window, you will need to establish a connection with your Report Server database. This can be done using the From Database drop-down list with the From SQL Server option (Screenshot 2).

image

Create a connection to your SQL Server instance which hosts the Report Server database using the Table Import Wizard. In the Choose How to Import Data screen, select the second option i.e. Write a query that will specify the data to import. Once you click Next, provide a friendly name for the query and copy-paste the query shown below.

 select
b.Name,a.UserName,
CASE(a.RequestType)
WHEN 0 THEN 'Interactive'
WHEN 1 THEN 'Subscription'
WHEN 2 THEN 'Refresh Cache'
ELSE 'Unknown'
END AS RequestType,
a.Format, a.InstanceName,
CASE(a.ReportAction)
WHEN 1 THEN 'Render'
WHEN 2 THEN 'BookmarkNavigation'
WHEN 3 THEN 'DocumentMapNavigation'
WHEN 4 THEN 'DrillThrough'
WHEN 5 THEN 'FindString'
WHEN 6 THEN 'GetDocumentMap'
WHEN 7 THEN 'Toggle'
WHEN 8 THEN 'Sort'
WHEN 9 THEN 'Execute'
WHEN 10 THEN 'RenderEdit'
ELSE 'Unknown'
END AS ItemAction,
a.TimeStart,a.TimeEnd,a.TimeDataRetrieval,a.TimeProcessing,a.TimeRendering,a.[Status],a.ByteCount,a.[RowCount]
from dbo.ExecutionLogStorage a
inner join dbo.[Catalog] b
on a.ReportID = b.ItemID 

Now data obtained from the query will be imported into the PowerPivot table. Now I create a new PowerView sheet in the Excel workbook. Add a new column to add the TimeDataRetrieval, TimeProcessing and TimeRendering columns so that the total report duration is available at a single glance.

Now save the PowerPivot data and add a new PowerView Excel sheet called “Execution Stats”. This would allow you to create a view as shown in Screenshot 3.

image

Leverage PowerView’s interactive capabilities to view the Report execution statistics, the report processed status (failure or success) and the processing, rendering and data retrieval times for each report.

I have uploaded the file to SkyDrive which would allow you to connect to your own Reporting Services database and use the same report format for analysis. Stay tuned for additional PowerView sheets to give you various slicing and dicing options on report execution statistics.

Excel File Download Link: http://sdrv.ms/17ZmbK0 (Filename: SSRS_Exec_Stats.xlsx) Works with Excel 2013 only.