|
Many ERP applications use SQL Server as their backend database. A slow-running database
server would severely affect the productivity of your business. This article gives
you tips to fix most common SQL Server performance problems quickly and inexpensively.
Some tips require monitoring performance counters. You can use System Monitor, a
tool that comes pre-installed on Windows. Note that monitoring puts its own load
on the server. You must not run System Monitor (or SQL Profiler) from the SQL Server
machine itself. You must run them on another machine. Log a typical 24 hour period
so that you get a big picture.
1. Index table access
The single most common cause of performance degradation is inadequate indexing on
the tables. The simple act of placing an index on the right column in a table can
often reduce execution time for a critical procedure from minutes to seconds or
from hours to minutes. It also reduces disk activity, CPU load, memory consumption,
etc., thereby boosting performance further.
What to index is not a simple question. Here are some guidelines:
If you create a primary key, it will automatically be indexed
Foreign keys must almost always be indexed
It is of critical importance to index columns that are frequently used in the WHERE
clause of important queries
2. Place tempdb database on a separate disk system or a fast disk system
Queries involving merges, joins, sorts, etc. require tempdb database. Placing tempdb
on a fast disk system improves performance.
3. SQL Server must be placed on its own server
Production SQL Server must not share resources with any other major application.
It must not be on a domain controller and it must not be on a web server. It must
be the only application running on the server.
4. Memory
Get as much memory as you can afford. SQL Server loves memory, and it is often the
cheapest way to get the performance boost you need.
To understand how your memory is being used, monitor SQL Server Buffer Manager:
Cache Hit Ratio. This value should be in the high nineties. 99% is okay. 95% is
not. Also, counter Memory:Pages\Sec must average 20 or below over a typical 24 hour
period
5. CPU
If counter Processor: % Processor Time frequently exceeds 80% for several minutes
at a time and/or System: Processor Queue Length (total) exceeds an average of 2
per processor (i.e. a total of 4 on a 2 CPU server) for several minutes at a time,
you may want to add more or faster processors. Keep in mind that the size of L2
cache is sometimes more important than actual processor speed, so get as much as
possible when you upgrade.
If you have massive amount of data, get a 64-bit CPU (they have become cheap) and
64-bit edition of SQL Server. You will see tremendous performance improvement.
6. Disk I/O
If Physical Disk: % Disk time averages over 60% for several minutes at a time and/or
Physical Disk: Avg. Disk Queue Length exceeds 2 per physical disk in an array (i.e.
a 5 disk RAID array is good for a queue length of 10), you probably could use more
or faster disk arrays.
Note that disk configuration on a database server is a complex subject and generally
it is better to consult an expert.
A closing thought. It is a common accounting practice to depreciate computer hardware
over five years. However, using this practice to mandate a five year replacement
cycle for a server is not a good idea. Hardware technology has been advancing very
rapidly. In my view, replacing systems on a two-year cycle will get you much better
ROI.
|
