Voice of the DBA

Tag: administration

  • Moving to Query Store

    In SQL Server 2016, Microsoft introduced the Query Data Store (QDS) as a tool that would capture data about the execution of queries inside of your database. This was a project that had been in the works for a number of years, and one that many of us that were bound by NDA agreements had been following. We were excited by the chance to actually gather some information on the.

    Are you using Query Store? You should be, as this tool will become more valuable over time. I know that there are potential overhead issues (3-5% for most people, but possibly larger). I would argue that the potential for better performance and understanding of our systems outweighs the overhead. After all, if we’re unwilling to devote some resources to measuring our systems, how do we really know what to improve?

    We upgraded the SQLServerCentral servers to SQL Server 2017 this year (2018), and I’ve been wanting to enable the Query Store. I’ve been slightly hesitant with over 75,000 air miles and 5,000 driving miles on the road since the upgrade. Being distracted and out of my routine isn’t the best way to document and carefully observe the effects of a change. Not to mention concerns over data leakage for a company bound by the GDPR. After a little discussion and debate, and my schedule slowing, I’m looking to change that soon.

    I don’t expect that a lot of improvement at SQLServerCentral from changing this, as our third party forums and much of the internal code is batch SQL, and quite a bit generated on the fly. However, there are some stored procedures, and I might be very wrong. While we’re over provisioned with resources to avoid any performance problems, I do expect that we’ll learn a few things. I hope we find places to better tune code, and with some documentation of the process, hopefully some of you out there might spot things our team doesn’t.

    If you’ve got stories of the QDS working well or not well, let us know. Certainly let Microsoft know as well. The QDS is a major part of the SQL Server platform improving in the future and there are enhancements in SQL Server 2019. While I don’t know that the QDS and some of the automatic tuning features remove the need for a data professional to watch a system, I’d like to think they do provide opportunities and insight for how we might better structure and develop applications, as well as help us find better patterns that are useful in our initial database coding.

    If you’ve got stories, Erin Stellato wants to know (and she has a few in the post). If you’re concerned about overhead, read her other post. If you’re confused, we’re working on some articles to help you learn more. Give the QDS a try, especially if you’ve got some less critical systems. Part of our job is learning how to use new tools, and this is one that ought to be on most DBAs ToDo list.

    Steve Jones

    The Voice of the DBA Podcast

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

  • New Database Options

    I saw recently that Azure SQL Database is getting a few more Database Scoped Options for that platform. These are intended to give more control over the way in which the engine behaves, without requiring each database on a server to function the same way. I expect to get to the on-premises product at some point, where they’ll be even more useful as we often might want different behavior for different contexts on an instance.

    While there are advantages to managing all databases in an instance in the same way, I do think that more and more we consolidate databases at times and it’s better to have additional control when needed at the database level. This week, I wonder if there are things that you wish you would have been able to specify for each individual database.

    What options would you want to see added at the database level? 

    I think that many of the options we’ve been given in current versions, as well as the newer ones appearing in SQL Server 2019 are a good start. I don’t know which instance level settings I might want here, but I certainly would like to see newer capabilities at the database level. It would be nice to see the capabilities for jobs and alerts to be set at the database level. Even if this were a part of the Agent subsystem, having the ability to keep these jobs within a database and have the agent read them would be useful.

    Moving more capabilities to the database level gives us more flexibility in separating the workloads for different applications. With the movement of the platform code, and many customers, to Azure SQL Database where the system requires less dependence on an instance, it makes sense to start including more options at the database level. I would guess that at some point most of the settings that we need for manage a system will be included and set at the database level.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Two Types of Performance Counters

    I had an issue where an instance of SQL Server was only showing the XTP (In Memory) performance counters. None of the other SQL Server counters were available, so I followed the procedure I’d written about previously. Once that was completed, I restarted SQL Server and looked.

    No counters, still.

    Hmmm. It was then I scrolled further and realized that I had the SQL Server Agent counters, but not the database engine ones. I looked closer in the Performance folder for my instance and noticed this:

    2018-05-29 21_30_31-Binn

    There are two .ini files. There are

    • perf-<named instance>sqlcrt.ini
    • perf-SQLAgent<instance>sqlagtctr.ini

    These refer to the counters for the database engine and the Agent subsystem. I had unknowingly copied the Agent file for the lodctr.exe call rather than the other one.

    Lesson learned. I had to re-run the procedure with the engine ini file and restart the instance again.

    If you need to add counters, make sure you load both and run a restart, otherwise you might incur more downtime than you expect.

  • No @@ServerName on Linux

    I setup a new instance of SQL Server on Linux some time ago. At the time, the Linux machine didn’t have any Samba running, and no real “name” on the network. As a result, after installing SQL Server I got a NULL when running SELECT @@SERVERNAME.

    The fix is easy. It’s what you’d do if you had the wrong name. You run:

    sp_addserver ‘Ubuntu’, Local

    In this case, Ubuntu was the name I gave my machine under the Samba server, since that’s the OS. I don’t plan on adding more for now, so this is fine.

    After the chance, I couldn’t restart the instance from SSMS, so on the host itself, I ran a

    sudo systemctl stop mssql-server
sudo systemctl start mssql-server

    That completed, and my @@servername is now working.