Press "Enter" to skip to content

Curated SQL Posts

Thinking About Dead Connections

Published 2017-01-23 by Kevin Feasel

Lonny Niederstadt has a half-baked idea on dead connections:

In SQL Server 2005, a valuable addition was made.  SQL Server would use a “Keep Alive” value specified specifically per instance, rather than the KeepAliveTime specified by the Windows registry.  It defaulted to 30000 ms/30 seconds.

The blog post below explains this new addition, and mentions that the interval for SQL Server will be a fixed 1000 ms/1 second, regardless of the KeepAliveInterval specified in the Windows registry. At the time SQL Server 2005 was introduced, TCPMaxDataRetransmissions from the Windows registries still controlled the maximum number of probes.

Read on for more.

Comments closed

Streaming Data To Power BI

Published 2017-01-23 by Kevin Feasel

Sacha Tomey builds a quick Powershell script to feed streaming data into Power BI:

Peter showed off three mechanisms for streaming data to a real time dashboard:

  • The Power BI Rest API
  • Azure Stream Analytics
  • Streaming Datasets

We’ve done a fair bit at Adatis with the first two and whilst I was aware of the August 2016 feature, Streaming Datasets I’d never got round to looking at them in depth. Now, having seen them in action I wish I had – they are much quicker to set up than the other two options and require little to no development effort to get going – pretty good for demo scenarios or when you want to get something streaming pretty quickly at low cost.

Click through for more details and a sample script.

Comments closed

CONCAT_WS()

Published 2017-01-23 by Kevin Feasel

Manoj Pandey points out a new string function in SQL Server vNext:

Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.

This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.

The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.

It’s a small change, but I think a rather useful one.  Do think about how you’d want to interpret NULL values, though, as CONCAT_WS() does not include separators for NULL values.

Comments closed

Cloned Database Size

Published 2017-01-23 by Kevin Feasel

Joey D’Antoni tests how large database files are after running DBCC CLONEDATABASE:

One of the recent feature introductions to SQL Server is dbcc clonedatabase, a feature that lets you create a “data-less” clone of you database. All of the statistics and objects come into your cloned database, however none of the data does. This is perfect for development or performance tuning exercises, where you want all the metadata, but do not want the security risk of dealing with production data.

Recently I had the opportunity to use clonedatabase on a very large database. I was concerned about the size of the data files and how this would impact space on my volumes. Books Online is fairly clear, but I wanted to see for myself.

Click through for the answer.

Comments closed

NCCI Online Rebuild

Published 2017-01-23 by Kevin Feasel

Niko Neugebauer notes a big improvement in vNext:

For the Columnstore Indexes, the only online operation for the indexes that was available so far, was the Row Group Merging and Removal with ALTER INDEX REORGANIZE (as well as the Tuple Mover operations). With appearance of HTAP scenarios (Hybrid Transactional Analytical Processing aka Operational Analytics) in SQL Server 2016, there was a huge need for the online index maintenance, making sure that the operational part of the HTAP runs smoothly. For any online business, taking their application down for an hour means loosing real money and even worse – loosing credibility from their customers. To my knowledge, Microsoft was very much aware and was working on improving this missing part.
For the SQL Server vNext version (after SQL Server 2016) in CTP 1.2, yesterday, we have finally received the first Online Rebuild operation for the Columnstore Indexes – in this case for the Nonclustered Columnstore Indexes, and this is a huge news for anyone using the HTAP scenarios.
Naturally this feature is Enterprise Edition Only, and like ever before – if you are running a critical workload, you need to step up and use the Enterprise Edition.

Online clustered columnstore reorganization in 2016 was a life-saver, and I’m looking forward to online clustered columnstore rebuilding at some point in the future.

Comments closed

Automating Stats Maintenance With Azure SQL DW

Published 2017-01-20 by Kevin Feasel

Grant Fritchey shows how to create automated statistics maintenance for an Azure SQL Data Warehouse database:

NOTE: The most important habit you can start with in Azure is putting everything into discrete, planned, Resource Groups. These make management so much easier.

Once the account is set, the first thing you need is to create a Runbook. There is a collection of them for your use within Azure. None of them are immediately applicable for what I need. I’m just writing a really simple Powershell script to do what I want:

Runbooks are an important part of Azure maintenance, and this is a gentle introduction to them.

Comments closed

Right-Sizing Elastic Pools

Published 2017-01-20 by Kevin Feasel

Arun Sirpal points out a nice potential money-saving feature with Azure:

The recommendation is to setup a standard 50 EDTU pool. I am convinced that this pool is a new pricing tier. Even though the cost saving is small it is still clever that it suggests this. I assume the analysis done in the background really does understand my utilization patterns as we know that the patterns are absolutely crucial for when using elastic pools so it is something to definitely consider.

Within a click of a button the portal will create it for you.

It’s interesting that the feature can actually save you money rather than just telling you that you need to buy more expensive services.

Comments closed

The Importance Of Powershell

Published 2017-01-20 by Kevin Feasel

Kevin Hill explains why he’s advocating that DBAs learn Powershell:

2 very solid reasons (there are others) that every DBA should be learning and using PowerShell:

1 – Its very useful for admin at the O/S level.

At my current client I am team lead of System and SQL Admins, along with doing any of the work that comes our way.  This means we need to be able to manage the modest server farm we have.  Its big enough that we can’t log onto every server every day, but small enough nobody wants to buy a proper monitoring toolset.  So…PS to the rescue!

Read on for the other reason.  I think the relatively poor Powershell tooling with SQL Server (with respect to other groups like Exchange) limited general acceptance, but they’ve made some big improvements over the past year and there are some sharp minds in the community working to make Powershell even more important for DBAs.

Comments closed

ApplicationName On Invoke-SqlCmd2

Published 2017-01-20 by Kevin Feasel

Andy Levy improves the Invoke-SqlCmd2 cmdlet:

I decided to change this around so that it no longer uses string formatting, but instead a SqlConnectionStringBuilder. I had a couple reasons for this:

  • It will eliminate redundant code. There are several common elements in each of the ConnectionStrings above. If more complex logic is needed, there are potentially more copies of this ConnectionString kicking around.

  • It’s prone to copy/paste and other editing errors. If there’s a change that affects both versions of the ConnectionString and the developer just copies the line from one branch of the if statement to the other, code will be lost or invalid values will be substituted because of positioning.

This is something I’d like to see make it to the main cmdlet.

Comments closed