Best way to speed up MS SQL server

Question

I was wondering if anyone has some general sys admin questions on how best to setup MS SQL server for speed. I have placed the log files on a different drive with its own controller and moved the indexes to their drive but on the same controller as the main data files.

What other way can a system admin setup MS SQL server to speed up disk access, query times?

Answer 1

Speedy RAID configs and loads of memory.

Answer 2

In order of impact on performance, from most important to least:

• Write faster code using appropriate data definitions and indexes
• Use faster hardware
• Configuration tricks of the kind you're asking about here.

While a poorly-configured or under-powered server can kill performance, the typical setup should be good enough that this kind of thing is now pretty far down the list of what you should worry about from a performance perspective.

Of course, if you have a specific unexplained bottleneck then maybe you need to configure something. But you should share that information as well.

Answer 3

Use the Database Engine Tuning Advisor

Answer 4

The question is quite general, so a general answer will fit:

• Use fast hardware. Have as much RAM as possible.
• If using a 32 bit OS, have AWE working, especially if the machine is a dedicated DB server.
• If using 64 bit OS - even better. Much more RAM could be put to good use.
• Analyze indexes and application performance regularly. When needed, rebuild indexes, create new ones, delete old ones, etc.
• Learn the different types of indexes - Clustered, Partitioned, etc.
• When necessary, use indexed-views.
• DB Tuning advisor could help.
• Use a LoadBalancing solution to have multiple servers running the DB.
• Understand the nature of the application - OLAP apps has other needs than DataWarehousing apps. This would affect the structure of the tables, disk spanning, etc.

Answer 5

Be careful of the DTA (tuning advisor). It all depends on the workload.

Quick things: Index better. Look for long running queries, or slow ones, and add indexes to support here. If this is OLTP, beware of adding too many indexes.

Write better code, post slow queries somewhere (here, or SQLServerCentral.com) and get ideas for speeding them

More memory

Separation of logs from data physically helps, but you have to be IO bound for this to make a difference.

Answer 6

First of all, I would try to identify the bottleneck; what is the 'thing' where you can improve most / What is the slowest part of your system ?

Is it your code ? (Appropriate indexes, set-based processing, avoid cursors as much as possible, ... ) Is it hardware - related ? Is it configuration-related ? - logs on a separate filegroup / separate disk - can you move some tables to another filegroup , so that tables that are often joined together in queries are in separate filegroups - do you rebuild the indexes often ?