Press "Enter" to skip to content

Author: Kevin Feasel

New Columnstore Trace Flags

Published 2016-07-29 by Kevin Feasel

Niko Neugebauer looks at a few trace flags which modify columnstore index behavior:

Starting with SQL Server 2016, if you have enough RAM and suffering from the TempDB Spills that do have a significant impact on your workload, then you can enable the Trace Flag 9389 that will enable Batch Mode Iterators to request additional memory for the work and thus avoiding producing additional unnecessary I/O.

I am glad that Microsoft has created this functionality and especially that at the current release, it is hidden behind this track, and so Microsoft can learn from the applications before enabling it by default, hopefully in the next major release of SQL Server.

There’s a lot of good stuff in here.  Read the whole thing.

Comments closed

Spark 2.0 Out

Published 2016-07-29 by Kevin Feasel

Apache Spark 2.0 has officially been released.  Vinay Shukla gives us some highlights:

Performance
Project Tungsten has completed another major phase and with new completely new stage code generation, significant performance improvements have been delivered. Parquet and ORC file processing have also delivered performance improvements.

Databricks Community Edition offers (tiny) free clusters with Spark 2.0 on top of Scala 2.10 and Scala 2.11.

Comments closed

At Time Zone

Published 2016-07-29 by Kevin Feasel

Rob Farley looks at the AT TIME ZONE operation in SQL Server 2016:

But despite the longwindedness of it, I love it, because at no point did I need to figure out that Adelaide was in +10:30, or that Eastern was -5:00 – I simply needed to know the time zone by name. Figuring out whether daylight saving should apply or not was handled for me.

It works by using the Windows registry, which has all that information in it, but sadly, it’s not perfect when looking back in time. Australia changed the dates in 2008, and the US changed its dates in 2005 – both countries saving daylight for more of the year. AT TIME ZONE understands this. But it doesn’t seem to appreciate that in Australia in the year 2000, thanks to the Sydney Olympics, Australia started daylight saving about two months earlier. This is a little frustrating, but it’s not SQL’s fault – we need to blame Windows for that. I guess the Windows registry doesn’t remember the hotfix that went around that year. (Note to self: I might need to ask someone in the Windows team to fix that…)

Like most companies dealing with multiple time zones, we ended up building a table and function to translate.  This is a nice, built-in way of doing something very similar.

Comments closed

Latch Spinlocks

Published 2016-07-28 by Kevin Feasel

Ewald Cress discusses a “secret” spinlock within latches:

No matter how bad contention gets for normal spinlocks, at least we account for cycles spent spinning: this stuff gets exposed in sys.dm_os_spinlock_stats and can allow us to diagnose and quantify contention. However, spinning done on a latch’s spinlock gets no obviously visible accounting. As such, if we did somehow manage to accrue a large number of spins on a very hot latch, it wouldn’t be obvious that the time went into spinning. This is not to say of course that it is necessarily a common problem, just that it would be hard to prove one way or the other.

If I appear to be painting a bleak picture, I apologise. Given the importance of latches, especially buffer latches, one would assume that the SQL Server development teams would be on the constant lookout for opportunities to identify issues and mitigate against them. And this is exactly the kind of scenario where some bright spark comes up with the idea of superlatches.

Read the whole thing.

Comments closed

AutoAdjustBufferSize

Published 2016-07-28 by Kevin Feasel

Koen Verbeeck looks at an update to AutoAdjustBufferSize in SSIS 2016 CU1:

Anyway, one of the particular updates that caught my attention was Adds the adjusted buffer size to the BufferSizeTuning event when AutoAdjustBufferSize is enabled in SSIS 2016. In simple terms, it allows you to log  the size of the data flow buffer set automatically by the AutoAdjustBufferSizeproperty. This property basically automatically calculates the buffer size needed to reach the amount of rows in the buffer specified by DefaultBufferMaxRows. Therefore, it ignores  the DefaultBufferSize property. Unfortunately, this new property is set to false by default.

Logging is at the Verbose level, so you won’t use it very often, but for test purposes, it’s interesting to see those changes.

Comments closed

Always Encrypted And Temporal Tables

Published 2016-07-28 by Kevin Feasel

Raul Gonzalez wants to combine Always Encrypted with temporal tables in SQL Server 2016:

Again the wall. There is no way you can choose a temporal table and apply encryption to a column or columns using the wizard.

I tried then using the powershell (after manually creating the keys) as this is true the only way to encrypt existing columns, just in case.

Raul doesn’t stop there, though, and he does figure out a workaround.

Comments closed

Custom X Axis Range

Published 2016-07-28 by Kevin Feasel

Matt Smith shows how to set a custom X axis range on Power BI charts:

The problem was the auto options for the X-Axis range was wrong and he recommended setting it manually.

To this is simple, click on the visual with the faulty X-Axis, then on the left hand menu, click the paint brush, then expand the X-Axis and manually enter the start and end

What’s interesting is that the answer came from submitting feedback from within the application.

Comments closed

Ways To Embed Power BI

Published 2016-07-28 by Kevin Feasel

Adam Saxton gives three methods for using Power BI on your own website:

Developers have the option to use the Power BI REST API’s to embed tiles or reports into their website or application. This option does require that the end user is signed into Power BI. This means that they will need to have signed up for Power BI. They will then see the items that they have access to.

Official Documentation – Power BI REST API’s

GitHub Sample Repo

I only remembered the first option, but the REST API is very interesting.

Comments closed

Amazon RDS Supports Native Backup And Restore

Published 2016-07-28 by Kevin Feasel

Brent Ozar reports that Amazon now allows you to use native SQL Server backups for getting data in and out of RDS:

Until now, the single biggest problem has been that both Azure SQL DB and Amazon RDS SQL Server don’t give you access to backup files. If you wanted to get your data out, you were hassling with things like import/export wizards, BCP, or sync apps.

Today, Amazon RDS SQL Server announced support for native backup/restore to Amazon S3.

This is a really, really, really big deal, something Azure SQL DB doesn’t support (and I dearly wish it did). I get even more excited reading this because now Microsoft has to do it in order to remain competitive, and that’ll make Azure SQL DB a much more attractive product for traditional DBAs.

This makes the migration strategy to and from RDS significantly easier.  Brent gives a few examples of how this will help customers.

Comments closed

Parallel Backups Using Powershell Workflows

Published 2016-07-28 by Kevin Feasel

Sander Stad wants to use Powershell workflows to back up databases in parallel:

It’s possible to use a workflow to execute your backups. You have to take into consideration that there is a downside. If you execute all the backups at once you’ll probably get issues with throughput if you’re dependent on a slow network for example.

You could always add another parameter to filter out specific databases to make sure you execute it as a specific set.

Sander does include his script, so check that out.

Comments closed