[Video] SQL Server Deadlocks

SQL Server Deadlocks – Also known as “The deadly Embrace” occurs when there are 2 processes and neither can advance and get the relevant lock it needs because the other process involved is preventing it from getting the resource it requires.

deadlocks

In this quick video clip I create a deadlock scenario and check how to get some deadlock information via system health extended events session.

Below is the T-SQL for querying the system health session. This was against a SQL Server 2014 install so you may need to change the file location.

SELECT top(1)
CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime2') AS [TimeOfDeadlock],
CONVERT(xml, event_data).query('/event/data/value/child::*') AS [DeadlockGraph]
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\system_health*.xel', null,  null, null)
WHERE object_name = 'xml_deadlock_report'
ORDER BY [TimeOfDeadlock] DESC

Azure Cloud “Fear” Busting #4 – Losing Control?

So the decision to move to the cloud has been made but there is a fear from people that once it happens they will lose control of their job function. Does this sound familiar? DBA’s, you will be needed but expect some sort of change (That’s my opinion).

Continue reading

SQL Server Rookie Mistakes

A quick elementary post which is my entry to this months’ T-SQL Tuesday entry hosted by a good friend SQLDoubleG http://www.sqldoubleg.com/2017/07/03/tsql2sday-92-lessons-learned-the-hard-way/.

TSQLTues

We are here to talk about mistakes we used to make. There is one mistake that I am going to discuss and is something that I used to do 10 years ago, obviously I do not do this anymore.

Continue reading

SQL Server Memory Metrics

A very quick post for today, recently I have been working on some code to gather metrics around SQL Server memory, more specifically, how much memory is on your server, your total / target memory and PLE. (If you want to know more about total vs target see this link: https://blobeater.blog/2017/03/01/sql-server-target-vs-total-memory/)

Continue reading

Azure Robots – Index Performance Recommendation

They are watching me and my Azure SQL Database and recently I noticed a low impact performance recommendation was made. Naturally I became very interested. Within your database (when in the portal) under operations you may notice something similar to the below:

Continue reading

AD Authentication and Azure SQL Database

Setting up AD authentication with Azure SQL Database sounds simple, it is assuming you plan carefully. I did run into issues but once rectified it felt great using AD authentication in Azure rather than just SQL logins.

Continue reading