Useful T-SQL Queries for SQL Azure

sql-azure-logo
SQL Azure is a Platform-as-a-Service (precisely Database-as-a-Service) and it is used as a database hosted and managed by Microsoft Azure. There is difference between SQL Server and SQL Azure in terms of T-SQL queries.
This blog post would give some quick reference for the T-SQL queries which are used frequently

-- Copy the database
CREATE DATABASE [SampleDB_Copy] AS COPY OF [SampleDB]
-- Find the status of the database and copy in-progress
SELECT * FROM sys.databases
SELECT * FROM sys.dm_database_copies

-- Disable all indexes
 ALTER INDEX ALL ON HumanResources.Employee DISABLE;
 -- Enable all indexes
 ALTER INDEX ALL ON HumanResources.Employee REBUILD;

-- Disable all indexes
 ALTER INDEX ALL ON HumanResources.Employee DISABLE;
 -- Enable all indexes
 ALTER INDEX ALL ON HumanResources.Employee REBUILD;

-- Disable particular index
 ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee DISABLE;
 -- Enable particular index
 ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee REBUILD;

-- Check to see the status of the index
 select is_disabled,* from sys.indexes where OBJECT_NAME(object_id) = 'HumanResources.Employee';
 select is_disabled,* from sys.indexes where OBJECT_NAME(object_id) = 'HumanResources.Employee';

-- Finding the index fragmentation level for the indexes
 SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
 ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
 indexstats.avg_fragmentation_in_percent,
 indexstats.page_count,
 indexstats.fragment_count,
 indexstats.index_depth
 --,SUM([indexstats.avg_record_size_in_bytes] * [indexstats.record_count]) / (1024.0 *1024))) AS [IndexSize(MB)]
 --,[indexstats.lastupdated] AS [StatisticLastUpdated]
 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
 INNER JOIN sys.indexes ind
 ON ind.object_id = indexstats.object_id
 AND ind.index_id = indexstats.index_id
 ORDER BY indexstats.avg_fragmentation_in_percent DESC

-- Long Running queries

SELECT TOP 25
 OBJECT_NAME(qt.Objectid) EntityName,
 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT)ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) Query,
 qs.execution_count,
 qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
 qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
 (qs.total_elapsed_time/1000000)/qs.execution_count avg_elapsed_time_in_S,
 qs.last_execution_time,
 qs.total_logical_reads,
 qs.last_logical_reads,
 qs.total_logical_writes,
 qs.last_logical_writes,
 qs.total_worker_time,
 qs.last_worker_time,
 qp.query_plan
 FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
 WHERE qs.last_execution_time > DATEADD(hh, -24, GETUTCDATE())
 --AND qs.last_elapsed_time/1000000 > 0
 AND (qs.total_elapsed_time/1000000)/qs.execution_count > 0
 AND qs.Execution_Count > 5
 ORDER BY
 --qs.total_logical_reads DESC,
 --qs.last_execution_time DESC,
 --qs.last_elapsed_time/1000000 DESC
 (qs.total_elapsed_time/1000000)/qs.execution_count Desc

-- Calculates the size of individual database objects.

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
 FROM sys.dm_db_partition_stats, sys.objects
 WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
 GROUP BY sys.objects.name

-- Get bandwidth usage by database by hour (for billing)
 SELECT database_name, direction, class, time_period,
 quantity AS [KB Transferred], [time]
 FROM sys.bandwidth_usage
 ORDER BY [time] DESC;

-- Get Bandwidth cost by direction and type
 SELECT USAGE.Time_Period, USAGE.Direction,
 CASE WHEN USAGE.Direction = N'Egress'
 THEN 0.15 * USAGE.BandwidthInKB/(1024 * 1024)
 WHEN USAGE.DIRECTION = N'Ingress'
 THEN 0.10 * USAGE.BandwidthInKB/(1024 * 1024)
 END AS [CostInDollars]
 FROM (SELECT Time_Period, Direction, SUM(Quantity) AS [BandwidthInKB]
 FROM sys.Bandwidth_Usage
 WHERE DATEPART(yy, TIME) = DATEPART(yy, GetUTCDate())
 AND DATEPART(mm, TIME) = DATEPART(mm, GetUTCDate())
 AND class = N'External'
 GROUP BY Time_Period, Direction) AS USAGE;

