How to handle bulk UPDATE statements in large tables

Sometimes we are required to do mass database UPDATES. On very large tables this can become a problem. For example, I recently had a table with 80 million records in it which required a mass UPDATE. Instead of running one large UPDATE statement it is better to break the transactions into smaller batches. This will reduce the locking incurred on the table in question.

WHILE (2 > 1)
  BEGIN
    BEGIN TRANSACTION
     UPDATE TOP(1000) dbo.tbl_shipments
     SET ship_zone = 99999
     WHERE ship_zone = 0;
    
    IF @@ROWCOUNT = 0
      BEGIN
        COMMIT TRANSACTION
         BREAK
      END
    COMMIT TRANSACTION
    -- 1 second delay
    WAITFOR DELAY '00:00:00.200'
  END -- WHILE
GO

There is a really good article at http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes which goes into depth about the issue. Although this article is focused on deletes it also applies to UPDATE statements.

Send Email Notification when SQL SERVER restarts

The following script can be used to create a SQL AGENT Job which will fire when SQL SERVER is restarted. This can reduce the time it takes to trouble shoot issues. For example, I support a SQL transactional replication environment. Sometimes I get random replication failures only to find out a the publisher a database server I do not support has restarted.

USE [msdb]
GO

/****** Object:  Job [DBM113_Service_Restart_Notification]    Script Date: 05/07/2015 07:09:10 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/07/2015 07:09:10 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBM_Service_Restart_Notification', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Send_Email_Step]    Script Date: 05/07/2015 07:09:10 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send_Email_Step', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC msdb.dbo.sp_send_dbmail
@profile_name=''Alert_Profile'',
@recipients = ''alerts@lab.net'',
@copy_recipients = ''cc_alerts@lab.net'',
@body = ''This is an informational message only: SQL services possibly restarted on SERVER01'',
@subject = ''SQL Services Restarted on SERVER01'';', 
		@database_name=N'master', 
		@output_file_name=N'C:\temp\Service_Restart_Notification.log', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Service_Restart_Notification', 
		@enabled=1, 
		@freq_type=64, 
		@freq_interval=0, 
		@freq_subday_type=0, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20141008, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'd1fdebc3-e6fd-4653-8663-cf26986cb7db'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

SolarWinds SAM Appinsight for SQL SERVER causing deadlocks

I recently found a bug in the way Solar winds monitor’s our databases. I am running Solarwinds Server application Monitor 11.1 and SQL SERVER Application Insight. Ever since I turned on the Application Insight feature I started seeing deadlock’s occurring in my SQL SERVER environment. Upon further investigation I noticed the Solarwinds Application Insight for SQL SERVER monitoring application was doing a read committed operation on system tables in all my database.

I sent the email below to Solar winds support and suggested the Solar winds developer change the query to a read uncommitted. Several days later I heard back from the Solar winds application engineer stating that they had a script for me to run. I ran the script which updated the monitoring query to be read uncommitted. I saw an immediate drop in the number of deadlocks alerts coming from my SQL SERVER. The moral of the story is to test monitoring thoroughly before using it in production. As in this case the monitoring server was at times causing transactions to be terminated unexpectedly.


—–Original Message—–
From: myname@mycompany.com
Sent: Monday , January 12, 2015 12:31 pm GMT (GMT+00:00)
Subject: appinsight for sql is causing deadlocks

The issue I am seeing is that solarwinds is locking system tables when its
app insight sql monitor runs. Here is one example where app insight
deadlocked on tempdb.sys.sysidxstats. Might I suggest that solarwinds
change the software so it does a “dirty read” or in other words read
uncommitted. This would prevent the deadlocks from happening.

Here is a excerpt from the deadlock XML file. The SAM App Insight monitor is running in isolation level read committed.

<process id="process7481048" taskpriority="0" logused="0" waitresource="KEY: 2:281474980249600 (45001d8e01fc)" waittime="1219" ownerI
d="825842724" transactionname="SELECT" lasttranstarted="2015-01-11T13:36:28.200" XDES="0x80039960" lockMode="S" schedulerid="6" kpid="2680" status="suspended" spid="143" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-01-11T13:36:28.133
" lastbatchcompleted="2015-01-11T13:36:28.090" clientapp=".Net SqlClient Data Provider" hostname="SW01" hostpid="16352" loginname="DOMAIN\sa-monitor" isolationlevel="read committed (2)" xactid="825842724" currentdb="2" lockTimeout="4294967295" cliento
ption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="26" sqlhandle="0x02000000b680b70233f6dc8a15fa42960c50200f89756a25">
SELECT TOP 10
	t.NAME AS TableName,
	t.object_id,
	mainIndex.data
_space_id,
	p.rows AS RowCounts,
	SUM(a.total_pages) * 8 AS TotalSpaceKB,
	SUM(CASE i.[type]
	WHEN 2 THEN 8 * a.used_pages
	ELSE 0
	END) AS IndexSpaceKB
FROM [sys].[tables] t
INNER JOIN sys.indexes mainIndex ON (t.object_id = mainIndex.object_id AND mainIn
dex.[type] IN (0,1))
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN [sys].[partitions] p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN [sys].[allocation_units] a ON p.partition_id = a.container_id
WHERE t.NAME NOT LI
KE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID &gt; 255 GROUP BY t.Name, t.object_id, mainIndex.data_space_id, p.Rows
ORDER BY TotalSpaceKB DESC     </frame>
     <frame procname="adhoc" line="21" stmtstart="1748" stmtend="1786" sqlhandle="0x0200000
0db583517807077e95c2490bb681fca4f9e78724f">
EXECUTE (@sqlQuery)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>


<keylock hobtid="281474980249600" dbid="2" objectname="tempdb.sys.sysidxstats" indexname="clst" id="lock30e2f1d00" mode="X" associatedObjectId="
281474980249600">
    <owner-list>
     <owner id="process39d30c088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process7481048" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>

Solarwinds Support provided the following SQL to change the ISOLATION LEVEL to READ UNCOMMITTED. Since included the new SQL in Solarwinds I have not seen any deadlocks.

-- FB395905 AppInsight for SQL is causing SQL Deadlocks
-- specifying isolation level for polling
UPDATE cts SET Value = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @TimeStamp datetime
SET @TimeStamp = GETUTCDATE()
SELECT
	name as dbName,
	d.database_id,
	[compatibility_level],
	collation_name,
	[state],
	recovery_model,
	DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), bk.last_backup) as last_backup,
	avg_read_latency,
	avg_write_latency,
	avg_bytes_per_read,
	avg_bytes_per_write,
	num_of_reads,
	num_of_writes,
	@TimeStamp as TimeStamp
FROM sys.databases d
LEFT JOIN
(
SELECT bs.database_name, MAX(bs.backup_finish_date) last_backup
FROM msdb.dbo.backupset bs
WHERE server_name = SERVERPROPERTY(''ServerName'') GROUP BY bs.database_name
) AS bk ON d.name = bk.database_name
LEFT JOIN
(
SELECT  database_id,
	SUM(io_stall_read_ms / NULLIF(num_of_reads, 0)) AS avg_read_latency,
	SUM(io_stall_write_ms / NULLIF(num_of_writes, 0)) AS avg_write_latency,
	SUM(num_of_bytes_read / NULLIF(num_of_reads, 0)) AS avg_bytes_per_read,
	SUM(num_of_bytes_written / NULLIF(num_of_writes, 0)) AS avg_bytes_per_write,
	SUM(num_of_reads) AS num_of_reads,
	SUM(num_of_writes) AS num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
)AS [io] ON d.database_id = [io].database_id
WHERE name NOT IN (${UnmanagedDatabases})'
FROM APM_ComponentTemplateSetting cts
INNER JOIN APM_ComponentTemplate ct ON cts.ComponentTemplateID = ct.ID
WHERE ct.UniqueId = '15868507-6248-4DE6-9A83-561535EBC058' AND cts.[Key] = 'SqlQuery'

-- Enforce job rescheduling for ABSA applications
UPDATE a SET LastModified = GETUTCDATE()
FROM APM_Application a INNER JOIN APM_ApplicationTemplate at ON a.TemplateID = at.ID
WHERE at.CustomApplicationType = 'ABSA'

SQL Server deadlock logging with email alerts

This configuration was setup on Windows 2012 and SQL SERVER 2012, it was also tested on SQL SERVER 2008.

Create a table named DeadlockEvents, this will be used to store the date and time stamp of the deadlock event as well as the XML generated from the deadlock event.

STEP 1.
Create a table to hold the SQL SERVER deadlocks. One field will contain the time at which the deadlock occurred and the other the XML from the deadlock.


/** 
Create Table to hold deadlocks
**/

