Tag Archives: dba

Top 5 New Features in SQL Server 2017 (that I care about)

New Year, New Database Engine

This is either a database or a stack of licorice pancakes.

It’s finally here! A few days ago as of this writing, SQL Server 2017 was released for Windows, Ubuntu, RedHat and Docker. There are a lot of new things in SQL Server 2017 from Python support in Machine Learning* to better CLR security. But I thought I’d narrow down the list to changes that I’m most interested in.

1. sudo Your Way To A Better SQL Server

SQL SERVER IS ON LINUX NOW! No surprise this is the first thing on my list as I keep going on and on about it. But it’s really here!

Installation is a cinch, especially if you’re a Linux or Unix person. Curl the GPG keys, add the repository, apt-get (if you’re on a real Distro) the installer and run the setup. It’s really that easy.

If you want it even easier, then check out Docker. Slap it in and go!

All the main features are there, the engine (of course), agent, Full-text search, DB Mail, AD authentication, SQL command-line tools etc. Pretty much everything you need to get going on Linux even if you’re in a Windows-dominated environment.

2. It’s Like A Car That Repairs Itself

So this is a big one. With automatic tuning on, SQL Server can detect performance problems, recommend solutions and automatically fix (some) problems. There are two flavors of this, one in SQL Server 2017 and one in Azure; I’ll be talking about the one in SQL 2017 here.

Automatic plan choice correction is the main feature and it checks whether a plan has regressed in performance. If the feature is so enabled, it reverts to the old plan. As you can see below, a new plan was chosen (4) but it didn’t do so well. SQL 2017 reverted to the old plan (3) automatically and got most of the performance back.

Image Source: Microsoft

I’m sure that Microsoft will be expanding this feature in the future and we can expect to see more from this. Azure already has automatic Index tuning in place, so we’ll probably see that in the On-Prem version eventually.

3. Indexes That Start, Stop And Then Start Again

This is a feature I didn’t know I needed. Basically, it allows an online index rebuild that has stopped or failed (say, it ran out of disk space) to be resumed. The index build fails, you fix whatever made it fail, and then resume the rebuild, picking up from where it left off. The rebuild will be in the ‘PAUSED’ state until you’re ready to RESUME or ABORT it.

-- Start a Resumable index rebuild
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable]

-- PAUSE the rebuild:
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable] PAUSE

/* If you'd like to resume either after a failure or because we paused it. This syntax will also cause the resume to wait 5 minutes and then kill all blockers if there are any. */
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable]

/*Or if you just want to stop the whole thing because you hate unfragmented indexes */
ALTER INDEX [NCIX_SomeTable_SomeColumn] on [dbo].[SomeTable] ABORT

This is also great if you want to pause a rebuild because it’s interfering with some process. You can PAUSE the rebuild, wait for the transaction(s) to be done and RESUME. Pretty neat.

4. Gettin’ TRIM
A cute Sheepy.
There hasn’t been a picture in a while and I was afraid you might be getting bored.

This one is kind of minor, but it excited me a lot because it was one of the first things I noticed as a DBA that made me say ‘Why don’t they have a function that does that?’ (note the single quotes). TRIM will trim a string down based on the parameters you provide.

If you provide no parameters it will just cut off all the spaces from both sides. It’s equivalent to writing RTRIM(LTRIM(‘ SomeString ‘))

SELECT TRIM( '.! ' FROM ' SomeString !!!') AS TrimmedString;


5. Selecting Into The Right Groups

Another small important change. In previous versions of SQL Server, you could not SELECT INTO a specific Filegroup when creating a new table. Now you can, and it uses the familiar ON syntax to do it.

SELECT [SomeColumn] INTO [dbo].[SomeNewTable] ON [FileGroup] from [dbo].[SomeOriginalTable];

Also, you can now SELECT INTO to import data from Polybase. You know, if you’re into that sort of thing.

Honorable Mentions!

Here are some things that caught my eye, but didn’t really need a whole section explaining them. Still good stuff, though.

Query Store Can Now Wait Like Everybody Else

Query store was a great feature introduced in SQL Server 2016. Now they’ve added the ability to capture wait stats as well. This is going to be useful when trying to correlate badly performing queries and plans with SQL Server’s various waits.

See the sys.query_store_wait_stats system table in your preferred database for the deets. Obviously, you’ll need to turn on Query Store first.

Con The Cat With Strings

Just as minor as TRIM in some people’s books, but this is a great function for CONCAT_WS’ing (it’s not Concatenating, right? That’s a different function) strings with a common separator, ignoring NULLs.

SELECT CONCAT_WS('-','2017', '09', NULL, '22') AS SomeDate;


Get To Know Your Host

sys.dm_os_host_info – This system table returns data for both Windows and Linux. Nothing else, just thought that was neat.

Get Your Model Serviced

