A Fine Slice Of SQL Server
Derik Hammer notes that there is a bug in 2016 with SQL Agent jobs which have Powershell step types:
When executed through the SQL Agent, the SQLPS.exe mini-shell is called and the current working directory is switched to the SQLSERVER:\ provider. When you call a cmdlet that uses the FILESYSTEM provider under the context of the SQLSERVER provider the cmdlet will fail.
Derik has a Connect ticket open for this bug as well.
Comments closedAt this time, we want to remind you of a critical Microsoft Visual C++ 2013 runtime pre-requisite update that may be* required on machines where SQL Server 2016 will be, or has been, installed. Installing this, via either of the two methods described below, will update the Microsoft Visual C++ 2013 runtime to avoid a potential stability issue affecting SQL Server 2016 RTM.
* You can determine if an update is required on a machine via one of the two methods below:
Select View Installed Updates in the Control Panel and check for the existence of either KB3164398 or KB3138367. If either is present, you already have the update installed and no further action is necessary.
Check if the version of %SystemRoot%\system32\msvcr120.dll is 12.0.40649.5 or later. If it is, you already have the update installed and no further action is necessary. (To check the file version, open Windows Explorer, locate and then right-click the %SystemRoot%\system32\msvcr120.dll file, click Properties, and then click the Details tab.)
If you’re running 2016, please make sure that your systems are up to date. This post includes an easy T-SQL query you can run to see if you’re up to date already.
Comments closedGinger Grant provides an introduction to the Azure Data Factory dashboard:
Pick the location based on two factors, Azure Data Factory is not available everywhere so you are limited to use only the ones where it is available. If you pick one where it isn’t available, you will get an error message letting you know why you cannot create the resource. Whenever possible within Azure to pick the same resource where your data lives. There are charges within Azure if you migrate data across resources and no charge if you stay in the same resource. You may want to go look at where the data lives which will be used in Data Factory before deciding where to put it. I always check the Pin to Dashboard option so that I can find the resource later, but it is not required and can be done later. Click on the create button to create a Data Factory Resource. If you have selected Pin to Dashboard you will see a little window which says Deploying Data Factory. This little window goes away once Data Factory is completed, and you will have an entry in the list of resources for Data Factory.
Read the whole thing if you’re thinking of getting started with Azure Data Factory.
Comments closedNiko 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 closedApache 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 closedRob 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 closedEwald 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 closedKoen 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 closedRaul 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 closedMatt 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