As noted earlier, a checkpoint is essentially a heartbeat on the SQL Server. This process will execute on a regular basis. Your transaction log is where all data that is modified in the database is written. When truncate log on checkpoint is enabled, the committed transactions will be flushed to disk and then removed from the transaction log. The way this process works is that checkpoint process starts at the beginning of the log and starts flushing transactions to disk. Once it encounters the first open transaction, the checkpoint process termintes and waits for the next cycle to begin. If transactions are being held open for a significant period of time, the checkpoint process will be prevented from writing any subsequent committed transactions to disk. This also prevents any of these transactions from being flushed from the transaction log. Turning this option on obviously has a significant impact on recoverability. Since committed transactions are removed from the transaction log at every checkpoint of the database, you will not be able to back up the transaction log.

arithabort

The arithabort option controls what SQL Server will do when an arithmetic error occurs. When set to true, SQL Server will abort any query that causes an arithmetic error. This can include a divide by zero error or numeric overflow condition, in which the value is greater than the capacity of the defined datatype.

arithignore

Arithignore stops the SQL Server from raising an error if an arithmetic error is encountered.

nocount

By default, SQL Server will display a line in the query output that contains the number of rows affected by the statement. To disable this line from printing, turn this option on.

noexec

In the case of performance tuning, it is highly desirable to determine how a query will execute without actually going to all of the work of returning the result set. By setting noexec on, SQL Server will only do enough work to determine the query plan to be used to satisfy the request. This is normally used in conjunction with the showplan option.

parseonly

The parseonly option can be useful when developing SQL. The parseonly option will simply parse the SQL batch to determine if any syntax errors are encountered.

showplan

Every query that is submitted to SQL Server goes through a process where the optimal access plan is devised. This query plan is normally held internally to SQL Server. By turning on the showplan, SQL Server will display the query plan that was utilized to satisfy the request. You can then evaluate this plan to determine if additional indexes need to be created or if the query needs to be reformulated.

statistics time

The statistics time option is your best source of benchmarking information. It will display the number of milliseconds SQL Server spent parsing the SQL, formulating the query plan, gathering the data, and returning the data to the user. This can be used in an iterative approach to aid in identifying and tunng long running queries.

Keep in mind that when establishing benchmarks, you should always execute the query multiple times. I normally execute a query 3 times before beginning the benchmark. The benchmark time then consists of the average of the times during the 4^th through 6^th executions. The first three executions are used to ensure that the data is cached. The 4^th through 6^th executions will give you a true basis of comparison since you are ensured that the query will gather data from the data cache as much as possible. The final thing that should be obvious when establishing benchmarks is to make sure that you are executing on a server with no or minimal activity so as to not skew the results.

You might say that it would be nice to do this in an isolated environment, but you might not have the equipment available. The thing to keep in mind when making performance improvements is that you are not concerned with the absolute time it takes to run a query. You are conerned with the relative improvement. This means that any machine capable of running SQL Server can serve as your testing machine. If you are running on a machine that has a single P200 with 64 MB of RAM and you succeed in reducing the execution time by 80%, then you should expect at least an 80% improvement when running this on a quad processor Xeon with 2GB of RAM.

SQL Server 7.0 Server, Database, and Query options 1 2 3 4 5 6 7 8