Performance Audit Toolkit

02 April,2007 by Jack Vamvas

Use this Peformance Audit Toolkit as a basis for a Database Health Check

SQL Server Audit  (copy and paste into Microsoft Excel)        
Performance Monitor to analyse Hardware Bottlenecks  
 Counter Name  Average  Minimum  Maximum   
 Memory: Pages/sec         
 Memory: Available Bytes         
 Physical Disk: % Disk time        
 Physical Disk: Avg. Disk Queue Length         
 Processor: % Processor Time         
 System: Processor Queue Length         
 SQL Server Buffer: Buffer Cache Hit Ratio         
 SQL Server General: User Connections         
         
SQL Server Hardware Characteristics Describe Here      
 Number of CPUs        
 CPU MHz         
 CPU L2 Cache Size         
 Physical RAM Amount        
 Total Amount of Available Drive Space on Server        
 Total Number of Physical Drives in Each Array        
 RAID Level of Array Used for SQL Server Databases        
 Hardware vs. Software RAID        
 Disk Fragmentation Level        
 Location of Operating System        
 Location of SQL Server Executables        
 Location of Swap File        
 Location of tempdb Database        
 Location of System Databases        
 Location of User Databases        
 Location of Log Files        
 Number of Disk Controllers in Server        
 Type of Disk Controllers in Server        
 Size of Cache in Disk Controllers in Server        
 Is Write Back Cache in Disk Controller On or Off?        
 Speed of Disk Drives        
 How Many Network Cards Are in Server?        
 What is the Speed of the Network Cards in Server?        
 Are the Network Cards Hard-Coded for Speed/Duplex?        
 Are the Network Cards Attached to a Switch?        
 Are All the Hardware Drivers Up-to-Date?        
 Is this Physical Server Dedicated to SQL Server?        
         
Operating System Performance Considerations Your Configuration      
Which OS version are you running?        
Are the disk partitions formatted using NTFS 5.0?        
Is "NTFS data file encryption and compression" turned off?        
Does your server have the latest service pack?        
Does your server have the most current, Microsoft-certified hardware drivers?        
Is the Windows Server configured as a stand-alone server?        
Is the "Application Response" setting, set to "Optimize Performance" for "Background Services?"        
Has security auditing been turned on?        
How large is the server's PAGEFILE.SYS swap file?        
Have unnecessary services been turned off?        
Have all unnecessary network protocols been turned off?        
Is antivirus software being used?        
         
SQL Server Configuration Advanced Requires Default Value Current Value
Configuration Settings Setting? Restart?
affinity mask  Yes Yes 0  
awe enabled  Yes Yes 0  
cost threshold for parallelism  Yes  No  5  
cursor threshold  Yes  No  -1  
fill factor (%)  Yes  Yes  0  
index create memory (KB)  Yes  No  0  
lightweight pooling  Yes  Yes  0  
locks  Yes  Yes  0  
max degree of parallelism  Yes  No  0  
max server memory (MB) Yes  No  2147483647  
max text repl size (B) No  No  65536  
max worker threads Yes  Yes  255  
min memory per query (KB) Yes  No  1024  
min server memory (MB) Yes  No  0  
nested triggers No  No  1  
network packet size (B) Yes  No  4096  
open objects Yes  Yes  0  
priority boost Yes  Yes  0  
query governor cost limit Yes  No  0  
query wait (s)  Yes  No  -1  
recovery interval (m) Yes  No  0  
scan for startup procs Yes  No  0  
set working set size Yes  Yes  0  
user connections Yes  Yes  0  
         
Database Configuration Settings Default Value Current Value    
auto_close off      
auto_create_statistics on      
auto_update_statistics on      
auto_shrink off      
read_only off      
torn_page_detection on in 2000      
off in 7.0    
compatibility level 80 for 2000      
70 for 7.0    
database auto grow on      
transaction log auto grow on      
     
Indexing Checklist Your Response      
Have you run the Index Tuning Wizard recently?        
Does every table in each database have a clustered index?        
Are any of the columns in any table indexed more than once?        
Are there any indexes that are not being used in queries?         
Are the indexes too wide?         
Are tables that are JOINed have the appropriate indexes on the JOINed columns?         
Are the indexes unique enough to be useful?          
Are you taking advantage of covering indexes?          
How often are indexes rebuilt?         
What is your index fillfactor?        
         
Transact-SQL Checklist Your Response      
Does the Transact-SQL code return more data than needed?        
Are cursors being used when they don't need to be?        
Are UNION and UNION SELECT properly used?        
Is SELECT DISTINCT being used properly?        
Is the WHERE clause sargable?        
Are temp tables being used when they don't need to be?        
Are hints being properly used in queries?        
Are views unnecessarily being used?        
Are stored procedures being used whenever possible?        
Inside stored procedures, is SET NOCOUNT ON being used?        
Do any of your stored procedures start with sp_?        
Are all stored procedures owned by DBO, and referred to in the form of databaseowner.objectname?        
Are you using constraints or triggers for referential integrity?        
Are transactions being kept as short as possible?        
Application Checklist        
Is the application using stored procedures, strings of Transact-SQL code, or using an object model, like ADO, to communicate with SQL Server?         
What method is the application using to communicate with SQLServer: DB-LIB, DAO, RDO, ADO, .NET?         
Is the application using ODBC or OLE DB to communication with SQL Server?        
Is the application taking advantage of connection pooling?         
Is the application properly opening, reusing, and closing connections?         
Is the Transact-SQL code being sent to SQL Server optimized for SQL Server, or is it generic SQL?        
Does the application return more data from SQL Server than it needs?        
Does the application keep transactions open when the user is modifying data?        
         
SQL Server Job Checklist Your Response      
Are you running any unnecessary jobs?        
Are jobs scheduled to run during production lulls?        
Do any SQL Server jobs on the same server overlap?        
Do you have any non-SQL Server jobs that overlap?        
Have jobs that run T-SQL been optimized?        
Have you checked to see how long jobs run?        
Are there alternative to your current jobs?        
         
Use Profiler to identify poorly running queries Your Response      
Have you identified all long running queries?        
Have you prioritized the queries?        
Have you reviewed the execution plans of the above prioritized queries?        

text of the audit comes from an audit Report created for SQL Server 2000 by Brad McGee

Related Posts on wait stats and RAID levels

IO_completion on disk solved with Wait Stats and Perfmon

Powershell sql server security audit

SQL Server – RAID levels overview


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Performance Audit Toolkit


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer