[ACCEPTED]-Best way to speed up MS SQL server-performance
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.
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.
Use the Database Engine Tuning Advisor
0
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 ?
Speedy RAID configs and loads of memory.
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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.