SQL Exec Stats, Copyright © 2006-2010 Elemental Inc. All rights reserved.

Previous   Next

Main Screen

Below is the main screen. Target SQL Servers and Databases need to be set. Default security is SSPI, so the client must have proper permissions. Uncheck the SSPI box to set SQL Server user and password if desired. After the Target SQL Server is set, the button to the right of Databases can bring up a list of databases.

There are options for collecting the full XML Execution Plans (Exec Plans), Statistics Header (Stat Hdr) which contains the date each data distribution statistics was generated. Histogram information is currently not enabled.

The execution statistics can be statement level, batch level aggregation. A query hash aggregation will be considered for later addition. Another possibility is simply generating the execution plans for all stored procedures.

The default mode collects table and index information on one database, using the database in the config file or manually entered in the Databases drop box to the right of the Server box.

New in Build 2010-02-0x

Server and database selection

The currently accessible modes are Statement (dm_exec_query_stats, no grouping), Plan Cache (dm_exec_cached_plans) and Proc Plans (generate estimated plans for all stored procedures). The other modes are temporarily disabled while new work in is in progress and pending functionality review.

Statistics options are: 1) no statistics, 2) indexes only and 3) index and column statistics.

Sort options are CPU, duration and execution count. Sorting by logical reads is not enabled as I do not want to encourage this use.

The "Top" setting limits the number of query plans. I may change this later to limit based on the size of the query plans. Parsing 150MB of query plans from a 32-bit OS seems to be possible. A 64-bit platform should be able to support very large cumulative plan size.

On startup, the Debug Messages group Textbox at bottom will now display the host operating system and .NET Framework version, example

Microsoft Windows NT 5.1.2600 Service Pack 3, Win32NT, 5.1.2600.196608, Service Pack 3
Environment: 2.0.50727.3603, Major: 2, Minor: 0, Build: 50727, Size of IntPtr is 4

The Size of IntPtr should indicate whether the current process is running as full 64-bit or 32-bit, even if the underlying OS is 64-bit. The next line are the WMI Logical Memory Configuration, which works on 32-bit OS, but apparently not on 64-bit.

Step 1
Click the button ... to the right of the Databases box or the Multi-DB button

Single-Db


Multi-Db

Select the database(s) for which detailed table and index information should be collected.

Step 2
Click the Connect button at the lower left to start data collection. The SQL Exec Stats utility will collect all information and save the results in Excel and binary. Later versions may allow manual selection of which components to execute.

 

General Information

General system information is displayed in the grid to right of the Connection tab. The information includes @@VERSION, xp_msver, and dm_os_sys_info.

sys.configurations,

sys.dm_os_sys_info,

The view sys.dm_os_sys_mem, and sys.dm_os_process_memory are only available for SQL Server 2008.

The top 50 sys.dm_wait_stats are displayed:

The tab OS Perf Ctrs collects the point in time OS Performance Counters. Hopefully I can add regular sampling to display key performance counters over a period.

 

Databases

The Databases tab show database summary information. The columns start with the database name, the database id, the number of file groups, and the number of files. The next series of columns are calculated from other system views. The column "Size MB" is the total size of the data files. Reserved, Data and Index are the respective sizes computed for tables and indexes. Log is the size of the log files, and LUsed is the log used space. The columns Tables, Indexes, XmlIX and Stats are the number of tables, indexes, Xml Indexes, and statistics in each database.

The remaining Database columns are below.


Since the cost of calculating database space can be non-trivial when there are very many tables, indexes or allocations, the database space is calculated only for selected databases. During off-hours, it can be helpful to see the details for the entire set of databases.

Below is the initial query used to populate the Database grid from the sys.databases view. The size, space and objects related columns are populated later using values from other system views. sys.master_files

--Databases SQL:

SELECT name [Database], database_id [dbid], 0 Groups, 0 Files,
 0.0 Size, null Reserved, null [Data], null [Index], null Unallocated,
 0.0 [Log], 0.0 Lused, null [Tables], null [Indexes], null XmlIx, null [Stats],
 create_date, compatibility_level, user_access, [state],
 CONVERT(tinyint, is_in_standby) is_in_standby,
 recovery_model,
 CONVERT(tinyint, is_auto_create_stats_on) is_auto_create_stats_on,
 CONVERT(tinyint, is_auto_update_stats_on) is_auto_update_stats_on,
 CONVERT(tinyint, is_parameterization_forced) is_parameterization_forced
FROM sys.databases WITH (NOLOCK)
ORDER BY name

 

File IO

