HomeQuery OptimizerBenchmarksServer Systems ProcessorsStorage,  Scripts,  ExecStats
Contact: jchang6 at yahoo.com or jchang61 at outlook.com

System Architecture DMVs and NUMA (2018-09) 

SQL Scripts

Spaceused by Tables and Indexed Views

Below are some helpful scripts I have built up over the years. The first is an extension of sp_spaceused to provide table level summary.
sp_spaceused2 with table level details (2018-02)

sp_spaceused2 , use STRING_SPLIT to support list of tables (2018-10)

Indexes

An alternate to the index level space usage query is a system stored procedure, extending sp_helpindex.
sp_helpindex2 (2018-04-08)
is my version of extended index information. It would be desired if the table and index sp_spaceused extensions could be made into system views, but apparently SQL Server does not allow modifications to system resource database anymore?

this version allows an input list of one more tables, comma delimited
sp_helpindex2 (2018-10-25)

Partition level view

sp_partitions (2018-02-23)

Statistics Update

sp_updatestats2 (2018-04-10)

One of the problems in SQL Server has to do with statistics sampling being based on all rows in a random sample of pages. There is a mechanism to try to correct for the errors that could happen but it does work in some cases. The most serious problems affects indexes in which the lead column is not unique.

An alternative to the SQL Server sp_updatestats.
The internal statistics update is based on all rows from a random sample of pages.
There can be adverse effects for indexes in which the lead key is not unique
and may be especially severe if compounded.
See Statistics that need special attention.

sp_updatestats2 does fullscan on indexes excluding identity or single key column unique.

Test version for incremental statistics: sp_updatestats2 (test version)

Virtual Address Space Reserved

sp_vas: sp_vas for tracking virtual_address_space_reserved and related counters.

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.

References

Memory Management Architecture Guide
Memory configuration ...
SqlConnection.PacketSize
Memory Allocated To Plan Caching

Articles from MSSQLWIKI, Karthick P.K on SQL Server,
also see the general categories SQL Server Memory and SQL Server Engine
Basics of SQL Server Memory Architecture
SQL Server 2012 Memory

Flushing Single (and 2) Use Plans from the Plan Cache

sp_plancache_flush: sp_plancache_flush for flushing set number of Adhoc or Prepared plans used once or twice.

Older versions

Table Spaceused Summary (2008?) reports size, indexes, and other info.
Spaceused Summary (2012-04) and Index Summary(old) with key and include columns, size, and dmv index usage stats.

Trace is my standard Profiler script. Be careful in collecting execution plan information, as this could have high overhead.