SQL Server Quick Check - Notes

Checking a SQL Server over? Some notes on how to approach this.
Examine aspects in this order (Memory > Storage > CPU) as one issue can be a symptom of another.

Memory

Examine using Performance Monitor (Perfmon). Look at these counters -

Memory:Available MBytes
How much memory is available for Windows?
Stop SQL consuming too much by setting maximum memory (recommended tip).

SQLServer:Memory Manager/Target Server Memory (KB)
How much memory SQL wants.

SQLServer:Memory Manager/Total Server Memory (KB)
How much memory SQL has.
If this is less than the value it wants then more memory is needed to be allocated or installed.

SQLServer:Buffer Manager:Page Life Expectancy
300 seconds (5 minutes) for an OLTP system.
90 seconds (1 & 1/2 minutes) from a data warehouse.

The old method was SQLServer:Buffer cache hit ratio (ideal of 99%) but data volumes mean this counter has become meaningless now.


Storage

Examine these Windows counters using Perfmon for the Windows view of Storage performance

LogicalDisk:Avg.Disk sec/Transfer
Should be < 20ms (0.020 seconds) for volumes hosting sql data files

For further problems look for differences between
LogicalDisk:Avg.Disk sec/Read & LogicalDisk:Avg.Disk sec/Write
Could show issues with controller or RAID (e.g. slow write on RAID5).


For a SQL view of Storage performance, some TSQL to help out ...

On SQL 2000 looking at I/O statistics is achieved using fn_virtualfilestats

SELECT
   d.name as DBName
   ,RTRIM(b.name) AS LogicalFileName
   ,a.NumberReads
   ,a.NumberWrites
   ,a.BytesRead
   ,a.BytesWritten
   ,a.IOStallReadMS
   ,a.IOStallWriteMS
   ,CASE WHEN (a.NumberReads = 0) THEN 0 ELSE a.IOStallReadMS / a.NumberReads END AS AvgReadTransfersMS
   ,CASE WHEN (a.NumberWrites = 0) THEN 0 ELSE a.IOStallWriteMS / a.NumberWrites END AS AvgWriteTransfersMS
FROM ::fn_virtualfilestats(-1,-1) a
INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
INNER JOIN sysdatabases d ON d.dbid = b.dbid
ORDER BY a.NumberWrites DESC

For SQL 2005+, sys.dm_io_virtual_file_stats is a available.
This is a dynamic management function that shows how SQL is using the data files.

SELECT
    DB_NAME(filestats.database_id) AS DBName
    ,files.name AS LogicalFileName
    ,num_of_reads AS NumberReads
    ,num_of_writes AS NumberWrites
    ,num_of_bytes_read AS BytesRead
    ,num_of_bytes_written AS BytesWritten
    ,io_stall_read_ms AS IOStallReadMS
    ,io_stall_write_ms AS IOStallWriteMS
   ,CASE WHEN (num_of_reads = 0) THEN 0 ELSE io_stall_read_ms / num_of_reads END AS AvgReadTransfersMS
   ,CASE WHEN (num_of_writes = 0) THEN 0 ELSE io_stall_write_ms / num_of_writes END AS AvgWriteTransfersMS
FROM sys.dm_io_virtual_file_stats(-1,-1) filestats
INNER JOIN sys.master_files files
        ON filestats.file_id = files.file_id
        AND filestats.database_id = files.database_id
ORDER BY num_of_writes DESC

Further info on here c/o David Pless

If Disk Performance is an issue, consider these aspects

• Auditing disk configuration
• Physical file fragmentation - contig.exe 
• Is the RAID configuration appropriate for volume of transactions.

CPU

Performance Monitor (Perfmon) counters to determine processor use are -

Processor:% Processor Time

How busy is the processor?
Should be < 70%

Processor:% Interrupt Time 

Percentage of time spent servicing hardware interrupt requests.
Should be < 20%

Processor:Processor Queue Length

How many tasks are waiting for processor time?
Should be < 4 per CPU

For Processor counters, it may be desirable to monitor separate instances (different cores) in addition to monitoring the _Total instance. The _Total instance provides average readings and therefore disguises individual overworked or under-utilized processors / cores.
The processor counters are for everything installed on the system, not just SQL Server.

Foreign Keys without Indexes

Here are some scripts that provide index creation statements for foreign keys without indexes. NB : I am not advocating creating indexes on every foreign key. Their use depends on application design, (the sql it runs) and whether other covering indexes are present. SQL 2000 Version

/*
adapted from http://stackoverflow.com/questions/1406119/how-can-i-find-unindexed-foreign-keys-in-sql-server
Uses my NCI_tablename-indexname index naming convention
*/

DECLARE 
    @SchemaName varchar(255),
    @TableName varchar(255),
    @ColumnName varchar(255),
    @ForeignKeyName sysname

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT  cu.TABLE_SCHEMA, cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE   ic.CONSTRAINT_TYPE = 'FOREIGN KEY'

