Category Archives: TSQL

Enabling Transactional Replication: A bit of help

Posted on by

Over the past few months, I have discussed the feasibility of enabling transaction replication for customer databases on various occasions. Every time I end up writing queries to answer certain questions about the database… the most common one being if the tables that need to be replicated have primary keys.

So I finally decided to write a T-SQL script which will help me answer the most common questions asked about a database while deciding on the feasibility of enabling transaction replication.

The script doesn’t capture information like workload, performance metrics etc. to decide if the replication workload (snapshot and distribution agent) can be supported on the existing hardware and resources available in the environment.

My take on the matter is that this information is required only once we have figured out if transactional replication can be enabled on the database or not. Eg. If the main tables that need to be replicated do not have primary keys, then the question of resource availability and hardware capability is moot point!

The script below checks the following:

1. Existing of primary keys on the tables in the database. Objects (articles) without primary keys cannot be replicated as part of a transactional replication publication.
2. If the database has transparent database encryption enabled. The subscriber database is not automatically enabled for TDE in such a scenario.
3. Constraints, primary keys, triggers and identify columns which have  NOT FOR REPLICATION bit set and which objects do not. You might choose to replicate or not replicate some of these objects. However, you need to be aware of what you are replicating.
4. Tables having ntext, text and image columns as there are special considerations for handling DMLs on such columns.
5. XML schema collections present in the database. Modifications to the XML Schema collection are not replicated.
6. Tables with sparse column sets as they cannot be replicated.
7. Objects created using WITH ENCRYPTION option. Such objects cannot be replicated either.

As always, in case you think that there are additional checks that could be included in the script, then please leave a comment on my blog and I will add the same into the script.

Continue reading →

System Health Session and Deadlocks

Posted on by

I had blogged about retrieving deadlock related information using the default Extended Event session which runs by default on all SQL Server 2008 instances and above. However, once you have retrieved the XML deadlock graph, it could be quite cumbersome to read if the deadlock happens to be complex or involves multiple nodes. I frequently require the need to fetching the information about past deadlocks from the System Health Session data while working on customer environments. Due to the frequent repetitive nature of the data collection, I decided to automate this task.

I again decided to use a combination of Powershell and T-SQL to extract this information. The Powershell script (TransformtoXDL.ps1), which requires Powershell 2.0 or above, uses a T-SQL script (TransformtoXDL.sql ) to extract data from the System Health Session and outputs each individual deadlock graph as a separate .XDL file into a folder of your choice with the timestamp of the occurrence of the deadlock. Note that the time reported will be in GMT timezone.

The powershell script accepts two parameters: vServername for the SQL Server instance that you want to extract the data from and the vPath for the folder into which the XDL files should be saved into.

.\TransformToXDL.ps1 -vServername "<server name>" -vPath "C:\Tempdb\"

Yes… I have a folder called Tempdb on my C: drive!! Smile

A sample output is shown in the screenshot below:

image

The Transact-SQL script called TransformtoXDL.sql does the following:

1. Extracts the System Health Session data into a temporary table
2. Based on the version of your SQL Server instance, it performs the parsing to extract the deadlock graph. This script accounts for issues mentioned in KB978629. I would like to thank Michael Zilberstein [B] for the proposed corrective action on an issue that Jonathan Kehayias [B|T] had blogged about.
3. The last action that the script takes is to perform XML modification to get the XML deadlock data in the same format which is recognized by SQL Server 2012 Management Studio when viewing XDL files.

The powershell and T-SQL script can be downloaded here.


#    Script Name: TransformToXDL
#    Author: Amit Banerjee
#    Date: September 6, 2012
#    Description:
#    The script reads the deadlock graphs from the System Health Session
#    Ring Buffer and parses them to create an individual deadlock graph
#    in a folder of your choice.
#    Usage: .\TransformToXDL.ps1 -vServername "INST1" -vPath "C:\Tempdb\"
# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

Param ([string] $vServername,
[string] $vPath)

cls

# Load the SQL Server snap-in for using sqlcmd cmdlet
$ErrorActionPreference = "SilentlyContinue"
Import-Module sqlps
$ErrorActionPreference = "Continue"

Write-Host "`nConnecting to SQL Server instance " $vServerName " to extract deadlock information"
# Extract the deadlock graphs and parse them in the system health session
Invoke-Sqlcmd -InputFile "C:\Tempdb\TransformToXDL.sql" -ServerInstance $vServerName

# Function to get the information from the table stored in tempdb
function Get-SqlData
{
param(
[string]$serverName=$(throw 'serverName is required.'),
[string]$databaseName,
[string]$query
)

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)
$dt

}

