Friday, 8 December 2017

Error: 8623, Severity: 16, State: 1. The query processor ran out of internal resources

Caught out when generating some dynamic sql today. The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. Dynamically generating a the IN clause and I accidentally passed a list of over 22,000 values. It turns out IN can't take more than 10,000 values. 8623 The query processor ran out of internal resources and could not produce a query plan. I've obviously rewritten it, but thought the error message interesting.

Tuesday, 28 November 2017

Database growth from available backup history

SELECT  
 BackupRange.Database_name 
 ,First_Backup 
 ,Last_Backup 
 ,DATEDIFF(dd,First_Backup,Last_Backup) AS days_history 
 ,firstbackup.backup_size/1024/1024 AS FirstBackupSizeMB 
 ,lastbackup.backup_size/1024/1024 AS LastBackupSizeMB 
 ,(lastbackup.backup_size/1024/1024) - (firstbackup.backup_size/1024/1024) as GrowthMB 
 ,CASE WHEN DATEDIFF(dd,First_Backup,Last_Backup) > 0
  THEN ((lastbackup.backup_size/1024/1024) - (firstbackup.backup_size/1024/1024)) / DATEDIFF(dd,First_Backup,Last_Backup)
  ELSE 0
  END AS GrowthRate_MBday 
FROM  
(SELECT  
 [database_name] 
 ,MIN(backup_start_date) AS 'First_Backup' 
 ,MAX(backup_start_date) AS 'Last_Backup' 
FROM msdb.dbo.backupset  
WHERE [type] = 'D'
--AND [database_name] = N'mydatabase'  
GROUP BY [database_name]) BackupRange  
LEFT JOIN msdb.dbo.backupset firstbackup  
  ON  firstbackup.database_name = BackupRange.database_name
  AND firstbackup.backup_start_date = BackupRange.First_Backup
LEFT JOIN msdb.dbo.backupset lastbackup  
  ON  lastbackup.database_name = BackupRange.database_name
  AND lastbackup.backup_start_date = BackupRange.last_Backup

Wednesday, 10 May 2017

Searching SQL Code via sys.sql_modules

I've been used to searching stored procedures via tsql like this.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%mytablename%'

The problem this encounteres however is that ROUTINE_DEFINTION is NVARCHAR(4000) and hence only returns the first 4000 characters of a stored procedure. The alternative below searches the entire codebase.

SELECT DISTINCT
   SCHEMA_NAME(schema_id) as schema_name
  ,o.name AS Object_Name
  ,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%mytablename%'
ORDER BY 1,2

Thursday, 4 May 2017

Converting a datestring to datetime (yyyymmddhhmmss)

This uses STUFF to format a datestring correctly.

declare @datestring varchar(20) = '20170504103253'
-- Add colons and space to format datetime
SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')
select @datestring
-- Convert ....
select convert(datetime,@datestring)

2017-05-04 10:32:53

2017-05-04 10:32:53.000

Saturday, 11 March 2017

COUNT without GROUP BY

I found a novel way of doing a record count today, without using a GROUP BY.
Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.

The syntax is COUNT(*) OVER () AS [RecordCount]

I use it below to return the count of addresses in AdventureWorks.

SELECT DISTINCT 
      a.[City]
   ,sp.Name As StateProvince
   ,cr.Name AS CountryRegion
   ,COUNT(*) OVER (PARTITION BY sp.[StateProvinceID]) AS AddressesInThisProvince
   ,COUNT(*) OVER (PARTITION BY cr.CountryRegionCode) AS AddressesInThisCountry
   ,COUNT(*) OVER () AS TotalAddresses 
  FROM [AdventureWorks2014].[Person].[Address] a
  INNER JOIN [AdventureWorks2014].[Person].[StateProvince] sp
  ON a.[StateProvinceID] = sp.[StateProvinceID]
  INNER JOIN [AdventureWorks2014].[Person].[CountryRegion] cr
  ON sp.CountryRegionCode = cr.CountryRegionCode
  WHERE cr.Name = 'Germany'
  ORDER BY 1,2,3

Tuesday, 28 June 2016

CTE to create a sequence of numbers

DECLARE @start INT, @end INT, @increment INT
SELECT @start=0, @end=100000000, @increment = 50000
 
;WITH NumberList ( Number ) AS
(
    SELECT @start as Number
        UNION ALL
    SELECT Number + @increment
        FROM NumberList
        WHERE Number < @end
)
 
SELECT Number FROM NumberList OPTION (MAXRECURSION 0)

Tuesday, 12 January 2016

SQL Page Compression - Rollout script.

An update to my original script to apply PAGE Compression to all objects
Obviously, you need to weigh up the benefits of doing so first.

This version includes the rowcounts but orders the statements so you compress the table before it's associated indexes.
SELECT distinct rows,SCHEMA_NAME(schema_id) as SchemaName, Name AS TableName,'Table' AS ObjectType,1 AS ExecutionOrder,'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (MAXDOP=1,ONLINE=ON,DATA_COMPRESSION = PAGE);' AS [SQLCommand]
FROM sys.objects o
inner join sys.partitions p
on o.object_id = p.object_id
where o.TYPE = 'u'
and p.data_compression = 0
UNION ALL
SELECT distinct rows,SCHEMA_NAME(schema_id) as SchemaName, Name AS TableName,'Index' AS ObjectType,2 AS ExecutionOrder,'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (MAXDOP=1,ONLINE=ON,DATA_COMPRESSION = PAGE);' AS [SQLCommand] 
FROM sys.objects o
inner join sys.partitions p
on o.object_id = p.object_id
where o.TYPE = 'u'
and p.data_compression = 0
order by SCHEMA_NAME(schema_id),Name,ExecutionOrder