ID Number: Q62059
1.10 1.11 4.20
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
In general, frequently used simple operations should be contained in
stored procedures.
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
Clustered indexes are generally faster -- use them for most frequent
look ups, use nonclustered indexes for less frequent look ups.
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.
Additional reference words: Optimization and tuning