Category: Administration

Brent Ozar explains how it’s hard to explain things without context:

However, outside of that window, you may not have any wait time on CPU at all. If queries are simple enough, and there isn’t enough concurrency, then as we discuss in Mastering Server Tuning, you can end up with SOS_SCHEDULER_YIELD waits with no time attached to them. You wouldn’t notice a CPU problem at all outside of that 8AM window because you don’t have a CPU bottleneck – yet. And to be fair, you probably wouldn’t tune a server in this kind of shape, either.

But when you DO need to tune a server that isn’t running at 100% capacity, picking the right query to tune isn’t just about wait stats: it’s also about which queries you need to be more responsive, and how you need to plan for future load. If your company tries to run a flash sale, and a lot of folks try to check out at the same time, Query T is going to knock your server over. But wait-stats based tools won’t see that coming: they’ll still be focused on Query R, the only one that spends a lot of time waiting on CPU.

Good food for thought.

Comments closed

Michael J Swart has an interesting query for us:

You have excessive WRITELOG waits (or HADR_SYNC_COMMIT waits) and among other things, you want to understand where.

Microsoft’s advice Diagnosing Transaction Log Performance Issues and Limits of the Log Manager remains a great resource. They tell you to use perfmon to look at the log bytes flushed/sec counter (in the SQL Server:Databases object) to see which database is being written to so much.

After identifying a database you’re curious about, you may want to drill down further. I wrote about this problem earlier in Tackle WRITELOG Waits Using the Transaction Log and Extended Events. The query I wrote for that post combines results of an extended events session with the transaction log in order to identify which procedures are doing the most writing.

But there are times when you just want a quick and dirty script, and that’s what Michael has for us today.

Comments closed

Niko Neugebauer looks at database-scoped configuration settings in SQL Server 2019:

Looking at the picture on the left you can see the Database Scoped Configurations available in Sql Server 2019 that I took at the end of December 2019. Mainly we can see a difference of 2 new items in the configurations since the end of the last year – the VERBOSE_TRUNCATION_WARNINGS & LAST_QUERY_PLAN_STATS.

Niko explains what these are and also takes a look at offerings in Azure SQL Database.

Comments closed

Michael J Swart shows how you can create a Perfmon counter which tracks a user-defined value in SQL Server:

Use this to monitor something that’s not already exposed as a performance counter. Like the progress of a custom task or whatever. If you can write a quick query, you can expose it to a counter that can be plotted by Performance Monitor.

This might track queue length, number of items processed, or whatever other time series measure you need but don’t have a UI to display.

Comments closed

Kenneth Fisher takes us through some helpful commands when working with Windows Server (particularly Core edition):

I don’t know how many of you are working with Windows Core these days but personally I think it’s a pretty cool concept. You aren’t supposed to be logging into your servers all that often so why have the extra overhead of Windows? Windows Core removes all of that and comes back with something that looks and feels remarkably like DOS from when I started with computers. That said, most of us, myself included, aren’t used to just having DOS commands any more so here are some helpful tips. I should point out that there is actually more than just DOS commands available and I’ll go over a bit of that here as well.

One other command I find quite helpful is sconfig, which pops up the Server Configuration app. That’s how you can, for example install Windows updates if you don’t have WSUS.

Comments closed

Taryn Pratt points out that replicas of data can contain stale data:

Last week at Stack Overflow we had an internal hack-a-thon, or as we call it, a make-a-thon. I was on the bug-bashing team, which is the team that attempts to fix smallish bugs we haven’t gotten around to fixing, due to other time-constraints. I was tagged to investigate a bug about duplicate badges being awarded because it looked to possibly be an easy fix in SQL. At first glance it looked simple enough, but once I started digging in, I figured out very quickly it wouldn’t be.

It’s an interesting problem, but no solutions in the post. It’s a hard problem.

Comments closed