Azure SQL Database View Running Transactions / Blocking

With the Azure SQL Database, sometimes you want to view the current running transactions. Unfortunately there is no GUI as such like there is with normal SQL Server (right click, “Activity Monitor”), however it can be grabbed by executing sys.dm_exec_requests.

The following SQL is very useful as it shows details of all running transactions within the database, including highlighting if the transaction is blocked, or is causing blocking.

SELECT r.session_id
	,CASE 
		WHEN r.session_id IN (
				SELECT DISTINCT (blocking_session_id)
				FROM sys.dm_exec_requests
				)
			THEN 'Yes'
		ELSE ''
		END AS blocking
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,CASE r.transaction_isolation_level
		WHEN 0
			THEN 'Unspecified'
		WHEN 1
			THEN 'ReadUncommitted'
		WHEN 2
			THEN 'ReadCommitted'
		WHEN 3
			THEN 'Repeatable'
		WHEN 4
			THEN 'Serializable'
		WHEN 5
			THEN 'Snapshot'
		END AS transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT AS sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
GROUP BY r.session_id
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,r.transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT
ORDER BY r.total_elapsed_time

Azure SQL Database Index Size

Recently I’ve been doing some work on optimising indexes within Azure SQL Database. The following SQL from Daniel is very useful in showing the status of all the current indexes within a SQL Database. The SQL works for both SQL Server 2014 onwards, plus Azure SQL Database. I have not tested it on any other versions.

This code returns the following:

  • Object Type
  • Object Name
  • Index Name
  • Index Type
  • Partition (if any)
  • Compression
  • Data Space
  • Fill Factor
  • Rows
  • Reserved MB
  • In Row Used MB
  • Row Overflow Used MB
  • Out of Row Used MB
  • Total Used MB
SELECT --- Schema, type and name of object and index:
	       REPLACE(obj.type_desc, '_', ' ') AS objectType
	,       sch.[name] + '.' + obj.[name] AS objectName
	,       ISNULL(ix.[name], '') AS indexName
	,       ix.type_desc AS indexType
	,       --- Partition number, if there are partitions:
	      (CASE COUNT(*) OVER (
				PARTITION BY ps.[object_id]
				,ps.index_id
				)             
			WHEN 1
				THEN ''             
			ELSE CAST(ps.partition_number AS VARCHAR(10))             
			END) AS [partition]
	,       --- Storage properties:
	       p.data_compression_desc AS [compression]
	,       ds.[name] + ISNULL('(' + pc.[name] + ')', '') AS dataSpace
	,       STR(ISNULL(NULLIF(ix.fill_factor, 0), 100), 4, 0) + '%' AS [fillFactor]
	,       --- The raw numbers:
	       ps.row_count AS [rows]
	,       STR(1.0 * ps.reserved_page_count * 8 / 1024, 12, 2) AS reserved_MB
	,       STR(1.0 * ps.in_row_used_page_count * 8 / 1024, 12, 2) AS inRowUsed_MB
	,       STR(1.0 * ps.row_overflow_used_page_count * 8 / 1024, 12, 2) AS RowOverflowUsed_MB
	,       STR(1.0 * ps.lob_used_page_count * 8 / 1024, 12, 2) AS outOfRowUsed_MB
	,       STR(1.0 * ps.used_page_count * 8 / 1024, 12, 2) AS totalUsed_MB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.partitions AS p ON     ps.[partition_id] = p.[partition_id]
INNER JOIN sys.objects AS obj ON     ps.[object_id] = obj.[object_id]
INNER JOIN sys.schemas AS sch ON     obj.[schema_id] = sch.[schema_id]
LEFT JOIN sys.indexes AS ix ON     ps.[object_id] = ix.[object_id]
	AND     ps.index_id = ix.index_id