# Get the data stored in tempdb using the function defined above
$rows = get-sqldata $vServername  "tempdb"  "select row_id,event_time,deadlockgraph from tempdb.dbo.deadlock_graphs"

$vCount = 0
# Extract each row retrieved into an individual XDL file with the timestamp of the issue
foreach ($row in $rows)
{
if($row -ne $null)
{
$vCount++
$vFileName = $vPath + $vServername.Replace("\","_")+ "_" + $row.event_time.ToString().Replace(":","_").Replace("/","_") + ".xdl"
Write-Host "`nCreating file: "  $vFileName
$row.deadlockgraph | Out-File $vFileName
}
}

Write-Host "`nDeadlocks found: " $vCount.ToString()
Write-Host "`nPerforming cleanup"
Invoke-Sqlcmd -Query "IF EXISTS (SELECT TOP 1 name FROM tempdb.sys.objects where name = 'deadlock_graphs')
BEGIN
DROP TABLE tempdb.dbo.deadlock_graphs
END" -ServerInstance $vServerName

The above has been tested on SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. The resulting XDL files can be opened in SQL Server 2012 Management Studio. I am always looking for feedback. So please feel free to Tweet, Facebook or Email me regarding any issues or enhancements that you might need for the same.

You might want to remember that the T-SQL query used is a resource intensive query and it is preferable that you run this extraction exercise during non-business hours especially if your SQL Server instance is experiencing a large number of deadlocks.

SQL Feature Discovery Script

Posted on by

As part of my work, I very frequently have to collect information about the various database engine features that are currently being used on a particular SQL Server instance. Sometimes, this requires me to write T-SQL scripts to fetch the required information. I had updated my initial data collection script some time back and this gave me the idea to write up another set of T-SQL queries to fetch the information for the database engine features in use.

The script collects a bunch of information which are categorized under the following headings:

1. General Server Configuration
        Server Info
        Non-default sp_configure settings
        Server Settings
        Active Trace Flags
2. Replication Configuration
        Replication Publishers
        Merge Replication Publishers
        Replication Subscribers
        Replication Distributors
3. Full-text enabled databases
4. Linked Servers
5. SQL Agent information
6. Databases
        Database information
        Database file information
7. Server Triggers
8. Policy Based Management
9. Resource Governor
10. Database Mail
11. Log Shipping
12. Database Mirroring
13. SQL CLR Assemblies
14. sp_OA* procedures

Usage

  1. Download the script using the link given at the bottom of the page and save it to a file named SQL_DISCOVERY.SQL. Open the script file in a SSMS Query Window.
  2. Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
  3. Execute the script and specify SQL_DISCOVERY.html as the output file name so that we can get the output in the require HTML format.
  4. Once the script is completed, open the HTML file.

Script download: image

If you have any feedback about the script or feel any new additions to the existing data that is being captured, please feel free to leave a comment!

Technorati Tags: ,,

Missing indexes and Create Index command from the procedure cache

Posted on by

Recently I was working on a performance issue for SQL Server where I needed to identify the missing indexes on the database instance. This was quite easy since I have a T-SQL script to do this which I have already blogged about before. Now the next task was to identify the SQL statements for which these indexes were suggested. Now this was also easy since my friend Jonathan Kehayias [B | T] had already blogged about this. The next ask now was to get the Create Index command for the list of missing indexes received! Well…. this time I ended up with a strike as I didn’t have any options with me. So I got down to modifying Jonathan’s T-SQL script to add to new commands to provide the CREATE INDEX statement in a separate column of the temporary table #MissingIndexInfo which his script creates.

ALTER TABLE #MissingIndexInfo ADD CreateIndexStatement varchar(8000)

UPDATE #MissingIndexInfo
SET CreateIndexStatement = ‘CREATE INDEX <index name>’ + ‘ ON ‘ + statement + ‘ (‘ + ISNULL (equality_columns,”) + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (inequality_columns, ”) + ‘)’ + ISNULL (‘ INCLUDE (‘ + include_columns + ‘)’, ”)

 

Before you go ahead and start deploying these scripts to your environments, you need to be cognizant of the fact that the procedure cache may or may not have all the cached plans for all the queries that you want to examine. You would want to find out the missing indexes using the query here and compare it with the list that you retrieved using Jonathan’s query with the modification listed above. This sample script is an attempt to provide you with a list of indexes which may prove beneficial for your queries based on the Missing Indexes feature which was introduced in SQL Server 2005 and above. As always, you would still need to test before implementing these indexes onto a production server instance.

System Health Session: Part 4

Posted on by

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