How to find blocking queries in SQL Azure

The query below will display the top ten running queries that have been the longest total elapsed time and are blocking other queries.

SELECT TOP 10 r.session_id, r.plan_handle,  r.[sql_handle], 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, st.[text]  
FROM sys.dm_exec_requests r  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st  
WHERE r.blocking_session_id = 0       
and r.session_id IN  (SELECT distinct(blocking_session_id)  FROM sys.dm_exec_requests)  
GROUP BY r.session_id, r.plan_handle,      r.[sql_handle], 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, st.[text]  
ORDER BY r.total_elapsed_time DESC

How to find Azure SQL Database Usage

It is simple way to use below SQL query to extract Azure SQL Database usage metrics.

SELECT DB_NAME() as DatabaseName, 

         (max_size/128.0) / 1024 AS DBMaxSizeInGB ,

(size/128.0) / 1024 AS DBAllocatedSpaceInGB ,  

(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 / 1024 AS DBUsedSpaceInGB,

(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/  1024 AS FreeSpaceInGB ,

  Getdate() as Created_Date  

FROM sys.database_files

WHERE name in ('data_0' ) and DB_NAME() = 'MyDatabase';

How to add more partitions to existing table

 --STEP 1 : Find File group

SELECT name AS AvailableFilegroups
FROM sys.filegroups WHERE type = 'FG'

--AvailableFilegroups
-------------------
--PRIMARY


--- STEP 2 : Create Partition Range Function & Schema

SELECT * FROM sys.partition_functions WHERE [name] = N'PF_CREATED_DATE'  
SELECT * FROM sys.partition_schemes   WHERE [name] = N'PS_PURCHASE_ORDER' 
  

CREATE PARTITION FUNCTION [PF_CREATED_DATE](Date) 
AS RANGE RIGHT FOR VALUES (
N'2019-08-01',
N'2019-08-02',
N'2019-08-03',
N'2019-08-04',
N'2019-08-05',
N'2019-08-06',
N'2019-08-07',
N'2019-08-08',
N'2019-08-09',
N'2019-08-10',
N'2019-08-11',
N'2019-08-12',
N'2019-08-13',
N'2019-08-14',
N'2019-08-15')

CREATE PARTITION SCHEME [PS_PURCHASE_ORDER] AS PARTITION [PF_CREATED_DATE]  TO  ( 
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ) 


--- STEP 3 : Create TABLE & Index

 SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PURCHASE_ORDER](
[PURCHASE_ORDER_ID] [bigint] NOT NULL,
[CREATED_DATE] [date]  NOT NULL,
[LOAD_ID] [int] NOT NULL
) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


CREATE CLUSTERED INDEX [CI_CREATED_DATE] ON [dbo].[PURCHASE_ORDER]
(
[CREATED_DATE] ASC,
[PURCHASE_ORDER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


--- STEP 4 : Add new partitions

 -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-16')

  -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-17')

How do i change table schema to dbo

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName

Example : 1
ALTER SCHEMA dbo TRANSFER Archive.TableName


Example : 2
change schema from dbo to rpt

Source table : dbo.rpt.TableName ( Invalid table name with additional dot)

Step 1 : Rename table with _ under score.
                   dbo.rpt_TableName

Step 2 : Change schema to rpt
ALTER SCHEMA rpt TRANSFER dbo.rpt_TableName

    Now table name with fully qualified name    rpt.rpt_TableName

Step 3 : Rename table with correct name ( to remove rpt_ )
                      rpt.TableName

My Parents

This blog dedicated to my parents. Thanks for your love, blessings and support in my life. You are inspired me to share my knowledge to the world especially for SQL Server DBA fellow members.

C. SANGARAPPAN  (Father)

Born: 05-June-1929  Died : 24-November-2017 at 9:40 pm in India

&

S. MEENAKSHI (Mother)

Died :  02-June-2020 at 3:40 pm in India