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