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

SQL Bangalore UG Meet

I had the opportunity to speak at the SQL Bangalore UG Meet on 22nd December 2012. A big thank you to Vinod Kumar (B | T) for organizing this meet!!

The topic that I presented on was the SQL Server System Health Session which has been there since SQL Server 2008 and has been further enhanced in SQL Server 2008.

The MSDN documentation on the System Health Session is available here: http://msdn.microsoft.com/en-us/library/ff877955.aspx

All the System Health Session articles that I have written are available in this summary post: https://troubleshootingsql.com/2012/09/24/system-health-session-and-beyond/ (The RDL files for the reports demoed can be downloaded from here)

System Health Session Custom Reports for management studio can be downloaded from here: https://troubleshootingsql.com/2011/09/28/system-health-session-part-4/ (Note that the custom SSMS reports only work for SQL Server 2008 and SQL Server 2008 R2 instances)

Presentation delivered at the meet is available above!

As usual it was good fun presenting at the session! And what I really like about such sessions is that I get to meet the #SQLFamily in person!!

DSC01328

Creating custom XEvent Templates

SQL Server 2012 added the Extended Events UI which made working with Extended Events session much easier. You no longer need to chalk up lengthy T-SQL scripts to spawn off your Extended Events monitoring session. The UI also provides you an option to view the collected data and perform groupings and aggregations for easier analysis.

The templates that you see while configuring an Extended Event session are located at “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Templates\sql\xevent“. When you export an Extended Events session, you will be given an option to export the Session as a Template to the following folder: “C:\Users\<user name>\Documents\SQL Server Management Studio\Templates\XEventTemplates“. The template is always exported as an XML file. Now when you create a new Extended Event Session, you will see the template under the user templates section in the templates view as shown in the screen shot below:

You can now choose to create as many templates as you want for your monitoring purposes. However, the template files have to be deposited in the above mentioned location so that Management Studio can show them to you while creating an Extended Events session.

The above blog post is a manifestation of a seemingly simple question asked by Naman Vadhera yesterday while he was exploring the Extended Events UI option.

Reference: http://msdn.microsoft.com/en-us/library/ms174169.aspx

System Health Session and beyond

I had blogged extensively about System Health Session available in SQL Server 2008 and above. I had also demonstrated how the System Health Session can be used in conjunction with SQL Server Management Studio reports in previous series that I ran on my blog.

System Health Session Dashboard Reports for SQL Server 2008 and SQL Server 2008 R2 – A set of reports available for SQL Server 2008/SQL Server 2008 R2 which provide visualizations for the events tracked by the System Health Session. The reports can be used using the custom reports option in SQL Server Management Studio.

System Health Session Dashboard – Shows the basic information of the information that can be fetched from the System Health Session.

System Health Session Dashboard — sp_server_diagnostics and more – A look into the different issues tracked by the sp_server_diagnostics script and how to build visualizations using that information.

System Health Session Dashboard – sp_server_diagnostics – The basics of what is covered by the sp_server_diagnostics procedure and how to build visualizations on top of the data captured.

System Health Session Dashboard – Error Summary Report
– Shows information on how to write queries to fetch information about various errors tracked by the System Health Session. Shows information tracked by the enhanced System Health Session in SQL Server 2012.

System Health Session Dashboard – Health Summary Report
– Gives you information about the SQL Server instance health like CPU usage, memory etc. A screenshot of the report is shown below:

I have added UTC time formatting so that the time is shown in the same time zone based on the server on which you are viewing the report.

I will keep updating this post once I keep updating with the new reports. As usual, feedback is always welcome.

The report definitions (.rdl) can be downloaded from here.

Awesomesauce: Deprecated Features usages

SQLServer:Deprecated Features is a performance object that is available since SQL Server 2005. The number of deprecated features have definitely increased since SQL Server 2005. In SQL Server 2012, there are a number of deprecated features whose use on your SQL Server 2012 instance can be determined through the use of this performance object.

Some of the common features that you might be interested in finding out the usage are DBCC REINDEX, DBCC SHOWCONTIG, DBCC INDEXDEFRAG. Once you have found the usage of these, you can find out which application uses this by looking up the SQL Server default trace.

Example:

declare @tracename nvarchar(4000)

