Sunday, 3 June 2018

TSQL : HEAP table usage script

This script reports usage of HEAP tables over a 15 minute period.
It reports reads, writes and row counts for the tables.
I wrote it as I found a system exhibiting high values for Performance Counter 'forwarded records/sec'.

See Also : TSQL : HEAPS and forwarded records

IF OBJECT_ID('tempdb..#Snap1') IS NOT NULL DROP TABLE #Snap1
IF OBJECT_ID('tempdb..#Snap2') IS NOT NULL DROP TABLE #Snap2
IF OBJECT_ID('tempdb..#rowcounts') IS NOT NULL DROP TABLE #rowcounts

SELECT   
      s.object_id
    , OBJECT_SCHEMA_NAME(s.object_id) AS schemaname
    , OBJECT_NAME(s.object_id) AS tablename
    , i.name AS indexname
       , SUM(user_seeks) user_seeks, SUM(user_scans) user_scans, SUM(user_lookups) user_lookups, reads = SUM(user_seeks + user_scans + user_lookups), writes =  SUM(user_updates)
INTO #snap1
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
  AND OBJECTPROPERTY(s.object_id,'TableHasClustIndex') = 0 -- Reporting only on HEAPS
GROUP BY s.object_id,i.name

WAITFOR DELAY '00:15:00'

SELECT   
      s.object_id
    , OBJECT_SCHEMA_NAME(s.object_id) AS schemaname
    , OBJECT_NAME(s.object_id) AS tablename
    , i.name AS indexname
       , SUM(user_seeks) user_seeks, SUM(user_scans) user_scans, SUM(user_lookups) user_lookups, reads = SUM(user_seeks + user_scans + user_lookups), writes =  SUM(user_updates)
INTO #snap2
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
  AND OBJECTPROPERTY(s.object_id,'TableHasClustIndex') = 0 -- Reporting only on HEAPS
GROUP BY s.object_id,i.name

SELECT   sys_schemas.name AS schemaName
  ,sys_objects.name AS tableName
  ,sys_indexes.name AS indexName
  ,SUM(partition_stats.used_page_count) * 8 AS indexsizekb
  ,MAX(partitions.rows) AS tablerows
INTO #rowcounts
FROM sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
  ON partition_stats.[object_id] = sys_indexes.[object_id] 
    AND partition_stats.index_id = sys_indexes.index_id
INNER JOIN sys.objects sys_objects
  ON sys_objects.[object_id] = partition_stats.[object_id] 
INNER JOIN sys.schemas sys_schemas  
  ON sys_objects.[schema_id] = sys_schemas.[schema_id] 
  AND sys_schemas.name <> 'SYS'
INNER JOIN sys.partitions partitions
  ON partitions.object_id = partition_stats.object_id 
WHERE OBJECTPROPERTY(sys_objects.object_id,'TableHasClustIndex') = 0
GROUP BY sys_schemas.name
  ,sys_objects.name
  ,sys_indexes.name
ORDER BY sys_schemas.name
  ,sys_objects.name
  ,sys_indexes.name

SELECT   i.schemaname
  ,i.tablename
  ,i.indexname
  ,(j.user_seeks - i.user_seeks)  AS seeks_period
  ,(i.user_scans - i.user_scans) AS scans_period
  ,(j.user_lookups - i.user_lookups) AS lookups_period
  ,(j.reads - i.reads) AS reads_period
  ,(j.writes - i.writes) AS writes_period
  ,r.tablerows
  ,r.indexsizekb
FROM #Snap1 i
INNER JOIN #Snap2 j ON i.tablename = j.tablename
INNER JOIN #rowcounts r ON r.tableName = i.tablename AND r.indexName = i.indexname
AND COALESCE(i.indexname,'!') = COALESCE(j.indexname,'!')

TSQL : HEAPS and Forwarded Records

A little bemused that I'm still finding this in application code in the 21st Century, but there you go. A live app experiencing performance problems due to a significant number of heaps, each frequently used.
SELECT OBJECT_NAME(stats.object_id) as [Table],
idx.name as [Index],
stats.index_type_desc,
stats.page_count,
stats.avg_fragmentation_in_percent,
stats.forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS stats
INNER JOIN sys.indexes AS idx (NOLOCK)
ON stats.OBJECT_ID = idx.OBJECT_ID
AND stats.index_id = idx.index_id
WHERE forwarded_record_count > 0
ORDER BY forwarded_record_count desc
See also : HEAP table usage script

Friday, 1 June 2018

TSQL : Clustered Indexes, Identities & Primary Keys

I'm Currently looking at a large database where there are
  • HEAPS (tables without a clustered index)
  • IDENTITY columns that could serve as Primary Keys (but aren't)
  • PRIMARY KEYS that have been defined as non-clustered (someone has scripted them out and ignored the default clustered status of a PK).
Anyway, this is coming in useful ...


SELECT   SCHEMA_NAME(schema_id) AS SchemaName
  ,name AS TableName
  ,OBJECTPROPERTY(object_id,'TableHasClustIndex') HasClusteredIndex
  ,OBJECTPROPERTY(object_id,'TableHasIdentity') HasIdentity
  ,OBJECTPROPERTY(object_id,'TableHasprimarykey') HasPrimaryKey
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY 1, 2

Friday, 18 May 2018

SQLTips : CONCAT

The CONCAT function is available from SQL 2016+. It can save you a lot of messing around with adding strings together and coping with NULLS and empty strings.
SELECT CONCAT('The cat sat',' ', 'on', ' ', 'the mat')
GO
 

SELECT CONCAT(NULL, ' and void')
GO

 
DECLARE @int int = 99
DECLARE @varchar varchar(13) = ' red balloons'
SELECT CONCAT(@int,@varchar)
GO

 
DECLARE @varchar varchar(20) = 'The date today is '
DECLARE @dt datetime = GETDATE()
SELECT CONCAT(@varchar,@dt)
GO

Monday, 9 April 2018

Tables with Computed Columns

Tables with Computed Columns

SELECT
  SCHEMA_NAME(schema_id) AS schemaname
, t.name as tablename
, cc.name
, cc.definition
FROM sys.tables t
INNER JOIN sys.Computed_columns cc
ON t.object_id = cc.object_id

and without
select
  SCHEMA_NAME(schema_id) AS schemaname
, t.name as tablename
from  sys.tables t
WHERE object_id not in (SELECT object_id FROM sys.Computed_columns)

Friday, 16 February 2018

HASHBYTES and FOR XML to create a binary checksum

SELECT TOP (1000) [Id]
      ,[AboutMe]
      ,[Age]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[EmailHash]
      ,[LastAccessDate]
      ,CHKSUM = CONVERT(VARBINARY(20),HASHBYTES('MD5', (SELECT s.* FROM (VALUES(NULL))Foo(Bar) FOR XML AUTO, BINARY BASE64)))
  FROM [StackOverFlow2010].[dbo].[Users] s

Thursday, 4 January 2018

Mail process taking forever Suspended Process - msdb.dbo.sp_readrequest;1



Finding a process taking forever  in the suspended state, I wondered what it could be.
Fortunately Irina Tudose had already done the hard work for me.  It is a process used by the mail procedures in msdb. She recommends changing the default value for DatabaseMailExeMinimumLifeTime to resolve this.

See also

SQL Server: Why a Session With sp_readrequest Takes so Long to Execute

UPDATE msdb.dbo.sysmail_configuration
SET paramvalue = 60 --60 Seconds
WHERE paramname = 'DatabaseMailExeMinimumLifeTime'