This made the use of these types much more prevalent, and introduced another case where a transaction that tried to create and use such a type would deadlock:īEGIN TRANSACTION GO CREATE TYPE EmailAddress FROM VARCHAR ( 320 ) GO CREATE PROCEDURE dbo. Therefore, somewhere between RTM and SP4, they changed the internal handling for transactions that involved table variables using alias types.įast forward a few years to SQL Server 2008, where table-valued parameters were added ( see a good use case here). In SP4, however, they would all deadlock. In RTM, all of the above code samples worked just fine with no deadlocks. This didn't really solve the problem with changing the types, it just made the syntax a little cleaner. SQL Server 2005 came around, and introduced new DDL syntax for creating alias types: CREATE TYPE. While they may make your metadata more self-documenting and data-definition-like, they are a royal pain if you ever want to change them, which may be a topic for another post. Of course, this scenario wasn't very widespread at the time because, after all, not many people used alias types in the first place. foo = N'whatever' GO DECLARE EmailAddress SET = N'whatever' EXEC dbo. foo EmailAddressĪS BEGIN SET NOCOUNT ON SELECT END GO EXEC dbo. The DeadlockGraph column should contain an XML document that shows all the properties of the deadlock graph event.BEGIN TRANSACTION GO EXEC sp_addtype = N'EmailAddress', = N'VARCHAR(320)' GO CREATE PROCEDURE dbo. Examine the contents of the DeadlockEvents table by running the following script: SELECT * FROM DeadlockEvents SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) Īfter provoking the deadlock, wait several moments for SQL Server Agent to activate the alert and run the job. After a short time-out, SQL Server will choose either this script or the script in the sample as the deadlock victim and end the transaction. This script blocks, waiting to acquire a lock on Production.Location. Run the following script in the first query tab. SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) This script produces one result set and then blocks, waiting to acquire a lock on Production.Product. Run the following script in the second query tab. SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) This script produces one result set and finishes. Run the following script in one of the query tabs. In SQL Server Management Studio, open two SQL Query tabs and connect both queries to the same instance. change MSSQLSERVER to the name of the instance.ĮXEC _add_alert to * FROM Deadlock Graph' To monitor deadlocks for a different instance, Add an alert that responds to all DEADLOCK_GRAPH events for Set the job server for the job to the current instance of SQL Server.ĮXEC _add_jobserver = N'Capture Deadlock Graph' Add a jobstep that inserts the current time and the deadlock graph intoĮXEC = N'Capture Deadlock graph into N'INSERT INTO DeadlockEvents (AlertTime DATETIME, DeadlockGraph XML) ĮXEC _add_job Deadlock for responding to DEADLOCK_GRAPH events' IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL To change the instance that the alert monitors, substitute the instance name for MSSQLSERVER in the for the alert.įor SQL Server Agent to receive WMI events, Service Broker must be enabled in msdb and AdventureWorks2022. The WQL statement requests any DEADLOCK_GRAPH event in the default instance. For this alert, SQL Server Agent monitors the default instance on the local computer. For a WMI alert, SQL Server Agent creates a notification query using the namespace and WQL statement specified. The alert runs the job whenever a deadlock graph trace event would be logged. Because the job step uses the Transact-SQL subsystem, the job step does not specify a proxy. Notice that SQL Server implicitly converts the string into XML format. The job step retrieves the TextData property of the WMI event instance and inserts that value into the DeadlockGraph column of the DeadlockEvents table. The script first creates the job that the alert will run, adds a job step to the job, and targets the job to the current instance of SQL Server. The table contains two columns: The AlertTime column holds the time that the alert runs, and the DeadlockGraph column holds the XML document that contains the deadlock graph. Exampleįirst, a table is created in the AdventureWorks database to hold the deadlock graph event. Notice that, although several Service Broker objects are involved in processing the notification message, the WMI Event Provider handles the details of creating and managing these objects. SQL Server Agent submits a WQL request, receives WMI events, and runs a job in response to the event. The following sample presents a simple alert that saves XML deadlock graph events in a table for later analysis. One common way to use the WMI Event Provider is to create SQL Server Agent alerts that respond to specific events.
0 Comments
Leave a Reply. |