TO MONITOR THE SQL DB Connection & Statistics ( Azure & On-Premises)

Usually when you want to understand the SQL Usage, it is also better to look at the number of connections SQL Server has made to get an understanding of what kind connection is being made and from how long users are connected to the SQL DB/Instance.

We can use the below simple Queries  to identify the number of connections the SQL Instance is running at that moment.

USE MASTER

GO

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

USE THE BELOW QUERY TO RUN IT ON AZURE SQL TO OBTAIN THE CONNECTION STATISTICS

We can use the below mentioned query to obtain connection Statistics on SQL Azure.

SELECT * FROM SYS.DATABASE_CONNECTION_STATS

WHERE DATABASE_NAME = '<DATABASENAME>' AND START_TIME > '<TIMESTAMP>' --Provide Time in this format '2014-06-12 00:00:00.0000000'

ORDER BY START_TIME DESC

TO FIND SQL CPU & ACTIVE SESSIONS FOR LAST 24 HOURS - SQL Azure.

Sometimes, as DBAs at times you might want to know what are the Active Sessions and CPU Utilization / Usage for the last 24 hours. It is very useful to keep a tab on the Usage of SQL CPU/ Active Sessions. Especially, in the Cloud Era, we should know the DTU ( Database Throughput Unit ) of the SQL Server that you are choosing to spin up in Cloud environment. Also, in the Portal, most of the service providers will show a rolling dashboard for a max of 3 months.

In case, if you want to understand the trend of what was the usage of SQL CPU and Active Sessions for a period of One Year in Azure that can help you decide on what pricing tier you should choose. 

Here's what we can do :- 

Use the below mentioned query and schedule a job from any of your VMs ( because SQL Agent is not available in SQL PAAS Or Create a Powershell cmd / Task Scheduler jobs to run the query ) and populate them in a History table on a daily basis. 

Keep a tab on the Active Sessions and CPU Utilization of the SQL Instance / Server over a period of time to understand the usage/utilization to take better decisions on choosing appropriate SQL DTU / SKU that suits your needs which in turn can be cost effective as well.

USE MASTER

SELECT TOP 288 START_TIME, END_TIME, ACTIVE_SESSION_COUNT, ACTIVE_WORKER_COUNT, AVG_CPU_PERCENT,

AVG_PHYSICAL_DATA_READ_PERCENT,AVG_LOG_WRITE_PERCENT

FROM SYS.RESOURCE_STATS 

WHERE DATABASE_NAME = '<DATABASENAME>' 

ORDER BY START_TIME DESC

hope this helps :)

How To Identify Fragmentation levels of Indexes and Defragment them Regularly ?

We often come across Developers / DBAs talking about the Job runs taking really long or the queries are taking very long to provide output. The First thing everyone thinks about is to check if there are any blocks / look at the fragmentation level / How long does the query take to pull the data.

Although it is simple to run some queries and fetch the results, but, it needs some manual efforts on understanding the fragmentation levels and which indexes needs to be Reorganized / Rebuilt.

Hence, I have come up with a simple stored procedure that identifies fragmentation level and then Reorganizes or Rebuilds them based on a certain percentage.

Here is the SP Query that we can use to create as a Stored Procedure and 

Create a SQL Agent Job to execute this SP based on whatever frequency suitable for your database system.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spDefragmentIndexes]

@readonly BIT = 0,

@RunDefragmentIndexes bit = 1,

@maxfrag DECIMAL=30

AS

BEGIN

-- Declare variables

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @objectowner VARCHAR(255)

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @indexname CHAR(255)

IF ISNULL(@RunDefragmentIndexes,0) = 1

BEGIN

-- CREATE THE TEMPORARY TABLE TO HOLD FRAGMENTATION INFORMATION

CREATE TABLE #FragmentedIndexes (

 DBName  CHAR (255)

,TableName  CHAR (255)

,IndexName  CHAR (255)

,ObjectID INT

,index_type_desc  CHAR (255)

,IndexSpaceUsedBytes DECIMAL

,avg_fragmentation_in_percent DECIMAL

,fragment_count INT

,avg_fragment_size_in_pages DECIMAL

,page_count INT

)

INSERT INTO #FragmentedIndexes (DBName,TableName,IndexName,ObjectID,index_type_desc,IndexSpaceUsedBytes,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count)

SELECT

 DB_NAME() AS DBName

,'[' + s.Name + '].[' + OBJECT_NAME(ps.object_id) + ']' AS TableName

