INF: SQL Server Performance Analysis

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

The following information is a high-level description of general SQL Server performance analysis.

MORE INFORMATION

General Performance

On stand-alone systems, allocate all available physical memory to SQL Server.

On non-stand-alone systems, subtract memory requirements of other applications from available physical memory.

The percent of memory allocated to the procedure cache is configurable.

The procedure cache needs to be higher during development than during production.

Triggers, rules, and defaults also go in the procedure cache.

Performance can be improved by having devices on separate disks.

The log device should preferably be on a separate device.

If the recovery interval is too low, it can hurt performance and fill the device.

If the recovery interval is too high, it causes periodic massive slowdowns.

Time Slice

The time slice is configurable (the default is 100 milliseconds).

If the time slice is too low, it will slow down the system due to overhead.

If the time slice is too high, it can cause long response times when one process doesn't schedule out for a long time.

Stored Procedures

Stored procedures are faster than ad-hoc queries because they are stored in the procedure cache. It is possible for stored procedures to run slower than ad-hoc queries if the search value cannot be determined until run time. This can happen as when using following type of stored procedure creation:

   create procedure how_fast @parm int as
   begin
   select x from y where z=@parm
   end

Indexes

For a query to run fast on a large table, you must have an index on the columns in the WHERE clause.

The query optimizer chooses the best-looking index, depending on the following factors:

   Clustered or nonclustered indexes
   Statistics
   Nonclustered index covering query
   Selectivity
   Size of table
   Uniqueness

Nonclustered indexes that use "cover query" are very fast, you can add nonkey columns to nonclustered indexes to make specific queries run faster.

The ORDER BY clause will not force a sort if the chosen index is already in the right order.

Indexes must be updated when their tables are updated. When there are a lot of indexes, the UPDATE, INSERT, and DELETE commands can be very slow.

Problem Analysis

The following is the approach to take if a query is running too slowly:

• You should first see whether useful looking indexes exist.
• Be careful about views. If the query uses a view, the query can be a lot more complicated than it appears.
• Be careful about triggers. It may be that the trigger is running slowly, not the user-submitted query itself.
• If there are useful-looking indexes, see what indexes the optimizer is choosing. To do this, set "noexec" on, and also set "showplan" on.
• If the optimizer is not choosing good indexes, make sure the statistics are up to date by using the UPDATE STATISTICS command.