Press "Enter" to skip to content

Author: Kevin Feasel

Sparse Columns

Published 2016-06-21 by Kevin Feasel

Slava Murygin discusses sparse columns:

“Sparsing” is the way SQL Server optimizes spacing for NULL values at the cost of overhead for non-NULL values.
In other words, if you expect having in your column more nulls than not nulls you can SPARSE that column to optimize the space.

I’ve seen the situations when a lot of columns in Data Mart tables were almost completely filled with NULLS and I started wondering if “SPARSE” can be a good tool to gain some space.

Read the whole thing.  I am not a fan of sparse columns because they prohibit things like page-level compression.  Be sure to read the restrictions on using sparse columns before you give them a try; on net, I think they’re more trouble than they’re worth except in edge cases like extremely denormalized tables collecting thousands of data points from sensors.

Comments closed

Handling Division Failure

Published 2016-06-21 by Kevin Feasel

Reza Rad looks at how division can go wrong in Power Query:

In this example I just returned zero if I find error. but you can return error message if you like with [Revenue Per Item][ErrorMessage]. This method is great error handling method when an error out of blue happens in your data set. I always recommend using TRY method to get rid of errors that might stop the whole solution to work properly.

I have to mention that steps above are separated to show you how the output of try expression looks like. In fact you can combine both steps above in single step with TRY OTHERWISE as below (Thanks to Maxim Zelensky for pointing this out);

The end result is code which is a bit more complex, but safely handles a number of edge cases.

Comments closed

SSIS Method Not Found

Published 2016-06-21 by Kevin Feasel

Regis Baccaro ran into a rather lengthy error when trying to create an SSISDB catalog:

The error I got was :

Method not found: ‘Void Microsoft.SqlServer.Management.IntegrationServices.EnableSsisSupportAlwaysOnSqmHelper.Initialize()’. (Microsoft.SqlServer.IntegrationServices.UITasks)

Looking at the documentation for the namespaceMicrosoft.SqlServer.Management.IntegrationServices I quickly figured out that I would be able to create the SSIS Catalog manually using PowerShell.

But then I couldn’t locate the Microsoft.SqlServer.Management.IntegrationServices dll anywhere except from in the GAC so I had to load it a somewhat cumbersome way (with help from Remo). Below is the script I used for doing that.

It’s a strange error, but Regis does provide a workaround.

Comments closed

UPDATE() Inside Triggers

Published 2016-06-21 by Kevin Feasel

James Anderson shows how to use the UPDATE() function inside a trigger to operate selectively on data:

This use of the UPDATE function for selective logging can be very useful when used on tables with columns such as: LastOrderDate, LastLoginDate, etc as these columns are often updated but those changes are probably not required to be logged.

One interesting point is that even if our trigger was configured to fire on DELETEs, the UPDATE function would not return true and therefore the change would not be logged. This makes sense as a DELETE affects all columns, so checking for a particular column is not required. If we wanted to log DELETEs to our ProductPriceLog table, we would use a trigger that fired on DELETEs.

But check the comments to make sure you know when UPDATE() fires—it’s not just when a particular column changes values.

Comments closed

Cardinality Estimator Trace Flags

Published 2016-06-21 by Kevin Feasel

Kim Tripp shows how to set which cardinality estimator you want to use on a per-query basis:

However, the bad news is the QUERYTRACEON is limited to SysAdmin only (be sure to read the UPDATEs at the end of this post). Jack Li (Microsoft CSS) wrote a great article about a problem they solved by using a logon trigger to change the CE for an entire session: Wanting your non-sysadmin users to enable certain trace flags without changing your app? Now, I do want to caution you that setting master to trustworthy is not something you should take lightly. But, you should NOT let anyone other than SysAdmin have any other rights in master (other than the occasional EXEC on an added user-defined SP). Here are a couple of posts to help warn you of the danger:

A warning about the TRUSTWORTHY database option
Guidelines for using the TRUSTWORTHY database setting in SQL Server

Read on for a couple of options.

Comments closed