The File IO tab displays file IO information from dm_io_virtual_file_stats. This represent the counter values since the instance startup. Later versions may show differential file IO values over set time period. Note that the first line of totals is for data files and the second line is for log files.

The last columns show the calculated Average Bytes/Read, Average ms/Read, Average Bytes per Write, the Average ms/Write, and the physical file name. Care should be taken in interpreting the file IO values based on this view. In particular, the reads probably includes database backups, which may generate 1MB reads at high latency, distorting the average values for normal operations.




The disk latencies for data reads and log writes should be color coded for high values. For some unknown reason, the first run does not show the color coding. But a subsequent runs do show color coding.

The SQL for this is in the text file output. Example below:

--File IO Stats

SELECT DB_NAME(v.database_id) [Database], m.name [File] ,
  v.database_id [dbid], v.file_id [fileid], m.type, m.data_space_id dsid, m.state,
  CONVERT(real, (m.size*8./1024.)) [Size], 0 TotExtents, 0 UsedExtents,
  num_of_reads Reads, num_of_bytes_read ReadBytes, io_stall_read_ms RdIOStall,
  num_of_writes Writes, num_of_bytes_written WrBytes, io_stall_write_ms WrIOStall,
  ISNULL(p.io_pending_ms_ticks,0) IOPen_ms,
  ISNULL(p.io_pending,0) IOPending,
  ISNULL(IOPenCnt,0) IOPenCnt,
  m.physical_name AS PhyName
FROM sys.dm_io_virtual_file_stats(NULL, NULL) v
INNER JOIN sys.master_files m WITH (NOLOCK) ON m.database_id = v.database_id AND m.file_id = v.file_id
LEFT JOIN (
 SELECT io_handle, COUNT(*) IOPenCnt,
  SUM(io_pending_ms_ticks) io_pending_ms_ticks, SUM(io_pending) io_pending
 FROM sys.dm_io_pending_io_requests WITH (NOLOCK)
 GROUP BY io_handle
) p ON p.io_handle = v.file_handle
ORDER BY v.database_id, v.file_id

 

Table Space

The tab Table Space lists space usage by table (and views) for each selected database. The first colums is dbid to distinguish database. Object id is included in case it is needed. The type distinguishes between user tables, and views. The average byte per row is computed based on the number of data leaf pages only. Non-clustered indexes and upper level clustered index pages are not included.

 

The query for table space is as follows:

--Table Space used:

DECLARE @dbid int SELECT @dbid = DB_ID()

SELECT @dbid dbid, t.name [Schema], o.name [Object], x.object_id, o.type [Type] ,
  CASE [Rows] WHEN 0 THEN 0 ELSE 1024*Data/Rows END AvRsz ,
  [Rows], Reserved, Data, [Index] = index2, Unused = Reserved - Used ,
  Clust, IxCnt, XmlCnt, StatCnt, Hypothetical, Partitioned, Compress, o.create_date,
  lob_used lob, row_overflow_used ovr
FROM (
 SELECT ISNULL (i.object_id,0) object_id, Reserved = SUM(p.reserved), Used = SUM(p.used),
  Data = SUM(CASE WHEN (p.index_id < 2) THEN (p.in_row_data + p.lob_used + p.row_overflow_used)
  ELSE p.lob_used + p.row_overflow_used END ),
  index2 = SUM(CASE WHEN i.type =2 THEN (p.in_row_data) ELSE 0 END) ,
  Rows = SUM(CASE WHEN i.type<2 THEN p.row_count ELSE 0 END ) ,
  Clust = SUM(CASE WHEN i.type=1 THEN 1 ELSE 0 END) ,
  IxCnt = SUM(CASE WHEN i.type =2 THEN 1 ELSE 0 END) ,
  XmlCnt = SUM(CASE WHEN i.type = 3 THEN 1 ELSE 0 END) ,
  StatCnt = (SELECT COUNT(*) FROM sys.stats s WHERE s.object_id = i.object_id) ,
  Hypothetical = SUM(CASE i.is_hypothetical WHEN 1 THEN 1 ELSE 0 END) ,
  Partitioned = SUM(CASE WHEN ISNULL(Partitions,1) = 1 THEN 0 ELSE 1 END) ,
  in_row_data = SUM(in_row_data), lob_used = SUM(lob_used), row_overflow_used = SUM(row_overflow_used) ,
  Compress = SUM(CASE compress WHEN 0 THEN 0 ELSE 1 END)
 FROM sys.indexes i WITH (NOLOCK)
 -- JOIN sys.objects o WITH (NOLOCK) ON o.object_id = i.object_id AND o.type NOT IN ('S', 'IT', 'TF')
 LEFT JOIN (
  SELECT d.object_id, d.index_id, COUNT(*) AS Partitions ,
   reserved = 8*SUM(reserved_page_count), used = 8*SUM(used_page_count),
   in_row_data = 8*SUM(in_row_data_page_count),
   lob_used = 8*SUM(lob_used_page_count) ,
   row_overflow_used = 8*SUM(row_overflow_used_page_count) ,
   row_count = SUM(row_count) , compress = SUM(q.data_compression)
  FROM sys.dm_db_partition_stats d WITH (NOLOCK)
  INNER JOIN sys .partitions q WITH (NOLOCK) ON q.partition_id = d.partition_id
  GROUP BY d.object_id , d.index_id
 ) p ON i.object_id = p.object_id AND i.index_id = p.index_id
 GROUP BY i.object_id
 -- WITH ROLLUP HAVING i.object_id IS NOT NULL OR i.object_id IS NULL
) x
LEFT JOIN sys.objects o WITH (NOLOCK) ON o.object_id = x.object_id
LEFT JOIN sys.schemas t WITH (NOLOCK) ON t.schema_id = o.schema_id
WHERE o.type NOT IN ('S', 'IT', 'TF')
ORDER BY Data DESC

 

