| 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 SQL Server: 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? |
|
|
|
|
