Press "Enter" to skip to content

Author: Kevin Feasel

Columnstore Index Reorganization

Published 2016-03-08 by Kevin Feasel

Sunil Agarwal has a couple of posts on columnstore index defragmentation in SQL Server 2016.

Part 1:

Let us now look at how you can use REORGANIZE command to defragment your columnstore index. Note, this command is only supported for clustered columnstore index (CCI) and nonclustered columnstore index for disk-based tables. In the example below, I create an empty table and then create a clustered columnstore index and finally I load 300k rows.  SQL Server 2016 loads data from staging table into CCI in parallel when you specify TABLOCK hint. The machine I ran this test on has 4 logical processors so the 300k rows got divided into 75k each between 4 threads. Since each thread was loading < 102400 rows, the columnstore index ends up with 4 delta rowgroups as shown below.

Part 2:

A compressed rowgroup is considered as fragmented when any of the following two conditions is met

  • Less than 1 million rows but the trim_reason ( please refer to https://msdn.microsoft.com/en-us/library/dn832030.aspx ) is other than DICTIONARY_SIZE. If the size of a compressed rowgroup is reduced because it has reached the maximum dictionary size, then it can’t be further reduced

  • It has nonzero deleted rows that exceeds a minimum threshold.

I just got finished with a first draft of a script to determine whether reorganizing a clustered columnstore index partition would be worthwhile, so this is great timing.  I hope to make my script available soon, after I incorporate Sunil’s heuristics.

Comments closed

Index Usage Stats Bug Fixed

Published 2016-03-08 by Kevin Feasel

Kendra Little reports that rebuilding an index no longer clears out sys.dm_db_index_usage_stats:

Testing SQL Server 2016 RC0 today, I see that the bug for index_usage_stats has been fixed in this release! When I generate scans, seeks, and updates against an index, running ALTER INDEX REBUILD no longer resets the information. I can still tell which indexes have been used and which have not since the database came online, just like we had  in SQL Server 2008 R2 and prior.

Kendra has created a Connect item to fix a separate bug where rebuilding an index clears out the missing index recommendations for that table.

Comments closed

SSMS Shortcuts

Published 2016-03-08 by Kevin Feasel

Andy Mallon has a cheat sheet PDF from his great presentation on SSMS and Windows shortcuts:

On my flight home, I spent some time putting some of the things from my presentation into a one-page cheat sheet. I’ll have these printed up and have them on-hand for next time…but if you want it now, you candownload the PDF and print one for yourself.

My recommendation is to attend Andy’s talk when Mike Hillwig and Brent Ozar are trolling him from the back.

Comments closed

Making Text Search Faster

Published 2016-03-08 by Kevin Feasel

Rob Farley looks at binary collations and specifically hash functions on aggregation:

If there’s an index on the column you’re grouping, then changing the collation is going to hurt a bit. Grouping could take advantage of a Stream Aggregate under our indexed collation, but changing the column is like throwing it away the index order (ORDER BY doesn’t get handled well by changing the collation) means a Hash is required. But comparing two query plans that both use Hash Match (Aggregate), one on a case-insensitive collation and one on a binary collation, then I found the latter was slightly faster. Not as drastic a change as searching, but still 10-30% better. One would run in about 12 seconds, and one in about 10.

Be sure to check out his comments for more details.

Comments closed

Fix SQLPS

Published 2016-03-07 by Kevin Feasel

Chrissy LeMaire wants Microsoft to fix three things with SQLPS:

SQLPS has a lot of of bugs that need to be addressed (I’ll get to that soon), but I propose we start with these three.

  1. SQLPS module is slow to load
  2. Loading SQLPS module changes current directory to PS SQLSERVER:\>
  3. SQLPS module uses unapproved PowerShell verbs

Each item even has suggested fixes. The fixes are pretty straightforward (said the DBA who doesn’t do QA). Bugs 1 and 2 suggest modifying a few lines in SqlPsPostScript.ps1, while number 3 probably requires a recompile and we’re not really sure how challenging that will be.

Please upvote the Connect items if you use SQLPS…or don’t but would if it worked better.

Comments closed

Granting Permissions In AGs Or On Mirrors

Published 2016-03-07 by Kevin Feasel

Matan Yungman discusses how to grant permissions only to the replica database in a mirroring or Availability Groups scenario:

You work with Database Mirroring or AlwaysOn AG, and you want to make sure your end users work only on the secondary server. How should you do that?

This solution feels a little hacky to me.  There’s enough value in it that I could see companies doing this, but it’d be nice if there were an easier way.

Comments closed

DBCC UPDATEUSAGE

Published 2016-03-07 by Kevin Feasel

Slava Murygin has some information on the DBCC UPDATEUSAGE command:

As you can see, UPDATEUSAGE found three problems with that table, while was running in “ESTIMATEONLY” mode. When it run in “Fix” mode it also found and fixed only three problems. The number of rows was still left unfixed.

I think it’s fair to say that this is a relatively uncommonly-used DBCC command, but can definitely be useful in a subset of circumstances.

Comments closed

Plan Explorer Math

Published 2016-03-07 by Kevin Feasel

Aaron Bertrand walks through display differences between SQL Sentry Plan Explorer and SSMS:

Now, in the process of developing Plan Explorer, we have discovered several cases where ShowPlan doesn’t quite get its math correct. The most obvious example is percentages adding up to over 100%; we get this right in cases where SSMS is ridiculously off (I see this less often today than I used to, but it still happens).

Interpreting execution plans is not a trivial exercise, and this is an interesting look at how SQL Sentry developers (and supporters within the broader community) have worked on it through the years.

Comments closed

Don’t Use Write-Host

Published 2016-03-07 by Kevin Feasel

Steve Jones warns us away from Write-Host in Powershell:

I’ve written a few scripts and programs lately, mostly just for fun. In those scripts, I’ve used Write-Host to return output. To me, it’s been like “Print” in various languages where I can get output of a program. Often I’ll use a method/function to get info and then use print to output that to the caller.

However a few people noted that in my last script, Write-host wasn’t necessary. When I asked why, both Mike Fal and Drew Furgiule responded.

Mike and Drew are smart cookies.  Write-Host has some major limitations which hinder developers’ abilities to modularize and package viable code.

Comments closed

Columnstore On Temp Objects

Published 2016-03-07 by Kevin Feasel

Niko Neugebauer looks at creating columnstore indexes on temporary objects to see which ones are allowed and what limitations exist:

There is a very usable support for Columnstore Indexes within the temporary objects, but they are not appearing in any of the DMV’s to be analysed or optimised. This is especially sad in the relation to the global temporary tables which are some of the more useful temporary objects.

For the most part, I’d consider these reasonable results.  Hopefully we can get columnstore stats on temp tables, but even that’s not a huge loss.

Comments closed