Category: Administration

Bob Ward has a post on automatic tuning in SQL Server 2017:

One of the key points I’ve been making to our customers about SQL Server on Linux is that the core database engine is the same as on Windows. Since Automatic Tuning is built into the core database engine, this feature works exactly the same on SQL Server on Linux.

As you watched my demo of Automatic Tuning for SQL Server on Windows, I used the Windows Performance Monitor to show the workload performance when automatic tuning corrects a query plan regression problem. But what about Linux? Windows Performance Monitor will not work with that platform.

Turns out I have some smart people working with me at Microsoft. I got help from Pedro Lopes from our Tiger Team and the engineering team who built SQL Operations Studio to show automatic tuning using that tool.

• Pedro helped me build scripts that would query the DMV, sys.dm_os_performance_counters, which runs on SQL Server on Linux as it does on Windows (because it is built into the core engine). These scripts store results from this DMV in a temp table, and then query the temp table.

• The folks at SQL Operations Studio showed me how to collect the result set from the temp table in a manner that would allow me to show the data in a Time Series chart in the tool. Kind of like a static perfmon chart of SQL Server counters.

With a bonus shout out to Tracy Boggiano.

Marek Masko shows us how to start and stop SQL Server instances in Windows and Linux from the command line:

On Linux, we don’t have yet named instances, so all commands are executed against default instance. SQL Server can be managed using thesystemctl command.

To check the current state of SQL Server instance you can run this command:

Click through for more.

Adrian Buckman shows us how to re-send an e-mail that was sent through DB Mail:

Sometimes emails from SQL Server go missing, especially when you share an inbox with colleagues. On most occasions it doesn’t always matter as the job that generated the email can simply be re ran to produce the email once again, but what about those emails that contain time specific information? we cannot simple just kick off the job again as it may be coded to evaluate right now and not provide us with the information that was sent 2 hours ago.

I was faced with exactly this issue the other day – i needed the information from the email that was sent at that given time so I wrote a bit of code to get the job done then decided that if I tidy this code up I could make it reusable so here is what I come up with:

Check out Adrian’s helpful script.

Kenneth Fisher has a script to take information out of the error log and put it into a table:

This is one of my favorite scripts. It pulls all of the data from the error log and dumps it into a temp table. I did one version here but it was pretty specific to I/O errors. I also gave some homework to find/build a script like it. So if you didn’t do the homework here is a nice little (more generic) script that I personally find extraordinarily handy. There are two parameters at the top that will restrict the data pulled in, and a query against #LogInfo at the bottom that filters out backupand logon entries since they tend to get in the way of what I’m looking for. That said, if you want to create a report on logons (or backups) you’ll want to modify the query. I do leave #LogInfo open and the end so you can run multiple queries against it.

Click through for the script.

Dave Mason shows how to configure alerts to fire when tempdb is low on disk space:

Naturally, the job runs on a predefined schedule. But how frequently should we check disk/available space for [tempdb]? The temporary nature of [tempdb] makes this a difficult question: objects within aren’t saved from one session of SQL Server to another, and evidence to explain runaway growth or loss of available space may be gone before an assessment can be made. Whatever schedule I decide on, I’ll always wonder if it’s frequent enough (or too frequent).

It’s tempting to “over-schedule” a job’s frequency, perhaps as much as every X seconds. Asking SQL Server “Are we out of disk space?” over and over again doesn’t make a lot of sense to me, though. It reminds me of Bart and Lisa asking Homer “Are we there yet?”until he snaps. Ideally, instead of asking, I want SQL Server to *tell me* when disk/available space is running low.

Read on to see how Dave does this.

Brent Ozar has a few tips to see if you have too much memory:

Sounds impossible, right? The saying goes that you can never be too rich or too thin or have too much memory.

However, there is one good indication that your SQL Server is probably overprovisioned, and to explain it, I need to cover 3 metrics.

1. Max Server Memory is set at the instance level: right-click on your SQL Server name in SSMS, click Properties, Memory, and it’s “Maximum server memory.” This is how much memory you’re willing to let the engine use. (The rocket surgeons in the audience are desperate for the chance to raise their hands to point out different things that are or aren’t included in max memory – hold that thought. That’s a different blog post.)

If you have too much memory, I’d happily borrow a cup of RAM.

Randolph West looks at a baseline for the maximum number of Virtual Log Files for a database:

In a transaction log with too many or too few VLFs we might experience performance issues under a normal workload, as well as during the backup and restore process.

So what is the “right” amount? In customer engagements, I follow a guideline proposed by Glenn Berry of SQLskills.com in his Diagnostic Information Queries, to keep the number of VLFs at or below 200. In my opinion, any number higher than that is cause for concern.

On the other hand, Brent Ozar Unlimited has a popular script called sp_Blitz which proposes a maximum VLF count of 1000. To Brent, a number higher than that is cause for concern.

I tend toward the lower number, but if you have a smoothly-functioning environment and some databases have 700 or 900 VLFs, I probably wouldn’t give it a second thought.