--- Data space is either a file group or a partition function:
LEFT JOIN sys.data_spaces AS ds ON     ix.data_space_id = ds.data_space_id
--- This is the partitioning column:
LEFT JOIN sys.index_columns AS ixc ON     ix.[object_id] = ixc.[object_id]
	AND     ix.index_id = ixc.index_id
	AND     ixc.partition_ordinal > 0
LEFT JOIN sys.columns AS pc ON     pc.[object_id] = obj.[object_id]
	AND     pc.column_id = ixc.column_id
--- Not interested in system tables and internal tables:
WHERE obj.[type] NOT IN (
		'S'
		,'IT'
		)
ORDER BY sch.[name]
	,obj.[name]
	,ix.index_id
	,p.partition_number;

Example Results:

Azure AD Connect – Force Sync Via Command Line

Azure AD connect is a new tool which replaces DirSync for syncing Active Directory information to Azure from the local active directory.

Synchronisation automatically works in the background, but sometimes you want to push through a synchronisation for some reason or another (rather than waiting for the next time it runs). This can be done by running the following 2 commands from the C:\Program Files\Microsoft Azure AD Sync\bin directory:

DirectorySyncClientCmd.exe initial
DirectorySyncClientCmd.exe delta
  • initial – this forces a full sync
  • delta – this forces a delta sync

Example:
Azure AD Connect Command Line

Status can be checked by running “Synchronisation Service” and checking the log. In the example below, 452 entries where unchanged and 1 was deleted as part of the forced sync.
Azure AD Connect Synronisation Service Log

Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Came across this website this evening while doing some research, wish I’d have found it earlier. Very useful in brushing up your skills in Microsoft products, including Server 2012, Azure, Office 365 etc. And best of all, it’s free…!

Looking for a simple, effective way to get training on Microsoft’s Cloud technologies? Microsoft Virtual Academy!

Source: Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Microsoft Azure – Redis

Microsoft have implemented the open-sourced Redis key-cache and store in Azure.

Similar to have there are “Basic” and “Standard” levels of Virtual Machines, with associated redundancy, with Redis there are the same options available:

  • Basic – Single node
  • Standard – 2 node (in a primary/secondary setup), with built in replication and automatic failover

Redis Cache Azure Portal

Basic is ideal for development and non business critical (no SLA), whereas Standard is best for business critical systems (with a 99.9% SLA – 1 minute downtime per day)

Cache sizes range from 250mb through to 53GB. Prices range from £11 a month (250mb on Basic) to a huge £995 a month (53GB on Standard).

I’ve been using Redis on Azure for a good month or two now (started when it was in Preview before general release availability), speeds and reliability is impressive, with all requests taking less than 10ms. (only exception being GET * which returns all keys and takes about 50ms).

Redsmin

There are only a few changes which I would like to see:

  • Ability to manage via current management portal (only available on beta portal)
  • Ability to upgrade/downgrade packages on the fly (need to delete and recreate from scratch)
  • Improved statistics (although I currently use Redsmin)
  • Improved SLA for standard package

I thought about writing a guide, but Microsoft have a very good guide on their website about how to setup and use their Redis cache here: How to use Redis Cache in Azure

Monitoring Azure SQL Database CPU Usage

Sometimes there is a need to monitor Microsoft’s Azure SQL database usage, but your after the raw figures rather than an impressive looking graph.

Running the following SQL against the master database will give you the recent information:

DECLARE @DatabaseName nvarchar(50);
SET @DatabaseName = '*databasename*';

SELECT getdate() AS [Current Server Time];

SELECT start_time AS [Start of Period],
end_time AS [End of Period],
active_session_count AS [Sessions At End of Period],
avg_cpu_percent AS [Average CPU Usage %],
avg_physical_data_read_percent AS [Average Log Reads %],
avg_log_write_percent AS [Average Log Writes %]
FROM sys.resource_stats

WHERE start_time > DATEADD(minute, -240, GETDATE()) AND database_name = @DatabaseName
Order By start_time DESC;

Here’s an example of the results:
Azure SQL Database Processor Usage