Are Query Plans All We Need?

In How Can Query Plans Help Us?, we looked at the nature of T-SQL and then at some of the benefits that understanding query plans can bring us. But are query plans a silver bullet? Can we overcome all optimization challenges with query plans only or are there other tools we need on our utility belt?

Continue reading Are Query Plans All We Need?

How Can Query Plans Help Us?

In T-SQL Query Plan Analysis Prelude we looked at some common reasons why queries can sometimes go on a snail speed contest and that query plans are one of the ways we can know how to transform those carefree molluscs into caffeinated cheetahs.

So how exactly can query plans help us do that? Are they like magic wands that turn over-ripe pumpkins into dubious-expiry carriages and make used teeth disappear in return for equally used pennies?

Well… Hold that thought for a minute.

Continue reading How Can Query Plans Help Us?

T-SQL Query Plan Analysis Prelude

Have you ever found yourself pulling your own hair with both hands, desperate to understand why that stubborn T-SQL query runs so desperately slow? Were you intrigued by the cool if complex visuals of its query plan? Did you try to turn its knowledge into insight but couldn’t figure out a Hash Match from a Nested Loop?

Query Plan Example

If so, this series might be for you.

Continue reading T-SQL Query Plan Analysis Prelude

PowerShell: Warming Up

So you’ve decided to learn PowerShell? Then we’re on the same boat, you and I!

While I have been playing around with PowerShell for a bit now and for a variety of different purposes, I have yet to sit down and learn the details from the very beginning. PowerShell may have a relatively easy learning curve but relying on your existing knowledge of the .NET Framework will only take you so far.

In this series, I’m going to start the process of learning PowerShell from the ground up, exploring the details that make this great tool such a pleasure to play with.

Continue reading PowerShell: Warming Up

MDX: Revving Up Engines

So you want to learn MDX? We have something in common then!

Whilst preparing for an exam requiring decent MDX skills, I started wondering if I really had them. I mean, I know more or less how to write MDX but do I really know MDX? As the saying goes, you only really know something once you can explain it to a six year-old. Now while I doubt there are that many six year-olds assigning a higher priority to learning MDX than running around in the playground, I do find value in the premise.

So I thought, why not do a series on MDX? Well, here it goes.

Continue reading MDX: Revving Up Engines

How To Add NonClustered ColumnStore Indexes To Every Table

Sometimes I feel really lazy. I’m just tinkering with a proof of concept database, a decent amount of test data, I just want to do some moderately fast querying over it and don’t really feel like going about creating proper indexes for everything just yet. I really only want to slap a ColumnStore on everything at once so I can get on with work and run some fast questions over the data.

Luckily, that’s what the cheat script below is for:

/* set the prefix for the indexes here */
DECLARE @IndexPrefix NVARCHAR(MAX) = 'TEST_NCCSI_';

/* dont worry about these */
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @NL NVARCHAR(MAX) = NCHAR(13) + NCHAR(10);
DECLARE @TB NVARCHAR(MAX) = NCHAR(9);

/* and off we go */
WITH Query AS
(
	SELECT
		SchemaId = S.schema_id,
		SchemaName = S.name,
		TableId = T.object_id,
		TableName = T.name,
		ColumnId = C.column_id,
		ColumnName = C.name,
		IsFirstColumn = CASE WHEN C.column_id = MIN(C.column_id) OVER (PARTITION BY S.schema_id, T.object_id) THEN 1 ELSE 0 END,
		IsLastColumn = CASE WHEN C.column_id = MAX(C.column_id) OVER (PARTITION BY S.schema_id, T.object_id) THEN 1 ELSE 0 END
	FROM
		sys.tables AS T
		INNER JOIN sys.schemas AS S
			ON S.schema_id = T.schema_id
		INNER JOIN sys.columns AS C
			ON C.object_id = T.object_id
)
SELECT
	@SQL +=

		/* add ddl statement with first column */
		CASE WHEN Q.IsFirstColumn = 1 THEN
				'CREATE NONCLUSTERED COLUMNSTORE INDEX ' + QUOTENAME(@IndexPrefix + Q.TableName) + ' ON ' + QUOTENAME(Q.SchemaName) + '.' + QUOTENAME(Q.TableName) + @NL +
				'('
		ELSE
			''
		END

		/* add column name */
		+
		@NL + @TB + QUOTENAME(Q.ColumnName)

		/* add comma after interim columns */
		+
		CASE WHEN Q.IsLastColumn = 0 THEN ',' ELSE '' END

		/* close ddl after last column */
		+
		CASE WHEN Q.IsLastColumn = 1 THEN @NL + ')' + @NL + @NL ELSE '' END
FROM
	Query AS Q
ORDER BY
	SchemaId,
	TableId,
	ColumnId
;

SELECT @SQL;
PRINT @SQL;

Note that this straightforward script doesn’t try to partition align the indexes, detect invalid columns or any other fancy stuff. If you do end up adding those details, feel free to comment and I’ll add those in too.