Voice of the DBA

Author: way0utwest

  • The New SQL Server 2016 Edition Limits

    I was thrilled with SQL Server 2016 SP1. I’ve been asking for all features to be in all editions for a long time, and now we have most of the features available. There are still a few enterprise only items, but these are based on speed and scale, and not programming, and I can live with those. While I’d still like some “in between” edition that had higher limits than Standard, or even some sort of core/RAM add-ons, I recognize there’s a complexity in doing this that might not be worth undertaking.

    That changes in SP1 that allow features like snapshots, CDC, Columnstore indexes, and more mean there are some other changes that are important to understand. The SQL Server Tiger Team recently posted a note about some of the limit changes in SQL Server, showing how some of the memory limits have been raised with the new Service Pack.

    In the last few versions, SQL Server Standard Edition (SE) has been limited to 128GB of RAM in a server. While that might not be a lot to some of you, I’ve rarely had servers with more memory, so that’s been a good edition for me. However, when considering using Columnstore or Memory-Optimized tables, I could see why there could be some concern over memory shortages. After all, SQL Server really, really likes lots of memory.

    There still is a 128GB limit for the buffer pool cache in SE, but this is separate from the Columnstore cache and memory-optimized data stores. For Columnstore cache, you get an additional 32GB of RAM that can be used per instance. That means that if I use Columnstore indexes, I could use 160GB for caching data (128 + 32). What’s more, if I decide I need to use In-Memory OLTP tables, I get an additional 32GB per database to use here. If I have a single database with both features, that means I can use 192GB of RAM for data. If I have two databases, I could get 224GB of data in RAM. Of course, things aren’t quite this simple, and certainly, running out of RAM with In-Memory OLTP tables is bad.

    While these changes might not mean much for some people, I bet lots of customers will appreciate the chance to use these features, and not take any existing RAM away from their SE server. Of course, if you only bought a server with 128GB, you might want to get a little more before you start playing with Columnstore or In-Memory OLTP tables. And if you really need lots of RAM, Enterprise Edition now supports 24TB of RAM. I can’t wait to read a post when someone publishes their experiences on that system.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 6.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Querying My Named Instance in PoSh–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I was looking at some sample code the other day and it looked like this.

    cd sqlserver:\sql\localhost\default\databases

    This allows you to browse the list of databases on your local instance. However, this is for a default instance, which I don’t have on this host. How can I get to a named instance? Usually I connect as .\SQL2016, so where does that fit in PowerShell?

    The format you see above is for the SQLServer provider, which is provided as part of the SQLServer PoSh module (or SQLPS if you haven’t adopted the new cmdlets).

    If I start at the SQLServer:\ node, I see this:

    2017-01-03 15_31_37-powershell

    Let’s drop into the SQL node and see that.

    2017-01-03 15_32_32-powershell

    At this point, I see my localhost and my local computer name. These are really the same thing. We can see that by querying each of them.

    2017-01-03 15_33_12-powershell

    In my case, I like to type fewer characters, so I’ll look at the Plato node. If I change here, I can also change to an instance, as shown here.

    2017-01-03 15_34_21-powershell

    Now I can see the databases by changing to that path and getting the directory.

    2017-01-03 15_34_31-powershell

    Thus, we can see that to query an instance, we use this path:

    SQLServer:\SQL\Host\instance

    If you want to use the default instance, then use “default”.

    A simple query path, but one that some people might wonder about, substituting the named instance for localhost, and not realizing that “Default” means just the default instance.

  • The War Room

    I remember the first time I worked in a large, 10,000+ employee company, and we had a crisis with our systems. A number of us crowded into the cold computer room, gathering around a few workstations and trying to solve the issues. We worked furiously to restore service, with various managers and executives periodically knocking on the locked door wanting status updates, unaware they were slowing us down. Eventually we stabilized things, but it was a chaotic and inefficient environment with too many people involved and more time spent talking and discussing problems than solving them.

    Later I worked in a similarly sized, but more mature company. We had various virus issues, including SQL Slammer. When we realized there was a crisis, we’d contact a director who would convene a crisis team. There were designated individuals from various groups (network, security, database, etc.), each of which had a backup, but only one representative from each area was a part of the team. Those were the only people that participated in the meetings, giving status updates, or taking actions to be distributed to their team. Each was responsible for coordinating the activities of their area with others. Status updates were scheduled regularly with a specific individual posting them. The director leading the crisis would update executives.

    I thought back on these experiences (I was the main person in the database area) when I read about the Amazon war room experiences around launching one of their products. While our crisis management wasn’t quite like this, it was fairly well scripted. There were times that the process didn’t proceed smoothly, but it worked well overall for problem situations. I wish that we had handled deployments a little more formally, though not as strictly as Amazon did. Our deployments didn’t have a large an impact as a product launch, but we certainly could have used more coordination between different groups. I remember no shortage of networking/firewall issues, security mismatches, or missed communications with customers from deployments.

    I’d like to see software deployment to be an easier and simpler process. My hope is that more people learn to code better, and they implement unit tests to ensure they meet requirements and prevent regressions. I want to see automated deployments into staging environments to catch potential issues, and eventually, smooth execution from the client perspective. I want these things to happen for both database and application software.

    A better development, test, and deployment process doesn’t mean that there isn’t a need for strong coordination among everyone involved, and certainly doesn’t mean a crisis team isn’t prepared to respond if there are issues. Thinking ahead to potential issues and ensuring everyone is on the same page helps to smooth any of the bumps that will occasionally crop up. At least, I expect they are only occasional if you follow a good CI/CD process.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Useful #SQLPrompt Tips

    One of the tools that Redgate write is SQL Prompt. This might be my favorite product, and I’m constantly impressed by the improvements that the dev team releases. In fact, I’m also impressed by the rate at which they produce changes, with updates happening every week or two.

    At this point, the product has a lot of features, some of which I’ve forgotten about, and a few that slipped by me. At SQL in the City streamed last year, I watched a short piece from Carly Meichen that taught me a couple things. I’ll write about a few of them, but take a look at this video and see if any of these will help you become more productive.