a)
Change the database to use the row versioning to reduce
the writing / reading blocking. Connect to the master database (as sa or system
admin) and execute this statement: ALTER DATABASE [change it
to your database name] SET READ_COMMITTED_SNAPSHOT ON
b)
Create a job to defragment the tables on a weekly
basis if not exist already. The script for the Job is included in attachment A.
c)
Change the size of the TempDB database to at least 100
MB and move it to a separate Drive if possible and needed.
d)
On 32bit OS enable AWE if the total memory is more
than 4GB. On both 32bit and 64bit SQL Server instances make sure to have the
upper limit for the memory (usually total - 2GB for the OS).
e)
Change the query parallelism on the SQL Server
instance from the default 0 to the number of CPUs minus 2 on the server.
f)
Move system
databases to a separate drive if possible.
g)
Create the job to maintain new Sequence tables (purge
them nightly). The script for the job is in attachment B.
h)
Check and improve the Hard drives' IO. There are
generally two tasks to verify and optimize the hard drives:
h.1.
Check the hard drive partitioning and alignment. Here is the link for info: http://msdn.microsoft.com/en-us/library/dd758814.aspx
h.2.
Check the hard drive configuration with the SQLIO tool
and adjust them if needed accordingly with the hardware vendor configuration
tools (Dell, HP, EMC, etc.). Here is the link for the SQLIO: http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO
Related Topics
Appendix C: Database Improvement Steps
Script A
Script B