Home, Optimizer, Benchmarks, Server Systems, Processors, Storage, Scripts

Mysterious Performance Symptoms in SQL Server

SQL Server is said to be robust in that under certain circumstances, it can run essentially forever without problems. The underlying reason for this is that the database engine makes nearly exclusive use of standardized (8KB) pages managed by its own buffer pool. What means is that the virtual address space can be easily reused.

Compare this to a normal application that allocates memory (technically, it is probably allocating virtual address space) of arbitrary sizes directly from the operating system. Even if the application is very good at releasing objects, the virtual address space (VAS) will become fragmented. Cleaning up a fragmented VAS will most probably cause serious disruptions to the running process. More serious problems can occur if objects are not released after going out of scope. The easier resolution is to simply restart the application process, an example being the recycle setting in IIS.

Of course, the disclaimer is that the SQL Server does not use standard sized memory exclusively from its buffer manager. There are operations that allocate directly from the underlying operating system (Windows or Linux) in which case we should expect that the database would then have the same issues as normal applications.

When these occur, the symptoms might seem mysterious to many DBA's whose skill set is normally in query tuning, indexing, statistics, and perhaps execution plan analysis (because many of the indicators for these are just fine). When virtual address space fragmention or exhaustion occurs, the symptoms might include: 1) failures on the open connection call, 2) the sp_reset_connection associated with connection pooling being slow, and 3) a constricted plan cache size, possibly periodic flushing of plan cache. Either of the reduced flushed plan cache could result in higher CPU as a result of more frequent compiles. A related symptom might be (all) compiles being blocked.

In this case, it would be good to be fully aware of which specific elements make allocations outside of the SQL Server buffer system. Make sure that these elements are used correctly and only when necessary. Also ensure the proper stress and endurance testing has been conducted based on the requirements of the application regarding when or whether SQL Server restarts are possible.

A partial list is as follows: Extended stored procedures, including some system procedures, CLR functions and procedures, and Spatial Data. Perhaps an unexpected item is the connection network packet size set larger than 8060 bytes. Each thread also has a thread stack that is allocated from outside of the SQL Server buffer pool.

The now deprecated(?) API Server cursors are an example. This includes both the set of system procedures associated with sp_cursor, and sp_prepare/sp_execute, the corresponding sp_cursorclose and sp_unprepare. Anytime the procedure has an integer output parameter that is named or described as a handle is a good hint there is underlying C/C++ code that might make OS memory allocations.

Another example is sp_xml_preparedocument and its corresponding sp_xml_removedocument. There is a helpful DMV/F for open xml documents in sys.dm_exec_xml_handles. There is also the DMV/F sys.dm_exec_cursors which reports on cursors create with either OPEN CURSOR or sp_cursor. Unfortunately, there does not appear to be a DMV/F for sp_prepare created handles left open.

Finally, I have created a system procedure sp_vas (2018-07): sp_vas for tracking virtual_address_space_reserved.