Resource Leak

If a resource leak continues for a long period of time, the operating system should return the appropriate return code to the application, which should log this. For example, if you receive operating system error 8, “Not enough storage,” the operating system should handle the situation by not granting further resource requests. However, a continued application resource leak may not be handled by the operating system under all conditions, resulting in a blue screen or operating system or application failure.

Almost all resource leaks will manifest themselves as a gradual increase in consumption of some resource, such as handles, virtual memory, private bytes, and so on. Therefore, the easiest way to rule in or out a resource leak is to run Windows NT Performance Monitor and log all objects to a file. When the problem occurs, examine the logged performance data for signs of a leak. Some good counters to examine are: handle count, page file bytes, pool paged bytes, pool nonpaged bytes, private bytes, thread count, virtual bytes, and working set for each process running on the computer.

It is not necessary to classify certain values as normal or abnormal. Focus on identifying leaks by the continuous nature of the increase, not by the absolute value at a given time. Remember it is normal for the Windows NT Performance Monitor private bytes counter for Microsoft® SQL Server™ to start well below the configured min server memory setting value, and then increase with activity until it roughly approaches, but does not significantly exceed, that value.

If one of the logged Windows NT Performance Monitor counters continuously increases for the Sqlservr.exe process, and if reaching a certain value repeatedly coincides with a Microsoft Windows NT® blue screen or operating system failure, it should be pursued temporarily as a Microsoft Windows® issue until the cause of the continuous SQL Server resource leak is understood. Otherwise, it should be pursued as a system layer problem.

See Also