Thinking About Dead Connections

Lonny Niederstadt has a half-baked idea on dead connections:

In SQL Server 2005, a valuable addition was made.  SQL Server would use a “Keep Alive” value specified specifically per instance, rather than the KeepAliveTime specified by the Windows registry.  It defaulted to 30000 ms/30 seconds.

The blog post below explains this new addition, and mentions that the interval for SQL Server will be a fixed 1000 ms/1 second, regardless of the KeepAliveInterval specified in the Windows registry. At the time SQL Server 2005 was introduced, TCPMaxDataRetransmissions from the Windows registries still controlled the maximum number of probes.

Read on for more.

Cloned Database Size

Joey D’Antoni tests how large database files are after running DBCC CLONEDATABASE:

One of the recent feature introductions to SQL Server is dbcc clonedatabase, a feature that lets you create a “data-less” clone of you database. All of the statistics and objects come into your cloned database, however none of the data does. This is perfect for development or performance tuning exercises, where you want all the metadata, but do not want the security risk of dealing with production data.

Recently I had the opportunity to use clonedatabase on a very large database. I was concerned about the size of the data files and how this would impact space on my volumes. Books Online is fairly clear, but I wanted to see for myself.

Click through for the answer.

Switching Instead Of Renaming Tables

Kendra Little has an interesting solution to when you need to swap out an old table for a new version:

This pattern works in SQL Server 2014 and higher. And it even works in Standard Edition of 2014.

Some folks will see the word ‘Switch’ in this pattern and assume the pattern that I’m suggesting is Enterprise Edition only for versions before SQL Server 2016 SP1.

However, oddly enough, you can use partition switching even in Standard Edition, as long as the tables only have one partition.

And all rowstore tables have at least one partition! That happens automagically when you create a table.

Read the whole thing.

Perfmon Counters Of Interest

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.

Disabling Office 365 Groups

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.

Setting Your Maximum Memory

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.

USE HINT In SQL Server 2016 SP1

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.

Basics Of Azure Analysis Services Management

Bill Anton walks through some of the basics of managing an Azure Analysis Services cube:

The quickest win – from an ROI perspective – for Azure AS is the ability to pause the instance during extended periods of inactivity – for example, at night, when there aren’t any users running reports.

This can be achieved via the Suspend-AzureRmAnalysisServicesServer cmdlet we saw in the previous post.

Read on for a few tips of this ilk, including resizing the server.

Turning On SQL Authentication

Kenneth Fisher hits a frequent cause of login failure:

No go. Next thing to check is the password. The error I’m getting does indicate the incorrect password so it’s a distinct possibility. Now when I moved the server principal I made sure to copy the password hash from the old server so the password should be the same. However, I’ve made mistakes before, and odd things happen, so I decided to change the password just in case. Before I did I backed up the original password just in case.

The solution is something that I’ve seen a lot of sysadmins forget to do in their setup processes.

DDL And The Optimizer

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.


January 2017
« Dec