Not something I’m jumping for joy about, but it is a good change of pace (I think). No more Service Packs, only Zuul… er… just Cumulative Updates. Check out my article on it if you want to know more about all the Service Model changes.

And Lots Of Other Things

Of course, there’s hundreds other things that are in SQL Server 2017 and related features. I didn’t even touch on the SSIS, SSAS, SSRS, MDS or ML stuff. Check out the shortened list here, broken down by category. Exciting new toys!

-CJ Julius

* Seriously, how do you release a Data Science platform and not include Python? That’s like releasing a motorcycle with only the rear tire. Yes, you can technically use it, but you you’re limiting yourself to a customer base with a very selective skill-set.

Creating A Simple Database Inventory Manager with Powershell – Introduction

Which databases names have the letter “B”?

And then we shall rule the world!
And then we shall rule the world!

All DBAs should keep track of their Servers/ Instances/ etc not only for their own edification, but for Management and security reasons as well. If you’re not, then you need to, as it comes in incredibly handy even if it isn’t a requirement of the job.

Most of the time, this information is compiled into a spreadsheet of some kind or possibly in a word processing document somewhere. Keeping this data up-to-date and accurate is a pain, especially when you have to break it out into multiple tabs and/or over multiple documents.

You could get a full-blown inventory manager that collects and compiles all the data and organizes it for you. But there’s a definite cost to that solution and not one that all companies will find useful (Read: “It’s not in the budget this quarter”).

What if you can’t get someone to shell out the money for a product like that? Then you have to either keep with the spreadsheets (yuck) or you need to find another solution with the tools you have.

What’s the Catch?

A simple frontend for your Database Inventory
A simple front-end for your Database Inventory

So, this is my attempt to resolve this issue using two tools that any MSSQL DBA should have: Powershell and SQL Server. I will point to other software products or versions both paid and free below, but the core code should run using things you should already have. That said, here’s my software list:



Also I will be making a few assumptions:

  1. Your infrastructure security is set up using Active Directory.
  2. Setting up a new instance is already done and you can connect to it.
  3. Your personal login or the login you are using to execute the code is able to query the relevant system tables and server info on each of the target systems.
  4. You know your current Server\Instance setup.
  5. There is no fifth thing.

What You Get

By the time this series is finished you’ll have a simple GUI front-end (shown above) for current data with all of your servers, instances and all the information you could want about them.

We can pull back and organize any data that SQL Server or Windows Server can spit out including, but not limited to:

  • OS Version, Service Pack.
  • SQL Services running, their statuses and logon information.
  • SQL Server Instance names, versions, and editions.
  • SQL Server Database names and sizes.
  • Ability to dynamically re-pull any of this information if needed.

I will walk you through my solution piece by piece over 4 posts (5 including this intro) that will consist of the following parts. This list will be updated with links to the different sections as they are released.

Part I: Building the Repository Database and Tables
Part II: Creating the Repository Stored Procedures
Part III: Coding the Data-Pulling Powershell
Part IV: Putting together the GUI Front-end

Addendum I: Simple Database Inventory Manager 2.1
Addendum II: Simple Database Inventory Manager 2.3

See you soon in Part I: Building the Repository Database and Tables

–CJ Julius

MongoDB – Deleting Lots of Data Without Lock

Newer versions of MongoDB (3.x) handle locking better.

MongoDB doesn’t handle locking too well. It’s getting better in new versions (3.2 looks to be a strong contender) with collection and object level locking, but it’s still not great.

In an environment I was working on some time back, there was a need to remove lots of data on a regular basis to keep the size of the database manageable. The developers had implemented a snazzy maintenance system that NULL-ed the large fields that contained payloads for the data in order to save space after their utility was over. This maintenance system was run once a day and locked the collections/databases for that time; the brief period of locking was considered acceptable in this environment.

However, this didn’t free up any space in the system, because of the way MongoDB, (at the time 2.6, but 3.0 seems to have the same issue without Wired Tiger) NULL-ing the field did free up the space, but did not actually allow for new data to be inserted into its place.  The only way to free the space was to take the database offline and repair or compact it once a month. Not an option.

Because of the needs of the system, we were able to delete the whole object and reinsert it without the payload, but this caused excessive overhead and just turned our disk space problem into a CPU and memory one.

It was decided that since we did nightly backups, deleting the entire object was the way to go, but we’d need to do it manually until development could make the appropriate changes to the maintenance system. The problem then arose of deleting at least a week’s worth of data all in one go. This could take several hours and lots of locking if done all at once.

The solution was to have a process that deleted a set of the data from the database, pause for a moment to let other systems insert/delete/update, and it couldn’t require a large development cost (ie the DBA [spoiler: me] had to do it).

My solution as  a patch job was to have a java-script “job” run by a Windows batch script every week to delete data older than a certain date.  You can view my simple mongoMaint batch script on my github here and the javascript file that it calls here.


-CJ Julius