Whether you coding in C# or TSQL, you can often have a variety of challenges in your code that never expose themselves on a development box. Good practice is create unit tests that runs for ever exercising all code paths and seeing what happens.
When you do this, you want to open PerfMon and watch the following items carefully (there are other items that are performance related – these are items indicating common code problems).
|Memory||Free System Page Table Entries||> 5000: Memory Leak|
|Pool Non-Paged Bytes||> 175MB: Memory Leak|
|Pool Paged Bytes||> 250MB: Memory Leak|
|Pages Per Second||> 1000: Memory Leak|
|Processor||% User Time||>80%: Code refactor may be needed|
|Process||Handle Count||> 10000: Handle Leak|
|SQL Server:SQL Statistics||Failed Auto-Parms/Second||High number: refactor TSQL|
|SQL Server: Locks||Average Wait (MSec) |
|High value: Transaction code problems and/or index structure|
Also look in the System Event Log for:
- Event ID 2020: Memory Leak
One approach is to set the polling frequency to 60 seconds or more and then see if any counter grows over time.