Thursday 15 December 2011

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


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.

Wednesday 7 December 2011

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]

Saturday 3 December 2011

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

Thursday 1 December 2011

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