Wednesday, 10 May 2017

Searching SQL Code via sys.sql_modules

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


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.

   SCHEMA_NAME(schema_id) as schema_name
  , AS Object_Name
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%mytablename%'

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


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.

   ,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

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
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

Thursday, 28 May 2015

Investigating a (suddenly) slow running query...

Some Questions / lines of enquiry ...

What else was going on on the server at that time?

  • Is blocking occuring?
  • Is another process monopolising resources (memory, cpu, i/o)

What has changed?

  •  Any code changes
  •  Any configuration change
  •  Any change in data volume (influx of new data)

Is all maintenance happening? (it won't take long to check)

  • Are statistics up to date
  • How is index fragmentation
  • Are backups occurring (remember full backups control log fie growth)
Can the query be run manually?
- Look at Execution plan

Is it a stored procedure?
- Look at Execution plan for parameter differences (parameter sniffing)

Parameter Sniffing

A short reminder, again to get this fresh in my mind....

Parameter Sniffing is when a previously compiled execution plan is reused with different parameter values. This is by design and is generally good as we are saving time by not compiling a new plan. Occasionally though it can lead to suboptimal choices when the procedure is run again, with new parameter values.

If a procedure returns only 1 row with 1 parameter value and 1 million with a different parameter value then the optimal plans for retrieving those rows may be very different.
Here the retrieval of a million rows wold be done using the plan generated for 1 row.

2 ways around parameter sniffing are forcing a recompile (WITH RECOMPILE) in the procedure definition or using the OPTIMIZE FOR query hint.

Simple Talk : Parameter Sniffing
Brent Ozar : Parameter Sniffing
MSDN : Parameter Sniffing & Workarounds