Databases Reference
In-Depth Information
Along with Memory:Pages/sec, also check the Paging File:% Usage performance counter to
estimate paging usage. If you frequently find this value to be more than 20 percent, you are
probably falling short of memory.
SQL Server:Buffer Manager:Buffer cache hit ratio indicates the number of times SQL Server
reads data from the cache. It is desired that this be more than 90 percent. If this number
is frequently low, either you have a memory shortage or you need to check your query and
indexes. If you fetch a large amount of data, it alone may occupy much of the memory and can
cause SQL Server to read data from disk instead of from the memory. Check indexes. Make
sure that large tables are not getting scanned. Try to limit the number of rows in your queries.
Check the value of the SQL Server:Buffer Manager:Page life expectancy performance
counter. This value represents the life of data pages in seconds. Microsoft recommends
this value to be at least 300 seconds. For instance, if this value is less than 300 very often,
it means life of the data pages is less than 5 minutes, that they don't stay longer than this
duration in memory, and that they are removed from memory once the duration has elapsed.
If the SQL Server:Memory Manager:Memory Grants Pending performance counter frequently
suggests waiting processes, you should probably increase your memory.
For any reason if you find that there is a shortage of memory and paging is occurring quite
frequently, you should first check if there are any other services or applications other than SQL
Server that are heavily taking up the memory. If you find any such applications or services, try
to move them to a different server. If you can't do this, then add more memory to the server
and allocate the required amount of memory to SQL Server.
If the server is dedicated to SQL Server only and there are no other services or applications,
then you should analyze your queries and indexes to make sure that they are optimized. If
you find that the queries and indexes are optimized but all the memory is still used up by SQL
Server, you may probably need to add more memory to your server.
Correlating performance data with SQL Trace
You can also correlate the results of Performance Monitor with SQL Server
Profiler. For this, you need to create a user-defined data collector set
and save it to a file. When you gather performance data in Performance
Monitor, SQL Server Profiler also needs to be running simultaneously.
Once you are done with collecting data, you can import performance data
into SQL Server Profiler by selecting Import Performance Data from the
File menu. Importing performance data into SQL Server Profiler will allow
you to correlate SQL Trace events with performance data for a given point
of time on the system monitor graph.
Search WWH ::
Custom Search