Sql-server – Required values for the performance counters

SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms

750ms wait time per flush is huge. It means every transaction statement must wait in average 750ms to commit. If you don't use explicit transactions for writes it means each write statement (INSERT/UPDATE/DELETE) must wait 750ms to complete. However this does not corroborate with the other sttaement 'PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms.'. One explanation is that you have log growths (you do, the counters say so) which skew the log flush wait times but that would only explain averages over long periods.

SQLServer:Databases\Log Growths, I'm at 325

Figure out which database is growing and resize the log appropriately. You should not occur growth events in production, they are extremely expensive. the counter has instances per database.

PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms. Is it normal for the Log Files to have consistent writes with no break?

Yes. A transaction cannot commit unless the log was written to disk. As long as you have transactions, you will have log disk writes.

PhysicalDisk\Avg. Disk sec/Write counters: For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms.

It would be good to know why. Can you correlate it with a specific activity that causes the spike? Can you correlated it with database checkpoints occurring?

Memory\Pages Input/sec counter, the values fluctuate dramatically and have an average of 6 but have many spikes well upwards of 100 (some as high as 400).

So you're paging. Why? Insufficient memory? Any other offending process on the server host? Virtualized overcommitted environment? Only you can tell. Does swapping IO overlap with any of the IOs above (same physical path, ie. same disks)? Then some of the SQL timers may be influenced by this paging spikes.

As you already know there is No general formula to calculate max server memory you can do some quick maths and reach to a value but still you would need help of Perfmon counters at last to monitor memory usage and change accordingly. I know below general formula and I use it as well. I learned this formula from This Link

For SQL Server 2005 to 2008 R2

Please note from SQL Server 2005 to 2008 R2 max server memory only controls buffer pool. So max server memory configuration is bit tedious here and involves few calculations

1. Leave 2 G memory straight away for Windows OS.

2. Of course system would have antivirus running. Please leave 1.5G for Antivirus. Please note Mcafee and SQL Server do not go hand in hand so make sure you leave enough for it. You can also check perfmon counter Perfmon Process-> Private bytes and Working Set to monitor memory usage by AV and other small applications running on SQL Server box

3. Consider the memory requirements of the drivers/firmwares.You have to derive it based on memory requirements by drivers installed on the system. RAMMAP tool can help

4. Consider the NonbPool (aka MTL or MTR) memory requirements by SQL Server.

   select  sum(multi_pages_kb)/1024 as multi_pages_mb from  sys.dm_os_memory_clerks
   

   + Max worker threads * 2MB

   + Memory for direct Windows allocations approximately 0 to 300 MB in most of the cases but you may have to increase it if there are many 3 party components loaded in SQL Server process (Including linked server dll’s, 3rd party backup dll’s etc.)

   + If you are using CLR extensively add some additional memory for CLR.

5. Consider the memory requirement by jobs (Including replication agents, Log shipping etc. ) and packages that will run on the server. It can very from MB's to GB's according to number of jobs running. For medium sized server you can take it as 250 MB

6. Make sure there is good enough free space for operating system.

   Approximately (100 MB for each GB till 4G) + (50 MB for each additional GB till 12GB) + (25 MB for each additional GB till your RAM size)

7. Other memory requirements.

   If you have any other memory requirement specific to your environment.

   Max server memory= Total physical memory – (1+2+3+4+5+6+7)

   I have not included memory configuration for SSIS.SSRS,SSAS you would also need to subtract memory required by these services from total physical server memory.

   After you have configured above you need to monitor following counters

• SQLServer:Buffer Manager--Page Life Expectancy(PLE):

• SQLServer:Buffer Manager--CheckpointPages/sec:

• SQLServer:Memory Manager--Memory Grants Pending:

• SQLServer:memory Manager--Target Server Memory:

• SQLServer:memory Manager--Total Server memory

For SQL Server 2012/2014.

From SQL Server 2012 onwards setting up max server memory has become easy. Because now max server memory almost accounts for all memory consumption. Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

You can allocate 75-80% to SQL Server and then use perfmon counters to monitor memory usage. In SQL Server 2012 few perfmon counters have been deprecated. Buffer manager counter is deprecated you must use memory manager counter

• SQL Server: Memory Manager-- Target Server Memory (KB)

• SQL Server: Memory Manager--Total Server Memory (KB)

• SQL Server: Memory Manager- Free Memory (KB)

• SQL Server: Memory Manager--Database Cache Memory (KB)

On value of PLE, I have used formula by Joanthan and fortunately it has worked for me.