Tuesday, June 28, 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, January 12, 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

Thursday, May 28, 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)