HK SQL Server Doctor

2015-07-03

Get current stored proc name and params list

Inside a stored procedure, it can get the current stored procedure name and its parameters list by the follow statements:
DECLARE @names varchar(MAX) = OBJECT_NAME(@@PROCID) + ' ';
SELECT @names += name + ',' FROM sys.parameters WHERE [object_id] = @@PROCID ORDER BY parameter_id;

2015-06-02

Stored Procedure Error Handling Pattern

Thanks to SQL Server MVP Erland Sommarskog, we got a unified and reliable error and transaction handling in stored procedures. Here I briefly demonstrate how to do.

1. Create the error handler sp:
-- =============================================
-- Author: Erland Sommarskog
-- Description: Error Handler SP
-- Usage: To be called inside CATCH block of a stored proc to reraise error. Error Line Number can be precisely checked by using [sp_helptext] system stored proc., e.g. sp_helptext '[dbo].[the_sp_name]'
-- Reference: General Pattern for Error Handling (http://www.sommarskog.se/error_handling/Part1.html)
-- =============================================
CREATE PROCEDURE error_handler_sp AS
BEGIN
DECLARE @errmsg nvarchar(2048),
@severity tinyint,
@state tinyint,
@errno int,
@proc sysname,
@lineno int

SELECT @errmsg = error_message(), @severity = error_severity(),
@state = error_state(), @errno = error_number(),
@proc = error_procedure(), @lineno = error_line()

IF @errmsg NOT LIKE '***%'
BEGIN
SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') +
', Line ' + ltrim(str(@lineno)) + '. Errno ' +
ltrim(str(@errno)) + ': ' + @errmsg
END
RAISERROR('%s', @severity, @state, @errmsg)
END
GO

2. Employ the error handling pattern in your stored procs:
/* TESTING TABLE */
CREATE TABLE sometable(a int NOT NULL,
b int NOT NULL,
CONSTRAINT pk_sometable PRIMARY KEY(a, b))
GO

/* ERROR HANDLING PATTERN */
CREATE PROCEDURE insert_data @a int, @b int AS
BEGIN
SET XACT_ABORT, NOCOUNT ON
/*
* The first line in the procedure turns on XACT_ABORT and NOCOUNT in single statement. This line is the only line to come before BEGIN TRY.
* Everything else in the procedure should come after BEGIN TRY: variable declarations, creation of temp tables, table variables, everything.
* Even if you have other SET commands in the procedure (there is rarely a reason for this, though), they should come after BEGIN TRY.
*/
BEGIN TRY
/* Non-transactional statements, e.g. variable declarations */
BEGIN TRANSACTION /* If your procedure does not perform any updates or only has a single INSERT/UPDATE/DELETE/MERGE statement, you typically don't have an explicit transaction at all. */
/* Transactional statements */
INSERT sometable(a, b) VALUES (@a, @b) -- sample
INSERT sometable(a, b) VALUES (@b, @a) -- sample
COMMIT TRANSACTION
/* Non-transactional statements, e.g. final SELECT to return data or assign values to output parameters */
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION -- Rolls back any open transaction
EXEC error_handler_sp -- Reraises the error
RETURN 55555 -- any error return code you defined on your application (Non-zero, zero is usually understood as success)
END CATCH /* NEVER have any code after END CATCH for the outermost TRY-CATCH */
END
GO

/* TEST CASES */
-- Outer SP
CREATE PROCEDURE outer_sp @a int, @b int AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
EXEC insert_data @a, @b
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
EXEC error_handler_sp
RETURN 55555
END CATCH
GO
EXEC insert_data 9, NULL
EXEC insert_data 8, 8
EXEC outer_sp 8, 8
EXEC outer_sp null, null
EXEC sp_helptext '[insert_data]' -- check error line number of the error throwing stored proc.

2015-05-18

Update Statistics based on the Percentage of Modifications

As the sp_updatestats nearly update all statistics in your database (it will update a statistic as long as it has had one row modified), I don't recommend this old fashioned method. A new DMF called dm_db_stats_properties was added in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1, we can use this DMF to check the number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated. Based on the percentage of modifications (#modifications / #total rows), we can pick out the statistics to update.

DECLARE @modifiedPercent int = 20

SELECT
'UPDATE STATISTICS ' + sch.name + '.' + so.name + ' [' + ss.name + '] WITH FULLSCAN' AS UpdateStatsStmt,
sch.name + '.' + so.name AS TableName,
ss.name AS Statistic,
sp.last_updated AS StatsLastUpdated,
sp.rows AS FilteredRows,
sp.unfiltered_rows AS TotalRows,
sp.rows_sampled AS RowsSampled,
sp.modification_counter AS Modifications
FROM sys.stats AS ss
JOIN sys.objects AS so ON ss.object_id = so.object_id
JOIN sys.schemas AS sch ON so.schema_id = sch.schema_id
CROSS APPLY sys.dm_db_stats_properties (so.object_id, ss.stats_id) AS sp
WHERE so.type = 'U'
AND sp.modification_counter / sp.unfiltered_rows >= @modifiedPercent
ORDER BY TableName

2015-04-17

Quick Solve Unclosed Transaction

The quickest way to find a process that transaction is unclosed and what statements it's running is:

1. DBCC OPENTRAN

Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the CURRENT Database of this DBCC OPENTRAN running. You can obtain the Process ID.

2. DBCC INPUTBUFFER(@spid)

After you obtained the Process ID from DBCC OPENTRAN, you can run DBCC INPUTBUFFER to get the last statement sent from the client that the process run by passing in the process ID into it.

Example: We will create a bad stored procedure and run it for demonstration purpose.

/* A Bad Stored Proc. */

USE [TestDB]

CREATE PROC [dbo].[UnclosedTran]

@p1 varchar(50)

BEGIN

BEGIN TRAN

UPDATE Test1 SET col1 = @p1

END

/* Run that SP */

USE TestDB

EXECUTE UnclosedTran 'ABC'

/* Find the SPID of oldest process with transaction unclosed */

/* use sp_MSForEachDB to loop through all databases */

EXEC sp_MSForEachDB 'DBCC OPENTRAN(?)'

/* Get the SPID value from the DBCC OPENTRAN output, then see its input buffer */

DBCC INPUTBUFFER(61) /* replace your spid value */

After you take notes of the “EventInfo” (the bad stored procedure / sql script), then you may decide to KILL the process. E.g.

KILL 61 /* replace your spid value */

2015-04-15

How to Add New Table(s) to Transactional Replication WITHOUT Re-Generate the Whole Snapshot

You have transactional replication configured in your production environment. You need to add a new article (table) to the publication. You wish to initialize only the new article added to the publication, in order to avoid taking a total snapshot of all existing articles in the publication. Below steps teach you how to do:

1. Set Publication Properties

USE <DB>

DECLARE @publication sysname = '<publication>'

-- check immediate_sync

EXEC sp_helppublication @publication

-- If immediate_sync = 1, fix it (independent_agent must be 1) by disable immediate_sync

EXEC sp_changepublication

@publication = @publication,

@property = 'allow_anonymous', @value = 'False'

EXEC sp_changepublication

@publication = @publication,

@property = 'immediate_sync', @value = 'False'

--check

EXEC sp_helppublication @publication

2. Add New Table(s) to the Publication's Articles list

Right-click the Publication -> "Properties" -> "Articles" -> Uncheck "Show only checked articles in the list" -> Check the "New Table" in the list -> Press "OK", e.g.

3. Generate mini-Snapshot for the New Table(s)

Right-Click that "Publication" -> "View Snapshot Status" -> Press "Start".

It should only generate 1 article (if only one new table was added) as shown below:

2015-04-10

Analyzing Deadlock using built-in "system _health" Extended Event

Background:

By default, SQL Server automatically starts an Extended Events Session called “system_health” when it start-up. This session collects information includes any deadlocks that are detected. (ref. http://blogs.msdn.com/b/psssql/archive/2008/07/15/supporting-sql-server-2008-the-system-health-session.aspx

https://msdn.microsoft.com/en-us/library/ff877955.aspx)

Without any extra monitoring and loading, this default enabled “system_health” X-Events Session gives us a way to check the deadlock information, including the locked objects (table/index) and SQL statements/stored procedure calls involved in the deadlock.

This “system_health” events session logs detected events into memory, ring buffer size 4MB, when the buffer fills up it will overwrite the oldest entries.

How to do:

Run the following SELECT statement in production server:
(ref. http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx)Use Master

SELECT

xed.value('@timestamp', 'datetime') as Creation_Date,

xed.query('.') AS Extend_Event

FROM

(

SELECT CAST([target_data] AS XML) AS Target_Data

FROM sys.dm_xe_session_targets AS xt

INNER JOIN sys.dm_xe_sessions AS xs

ON xs.address = xt.event_session_address

WHERE xs.name = N'system_health'

AND xt.target_name = N'ring_buffer'

) AS XML_Data

CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)

ORDER BY Creation_Date DESC

The result is ordered by the time of the deadlock (latest on top) like following:
Click on the xml inside “Extend_Event” in the result, it will show you the deadlock xml report (the xml tags < and > were transformed to < and >, you can copy the XML into a notepad, and replace them for better view). E.g.

<victim-list>

<process-list>

</frame>

</frame>

</executionStack>

EXEC uspDeadlockTest_2 2002 </inputbuf>

</process>

</frame>

</frame>

</executionStack>

EXEC uspDeadlockTest_1 101 </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

</deadlock-list>

</value>

</data>

</event>

As you can see above, the affected sql statements or stored procedure calls are in the <inputbuf> element. The deadlock victim (process being killed and rolled-back by SQL Server engine) is identified by the process ID in <victimProcess>. The objects (table/index) involved are represented by Resource-IDs in different formats, including TAB (table), PAGE, KEY (index), and RID (row).
(ref.: https://support.microsoft.com/en-us/kb/224453 – section “Waitresource”)

In order to resolve the waitresource ID into the table/index name, you can run the follow SQL statements.
(these SQL can also be run in a Non-production sql server with a production database backup image restored on it, better the latest backup, as the object or data-page may not exists if the backup was too old).

The 1^st number in all different waitresource ID formats is the db_id, you can check the database name by this SQL:

SELECT DB_NAME(db_id) /* this DB_NAME statement can only be run on production server */

Then you can run the follow SQL for different waitresource formats, by setting the current database as the affected database:

USE <DBName>

For Object ID (OBJECT:db_id:object_id) / Table ID (TAB:db_id:object_id) :

SELECT OBJECT_NAME(object_id);

For HOBT ID (KEY:db_id:hobt_id) :

SELECT o.name AS TableName, i.name AS IndexName, SCHEMA_NAME(o.schema_id) AS SchemaName FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id WHERE p.hobt_id = hobt_id

For Page ID (PAGE:db_id:file_id:page_id) / Row ID (RID:db_id:file_id:page_id:slot) :

DBCC TRACEON(3604)

DBCC PAGE('DBName', file_id, page_id)

The object id will be shown in the result, Metadata: Object_Id = ???

The index id will be shown in the result, Metadata: IndexId = ???

Then you can get the table name and index name by OBJECT_NAME function and sys.indexes DMV.

SELECT OBJECT_NAME(Object_Id)

SELECT name FROM sys.indexes WHERE object_id = object_Id AND index_id = IndexId

2015-04-01

Email Alert of Long Uncommitted Transactions

Long running transaction in database causes performance impact to your system. Process blocking and deadlock will be arisen by lock waits on the resources being acquired by the long running transaction. Transaction log space cannot be reclaimed as there is an active transaction, the transaction log eventually out of space and the database becomes inaccessible (read-only). Also, if you have turned on READ_COMMITTED_SNAPSHOT for a database, SQL Server saves the before image of the data before starting a transaction in tempdb. This is called version store. If a transaction is running for a long time, the version store does not get cleared and it continues to grow filling up tempdb data file.
In order to monitor any long running (uncommitted) transactions in your database, you can create an email alert to send notification to your DBA team using Database Mail. Thanks for the script provided by Paul, I wrote the below script to send such email alert. You can create a SQL Agent Job, paste this script as the job step, and schedule the job to run periodically. (This script uses another stored procedure ConvertTableToHtml to render the result as HTML table, you should create this stored procedure before creating your job).

SET NOCOUNT ON;

-- **** Threshold in seconds ****
DECLARE @timeoutSec int = 15

DECLARE @recipients varchar(max), @subject nvarchar(255), @body nvarchar(max)

-- **** EMAIL RECIPIENTS, semicolon-delimited ; ****
SET @recipients = 'peter.lee@guosen.com.hk'

SET @subject = 'Long Uncommitted Transaction'
SET @body = 'Uncomitted transactions running longer than specified threshold ' + CAST(@timeoutSec AS varchar(10)) + ' seconds.'

DECLARE @htmlTable varchar(max)
CREATE TABLE #t (
[session_id] int,
[Login Name] nvarchar(128),
[Database] nvarchar(128),
[Begin Time] datetime,
[Duration (min.)] varchar(50),
[Last T-SQL Text] nvarchar(max)
)

INSERT #t SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
CAST(CAST(DATEDIFF(second, [s_tdt].[database_transaction_begin_time], GETDATE()) / 60.0 AS decimal(9, 1)) AS varchar(50)) AS [Duration (min.)],
[s_est].text AS [Last T-SQL Text]
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
WHERE DATEDIFF(second, [s_tdt].[database_transaction_begin_time], GETDATE()) > @timeoutSec
AND s_tdt.database_id <> DB_ID('msdb') -- EXCLUDE msdb system database
ORDER BY
[Begin Time] ASC;

DECLARE @rowcount bigint
SELECT @rowcount = COUNT(*) FROM #t

IF @rowcount > 0
BEGIN
--Sending Mail
EXEC master.dbo.ConvertTableToHtml 'SELECT * FROM #t', @htmlTable OUTPUT
SET @body = @body + '<br/>' + @htmlTable
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@body = @body,
@body_format = 'HTML',
@subject = @subject,
@importance = 'High';
END

DROP TABLE #t

Sample result:

Be aware that this script excluded the msdb database, as SQL Server uses this system database to run some background tasks.

網頁

2015-07-03

Get current stored proc name and params list

2015-06-02

Stored Procedure Error Handling Pattern

2015-05-18

Update Statistics based on the Percentage of Modifications

2015-04-17

Quick Solve Unclosed Transaction

2015-04-15

How to Add New Table(s) to Transactional Replication WITHOUT Re-Generate the Whole Snapshot

2015-04-10

Analyzing Deadlock using built-in "system _health" Extended Event

2015-04-01

Email Alert of Long Uncommitted Transactions