Wednesday, August 28, 2013

Helpful SQL Queries for measure performance of database

SQL queries are easily readable if they are in a standard SQL format; this can be achieved with the help of an online SQL formatting tool at:http://www.dpriver.com/pp/sqlformat.htm 

1. Following set of queries can be used to get various values from the database.

  • Find all columns in a Database

SELECT SC.name AS ColumnName,
       ST.name AS TableName
FROM   sys.columns SC
       INNER JOIN sys.tables ST
               ON ST.object_id = SC.object_id
ORDER  BY ST.name,
          SC.name 

  • List all the Functions in a Database


SELECT name AS function_name,
       type_desc
FROM   sys.objects
WHERE  type_desc LIKE '%FUNCTION%'; 

  • List all the Stored Procedures in a Database 

SELECT name
FROM   sys.procedures
ORDER  BY name 

  • List all the views in a Database

SELECT name
FROM   sys.views
ORDER  BY name   

  • List all the tables in a Database

SELECT name
FROM   sys.tables

  • Find specific column and related table names

SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%column name%'ORDER BY schema_name, table_name

  • Data type of a column


 SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE 

    c.object_id = OBJECT_ID('YourTableName')

  • Find specific column and related table names


SELECT t.name AS table_name,

SCHEMA_NAME(schema_id) AS schema_name,

c.name AS column_name

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%column name%'

ORDER BY schema_name, table_name
2. Queries to check if best practice is followed in SPs
SELECT routine_name,
       routine_definition
FROM   information_schema.routines
WHERE  routine_definition LIKE '%select * from%'
       AND routine_type = 'PROCEDURE' 
Note: Verify for each combination, i.e., select*from, select *from, select* from.

  • Query to identify if any SP uses IN or Not IN

SELECT routine_name,
       routine_definition
FROM   information_schema.routines
WHERE  routine_definition LIKE '% in (%'
       AND routine_type = 'PROCEDURE' 
SELECT routine_name,
       routine_definition
FROM   information_schema.routines
WHERE  routine_definition LIKE '% not in (%'
       AND routine_type = 'PROCEDURE' 
