Reputation: 173

Best way to speed up MS SQL server

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?

Upvotes: 10

Answers (6)

Moshe

Reputation: 2668

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.

Upvotes: 20

Joel Coehoorn

Reputation: 415600

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.

Upvotes: 3

Frederik Gheysels

Reputation: 56934

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 ?

Upvotes: 1

Steve Jones

Reputation: 148

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.

Upvotes: 0