USE DBA ;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
BEGIN
    DROP TABLE DeadlockEvents ;
END ;
GO

CREATE TABLE DeadlockEvents
    (AlertTime DATETIME, DeadlockGraph XML) ;
GO

STEP 2.
Add a SQL Agent job to trigger an insert of a deadlock record when a deadlock event occurs.

The following fields will need to be populated with your environment specific information.
@owner_login_name=N’DOMAIN\sa-job-owner’


/** Add JOB**/

USE [msdb]
GO

/****** Object:  Job [DBM2_Capture_Deadlock_Graph]    Script Date: 11/13/2014 13:54:07 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 11/13/2014 13:54:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBM2_Capture_Deadlock_Graph', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Job for responding to DEADLOCK_GRAPH events', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'DOMAIN\sa-job-owner', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Insert graph into LogEvents]    Script Date: 11/13/2014 13:54:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')', 
		@database_name=N'DBA', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [send email]    Script Date: 11/13/2014 13:54:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec sp_deadlock_alert', 
		@database_name=N'DBA', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

STEP 3.
This stored procedure is run when the SQL Agent job is triggered. I sends a copy of the deadlock XML to the email address specified as the recipient.


/** Add stored proc **/

use DBA;
GO
create procedure sp_deadlock_alert
AS