select @tracename = path from sys.traces where id = 1
select TextData, StartTime, EndTime, spid, ServerName, HostName, error, SessionLoginName ,ApplicationName, TransactionID
from fn_trace_gettable(@tracename,default)
where EventClass = 116
and (TextData like '%showcontig%' or TextData like '%reindex%' or TextData like '%indexdefrag%') 

Download the T-SQL script to find out the deprecated features being used on your SQL Server 2012 instance using the script below.

Script name: Deprecated_Features.sql

The above is a good way to determine the use of deprecated features and which requires a change in your SQL Server environment.

References
Discontinued Engine Features in SQL Server 2012
http://msdn.microsoft.com/en-us/library/ms144262.aspx
Deprecated Engine Features in SQL Server 2012
http://technet.microsoft.com/en-us/library/ms143729(SQL.110).aspx

Awesomesauce: Perf Dashboard on Native SSRS

I had been asked the question about using the Perf Dashboard reports from a Reporting Services instance multiple times. Though this was not the intention of launching the SQL Server Performance Dashboard Reports but there is a compelling need at times to have these accessible from a web URL. In today’s age, you will find a lot of DBAs monitor their SQL Server instances remotely. Sometimes, there is another layer of complexity added to this when they want to just look at the health of the SQL Server instance by accessing a URL exposed through a corporate server in their environment. In such cases, you wouldn’t want to jump through a few hoops of setting up your VPN connection and blah blah.

So with all that in mind, let’s talk about how you can get your SQL Server 2012 Performance Dashboard to your existing SQL Server Reporting Services instance.

  • First you need to create a new Reporting Services Project using SQL Server Data Tools (SSDT).
  • Use the solution explorer to add all the existing performance dashboard reports from the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard folder.
  • Modify the project properties to ensure that the data source gets written to the same folder as the reports.
  • Create a shared data source pointing to your SQL Server instance.
  • Open each report that was imported into the project and modify the data source properties to use the shared data source that you created in the above step.
  • Modify the project deployment properties as shown below.

image

Now you are ready to deploy your Performance Dashboard SSRS project to your reporting services instance.

What can you do next?

You can now set up Report Server subscriptions for the set of reports that you wish to receive via email. You can get a status report of your SQL Server instance without even having to lift a finger and that to right in your mailbox! Smile

Note that Performance Dashboard is a tool which is provided “AS-IS” by Microsoft. The steps mentioned above will help you deploy the existing Performance Dashboard reports to a SQL Server Reporting Services instance. However, the failure to deploy these reports as mentioned above is not liable for support by Microsoft SQL Server Support team.

System Health Session: Part 4

This is the last post for the System Health Session series. In this post, I shall show you how to extract deadlock related information from the deadlock graph captured by the System Health Session.

The deadlock graph captured typically has three distinct nodes:

victim-list – Deadlock victim’s process identifier
process-list – Information pertaining to all the processes involved in the deadlock
resource-list – Information about the resources involved in the deadlock

The query below will provide you with the time stamp when the deadlock was reported along with victim process identifier.

 -- Fetch the Health Session data into a temporary table

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'
-- Gets the Deadlock Event Time and Victim Process
SELECT C.query('.').value('(/event/@timestamp)[1]', 'datetime') as EventTime,
CAST(C.query('.').value('(/event/data/value)[1]', 'varchar(MAX)') AS XML).value('(<a>/deadlock/victim-list/victimProcess/@id)[1]','varchar(100)'</a>) VictimProcess
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report'
-- Drop the temporary table
DROP TABLE #SystemHealthSessionData 

The next query (when provided with an event time from the above query output)
will provide you a parsed version of the process list in a tabular format which
can be easier to read when you have a large number of sessions involved in the
deadlock.

 -- Fetch the Health Session data into a temporary table

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

-- Parses the process list for a specific deadlock once provided with an event time for the deadlock from the above output

