Category: Administration

Allen White shares his list of interesting perfmon counters:

Paging File(_Total)\% Usage

When Windows runs out of memory it takes large chunks of memory and swaps it out to disk, to the Paging File. Unfortunately, the slowest operation in all computing is writing to disk, regardless of the physical media involved, so swapping memory to disk is naturally going to slow down the performance of your system. Keeping an eye on this counter will help you know when you are encountering memory issues, and you can then take action to resolve the conflicts.

There are dozens of interesting counters you could use, but I appreciate that Allen stuck with 15.

John White says, spoilers, you can’t:

The bottom line of all this is that even if you use Office 365, and you think that you have disabled Groups in your tenant, the chances are that you could be in for a surprise. If any of these dependent services are in use, the chances are that you already have several created.

Groups are the bedrock of all new features in Office 365 moving forward – it is therefore a good idea that your organization understand them as soon as possible. Their inevitability is also another strong argument for paying close attention to them. If you are currently discussing whether or not they should be used, I would strongly encourage you to shifting that discussion to how they should best be used.

Read the whole thing if you’re getting into Office 365.

Update:  John has a clarification, showing that it is possible, but it’s not trivial.

Thomas Rushton provides a script to set max memory on a SQL Server instance:

The thing to do, ideally, is to configure the maximum server memory when you build the server; however, sometimes you walk into a place where there are many servers where this hasn’t been done, or are otherwise looking for a quick way to determine what the setting should be. Jonathan Kehayias of SQLSkills blogged about a sensible SQL Server Maximum memory calculation (in response to a post elsewhere about a really dodgy memory config advisor, but I’m not going to link to that…)

What I’ve done below is codify that knowledge into a nice friendly T-SQL query that you can run, below. It makes use of the sys.dm_os_sys_info DMV to get the memory physically in the server; that DMV, though, has changed form between SQL 2008R2 and SQL 2012, the new version reporting physical_memory_kb whereas the previous version had physical_memory_in_bytes. Hence a bit of dynamic SQL nastiness at the start of the query.

Click through for the script, but make sure to tweak it for your environmental peculiarities.

Lori Brown documents the hints available with SQL Server 2016 SP1’s USE HINT syntax:

USE HINT ( ‘hint_name‘ ) Provides one or more additional hints to the query processor as specified by a hint name inside single quotation marks. Hint names are case-insensitive. USE HINT can be utilized without having to be a member of the sysadmin server role.

It’s an interesting list.

Grant Fritchey notes that data definition language (DDL) queries do not appear to go through the query optimizer:

We can check whether or not you’re going to get an execution plan for this in two easy ways. You can capture the estimated plan, or run the query and capture the actual plan. Since most of the time, it’s preferable to work with the added runtime information that an actual plan provides, let’s start there. However, if you execute the query above and try to capture an actual plan, you will not get one. So, let’s capture the estimated plan instead. Here’s what we see:

Not much of a plan really.

Read on for that, and start holding your breath for a new execution plans book.

Monica Rathbun begins a new challenge by describing the importance of one tiny form control:

When I worked at the Port of Virginia, I was a little less experienced in SQL and didn’t notice this lovely little option. I of course failed to set it. Can anyone guess what happened? YEP, we got low on resources in the wee hours of the morning and SQL kicked off an Error 017-Insufficient Resources. Thousands of emails were generated and caused the Exchange server to go down as well as some other issues that arose because of this. The worst part is that all the emails had to finish processing before we could delete them from the system. I think when all was said and done there was well over 250k messages it created.

She also includes a list of important alerts; just make sure to set the delay.