Change your thoughts and you change your world.

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

Query to identify if any SP uses ‘Select * from’

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

Change your thoughts and you change your world.

Wednesday, August 28, 2013

Helpful SQL Queries for measure performance of database