SQL Server Audit

Change Tracking in SQL Server 2008

Introduction SQL Server 2008 includes  several inbuilt features for Auditing – namely Change Data Capture (CDC) and Auditing and Change Tracking – this article we are going to discuss  Change Tracking. What is Change Tracking Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. In case of data warehousing […]

Audit Data Modifications

Audit Data Modifications This is one DBA’s tale of auditing his databases for modifications.  There will be many different ways that you can approach this task, and reach a solution.  Your solution may be similar to or different from mine.  I will explain how I reached my solution, and give you some good ideas of […]

SQL Server 2005 Database Options and Configuration Performance Audit Checklist

Database Options and Configuration Settings Database Configuration Settings Default Value Current Value auto_close off auto_create_statistics on auto_update_statistics on auto_update_statistics_asynch off auto_shrink off read_only off page_verify checksum parameterization simple compatibility level 90 database auto grow on transaction log auto grow on Enter your results in the table above.  Every Database Needs to Be Audited . When […]

SQL Server 2005 Server Configuration Performance Audit Checklist Part 2

Blocked Process Threshold This option is new to SQL Server 2005. The blocked process threshold option is used to specify the threshold, in seconds, when blocked process reports are generated. By default, no blocked process reports are produced. You can define an alert to be executed when this event is generated. For example, you can […]

SQL Server 2005 Server Configuration Performance Audit Checklist : Part 1

Performance Audit Checklist Enter your results in the table above. Most SQL Server 2005 Configuration Settings Should Not Be Changed In this section, we are going to take a look at some of the . These are SQL Server-specific settings that can be modified using Management Studio, SP_CONFIGURE, and in some cases, the Surface Area […]

SQL Server 2005 Performance Audit : Operating System Configuration Performance Checklist

SQL Server 2005 Operating System Configuration Checklist: Which OS version are you running? Are the disk partitions formatted using the most recent version of NTFS?Is “NTFS data file encryption and compression” turned off? Do you have at least 20% free space on each of your logical disk drives?Does your server OS have the latest service […]

SQL Server 2005 Performance Audit : SQL Server Hardware Configuration Checklist

  SQL Server Hardware Configuration Checklist:   Number of CPUs (and or Cores)     32-Bit vs. 64-Bit   CPU MHz      CPU L2 Cache Size      Physical RAM Amount     SAN vs. Local Storage   Total Amount of Available Drive Space on Server     Total Number of Physical Drives in Each Array     RAID Level […]

SQL Server 2005 Performance Audit : Introduction

As a SQL Server consultant, I often get requests to “look over” a SQL Server and to offer suggestions on how to boost its performance. Sometimes this task is easy, and other times it is difficult. Based on experience, I have discovered that my time is best spent looking first for the “easy-to-identify-and-solve” performance problems, […]

DDL Triggers in SQL Server 2005

Introduction In 2002 the US Congress enacted the Sarbanes-Oxley Act which required a company to have solid change manage procedures for IT systems. One of the requirement is to know who did what and when to the database objects. There are several ways to achieve this in SQL Server 2000 – C2 level auditing or […]

New Index Locking Granularity in SQL Server 2005 Gives You More Options

While most DBAs are familiar with how locking works with tables, few DBAs are familiar with how SQL Server 2005 applies locking to indexes. That’s right, indexes, just like tables, are subject to locking. And as most DBAs already know, inappropriate locking (in tables or indexes) can negatively affect the performance of SQL Server. Just […]