|
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.
|