;WITH CTE_HealthSession (EventXML) AS
(
SELECT CAST(C.query('.').value('(/event/data/value)[1]', 'varchar(MAX)') AS XML) EventXML
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report'
AND C.query('.').value('(/event/@timestamp)[1]', 'datetime') = '2011-09-28 06:24:44.700' -- Replace with relevant timestamp
)
SELECT DeadlockProcesses.value('(@id)[1]','varchar(50)') as id
,DeadlockProcesses.value('(@taskpriority)[1]','bigint') as taskpriority
,DeadlockProcesses.value('(@logused)[1]','bigint') as logused
,DeadlockProcesses.value('(@waitresource)[1]','varchar(100)') as waitresource
,DeadlockProcesses.value('(@waittime)[1]','bigint') as waittime
,DeadlockProcesses.value('(@ownerId)[1]','bigint') as ownerId
,DeadlockProcesses.value('(@transactionname)[1]','varchar(50)') as transactionname
,DeadlockProcesses.value('(@lasttranstarted)[1]','varchar(50)') as lasttranstarted
,DeadlockProcesses.value('(@XDES)[1]','varchar(20)') as XDES
,DeadlockProcesses.value('(@lockMode)[1]','varchar(5)') as lockMode
,DeadlockProcesses.value('(@schedulerid)[1]','bigint') as schedulerid
,DeadlockProcesses.value('(@kpid)[1]','bigint') as kpid
,DeadlockProcesses.value('(@status)[1]','varchar(20)') as status
,DeadlockProcesses.value('(@spid)[1]','bigint') as spid
,DeadlockProcesses.value('(@sbid)[1]','bigint') as sbid
,DeadlockProcesses.value('(@ecid)[1]','bigint') as ecid
,DeadlockProcesses.value('(@priority)[1]','bigint') as priority
,DeadlockProcesses.value('(@trancount)[1]','bigint') as trancount
,DeadlockProcesses.value('(@lastbatchstarted)[1]','varchar(50)') as lastbatchstarted
,DeadlockProcesses.value('(@lastbatchcompleted)[1]','varchar(50)') as lastbatchcompleted
,DeadlockProcesses.value('(@clientapp)[1]','varchar(150)') as clientapp
,DeadlockProcesses.value('(@hostname)[1]','varchar(50)') as hostname
,DeadlockProcesses.value('(@hostpid)[1]','bigint') as hostpid
,DeadlockProcesses.value('(@loginname)[1]','varchar(150)') as loginname
,DeadlockProcesses.value('(@isolationlevel)[1]','varchar(150)') as isolationlevel
,DeadlockProcesses.value('(@xactid)[1]','bigint') as xactid
,DeadlockProcesses.value('(@currentdb)[1]','bigint') as currentdb
,DeadlockProcesses.value('(@lockTimeout)[1]','bigint') as lockTimeout
,DeadlockProcesses.value('(@clientoption1)[1]','bigint') as clientoption1
,DeadlockProcesses.value('(@clientoption2)[1]','bigint') as clientoption2
FROM (select EventXML as DeadlockEvent FROM CTE_HealthSession) T
CROSS APPLY DeadlockEvent.nodes('//deadlock/process-list/process') AS R(DeadlockProcesses)

-- Drop the temporary table
DROP TABLE #SystemHealthSessionData

The script file for the above queries can be downloaded from here.

A sample output of the above two queries is shown below:

The second dataset which shows the parsed process list from the deadlock graph is for the timestamp highlighted above.

As mentioned in one of my previous posts, the custom reports used in the previous posts can be downloaded from here (Filename: System_Health_Session_Custom_Reports.zip).

Modification April 20th, 2012: Just updated the .sql files and added the Deadlock Report to the SkyDrive location.

Note: To make use of the deadlock graph captured by the System Health Session, you need to have the required update applied to avoid the issue mentioned in KB978629. The issue is addressed in:
981355    Cumulative Update package 1 for SQL Server 2008 R2
http://support.microsoft.com/default.aspx?scid=kb;EN-US;981355
977443    Cumulative update package 6 for SQL Server 2008 Service Pack 1
http://support.microsoft.com/default.aspx?scid=kb;EN-US;977443

If you don’t have the updates installed, then Jonathan (Blog | Twitter) has shown in his article Retrieving Deadlock Graphs with SQL Server 2008 Extended Events how to workaround the issue. Michael Zilberstein’s (Blog) article, Parsing Extended Events xml_deadlock_report, has an updated/corrected version of the T-SQL to fetch the deadlock information.

References:

Bart Duncan’s Weblog:
Deadlock Troubleshooting, Part 1
Deadlock Troubleshooting, Part 2
Deadlock Troubleshooting, Part 3