Partager via

Fine Tune your SQL Server 2000 Performance

  • Article

**This applies to SQL Server 2000**
 

When comes to performance tuning for SQL Server 2000, there are few things that you can look into:

1. SQL Performance Tools:

SQL Profiler and the indexer Tuning Wizard

SQL Profiler can be used to log to workload of the database and the log file from the SQL profiler will be using to fine tune the indexes to increase the performance

https://msdn2.microsoft.com/en-us/library/aa902645(SQL.80).aspx

SQL Query Analyzer & Execution Plan
It uses to analyze the SQL queries
https://msdn2.microsoft.com/en-us/library/aa178423(SQL.80).aspx

System Monitor Objects

Monitors and analyses the SQL Server’s operations

https://msdn2.microsoft.com/en-us/library/ms191246.aspx

2. Create and maintain indexes

The way you design and create the indexes affect the SQL server performances. The two main categories of indexes are clustered indexes and non-clustered indexes.
Clustered indexes are very useful for retrieving ranges of data values from a table. Nonclustered indexes are ideally suited for targeting specific rows for retrieval, whereas clustered indexes are ideally suited for retrieving a range of rows https://msdn2.microsoft.com/en-us/library/aa198340(SQL.80).aspx

3. Partition large dataset and indexes

Partition the databases stored on different disk drive will also improve the performance by increasing the amount of disk I/O parallelism

The simplest technique for creating disk I/O parallelism is to use hardware partitioning and create a single "pool of drives" that serves all SQL Server database files except transaction log files, which should always be stored on physically separate disk drives dedicated to log files only.

Learn more on “Using Partitions in a SQL Server 2000 Data Warehouse" from this site: https://msdn2.microsoft.com/en-us/library/aa902650(sql.80).aspx

4. Tune Applications and queries

There are few things you need to take into consideration when designing the databases for your applications:
- Using inequality operators in SQL queries will force databases to use table scans to evaluate the inequalities. This generates high I/O if these queries regularly run against very large tables. WHERE clauses that contain the "NOT" operators (!=, <>, !<, !>), such as WHERE <column_name> != some_value will generate high I/O.

- design database https://msdn2.microsoft.com/en-us/library/aa906003(SQL.80).aspx

- reduce the size of the resultset being returned by eliminating columns in the select list that do not need to be returned, or by returning only the required rows. This helps reduce I/O and CPU consumption

-use multiple statements to return the resultset

For more details on how to fin-tune your SQL Server database, please visit this site: https://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx