Versions Compared

Old Version 5

changes.mady.by.user Support

Saved on

compared with

New Version 6

changes.mady.by.user Administrator

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Buffer Hit Ratio: The buffer cache hit ratio counter shows the ratio of data pages found and read from the SQL Server cache. If the page doesn’t exist in the buffer cache, it has to be read from disk which downgrades performance.  It is recommended the Buffer cache hit ratio is 90% or higher. A low buffer cache hit ratio can be indicative of a memory problem.
  • Processes blocked: The processes blocked counter identifies the number of blocked processes. When one process is blocking another process, the blocked process cannot move forward with its execution plan until the resource that is causing it to wait is freed up.  Ideally there should not be any blocked processes, and any blocked processes should be investigated.
  • Page life expectancy: The page life expectancy counter measures how long pages stay in the buffer cache in seconds. The longer a page stays in memory, the more likely SQL Server will not need to read from disk to resolve a query. Generally, a page life expectancy below 5 minutes (300 s), can be indicative of memory problems.
  • Forwarded Records/sec: The number of rows per second fetched from a heap through forwarded record pointers. When this number is high, it means there were many updates to rows that no longer fit on the original page, and requires rebuilding the heap, or adding a clustered index to correct.   Ideally this number should be very close to 0.
  • Batch Requests/sec: Batch Requests/Sec measures the number of batches SQL Server is receiving per second and is an indicator of the amount of activity on the server. Sudden changes can reveal availability issues or unexpected changes in demand.
  • SQL Compilations/sec: The SQL Compilations/Sec measures the number of times SQL Server compiles an execution plan per second. Compiling an execution plan is a resource-intensive operation. Compilations/Sec should be compared with the number of Batch Requests/Sec to get an indication of whether or not complications might be hurting your performance. Ideally 90% of queries should be reused, so compilations/sec should be <10% of the batch requests/sec.


Tags

Read more on the Manage Tags page. 


Custom Scripts

 It is possible to execute custom PowerShell Commandlets and show the output on the Windows Server Dashlet.  The Custom Scripts page outlines this in detail.