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

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

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

sp_ms_marksystemobject

On searching for a table I found it to be hidden. Turns out it was marked as 'ms shipped' i.e was a system object.

select * from sys.tables WHERE name = 'sysssislog'

To achieve this yourself you can use sp_ms_marksystemobject

EXEC sp_ms_marksystemobject 'dbo.sysssislog'

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)

Suspect Database - Steps to Recovery

For reference, the steps I took to (successfully) recover a 2008 database that was marked as Suspect in Management Studio.

Run each separately, and review the results...


EXEC sp_resetstatus 'mySuspectDB'

ALTER DATABASE mySuspectDB SET EMERGENCY

DBCC CHECKDB('mySuspectDB')

ALTER DATABASE mySuspectDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('mySuspectDB', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE mySuspectDB SET MULTI_USER

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