Six tweaks to improve performance
So you've loaded the latest SQL patches and Windows service packs, updated
Patron Edge and PEO to the latest version and
trimmed
down transaction times, but your database just can't keep up with you.
Before going out and buying a brand new, top-of-the-line server for your
database, try some of these tweaks to get a bump in performance.
Update your compatibility level - If you updated at some point from
SQL Server 2000 to version 2005, chances are your database is still running in
SQL 2000 compatibility mode. Microsoft claims performance gains by updating to
2005 so make
sure that your database is taking advantage of it.
Keep things tidy - Imagine you
are making dinner and having a conversation at the same time, and end up
putting a corkscrew in the wrong drawer. The next time you need the corkscrew it
takes longer than usual to find it, right? So after a while you go and reorganize
everything to find things quickly again. Just like you need to reorganize the kitchen
cabinets every once in a while to find the things you need, you also need to
reorganize your hard disk and your database to make data easier to find. See this article for defragmenting
the physical filesystem, and Knowledgebase solution BB120081
for steps on rebuilding the database indexes.
Update statistics - SQL Server
keeps statistics about all of your tables and idexes. This includes things like
the number of rows in a table and helps SQL determine the best way to build a
query and return data. Keeping statistics up-to-date allows queries to run
their fastest. Check out Knowledgebase solution BB137060
for a how-to.
Split your database files - Reading
and writing from the hard disk is one of the biggest performance bottlenecks of
SQL Server. To reduce the time it takes to read and write from a disk, put the
database file on one hard disk and the log file on a second disk (on the same
machine). See solution BB113761
for steps to do this. If you have three hard disks, put the ‘Tempdb’ database
on a third disk for the best performance. It’s not too difficult to do, but you’ll
want to consult your system administrator first.
Check out your memory - Another great
way to improve performance is to ensure that the server has enough memory.
Right-click the taskbar and select Task Manager. On the Performance tab, check the
Commit Charge total against the Physical Memory total. If the Commit Charge is
regularly jumping higher than the Physical Memory, the machine is attempting to
convert hard disk space into memory, which is slow. This is a good case to buy
more memory for the server.
Give SQL an edge over other
applications - By default, Windows is configured to give running programs
priority over background services. We can switch this so that SQL Server and
other services have priority over programs run in the foreground. To do this,
go to Control Panel, System, Advanced. In the Performance section click
Settings and click Advanced in the new window that comes up. Finally, set the
Processor Scheduling section to Background Services and click OK.
Are there other tweaks you've found to get more out of your database server? Share your experience in the comments.