Press "Enter" to skip to content

Category: Administration

Notifications when Admins Connect to a SQL Server

Published 2019-07-12 by Kevin Feasel

Jon Shaulis builds a logon trigger to notify when sysadmins log into his systems:

I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.

Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.

Just make sure you get the trigger right and don’t block everybody from logging in. That’s an awkward situation.

Comments closed

Memory Defaults in SQL Server 2019

Published 2019-07-11 by Kevin Feasel

Randolph West looks at a new settings tab in the SQL Server 2019 installation:

In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias.

SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version.

On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server 2019. Last week we looked at MaxDOP, and this post will specifically look at the Memory tab.

It’s a small change but a nice one.

Comments closed

Auditing Databases in Use

Published 2019-07-09 by Kevin Feasel

Jason Brimhall shows how you can use Extended Events to figure out if that database is still in use:

Here we can see there are indeed some databases that are still in use on this server. If we are looking to retire the instance, or migrate relevant databases to a new server, we have better information about how to go about planning that work. Based on this data, I would be able to retire the ProtossZealot database but would be required to do something with the ZergRush and BroodWar databases. In the case of the ProtossZealot database that is not being used, we now have evidence to present back to the team that the database is not used. It is now up to those insisting on keeping it around to justify its existence and document why it must remain in tact.

Click through for the script Jason used to determine this.

Comments closed

Tracking Remaining Drive Space in SQL Server

Published 2019-07-05 by Kevin Feasel

David Fowler has a new stored procedure for you:

We love xp_fixeddrives here, it’s a quick and simple way to see how much space you’ve got available on your drives. But there are just a couple of things that I really wish it would do better.

Firstly, I’d love to see the total size of the drive and possibly even a percentage of free space left.

Secondly, mount points. If you’ve got any databases that are on mount points, it’s not going to give you any idea of what you’ve got left on there.
This is the reason that I put together sp_drivespace.

Click through for the script. It would be interesting to see if this works on Linux as well.

Comments closed

MAXDOP Configuration on Installation

Published 2019-07-05 by Kevin Feasel

Randolph West notes a change with SQL Server 2019:

SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version.

On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server 2019. This post will specifically look at the MaxDOP tab, and we’ll look at Memorynext week.

I like that they’re adding these things to initial setup; that makes it easier for people to remember that yeah, MAXDOP is important.

Comments closed

Checkpoints Under Simple Recovery

Published 2019-06-28 by Kevin Feasel

Max Vernon shows us how checkpointing works when your database is in the simple recovery model:

Even though the transaction has been rolled back, the log records will not be cleared until a checkpoint occurs. An automatic checkpoint could be triggered by other ongoing transactions being written to the log, or a manual CHECKPOINT statement could be executed. However, for a database that is not seeing frequent transactions, the log may stay nearly full for an extended period of time. This scenario might be seen often during development where there are a very limited number of transactions being generated. 

Read the whole thing. Just because you’re in simple recovery mode doesn’t mean the transaction log becomes any less useful.

Comments closed

Monitoring Big Data Clusters

Published 2019-06-27 by Kevin Feasel

Mohammad Darab continues a series on Big Data Clusters:

There are many ways to view the health of your Big Data Cluster. As of CTP 3.0, there are kubectl commands, mssqlctl commands as well as dashboards. For the sake of this series, I will focus on the dashboards. I will blog about some of the useful kubectl and mssqlctl commands in later posts.

The first dashboard is the Microsoft Cluster Administration portal (see below snapshot). This is a view into the Big Data Cluster Controller. As you can see from the image below, the Overview pane shows the Controller, Master Instance and all the pools. On the left hand side you can see more details. If you click on the “Service Endpoint” option, you will see a list of endpoints that you can bookmark.

Something I appreciate is that Microsoft thought ahead on what the monitoring story should look like rather than waiting until the end and slapping something together.

Comments closed

Breaking Down the MAXDOP Guidance Change

Published 2019-06-19 by Kevin Feasel

Joe Obbish digs into Microsoft’s new guidance for maximum degree of parallelism:

I’ve heard some folks claim that keeping all parallel workers on a single hard NUMA nodes can be important for query performance. I’ve even seen some queries experience reduced performance when thread 0 is on a different hard NUMA node than parallel worker threads. I haven’t heard of anything about the importance of keeping all of a query’s worker threads on a single soft-NUMA node. It doesn’t really make sense to say that query performance will be improved if all worker threads are on the same soft-NUMA node. Soft-NUMA is a configuration setting. Suppose I have a 24 core hard NUMA node and my goal is to get all of a parallel query’s worker threads on a single soft-NUMA node. To accomplish that goal the best strategy is to disable auto soft-NUMA because that will give me a NUMA node size of 24 as opposed to 8. So disabling auto soft-NUMA will increase query performance?

Joe takes a careful look at the documentation and brings up some really good questions.

Comments closed

Offloading Code Review Burdens with Automation

Published 2019-06-14 by Kevin Feasel

Ed Elliott argues that automation and testing can make code reviews easier:

OK so if we break this down into what a DBA should be doing as part of a code review:

– Ensure formatting is correct and any standards followed
– Have they introduces a SQL injection vulnerability?
– Consider any side effects of the actual change, for instance altering a clustered key on a 1 billion row table will take time – is this possible on a live system?
– Consider any performance effects – is this more prone to tempdb spills? How about deadlocks? Is the plan going to be terrible?
– Is the code going to do what the developer wants? Do they have the update statement correct in the merge statement?

That’s a lot, how can we help developers understand enough so that they can review their own code and cause fewer issues in production?

I believe this is a bit aspirational. Nevertheless, if you do get there, life gets easier.

Comments closed

Tracking xp_cmdshell Executions

Published 2019-06-14 by Kevin Feasel

Jason Brimhall shows how you can see when someone calls xp_cmdshell, including the call details:

What was the wait_type? Well, the obscure wait_type was called PREEMPTIVE_OS_PIPEOPS. What causes this wait? As it turns out, this is a generic wait that is caused by SQL pipe related activities such as xp_cmdshell.

Knowing this much information however does not get us to the root cause of this particular problem for this client. How do we get there? This is another case for Extended Events (XEvents).

Read on for two ways to approach this, both using Extended Events.

Comments closed