CREATE TABLE #temp1(    
    SchemaName varchar(255),
    TableName varchar(255),
    ColumnName varchar(255),
    ForeignKeyName sysname
)

OPEN FKColumns_cursor  
FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName

WHILE @@FETCH_STATUS = 0  
BEGIN

    IF ( SELECT COUNT(*)
    FROM        sysobjects o    
        INNER JOIN sysindexes x ON x.id = o.id
        INNER JOIN  syscolumns c ON o.id = c.id 
        INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
    WHERE       o.type in ('U')
        AND xk.keyno <= x.keycnt
        AND permissions(o.id, c.name) <> 0
        AND (x.status&32) = 0
        AND o.name = @TableName
        AND c.name = @ColumnName
    ) = 0
    BEGIN
        INSERT INTO #temp1 SELECT @SchemaName, @TableName, @ColumnName, @ForeignKeyName
    END


    FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@TableName, @ColumnName, @ForeignKeyName
END  
CLOSE FKColumns_cursor  
DEALLOCATE FKColumns_cursor 

SELECT 'IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = '''
       + 'NCI_' + TableName + '_' + ColumnName + ''') '
       + ' CREATE INDEX [NCI_' + TableName + '_' + ColumnName + '] ON [' + SchemaName + '].[' + TableName + ']([' + ColumnName +'])'
FROM #temp1 
ORDER BY TableName

DROP TABLE #temp1 

SQL 2005 Version

/*
adapted from http://encodo.com/en/blogs.php?entry_id=173
Uses my NCI_tablename-indexname index naming convention
Have added table schemas and 'IF EXISTS' checks to detect if index is already present
*/

SELECT
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''['
+ IndexSchemas.[name]
+ '].['
+ IndexTables.[name]
+ ']'') AND name = N''IX_'
+ IndexSchemas.[name] + '_' + IndexTables.[name] + '_' + IndexColumns.[name]
+ ''') '
+ 'CREATE NONCLUSTERED INDEX [IX_'
+ IndexSchemas.[name] + '_' + IndexTables.[name] + '_' + IndexColumns.[name]
+ '] ON ['
+ IndexSchemas.[name] + '].[' + IndexTables.[name] 
+ ']( ['
+ IndexColumns.[name]
+ '] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
FROM sys.foreign_keys ForeignKeys
INNER JOIN sys.foreign_key_columns ForeignKeyColumns
  ON ForeignKeys.object_id = ForeignKeyColumns.constraint_object_id
INNER JOIN sys.columns IndexColumns
  ON ForeignKeyColumns.parent_object_id = IndexColumns.object_id
  AND ForeignKeyColumns.parent_column_id = IndexColumns.column_id
INNER JOIN sys.tables IndexTables
  ON ForeignKeyColumns.parent_object_id = IndexTables.object_id
INNER JOIN sys.schemas IndexSchemas
  ON IndexTables.schema_id = IndexSchemas.schema_id
ORDER BY IndexTables.[name], IndexColumns.[name]

Enabling/Disabling constraints

-- Enable Constraints
ALTER TABLE tablename CHECK CONSTRAINT constraintName
ALTER TABLE tablename CHECK CONSTRAINT ALL 

-- Disable Constraints
ALTER TABLE tablename NOCHECK CONSTRAINT constraintName
ALTER TABLE tablename NOCHECK CONSTRAINT ALL

-- Add constraint, without checking existing data -
ALTER TABLE tablename WITH NOCHECK ADD CONSTRAINT PK_tablename_id PRIMARY KEY(id) 

Solace : Disabling All Constraints (use with caution, e.g for data loading)
Ode to Code : SQl Server Constraints
Joe Webb : Disable constraints on a table

Deprecated column types

Looking to the future, I'm examining an application for deprecated features. Data types text, ntext & image are marked for deprecation in a future version of sql (although they still appear to be present in SQL 2012).

Finding them is easy via the information_schema views -

SELECT table_catalog, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('text','ntext','image')

If you want more information you can use the system tables directly. -

For SQL2005+ -

SELECT a.name name, b.name, c.name, b.max_length, *
FROM sys.objects a
INNER JOIN sys.columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.system_type_id = c.system_type_id
WHERE a.type='u'  
AND c.name IN ('text','ntext','image')
ORDER BY 1,2,3

For SQL 2000 -

SELECT a.name tablename, b.name columnname ,c.name datatype, b.length, *
FROM sysobjects a  
INNER JOIN syscolumns b  ON a.id=b.id  
INNER JOIN systypes c  ON c.xtype=b.xtype
WHERE a.xtype='u'  
AND c.name IN ('text','ntext','image')
ORDER BY 1,2,3