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 !! :)