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?
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.
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?
If so, this series might be for you.
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.
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.
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.