Press "Enter" to skip to content

Curated SQL Posts

Always Encrypted With Powershell

Published 2017-01-06 by Kevin Feasel

Jakub Szymaszek shows how to configure Always Encrypted support from Powershell:

Note: In a production environment, you should always run tools (such as PowerShell or SSMS) provisioning and using Always Encrypted keys on a machine that is different than the machine hosting your database. The primary purpose of Always Encrypted is to protect your data, in case the environment hosting your database gets compromised. If your keys are revealed to the machine hosting the database, an attacker can get them and the benefit of Always Encrypted will be defeated.

That’s a good warning.

Comments closed

Azure Resource Explorer

Published 2017-01-06 by Kevin Feasel

Kenneth Fisher discusses the Azure Resource Explorer:

Now this is just default tab. The GET, PUT tab. Which basically shows you the get command of the resource manager API that calls this information, and if you hit the edit button you can actually change information in the JSON output and issue a PUT command to send it back. I’ll admit up front that this is a bit beyond me as I don’t do API calls and I’m new enough to Azure that I don’t know what I can and can’t change (everything I’ve tried so far hasn’t worked). There are several other tabs, though, including a Powershell one and I’m a bit more familiar with Powershell. In it, you can see some of the Powershell commands associated with the resource manager and this particular object.

Read on for more information.

Comments closed

Columnstore Partitioning

Published 2017-01-06 by Kevin Feasel

Niko Neugebauer warns against partitioning small tables with clustered columnstore indexes:

Needless to say that looking at the execution plans you notice that the actual execution plan shows 10 times difference between them, even though both tables contain the very same data!
The query cost for the partitioned table is staggering – it is around 10 times bigger (~8.8) vs (~0.81) for the first query.
The execution times reflect in part this situation: 12 ms vs 91 ms. Non-partitioned table performs almost 9 times faster overall and the spent CPU time is reflecting it: 15 ms vs 94 ms. Remember, that both tables are Columnstore Indexes based ! Partitioning your table in a wrong way will contain a huge penalty that might not be directly detectable through the execution plan of the complex queries. Well, you might want to use the CISL, just saying

If you can’t fill a single rowgroup, your partition is too granular.  Even then, I’d like to see double-digit rowgroups per partition, though that’s just me.

Comments closed

Polybase And Azure SQL Data Warehouse

Published 2017-01-05 by Kevin Feasel

I have a post on using Polybase with Azure SQL Data Warehouse:

That’s a header row, and I’m okay with it not making its way in.  As a quick aside, I should note that I picked tailnum as my distribution key.  The airplane’s tail number is unique to that craft, so there absolutely will be more than 60 distinct values, and as I recall, this data set didn’t have too many NULL values.  After loading the 2008 data, I loaded all years’ data the same way, except selecting from dbo.Flights instead of Flights2008.

Click through for more details, including the CETAS statement, which I’d love to see in on-prem SQL Server.

Comments closed

Filtered Indexes And Parameters

Published 2017-01-05 by Kevin Feasel

Erik Darling shows an example of what happens when you have a filtered index and parameterize the filter:

It Is Known

That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn’t have data for WHERE Bit = 0. That index would only be suitable for one variation of the query, so caching a plan that uses an index which can’t be reused for every variation isn’t feasible.

Read on for a couple examples, and check the comments on this as well.

Comments closed

Using RTVS

Published 2017-01-05 by Kevin Feasel

David Eldersveld gives three reasons why you might be interested in R Tools for Visual Studio:

2. Incorporate R projects as part of a broader Visual Studio solution
Many Visual Studio solutions end up being a collection of individual projects. More often than not, these projects are logically joined by virtue of being part of the same business solution, but each one can incorporate different components or languages. For example, you may architect a solution that involves separate projects for loading data­­ with Azure Data Factory, analysis with R, a front-end C# web app, etc. Rather than keep your R code siloed off in a separate solution, unite it with the rest of your code for development and source control.

This is my primary reason.  R Studio is still my go-to option, but RTVS is maturing fairly nicely.  It still feels slower than R Studio when displaying data on-screen (especially when you’re spitting out a couple hundred lines of text), but that Visual Studio integration will go far.  A fourth reason that David does not mention:  it generates the really ugly sp_execute_external_script code for SQL Server R Services.

Comments closed

Figuring Out Work-Life Balance

Published 2017-01-05 by Kevin Feasel

Sander Stad talks work-life balance:

The thing that comes up when I read this is that in most situations it will not work because you’re removing all the flexibility. The other thing is that most companies evaluate employees based on their availability and their flexibility.

There also a side note that employers are allowed to make different arrangements with employees.
Employers will probably adjust contracts from this point on that, if you’re in some sort of position where the availability is important, you’re obliged to answer which will render the law useless in a lot of situations.

There’s no one answer here, but it’s an important topic to think about.

Comments closed

Memory-Optimized Tables In Query Store

Published 2017-01-05 by Kevin Feasel

Enrico van de Laar looks at how Query Store treats memory-optimized tables and natively compiled stored procedures:

Interestingly enough, no runtime statistics are returned for our natively stored procedure. As it turns out, we need to specifically enable the collection of statistics for natively compiled stored procedures through the sp_xtp_control_query_exec_stats stored procedure. Keep in mind, though, that enabling the collection of statistics for natively-compiled stored procedure can cause performance degradation. Thankfully we can very easily control the collection of statistics by enabling it for only specific natively compiled stored procedures and turn it on and off directly.

Read the whole thing.  Long story short:  they work together nicely.

Comments closed

Elastic Pool Database Sizes

Published 2017-01-05 by Kevin Feasel

Vincent-Philippe Lauzon looks at how you can size databases with an Azure Elastic Pool:

We can’t change a database maximum size in the portal (as of December 2016).

Using ARM template, it is easy to change the parameter.  Here, let’s simply show how we would change it for an existing database.

Building on the example we gave in a previous article, we can easily grab the Pool-A-Db0 database in resource group DBs and server pooldemoserver:

Click through for all the details.  I highlighted this snippet as another point that the most important language for a Windows administrator to learn nowadays is Powershell.

Comments closed

Clustering SQL Server On Linux

Published 2017-01-05 by Kevin Feasel

Joey D’Antoni has some notes on clustering a SQL Server instance running on Linux.  First, some quick notes:

One other thing that wasn’t in BOL, that I had to troubleshoot my way through is that just like a cluster on Windows, you have a cluster identifier and floating IP address. I had to add that to /etc/hosts on each of my nodes to get it to resolve. The article mentions turning off fencing for non-prod environments—I had to do that in order to get failover working correctly in my environment.

Then some more notes:

It was faster than building a Windows cluster

It took me a while, I laughed, I cried, I cursed a lot, but if I look at the time it took for me to actually build the cluster and install SQL Server, it was a much faster process. Much of this comes down to the efficiency of the SQL Server installation process on Linux, which is as simple as running yum install mssql-server (mostly). Which leads me to my next point..

As Joey notes, SQL Server clustering on Linux is in its infancy.  It’s nice that it works right now, but expect improvements over the next version or two.

Comments closed