Columnstore With Integer Sequences

Published 2016-06-21 by Kevin Feasel

Niko Neugebauer talks about handling sequences and default values within columnstore indexes:

There are still no dictionaries – and trying to rebuild this table will not bring any effect at all, but take a look at the size of the segments – their size was lowered for almost 40% to ~1.6 MB!

This technic is very effective if you are compressing the columns that you do access rarely – it should be considered for the log tables for example.
Also notice that Columnstore Archival compression will not bring any significant changes – the original 2.6 MB will lower to 2.42 while the variable char column will not get any further improvements, making the improvement difference around 32%.

Warning: Do NOT use this technic without understanding the consequences – the processing of such columns will lower their effectiveness, since Predicate Pushdown will work in a very limited way, plus the Segment Elimination will not work at all.

fff

Comments closed

SOS_RWLock

Published 2016-06-20 by Kevin Feasel

Ewald Cress looks at SOS_RWLock, a reader-writer lock (at least the pre-2016 version):

This lock class can best be appreciated by comparing it to a mutex. Like the mutex, a reader-writer lock can only be acquired in exclusive mode by one requestor at a time, but instead of only exposing this exclusive-acquire (Writer) option, it alternatively allows acquisition in shared (Reader) mode. This stuff is completely natural to us database folks of course, because the semantics is a subset of the behaviours we get from familiar database locks.

Basic rules of the road:

  • Any number of simultaneous “clients” can share ownership of the lock in Read mode.

  • Readers block writers.

  • Writers block readers and other writers.

  • Blocking means that the requesting worker gets suspended (scheduled off the processor) and accrues a wait of a type specified in the lock acquisition request.

There’s a huge amount of detail here, and I for one am glad that there isn’t a quiz later..

Comments closed

U-SQL Parallelism

Published 2016-06-20 by Kevin Feasel

Saveen Reddy discusses parallelism with U-SQL jobs:

Previously an Data Lake Analytics account was limited to a total of 60 units on parallelism available to 3 concurrent U-SQL jobs. And each U-SQL job was limited to only using a maximum of 20 units of parallelism. However, sometimes developers want to run a U-SQL job that uses more parallelism. For example: they might want two concurrently running jobs that each use 30 units. Today, we are removing the per-job limit. Now you can concurrently run jobs that use any amount of parallelism as long as the total for the running jobs doesn’t go beyond the maximum for your account (currently 60 units of parallelism).

If you are already running U-SQL jobs, there’s no need to worry. This change doesn’t require any action on your part. Your jobs run just as they did before. But now, if you want to you can take advantage of all the parallelism in your account.

This doesn’t change the available performance units, so there’s no billing change.

Comments closed

Negative Width And Height In SSMS

Published 2016-06-20 by Kevin Feasel

Manoj Pandey ran into an issue with Management Studio wanting to open a window with a negative size:

By checking the error its obvious that there is something wrong with Width or Height of SSMS Query-Editor window.

So, I went to REGEDIT (In RUN, type regedit.exe) and after navigating here n there got the location where to update this property.

Navigate to folder: HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\13.0\

Here check the MainWindow property value (image below), it was showing: 0 451 109 -120 876 1

Change it to a positive value considering the width of your SSMS editor window, I replaced -120 with 1400

I had no idea that the main window size details were kept in the Registry.

Comments closed

Dealing With Backup Files

Published 2016-06-20 by Kevin Feasel

Kenneth Fisher discusses backup files, especially when they mysteriously double in size:

Did you know a single backup file can contain multiple database backups? When you backup a database to a file, if that file already exists, then by default the backup will be appended to the existing file. Causing the file to increase in size. If it’s the same database (and yes you can have a single file containing backups from multiple databases) then the file size will double or more. This behavior is controlled by the INIT/NOINIT clause of the BACKUP DATABASE command. NOINIT (the default) tells SQL to append the new backup to the existing file. INIT tells SQL to overwrite the existing backup files. Note the header of the file is not initialized.

This is good information to know.

Comments closed