DECLARE @profile varchar(30)
set @profile = (select top 1 name from msdb.dbo.sysmail_profile)

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile,
    @recipients = 'ctatro@lab.net',
    @body = 'DeadLock Graph',
    @subject = 'A deadlock has occurred, please review the provided attachment...',
    @body_format = 'TEXT',
    @query_attachment_filename = 'Deadlockgraph.xml',
    @attach_query_result_as_file = 1,
    @query_no_truncate = 1,
     --@query_result_width = 512,
     --@query_result_no_padding = 1,
    @query = 'SELECT TOP 1 [DeadlockGraph]
  FROM [DBA].[dbo].[DeadlockEvents]
  order by alerttime desc
    for xml PATH(''ROW''), root(''ROOT''), TYPE';
    

STEP 4.
Add alert to fire SQL Agent job when a deadlock event occurs.
The job named DBM2_Capture_Deadlock_Graph will file whenever a deadlock event occurs.
For this to work you token replacement enabled on the SQL AGENT.
Right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System page, select Replace tokens for all job responses to alerts to enable these tokens.

/** Add Alert **/
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH', 
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
    @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', 
    @job_name='DBM2_Capture_Deadlock_Graph' ;
GO

STEP 5.
Testing the deadlock alerting

Open a SSMS query window and execute the following SQL.

use dba;
BEGIN TRAN
update table_a set ID=ID where ID = 100;

update table_b set ID=ID where ID =100;

Open a second SSMS query window and execute the following SQL.
This should result in a deadlock which inserts a record into the DeadlockEvents table and sends an email alerting you of the deadlock.

use dba;
BEGIN TRAN
update table_b set ID=ID where ID =100;

update table_a set ID=ID where ID = 100;

Property Owner not available for database

The other day when trying to access the properties of a SQL Server database I received the following error.

Cannot show requested dialog.

Property Owner is not available for Database ‘[database_name]’. This property may not exists for this object, or may not be retrievable due to insufficient access rights.

It turns the fix was very simple. The database did not have an owner. Assigning an owner allowed me to access the database properties once again.

exec sp_changedbowner  @loginame =  &quot;DOMAIN\username&quot; ,  @map= remap_alias_flag 

Creating scheduled events in MySQL

Unknown to many, the MySQL instance has the ability to schedule events. This feature can be useful for scheduling activities like maintenance and batch processes. To get started the user in MySQL creating the event requires the privileges to create events.

1. The following will GRANT user jsmith the ability to create events on all DBs in MySQL.

GRANT event privileges on all DBs to user jsmith

2. GRANT event privileges to all tables in DB myschema.

GRANT EVENT ON myschema.* TO jsmith;

3. Enter the following code to create a scheduled event which runs every 1 hour starting at 2014-01-07 13:00:00.

CREATE EVENT evntTruncate -- event name
ON SCHEDULE EVERY '1' HOUR -- run every 1 hour
STARTS '2014-01-07 13:00:00' -- should be in the future
DO
TRUNCATE TABLE City; -- SQL statement to execute

4. Run the following query to verify that the event has been scheduled.

SELECT * FROM information_schema.events;

How to quickly capture SQL Execution plan and SQL Query Text for LINQ queries

I recently realized we had a gap in our database deployment processes. The .NET developers were generating queries using LINQ and having our operations folks deploy this new code. As a result the DBA’s were being bypassed. I had mentioned to our developers that I would like to see execution plans of all LINQ queries. However, the only way I knew how to capture this information was through a SQL profiler trace. Setting up a trace for each LINQ query is somewhat time-consuming. There is an easier way which involves searching the SQL Server plan cache. Below I have outlined a quick way to search the plan cache for LINQ queries that were just executed. Please do not run this in production; this is a task which should be performed in the development phases of the project.

1. Execute .NET LINQ query

2. Configure the @searchstring parameter to search for a unique characteristic of the LINQ query. For example the name of a table you are executing a LINQ query against.

3. Review the SQL text and execution plan which is returned in the column titled “query_plan” also review the column titled “query_text”

/** Configure search string parameter **/

declare @searchstring varchar(255);
set @searchstring = '%TableOrDatabaseName%';

WITH Plan_Cache_CTE (capture_date,
query_text,
execution_count,
total_logical_reads,
last_logical_reads,
total_logical_writes,
last_logical_writes,
total_physical_reads,
last_physical_reads,
total_worker_time,
last_worker_time,
total_elapsed_time_in_S,
last_elapsed_time_in_S,
last_execution_time,
query_plan,
plan_handle)
AS
(
SELECT
GETDATE() as capture_date,
SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1 ,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) as query_text,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_physical_reads, qs.last_physical_reads,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan,
qs.plan_handle
FROM sys.dm_exec_query_stats qs with (nolock)
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
)
select *
from Plan_Cache_CTE
where query_text like @searchstring and query_text not like '%Plan_Cache_CTE%'
order by last_execution_time desc;