- Introduction
- Analyzing Memory Usage
- Analyzing Processor Usage
- Analyzing Disk Usage
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Analyzing Memory Usage
Memory is so important to SQL Server that not having enough will really strain your hard drives. Basically, anything SQL Server can't commit to memory for processing will be forwarded to the disk subsystem—for recording upcoming queries that need to be executed, and so on. Processing instructions from memory is much quicker than getting these instructions from the hard drive (virtual memory) and executing them. The goal is adequate memory and cache to handle everything your system needs. These counters provide clues for making sure that memory on your SQL server is sufficient:
- SQL Server: Memory Manager: Memory Grants Pending. A
memory grant allocates SQL Server a workspace in memory to perform
queries. If this counter value is high, your SQL server is memory-constrained. A
high value indicates that too many memory grants are in a queue, waiting to be
carried out.
Often, large queries being executed by concurrent users will cause poor memory performance. Reducing the value of this counter increases memory performance. Look at improving your query performance, especially by converting hash-join queries to inner loop queries.
- SQL Server: Memory Manager: Total Server Memory. This
counter indicates how much dynamic memory SQL Server is consuming. The whole
point of dynamic memory allocation is to give and take as needed; SQL Server is
good at increasing dynamic memory usage, but not so good at decreasing it!
A good way to check whether this value becomes unusually high (and too quickly) is by stopping and restarting the SQL service. Record the initial value after restart, and then check it again after a couple of days. If the second value is significantly higher—say, more than double the initial value—SQL Server's dynamic memory-management process isn't giving memory back to the operating system as efficiently as it should. There may be a number of causes, such as not closing connections, or closing and destroying recordset objects in your web applications' ASP/ASP.NET code using SQL Server. Until the service is restarted, dynamic memory is still being allocated to these connections and objects that are no longer used. Each time an application with poor coding practices is compiled and executed against a database, dynamic memory allocation literally becomes exponential.
- SQL Server: Cache Manager: Cache Hit Ratio. If this value is consistently lower than 80%, your system isn't using enough memory to fulfill cache requests for good performance, but rather is using the disk subsystem. Try allocating more memory to SQL Server or increasing system memory.
- Memory: Page Reads/Sec. This counter can tell you whether the system has an adequate amount of memory for SQL Server. Paging occurs when Windows has to use the hard disk (virtual memory) because no system RAM is available. If Windows is reading more than five pages per second from the paging file, that's excessive paging; you don't have enough system RAM for good SQL Server performance. Removing any unnecessary applications from the server, disabling any unnecessary services, and adding RAM should bring paging to a reasonable level.