SQL Server performance depends on the interaction of many of factors, ranging from the hardware that the system runs on to the application coding techniques you can use. Here are seven ways you can improve SQL Server's performance.
7. Use the SQL Profiler
The key to application performance (and all database performance, for that matter) is minimizing I/O. Because applications are the users' gateway to the database, poor application performance can have a huge impact on system performance. Factors that affect application performance include selecting the proper indexes and using the appropriate cursor type. You can use the SQL Profiler to trace an application's database activity.
6. Use the SQL Performance Monitor
By regularly monitoring the server, especially its disk utilization, you can uncover important clues for troubleshooting performance problems. Before you have a problem is the best time to use the SQL Performance Monitor to establish a performance baseline for your server. SQL Server provides counters that the Windows NT Performance Monitor can track. Counters that can help you monitor disk activity include PhysicalDisk: Percentage Disk Time, PhysicalDisk: Average Disk Queue Length, SQL Server: Buffer Manager Page Reads/second, and SQL Server: Buffer Manager Page Writes/ second. These disk I/O counters are not turned on by default, so run diskperf-y first to use them.
5. Use Query Analyzer ShowPlan
The Query Analyzer ShowPlan option is useful when you develop queries and stored procedures and when you diagnose query performance problems. By executing your queries with ShowPlan, you can see the execution plan the query processor chooses and the estimated costs for that query.
4. Use the Index Tuning Wizard
You can use SQL Server's SQL Profiler to capture the server's workload, then submit the workload to the Index Tuning Wizard, which suggests index changes that can improve the system's performance. Use the SQL Profiler and Index Tuning Wizard regularly to make sure that your system continues to use the proper indexes as system usage changes over time.
3. Use automated tuning
SQL Server 7.0 is capable of an astonishing amount of self-tuning and dynamic performance adjusting. To ensure that automated tuning is helping you, leave the server settings—minimum server memory, maximum server memory, and the user connections option—in the default settings.
2. Plan disk subsystem usage
Planning disk subsystem usage begins when you set up the system. The placement of the paging file, transaction logs, and system databases can have a big impact on system performance and recoverability. For example, placing your transaction logs and database files on separate physical disks is always a good practice.
1. Get enough memory
Like all database systems, SQL Server is memory hungry. Memory is a cheap resource that you can't get enough of. No amount of tuning can make up for a lack of RAM.