,i.name AS IndexName

,i.object_ID As ObjectID

,ips.index_type_desc

,ps.reserved_page_count*8.0*1024 AS IndexSpaceUsedBytes

,ips.avg_fragmentation_in_percent

,ips.fragment_count

,ips.avg_fragment_size_in_pages

,ips.page_count

FROM sys.dm_db_partition_stats ps

INNER JOIN sys.indexes i

ON  ps.object_id = i.object_id

AND ps.index_id = i.index_id

LEFT JOIN sys.Tables t on t.object_id = ps.object_id

LEFT JOIN sys.schemas s on s.schema_id = t.schema_id

CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips

ORDER BY ips.avg_fragmentation_in_percent desc, ps.object_id, ps.index_id

IF @readonly = 1

BEGIN

-- WRITE TO OUTPUT START TIME FOR INFORMATION PURPOSES

--SELECT 'CURRENTLY FRAGMENTED INDEXES'

SELECT TableName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, avg_fragmentation_in_percent

FROM #FragmentedIndexes f

JOIN sysobjects so ON f.ObjectId=so.id

WHERE avg_fragmentation_in_percent >= @maxfrag

 AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

Order by avg_fragmentation_in_percent desc

END

-- BEGIN STAGE 2: (DEFRAG) DECLARE CURSOR FOR LIST OF INDEXES TO BE DEFRAGGED

DECLARE indexes CURSOR FOR

SELECT TableName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, avg_fragmentation_in_percent

FROM #FragmentedIndexes f

JOIN sysobjects so ON f.ObjectId=so.id

WHERE avg_fragmentation_in_percent >= @maxfrag

 AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 

-- OPEN THE CURSOR

OPEN INDEXES

-- LOOP THROUGH THE INDEXES

FETCH NEXT

FROM INDEXES

INTO @tablename, @objectowner, @objectid, @indexname, @frag

IF @readonly = 1

BEGIN

-- WRITE TO OUTPUT START TIME FOR INFORMATION PURPOSES

SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

END

WHILE @@FETCH_STATUS = 0

BEGIN

SET QUOTED_IDENTIFIER ON

--SELECT 'Now executing on ' + RTRIM(@indexname) + ' from table ' + RTRIM(@tablename) + ' with fragmentation of ' + CAST(@frag AS Varchar(10))

--SELECT(@execstr)

BEGIN TRY

IF (@frag < 30)

BEGIN

SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REORGANIZE '

END

ELSE 

BEGIN

SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REBUILD WITH (ONLINE = ON)'

END

IF (@readonly = 0)

BEGIN

 EXEC (@execstr)

END

END TRY

BEGIN CATCH

SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON ' + RTRIM(@tablename) + ' REBUILD WITH (ONLINE = OFF)'

IF (@readonly = 0)

BEGIN

 EXEC (@execstr)

END

END CATCH

--UPDATE STATS AFTER INDEX REBUILD / REORGANIZE

SELECT @execstr = 'UPDATE STATISTICS ' +  RTRIM(@tablename) 

EXEC (@execstr)

SET QUOTED_IDENTIFIER OFF

FETCH NEXT

FROM INDEXES

INTO @tablename, @objectowner, @objectid, @indexname, @frag

END

-- CLOSE AND DEALLOCATE THE CURSOR

CLOSE INDEXES

DEALLOCATE INDEXES

IF @readonly = 1

BEGIN

-- Report on finish time for information purposes

SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

END

select *from #FragmentedIndexes

drop table #FragmentedIndexes

END

END

TOP CPU Consuming Queries (Average CPU Consumption along with Query Hash)

Below query helps to pull the TOP CPU Consuming Queries with Query Hash – Statement Text columns shows you the list of queries that are consuming max CPU on an average that needs to be tweaked or worked on for improving the performance.

This Query helps to get the TOP CPU Consuming queries without any time constraints.

SELECT TOP 10 QUERY_STATS.QUERY_HASH AS "QUERY HASH",

    SUM(QUERY_STATS.TOTAL_WORKER_TIME) / SUM(QUERY_STATS.EXECUTION_COUNT) AS "AVG CPU TIME",
    MIN(QUERY_STATS.STATEMENT_TEXT) AS "STATEMENT TEXT"
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 2 DESC;

GO

This Query helps to get the TOP CPU Consuming queries in any given window provided the query is created between the time window.