Index Space and Usage

The Index Usage view includes index space, and index usage system view. The first set of columns include dbid, table (or view) index name, object_id, index_id, object type. The next columns are Reserved, Used and Leaf allocation in KB, followed by row count. The next colums is a code indicating the type of type index. Pk stands for Primary Key, Cl for clustered, U for unique and N for nonclustered.

The next colum is for the index key columns. The number in parenthesis is the column type. There is a file type.txt in the txt directory listing the system types. Type 56 is a four byte integer, 127 is bigint, 40 is the new date only type, 61 is the original datetime, and 167 is varchar.
The next four columns is user seeks, scans, lookups and updates from the index usage view. The four following columns indicate the number of execution plans that reference the index. The next four columns are the last user seek, scan, lookup and update from the index usage view.


The next eight columns are system seeks, scans, lookups, and updates, followed then by last system seek, scan, lookup and update.


The next set of columns are boolean columns from sys.indexes. The Plan Reference columns indicate the specific plans that reference each index in a specific manner.

The columns to the right indicated the number of execution plan references that include a scan, insert (or update) and delete, along with the specific plans that reference the operation.

The queries for index space and usage is as follows:

--Index Space SQL:

DECLARE @dbid int SELECT @dbid = DB_ID()
SELECT @dbid dbid, u.name [Schema], o.name [Object], ISNULL(i.name, '') [Index],
  i.object_id, i.index_id, o.type otype,
  p.reserved, p.used, p.leaf, p.row_count,
  i.type itype, data_space_id dsid, p.Partitions,
  CONVERT(tinyint, is_unique) is_unique,
  CONVERT(tinyint, ignore_dup_key) ignore_dup_key,
  CONVERT(tinyint, is_primary_key) is_primary_key,
  CONVERT(tinyint, is_unique_constraint) is_unique_constraint,
  CONVERT(tinyint, is_disabled) is_disabled,
  CONVERT(tinyint, is_hypothetical) is_hypothetical,
  CONVERT(tinyint, allow_row_locks) allow_row_locks,
  CONVERT(tinyint, allow_page_locks) allow_page_locks
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON o.object_id = i.object_id
INNER JOIN sys.schemas u WITH (NOLOCK) ON u.schema_id = o.schema_id
LEFT JOIN (
 SELECT object_id, index_id, COUNT(*) AS Partitions,
  reserved = 8* SUM(reserved_page_count),
  used = 8* SUM(used_page_count) ,
  leaf = 8* SUM(in_row_data_page_count+ lob_used_page_count+row_overflow_used_page_count),
  row_count = SUM(row_count)
 FROM sys.dm_db_partition_stats WITH (NOLOCK)
 GROUP BY object_id, index_id
) p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE o.type NOT IN ('S', 'IT','TF')
ORDER BY u.name, o.name, i.index_id

 

--Index Usage SQL:
DECLARE @dbid int SELECT @dbid = DB_ID()
SELECT s.database_id, s.object_id, s.index_id
, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update
, s.system_seeks s_seeks, s.system_scans s_scans
, s.system_lookups s_lookups, s.system_updates s_updates
, s.last_system_seek ls_seek, s.last_system_scan ls_scan
, s.last_system_lookup ls_lookup, s.last_system_update ls_update
FROM sys.dm_db_index_usage_stats s