--- monitor connections
 SELECT
 e.connection_id,
 s.session_id,
 s.login_name,
 s.last_request_end_time,
 s.cpu_time
 FROM
 sys.dm_exec_sessions s
 INNER JOIN sys.dm_exec_connections e
 ON s.session_id = e.session_id
 GO

-- Monitor query plans
 SELECT
 highest_cpu_queries.plan_handle,
 highest_cpu_queries.total_worker_time,
 q.dbid,
 q.objectid,
 q.number,
 q.encrypted,
 q.[text]
 FROM
 (SELECT TOP 50
 qs.plan_handle,
 qs.total_worker_time
 FROM
 sys.dm_exec_query_stats qs
 ORDER BY qs.total_worker_time desc) AS highest_cpu_queries
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
 ORDER BY highest_cpu_queries.total_worker_time desc

-- Get row counts for tables in current database
 SELECT OBJECT_NAME(object_id) AS [ObjectName], row_count, object_id, index_id
 FROM sys.dm_db_partition_stats
 WHERE index_id < 2
 ORDER BY row_count DESC;

-- Top Cached Plans By Execution Count
 SELECT q.[text], hcpu.total_worker_time,
 hcpu.execution_count, hcpu.plan_handle
 FROM
 (SELECT TOP (50) qs.*
 FROM sys.dm_exec_query_stats AS qs
 ORDER BY qs.total_worker_time DESC) AS hcpu
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
 ORDER BY hcpu.execution_count DESC;

-- Top Cached Plans By total worker time (CPU)
 SELECT q.[text], hcpu.total_worker_time,
 hcpu.execution_count, hcpu.plan_handle
 FROM
 (SELECT TOP (50) qs.*
 FROM sys.dm_exec_query_stats AS qs
 ORDER BY qs.total_worker_time DESC) AS hcpu
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
 ORDER BY hcpu.total_worker_time DESC;

-- Find top Avg CPU time queries
 SELECT TOP (25) MIN(query_stats.statement_text) AS [Statement Text],
 SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [Avg CPU Time],
 query_stats.query_hash AS [Query Hash]
 FROM (SELECT QS.*, SUBSTRING(ST.[text], (QS.statement_start_offset/2) + 1,
 ((CASE statement_end_offset
 WHEN -1 THEN DATALENGTH(st.[text])
 ELSE QS.statement_end_offset END
 - QS.statement_start_offset)/2) + 1) AS statement_text
 FROM sys.dm_exec_query_stats AS QS
 CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
 GROUP BY query_stats.query_hash
 ORDER BY [Avg CPU Time] DESC;

-- Top Cached Plans By total logical reads (Memory)
 SELECT q.[text], hcpu.total_logical_reads,
 hcpu.execution_count, hcpu.plan_handle
 FROM
 (SELECT TOP (50) qs.*
 FROM sys.dm_exec_query_stats AS qs
 ORDER BY qs.total_worker_time DESC) AS hcpu
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
 ORDER BY hcpu.total_logical_reads DESC;

-- Top Cached Plans By total elapsed time
 SELECT q.[text], hcpu.total_elapsed_time,
 hcpu.execution_count, hcpu.plan_handle
 FROM
 (SELECT TOP (50) qs.*
 FROM sys.dm_exec_query_stats AS qs
 ORDER BY qs.total_worker_time DESC) AS hcpu
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
 ORDER BY hcpu.total_elapsed_time DESC;

-- Get DTU details for V2 database.
-- Should run on master database
SELECT   start_time
	,end_time
	,avg_cpu_percent
	,avg_data_io_percent
	,avg_log_write_percent
FROM sys.resource_stats 
WHERE database_name = '' 
ORDER BY end_time DESC;