SELECT TOP 10 QUERY_STATS.QUERY_HASH AS "QUERY HASH",

SUM(QUERY_STATS.TOTAL_WORKER_TIME) / SUM(QUERY_STATS.EXECUTION_COUNT) AS "AVG CPU TIME",
MIN(QUERY_STATS.STATEMENT_TEXT) AS "STATEMENT TEXT"
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
WHERE QUERY_STATS.CREATION_TIME BETWEEN ('2016-07-19 11:00:00') AND ('2016-07-20 15:30:00')
GROUP BY QUERY_STATS.QUERY_HASH
ORDER BY 2 DESC;
GO


Hope it helps !!! :)

SQL Azure: Creating a Copy of a Database

Below is a simple query that you can use to create a copy of a Database quickly

This can be helpful in below scenarios: -

1. If you need to make any changes and validate.

2. Reproduce any Production issues
3. Quickly check and make some changes for validation
4. If you are unable to reproduce certain issues in Dev / SIT environment that can be reproduced in Prod.
5. If there is sensitive data that you do not want to keep it confidential, but, need to make certain changes and validate if it is reproducible.
6. If there's a code change / UI change that needs a Production like DB Pointer for validating the changes in UI.
7. Some SP / Schema changes and UI changes that would help validate if the changes are valid.
8. Quickly complete the production validation as well by pointing Dev / SIT / UAT UI to Copied database.
9. You want to scramble the sensitive data before taking a backup to share with Dev Team.
10. You want to make sure that you are not causing blocker for huge transactions occurring on the DB.

Query: -

You will have to open a new query with Master Database, (Obviously) because you cannot use the USE Command in SQL Azure DB

Right Click on the Master Database, Click New Query and run the below command.

CREATE DATABASE <<DB_COPYNAME>> AS COPY OF <<ACTUAL DATABASE>>

This will just say Command completed successfully - But, that does not mean you have a copy created already.

You will have to run the below query to check Percent_Complete is 100% to validate if Database has copied successfully to proceed with other operations.

Right Click on the Master Database, Click New Query and run the below command.

SELECT PERCENT_COMPLETE, DATABASE_ID, START_DATE, MODIFY_DATE, ERROR_CODE, ERROR_STATE FROM SYS.DM_DATABASE_COPIES

Hope it Helps !!!

How To Find PRIMARY and FOREIGN KEYS In The Database

Below Query helps us to list out all the Primary & Foreign Keys of the database - This also provides the FK Table, FK Column, PK Table , PK Column along with the PRIMARY KEY Constraints.


SELECT
   K_TABLE = FK.TABLE_NAME,
   FK_COLUMN = CU.COLUMN_NAME,
   PK_TABLE = PK.TABLE_NAME,
   PK_COLUMN = PT.COLUMN_NAME,
   CONSTRAINT_NAME = C.CONSTRAINT_NAME
FROM
   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
   ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
   ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
   ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
          SELECT
             I1.TABLE_NAME,
             I2.COLUMN_NAME
          FROM
             INFORMATION_SCHEMA.TABLE_CONSTRAINTS I1
             INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE I2
          ON I1.CONSTRAINT_NAME = I2.CONSTRAINT_NAME
          WHERE
          I1.CONSTRAINT_TYPE = 'PRIMARY KEY'
          ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME




Hope it helps !!!

  

How to resolve/ fix Orphaned users from SQL Database.

Below is a small snippet of cursor created to identify all the users who have the flag of IsSQLUser except for SID not Null & SID <> 0X0 (because they dbo, Guest, Information_Schema & sys accounts) and run Auto fix the orphaned users.

DECLARE @USRNAME VARCHAR(100), @COMMAND VARCHAR(100)
DECLARE CURSORS INSENSITIVE CURSOR FOR

SELECT NAME AS USERNAME FROM SYSUSERS
WHERE ISSQLUSER = 1 AND (SID IS NOT NULL AND SID <> 0X0)
AND SUSER_SNAME(SID) IS NULL ORDER BY NAME

FOR READ ONLY

OPEN CURSORS
FETCH NEXT FROM CURSORS INTO @USRNAME
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @COMMAND='SP_CHANGE_USERS_LOGIN ''AUTO_FIX'', '''+@USRNAME+''' '
EXEC(@COMMAND)
FETCH NEXT FROM CURSORS INTO @USRNAME
END

CLOSE CURSORS

DEALLOCATE CURSORS.

Hope it helps !! :)