|
Return to Newsletter Contents...
Improving SQL Server Performance
by: Charles Tournear, Senior Consultant, MCSE, MCSD, CRCP,
MCDBA, MCT
The speed and performance of a SQL server is dependent
on three areas; hardware speed and configuration, database design, and application
or query design.
Hardware
A SQL server needs fast processors to manage a large
number of queries. Most people look for systems with a lot of memory and fast processors.
One thing in configuring multi-processor systems that is often overlooked is that
the disk access is controlled by the operating system and not the SQL Server service.
Performance for disk access for loading data and accessing virtual memory can be
improved by dedicating one processor or at least 25% of the processing capability
to the operating system. For example, if you have a system with 4 CPUs, only allow
the SQL Server to use CPU 1, 2, 3 and let the operating system have the lower numbered
processor
CPU 0.
Database Design
Database design can greatly affect the speed of processing
a query. The number of tables that have to be joined to retrieve the data you need
in a query affects the speed of the query processing. Try to keep the number of
tables needed for most queries to a minimum. If a table is being used primarily
as a lookup or restriction on data choices, consider keeping the data itself in
the main table instead of having to link to the table with an id number to retrieve
the data. You can still use a foreign key to protect the data allowed, but you won’t
have to join to the table for queries.
Another area to consider is the maximum page size and
maximum row size for data storage when designing tables. For example, Microsoft
SQL Server has a maximum row and page size of 8060 bytes. So if you created a single
table with a total row size of 4100 bytes, the maximum number of rows that would
fit per page would be one and there would be 3960 bytes per page of wasted space.
It would be more efficient if the single table of 4100 per row could be broken down
into multiple tables. For example 2000 (allowing 4 rows per page) and 2100 (allowing
3 rows per page) thus reduces the amount of wasted space in the database.
When there is a one-to-many relationship between two
tables that will be joined often to lookup a record in the one and return the associated
many from the other table, the more records that can fit per page on the many side
can improve the speed of returning the related data. Creating indexes on the columns
that define the join will also help in the retrieval of data.
Application and Query Design
When designing an application that retrieves data from
a SQL Server data source, try to work in a disconnected process. If you maintain
a connection to the server while the application is running, most connectors poll
the server every 15 minutes just to verify that the server is still available. This
extra network traffic can slow down all other activities that also require network
access. Also try to be precise about the data that needs to be retrieved. Always
use a WHERE clause to reduce the amount of data to be returned. The more data returned
the longer the server is tied up processing your query and the longer you are blocking
other requests. Also never use the * with SELECT. List only those columns that you
actually need to use after the SELECT to again reduce the amount of data being returned.
Even if you do want to retrieve all the columns, list the column name instead of
using SELECT *. The * has to be resolved to an actual list of names before the query
can begin to be processed, which can require additional trips back and forth from
the client and server.
Go to Top |
Return to Newsletter Contents
|