Note: Verify for each combination of IN and Not IN, i.e., ‘IN(‘, ‘ IN (‘, ‘NotIN(‘, ‘Not IN(‘, ‘Not IN (‘.
  • SP without initial comment

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 




FROM INFORMATION_SCHEMA.ROUTINES 




WHERE ROUTINE_DEFINITION not LIKE '%================%'




AND ROUTINE_TYPE='PROCEDURE'
3. Following queries can be used to get performace details of the database
  • CPU utilization in seconds
SELECT (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, text,*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
order by CPU_Utilized_in_Seconds desc

  • To get longest running query, memory used & other details  
SELECT Top 10 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),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_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
ORDER BY qs.total_logical_reads DESC

  • Last query ran on SQL
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
  • Plan cache hit ratio
WITH cte1
     AS (SELECT [dopc].[object_name],
                [dopc].[instance_name],
                [dopc].[counter_name],
                [dopc].[cntr_value],
                [dopc].[cntr_type],
                Row_number()
                  OVER (
                    partition BY [dopc].[object_name], [dopc].[instance_name]
                    ORDER BY [dopc].[counter_name] ) AS r_n
         FROM   [sys].[dm_os_performance_counters] AS dopc
         WHERE  [dopc].[counter_name] LIKE '%Cache Hit Ratio%'
                AND ( [dopc].[object_name] LIKE '%Plan Cache%'
                       OR [dopc].[object_name] LIKE '%Buffer Cache%' )
                AND [dopc].[instance_name] LIKE '%_Total%')
SELECT CONVERT(DECIMAL(16, 2), ( [c].[cntr_value] * 1.0 / [c1].[cntr_value] ) *
                               100.0)
       AS [hit_pct]
FROM   [cte1] AS c
       INNER JOIN [cte1] AS c1
               ON c.[object_name] = c1.[object_name]
                  AND c.[instance_name] = c1.[instance_name]
WHERE  [c].[r_n] = 1
       AND [c1].[r_n] = 2;
  • Plan cache reuse
DECLARE @single DECIMAL(18, 2)
DECLARE @reused DECIMAL(18, 2)
DECLARE @total DECIMAL(18, 2)

SELECT @single = Sum(CASE ( usecounts )
                       WHEN 1 THEN 1
                       ELSE 0
                     END) * 1.0,
       @reused = Sum(CASE ( usecounts )
                       WHEN 1 THEN 0
                       ELSE 1
                     END) * 1.0,
       @total = Count(usecounts) * 1.0
FROM   sys.dm_exec_cached_plans;

SELECT ( @single / @total ) * 100.0; 
  • Identify if a column is reaching limit of the datatype
WITH cte_1
     AS (SELECT Object_name(a.object_id) AS table_name,
                a.name AS columnname,
                CONVERT(BIGINT, Isnull(a.last_value, 0)) AS last_value,
                CASE
                  WHEN b.name = 'tinyint' THEN 255
                  WHEN b.name = 'smallint' THEN 32767
                  WHEN b.name = 'int' THEN 2147483647
                  WHEN b.name = 'bigint' THEN 9223372036854775807
                END    AS dt_value
         FROM   sys.identity_columns a
                INNER JOIN sys.types AS b
                        ON a.system_type_id = b.system_type_id),
     cte_2
     AS (SELECT *,
                CONVERT(NUMERIC(18, 2), ( ( CONVERT(FLOAT, last_value) /
                                            CONVERT(FLOAT, dt_value) ) * 100 ))
                AS 'Percent'
         FROM   cte_1)
SELECT *
FROM   cte_2
ORDER  BY 'Percent' DESC;
  • Number of unused indexes
SELECT db_name() as dbname,
       o.name as tablename,
       i.name as indexname,
       i.index_id,
       user_seeks + user_scans + user_lookups as total_reads,
       user_updates as total_writes,
       (SELECT SUM(p.rows)
          FROM sys.partitions p
         WHERE p.index_id = s.index_id
           AND s.object_id = p.object_id) as number_of_rows,
       s.last_user_lookup,
       s.last_user_scan,
       s.last_user_seek
  FROM sys.indexes i
 INNER JOIN sys.objects o
    ON i.object_id = o.object_id
  LEFT OUTER JOIN sys.dm_db_index_usage_stats s
    ON i.index_id = s.index_id
   AND s.object_id = i.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND ISNULL(s.database_id, DB_ID()) = DB_ID()
   AND (
        isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND
        isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND
        isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())
       )
 ORDER BY total_reads DESC; 
  • Tables without clustered indexes defined
WITH CTE_1
AS
(
  SELECT db_name() as dbname,
         o.name as tablename,
         (SELECT SUM(p.rows)
            FROM sys.partitions p
           WHERE p.index_id = i.index_id
             AND i.object_id = p.object_id) as number_of_rows
    FROM sys.indexes i
   INNER JOIN sys.objects o
      ON i.object_id = o.object_id
   WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
     AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0
)
SELECT *
  FROM CTE_1
 WHERE number_of_rows > 1000;
  • Possible duplicate indexes







WITH  indexcols



        AS (SELECT object_id AS id,



                index_id AS indid,



                name,



                (SELECT CASE keyno



                          WHEN 0 THEN NULL



                          ELSE colid



                        END AS [data()]



                  FROM sys.sysindexkeys AS k



                  WHERE k.id = i.object_id



                    AND k.indid = i.index_id



                  ORDER BY keyno,



                    colid



                FOR



                 XML PATH('')



                ) AS cols,



                (SELECT CASE keyno



                          WHEN 0 THEN colid



                          ELSE NULL



                        END AS [data()]



                  FROM sys.sysindexkeys AS k



                  WHERE k.id = i.object_id



                    AND k.indid = i.index_id



                  ORDER BY colid



                FOR



                 XML PATH('')



                ) AS inc



              FROM sys.indexes AS i



           )



  SELECT DB_NAME() AS 'DBName',



      OBJECT_SCHEMA_NAME(c1.id) + '.'



        + OBJECT_NAME(c1.id) AS 'TableName',



      c1.name + CASE c1.indid



                  WHEN 1 THEN ' (clustered index)'



                  ELSE ' (nonclustered index)'



                END AS 'IndexName',



      c2.name + CASE c2.indid



                  WHEN 1 THEN ' (clustered index)'



                  ELSE ' (nonclustered index)'



                END AS 'ExactDuplicatedIndexName'



    FROM indexcols AS c1



    INNER JOIN indexcols AS c2



    ON



      c1.id = c2.id



      AND c1.indid < c2.indid



      AND c1.cols = c2.cols

      AND c1.inc = c2.inc; 




  • Performance related database settings

DECLARE @Result INT;
DECLARE @High INT; -- For settings you feel need to be righ
DECLARE @Med INT; -- For settings you want to know about but arent critical
DECLARE @Low INT; -- For settings that you want flagged but are low importance


SELECT @High = 70,
    @Med = 40,
    @Low = 10;


SELECT @Result = SUM(CASE WHEN [d].[compatibility_level] != [d2].[compatibility_level]
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[collation_name] != [d2].[collation_name]
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[user_access] != 0
                          THEN @Low
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_close_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_shrink_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[state] != 0
                          THEN @Low
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_create_stats_on] != 1
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_auto_update_stats_on] != 1
                          THEN @Med
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_nulls_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_padding_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_ansi_warnings_on] = 1
                          THEN @High
                          ELSE 0
                     END
                   + CASE WHEN [d].[is_arithabort_on] = 1

THEN @High
                          ELSE 0
                     END)
  FROM [sys].[databases] AS d 
  CROSS JOIN [sys].[databases] AS d2
  WHERE [d2].[name] = 'master'
    AND ([d].[database_id] = DB_ID()
    AND [d].[state_desc] = 'Online');


 
SELECT @Result;





Happy Coding!!
By -
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)