SQL Server Audit

The Final Word on How to Perform a SQL Server Performance Audit If you have gotten this far, you have done a lot of reading. In this final article on how to perform a SQL Server Audit, we will take a look at some best practices about how to best implement a SQL Server Performance [...]

SQL Server Query Performance Audit Checklist SQL Server Job Checklist 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? Enter your results in the table above. Identifying Long Running Queries is First Step At this step in the SQL [...]

SQL Server Job Performance Audit Checklist 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 [...]

Performance Audit Checklist 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 2000off in 7.0 compatibility level 80 for 200070 for 7.0 database auto grow on transaction log auto grow on Enter your results in the table above.  Each Database Needs to Be Audited [...]

Performance Audit Checklist SQL ServerConfiguration Settings AdvancedSetting? RequiresRestart? Default Value Current Value 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 [...]

Index Performance Audit Checklist 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? [...]

Performance Audit Checklist  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 [...]

Performance Audit Checklist  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 [...]

Performance Audit Checklist  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 Enter your results in the table above.   Use Performance Monitor to Help [...]