HK SQL Server Doctor

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-03-27

Transaction Log Disk Full due to Transactional Replication Not CatchUp

Problem Description:
When there are some bulk data operation (e.g. bulk data insert/update, create index/reindex, program bug leads to huge amount of updated rows) on a database publishing transactional replication, log reader agent may not catch up the bulk data operation. Transaction log of the affected DB cannot be truncated even log backup taken, until the log reader agent catch up the updated data. (Ref. http://support.microsoft.com/kb/317375 - section: Unreplicated transactions).

Emergency Resolution:
1. Assign extra transaction log space from another disk (if available, to buy time);
2. Stop the Log Reader Agent on the DB in Management Studio, by Right-Click the affected Publications, View Log Reader Agent Status, Click Stop;
3. Clear the pending replication commands in transaction log:
EXEC DB..sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
4. Drop all publications in DB:
EXEC DB..sp_droppublication 'all';
5. Disable Replication Publisher role of DB:
EXEC sp_replicationdboption 'DB', 'publish', 'false';
6. Check DB transaction log truncation NOT blocked by 'REPLICATION' again:
SELECT name AS DB, log_reuse_wait_desc FROM master.sys.databases;
7. Backup Transaction Log again to free space;
8. DBCC SHRINKFILE to shrink transaction log file (if required).

2015-03-25

You reduced a column size, but the table gets bigger, why?

Choosing correct data types could decrease the row size, and also improve performance. Sometime you may give the column too much space during table creation, and after the database already production running for a while, you would like to reduce the defined size for a column that well fit your system requirement. For example, you defined a column as fixed length nchar(50), but later you identify that using variable length nvarchar(10) is good enough.

For example, you created the table like this:

CREATE TABLE TestTbl (
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
col nchar(50) NULL
)
GO

The initial size, surely zero.

In order to simulate the production usage of this table, let's populate it with 100000 rows.

DECLARE @i int = 1
WHILE @i <= 100000
BEGIN
INSERT TestTbl (col) VALUES ('XXX')
SET @i += 1
END
GO

The table is populated with data.

As you identify that this column can use smaller defined size, you alter the column definition as below:

ALTER TABLE TestTbl ALTER COLUMN col nvarchar(10)

Now the column defined size is reduced, from fixed length nchar(50) to variable length nvarchar(10). But when you check the table size, surprisingly, it becomes bigger than before.

Why? Unfortunately one thing is not commonly known – alteration of the table never decreases the row size. When you drop the column, SQL Server removes column from the metadata but does not reclaim/rebuild the row. When you change column type from int to tinyint, for example, actual storage size remains intact – SQL Server just checks new value on insert/update stages. When you increase the size of the field (for example change int to bigint), SQL Server creates another bigint column and keep old int column space intact.
So how to fix it? Well, you need to rebuild clustered index. SQL Server will reclaim all space and rebuild the rows when you do that. By the way, clustered index rebuild is time consuming operation which locks the table. You can only do that in your system maintenance window.

Let's try to rebuild the clustered index:

ALTER INDEX [PK__TestTbl__3213E83F6E978ECC] ON TestTbl REBUILD

Now the table size is reduced finally!

The truth is, you should always carefully choose the type and length of every table column during the design phase.

網頁

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

2015-03-27

Transaction Log Disk Full due to Transactional Replication Not CatchUp

2015-03-25

You reduced a column size, but the table gets bigger, why?