Archive | November 2013

VMWare Certification – VCA-DCV

VMWare was recently doing a free promo of their new certification track ‘Associate’ and it caught my interest.

I decide to take the exam and now I am a VMware Certified Associate – Data Center Virtualization (VCA-DCV).

It was actually trickier than I envisioned. I would like to think I know a few things about VMWare, however I really haven’t used it for too long.  Overall, I would say that the exam does a pretty good job of assessing basic fundamental applications knowledge of the VMWare environment and what it means to virtualize servers.

 

Deadlocks — Are you watching them?

One of the strangest things I think is that SQL Server does not alert you to deadlocks by default.  I recently deployed some deadlock monitoring out so that I could be more aware of what exactly is happening on my servers.  Those of you with third party monitoring tools would already have similar functionality — this is mostly for those that are not spending a lot of money on monitoring software.  The foundation for this is available at http://msdn.microsoft.com/en-us/library/ms186385.aspx.

Things to watch for:

Third party applications seem to just throw deadlocks all of the time.  Some of them, if you google, simply suggest turning on snapshot isolation so that you get versioning.  Please make sure your tempdb situation is under control before taking this advice.

Sharepoint still sucks and the crawls seem to throw a lot of deadlocks.

And now to the fun part, the scripting!

I put this table in master, feel free to put it somewhere else.  I do most of my administration through my central management server so it makes deployment very simple.  As always, never deploy anything without knowing exactly what it is doing and always always always test on a development server first.

USE [master]
GO

/****** Object: Table [dbo].[DeadlockEvents] Script Date: 10/30/2013 2:06:06 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DeadlockEvents](
[AlertTime] [datetime] NULL,
[DeadlockGraph] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

 

Next up create a SQL Agent Job to that will be called by the alert you are going to create in a little bit.
USE [msdb]
GO

/****** Object: Job [[Monitor]] Capture Deadlock Graph] Script Date: 10/30/2013 2:06:33 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 10/30/2013 2:06:34 PM ******/
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'[Monitor] Capture Deadlock Graph’,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@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’sa’,
@notify_email_operator_name=N’SQL Server Notifications’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Insert graph into DeadlockEvents] Script Date: 10/30/2013 2:06:34 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Insert graph into DeadlockEvents’,
@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’INSERT INTO DeadlockEvents
(AlertTime, DeadlockGraph)
VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData)))”)

EXEC msdb.dbo.sp_send_dbmail
@recipients=”sqlservernotifications@yourcompanyhere.com”,
@body = N”$(ESCAPE_SQUOTE(WMI(TextData)))”,
@body_format=”HTML”,
@subject = ”DeadLock Occurred On Server”,
@importance = ”High” ;’,
@database_name=N’master’,
@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

And now you will create the alert:
USE [msdb]
GO

/****** Object: Alert [DeadlockWMI] Script Date: 10/30/2013 2:07:17 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’DeadlockWMI’,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@wmi_namespace=N’\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER’,
@wmi_query=N’SELECT * FROM DEADLOCK_GRAPH’,
@job_name=N'[Monitor] Capture Deadlock Graph’
GO

Note:  This uses WMI.

Finally — you will want to turn on a little check box in SQL Agent, but since we like to make as many changes through queries [CMS] as possible:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO

Now the final step is that you do need to restart SQL Agent in order to get these working properly.  Make sure you do not skip this step.

Extra fun — what about blocking?

Here is the alert:

USE [msdb]
GO

/****** Object: Alert [BlockingWMI] Script Date: 11/13/2013 7:28:29 AM ******/
EXEC msdb.dbo.sp_add_alert @name=N’BlockingWMI’,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@wmi_namespace=N’\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER’,
@wmi_query=N’SELECT * FROM Blocked_Process_report’,
@job_name=N’Put Your Job Here’
GO

A small addition for blocked processes — you will want to use sp_configure and determine what triggers a blocked process:

blocked process threshold (s) is the configuration item, so if you want it to trigger a blocked process after twenty seconds of blocking then do:

sp_configure ‘blocked process threshold (s)’, 20
reconfigure

Questions? Comments?  Leave a message!