The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Tuesday, March 19, 2024 Login
Public

Blog posts for the month of month,2018.
SQL Server negative usecounts in procedure cache1/29/2018 9:06:33 AM

I track stored procedure counts and plans for reporting on volume, when a plan changes and data mining the plans for missing indexes and other information.


The trick to this is you have to track it over time as the DMV tracks cumulative count. An issue I ran into recently was that the cumulative count was negative. I don't know and found a few links, but no real explanation. So now when I find a negative count I usually mark it for removal from the query cache so that it resets. I'm guessing that the count reached the maximum value and "rolled" over to a negative values (similar to reseeding an identity to a negative value when the maximum value of an integer data type has been reached).
https://social.technet.microsoft.com/Forums/en-US/abd274a7-5a6d-487c-a1df-01fb10e5b84b/dmexecrequests-showing-a-negative-value-in-the-logical-reads-column?forum=sqldatabaseengine
and
https://social.technet.microsoft.com/Forums/en-US/abd274a7-5a6d-487c-a1df-01fb10e5b84b/dmexecrequests-showing-a-negative-value-in-the-logical-reads-column?forum=sqldatabaseengine

Deadlocks finding1/19/2018 4:39:00 PM

Learning new things all the time.  Historically deadlocks have been hard to troubleshoot unless you were able to have a user recreate them or had some type of monitoring tool that was running an extended events session and/or a trace.  

Turns out that is not always true.  As usually you get the call, our application had a deadlock at 4am this morning, can you tell us what happened?  Of course you can, your the DBA, you were awake at 4am and monitoring their application for them.  BUT you can help them.

There are new things in SQL that make this easier, so learn them.  Links are below, script is in my script vault, and it's possible to see what happened with deadlocks at 4am, even though it's now 6am.  [on a real busy system it may not, but so far, it's been nice to me !].

Querying Deadlocks From System_Health XEvent

https://simplesqlserver.com/2016/01/25/querying-deadlocks-from-system_health-xevent/

and

Tracing Deadlock Graphs: Extended Events or Server Side Trace

http://littlekendra.com/2016/12/27/tracing-deadlock-graphs-extended-events-or-server-side-trace/

DECLARE @SessionName SysName

SELECT
@SessionName = 'system_health'

/*
SELECT  Session_Name = s.name, s.blocked_event_fire_time, s.dropped_buffer_count, s.dropped_event_count, s.pending_buffers
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE target_name = 'event_file'
--*/

IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN
DROP TABLE
#Events
END

DECLARE
@Target_File NVarChar(1000)
,
@Target_Dir NVarChar(1000)
,
@Target_File_WildCard NVarChar(1000)

SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = @SessionName
AND t.target_name = 'event_file'

SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File)))

SELECT @Target_File_WildCard = @Target_Dir + '\'  + @SessionName + '_*.xel'

--Keep this as a separate table because it's called twice in the next query.  You don't want this running twice.
SELECT DeadlockGraph = CAST(event_data AS XML)
,
DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)
INTO #Events
FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F
WHERE event_data like '<event name="xml_deadlock_report%'

;WITH Victims AS
(
SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)')
,
e.DeadlockID
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)
)
,
DeadlockObjects AS
(
SELECT DISTINCT e.DeadlockID
, ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)')
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources)
)
SELECT *
FROM
(
SELECT e.DeadlockID
, TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime')
,
DeadlockGraph
, DeadlockObjects = substring((SELECT (', ' + o.ObjectName)
FROM DeadlockObjects o
WHERE o.DeadlockID = e.DeadlockID
ORDER BY o.ObjectName
FOR XML PATH ('')
),
3, 4000)
,
Victim = CASE WHEN v.VictimID IS NOT NULL
THEN 1
ELSE 0
END
, SPID = Deadlock.Process.value('@spid', 'int')
,
ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)')
,
LockMode = Deadlock.Process.value('@lockMode', 'char(1)')
,
Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)')
,
ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29)
WHEN 'SQLAgent - TSQL JobStep (Job '
THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67)
ELSE Deadlock.Process.value('@clientapp', 'varchar(100)')
END
, HostName = Deadlock.Process.value('@hostname', 'varchar(20)')
,
LoginName = Deadlock.Process.value('@loginname', 'varchar(20)')
,
InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process)
LEFT JOIN
Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')
)
X --In a subquery to make filtering easier (use column names, not XML parsing), no other reason
ORDER BY DeadlockID DESC

SQL Agent Token - Cycle Job Log1/18/2018 8:16:54 AM

I guess SQL Server Tokens have been around since SQL 2005, but I just learned of them.

I needed this because I had a Transaction Log backup job, running every 4 minutes that was randomly failing.  The sql agent text was longer than was captured so I couldn't see the error in the log history, I went to the job to view the "output" file, and of course it was already overwritten by the next successful run.

I didn't want to change the job to "append" as I didn't want to have it grow "forever" and end up with a large job output text file on the server.

After some research I found this: 

http://mumblestiltskin.blogspot.com/2011/03/sql-server-job-step-output-file-using_08.html

https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps 

So I just simply modified the output step to "append" and added the token:

O:\MSSQL11.BCTAX03\MSSQL\JOBS\SQLAGENT_JOB_Backup_TRN(ALWAYSON)_StepID-1_$(ESCAPE_SQUOTE(STRTDT)).txt 

This uses a token in the job output file to create a file for each day, so the job is still set to "append", but you get one job file per day.

To manage the "daily" log files, I have a job that creates a sub-directory by day (yymmdd) and "sweeps" all the log files into it.  So I have one subdirectory per day with all the log files in.   Once a year I delete some directories, here's the code that does that, the whole job is in my script vault as well, under SQL Agent.

declare @xp_command varchar(8000)

declare @directory varchar(8)

declare @path varchar(1000)

declare @month varchar(2)

declare @day varchar(2)

declare @year varchar(4)

set @month = Right('00' + convert(varchar(2),DatePart(m,getdate())),2)

set @day = right('00' + convert(varchar(2),DatePart(d,getdate())),2)

set @year = convert(varchar(4),DatePart(yyyy,getdate()))

set @directory = @month + @day + @year

print @directory

set @xp_command = 'mkdir G:\CY_SQL_JOB_LOGS\' + @directory

print @xp_command

exec master..xp_cmdshell @xp_command

set @xp_command = 'move G:\CY_SQL_JOB_LOGS\*.* G:\CY_SQL_JOB_LOGS\' + @directory

print @xp_command

exec master..xp_cmdshell @xp_command

That's all there is too it !


Blog Home