[ACCEPTED]-Best way to speed up MS SQL server-performance

Accepted answer
Score: 20

The question is quite general, so a general 1 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.
Score: 3

In order of impact on performance, from 9 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 8 or under-powered server can kill performance, the 7 typical setup should be good enough that 6 this kind of thing is now pretty far down 5 the list of what you should worry about 4 from a performance perspective.

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

Score: 1

Use the Database Engine Tuning Advisor


Score: 1

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

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

Score: 0

Speedy RAID configs and loads of memory.


Score: 0

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

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

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

More memory

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

More Related questions