SQLCHAD – Life as a SQL Server DBA

I had a need to look for tables that violate company standards that every table must have a Primary Key as well as a Clustered Index. I put together a script that gives you each table name, whether it has a PK or not and whether it has a clustered index or not. You could add a where clause to only show those that violate one of these rules but the script as is shows all tables

SELECT 
    c.name + '.' + b.name as ObjectFullName, 
    'Y' as HasPrimaryKey,
    CASE i.index_id 
        WHEN '0' THEN 'N' 
        WHEN '1' THEN 'Y' 
    END as HasClusteredIndex  
FROM sys.tables b  
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
INNER JOIN sys.indexes i ON b.object_id = i.object_id and i.index_id IN (0,1)  
WHERE OBJECTPROPERTY(b.object_id, 'IsUserTable') = 1
AND EXISTS --Has PK
(
    SELECT a.name  
    FROM sys.key_constraints a  
    WHERE a.parent_object_id = b.OBJECT_ID  
    AND a.schema_id = c.schema_id  
    AND a.type = 'PK'
)
UNION 
SELECT 
    c.name + '.' + b.name as ObjectFullName, 
    'N' as HasPrimaryKey,
    CASE i.index_id 
        WHEN '0' THEN 'N' 
        WHEN '1' THEN 'Y' 
    END as HasClusteredIndex 
FROM sys.tables b  
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
INNER JOIN sys.indexes i ON b.object_id = i.object_id and i.index_id IN (0,1)  
WHERE OBJECTPROPERTY(b.object_id, 'IsUserTable') = 1
AND NOT EXISTS --No PK
(
    SELECT a.name  
    FROM sys.key_constraints a  
    WHERE a.parent_object_id = b.OBJECT_ID  
    AND a.schema_id = c.schema_id  
    AND a.type = 'PK'
)
ORDER BY HasPrimaryKey, HasClusteredIndex, ObjectFullName

I was looking around this morning for a way to see if a table has columns that whose max values would be larger than a 8K SQL Server Page causing 1 row of data to span multiple pages in SQL Server.  Within the database everything is managed using Pages which are 8192 bytes (8 X 1024) or 8KB.  I was playing with some of the catalog views within SQL Server 2008 and came up with the following script and wanted to share it with everyone.  The data you can hold on 1 SQL Server page is 8060 bytes (rest is used internally) so this script gets the MAX length of each column in a table, sums it up and displays a ‘Y’ or ‘N’ flag if that table has the possibility of one row spanning multiple pages:

SELECT
    OBJECT_SCHEMA_NAME(tab.object_id,DB_ID()) AS SchemaName,
    tab.name AS TableName,
    part.rows as NumberRows,
    SUM(col.max_length)  as MaxRowLengthBytes,
    CASE    WHEN SUM(col.max_length) > 8060 THEN 'Y'
            WHEN SUM(col.max_length) <= 8060 THEN 'N'
        ELSE 'UNKNOWN'
    END AS LargerThanPageFlag
FROM sys.tables tab
    INNER JOIN sys.all_columns col
        ON tab.object_id = col.object_id
    INNER JOIN sys.types datatypes
        ON col.system_type_id = datatypes.system_type_id
        AND col.user_type_id = datatypes.user_type_id
    INNER JOIN sys.partitions part
        ON part.object_id = tab.object_id
        AND part.index_id IN (1,0) --Used to get row counts
WHERE tab.is_ms_shipped = 0
GROUP BY OBJECT_SCHEMA_NAME(tab.object_id,DB_ID()), tab.name, part.rows
ORDER BY MaxRowLengthBytes desc

I have been focused a good bit lately into diving into the DMV’s to make my job easier.  Below are some useful ones that I use on a frequent basis.

Dymanic Management Views
sys.dm_exec_sessions (Current sessions)
sys.dm_exec_connections (Current Connections)
sys.dm_exec_requests (Currently executing requests)
sys.dm_exec_query_stats (previously executed SQL that exists in Plan Cache)
sys.dm_exec_cached_plans (Plan Cache)

Dynamic Management Functions
sys.dm_exec_sql_text (Extracts readable SQL from the sql_handle
sys.dm_exec_query_plan (Extracts XML execution plan using plan_handle)

Here are a few examples how these can be used together

--Find Procedures consuming the most logical reads
SELECT 
    DB_NAME(st.dbid) DBName,
    OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName,
    OBJECT_NAME(objectid,st.dbid) StoredProcedure,
    max(cp.usecounts) execution_count,
    sum(qs.total_physical_reads) total_physical_reads,
    sum(qs.total_physical_reads) / max(cp.usecounts) avg_physical_reads,
    sum(qs.total_logical_reads) total_logical_reads,
    sum(qs.total_logical_reads) / max(cp.usecounts) avg_logical_reads
FROM sys.dm_exec_query_stats qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
    INNER JOIN sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
WHERE DB_NAME(st.dbid) is not null 
and cp.objtype = 'proc'
GROUP BY DB_NAME(st.dbid),
        OBJECT_SCHEMA_NAME(objectid,st.dbid), 
        OBJECT_NAME(objectid,st.dbid)
ORDER BY sum(qs.total_logical_reads) / max(cp.usecounts) desc
 
--Find reusability of plan cache (high usecounts)
SELECT 
    c.cacheobjtype , 
    c.objtype , 
    c.usecounts , 
    c.size_in_bytes , 
    t.dbid , 
    t.text 
FROM sys.dm_exec_cached_plans as c
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) as t
WHERE c.cacheobjtype = 'Compiled Plan'
ORDER BY c.usecounts DESC