Press "Enter" to skip to content

Category: Administration

Keep .Net Framework Up To Date

Published 2015-12-16 by Kevin Feasel

Allan Hirt with a public service announcement:

Microsoft recently published an official .NET team blog post reiterating that .NET Framework versions 4, 4.5, and 4.5.1 will no longer receive security updates, support, or hotfixes as of January 12, 2016. This was first announced back in August of 2014, so it’s not like this is new news, but I can say from experience virtually no one is talking about it. MS’ new post talks more about the upgrade path. To sum it up, you need to install .NET Framework 4.5.2, 4.6, or 4.6.1 to be considered supported when it comes to your .NET Framework version. Security is a real issue for many, and those responsible may not know that your version of .NET Framework could be a possible attack vector. Is your security team aware of this impending problem? How will this affect your version matrices (you do have those, right?)?

This is a cross-cutting concern, and I know a majority of database administrators aren’t directly responsible for .Net Framework patches, but work with whoever is responsible and keep them up to date.

Comments closed

Measuring IOPs

Published 2015-12-16 by Kevin Feasel

Joey D’Antoni shows us how to measure IOPs (I/O Per Second) on a SQL Server instance/server:

That handy SQL Server:Resource Pool Stats counter and it’s Disk Read IO/sec and Disk Write IO/sec provide you with the data you need to give your SAN admin. In this screenshot this is an Enterprise Edition instance, and you can see my resource pools on the left side—so if you are using resource governor, you could use this to classify IO workload by application for potential chargeback situations.

Very useful, and when combined with Resource Governor, can help you throttle I/O effectively (as opposed to wildly flailing in the general direction of a fix).

Comments closed

Removing Bad Execution Plans

Published 2015-12-07 by Kevin Feasel

Andrea Allred shows one way of removing a bad query plan:

If you click on the query_plan link, you can see what the plan looks like.  After you have reviewed it and determined the plan is bad then you can paste your plan handle over the one below to remove it from the proc cache.

DBCC FREEPROCCACHE normally is something you don’t want to play with in production, but this is narrowly focused enough not to harm you down the line.

Comments closed

Windows Server Licensing Changes

Published 2015-12-04 by Kevin Feasel

Allan Hirt prepares us for a licensing letdown:

Say Hello to Core-based Licensing for Windows Server

This is the one that may annoy most folks. Like SQL Server, Windows Server 2016 licensing will be core-based, including the Core Infrastructure Suite SKU. Historically, Windows pricing has been MUCH lower than SQL Server, and no prices have been announced. So before anyone has a conniption, let’s see what the core pricing will be based on the chart shown on page 2, there are cases where the cost may be the same as it is today.

I’m now curious about how many people will hit a wall with Windows Server editions like we’ve seen with SQL Server 2008 R2.

Comments closed

SQL Server Startup Parameters

Published 2015-12-02 by Kevin Feasel

Shawn Melton shows us how to modify SQL Server startup parameters using Powershell:

Low and behold the StartupParameters property is one that can be read and set. So how do you set it? Well the one thing to remember is you DO NOT need to remove what is already in that property because IT WILL BREAK YOUR SERVER!

Let me be clear, setting the property means you need to append to what is already there, so don’t just go setting it equal to something like “-T1118”. Doing this will remove the required parameters to start SQL Server itself, and no it will never warn you of this…so proceed at your own risk.

Read the instructions; otherwise, you can mess up your installation, and that’d be a bad thing.

Comments closed

Powershell Service Management

Published 2015-12-02 by Kevin Feasel

Mike Fal gives us a pattern for managing SQL Server services with Powershell, WMI, and SMO:

I have built a function around using the second method that makes handling this process a little easier. Also, because I’m not a fan of passing passwords in plain text, I built the function to take a PSCredential object to keep my account information secure. In order to spare you the wall of text, you can view the full function on my GitHub repository.

The function can be loaded through a variety of methods, but once it is loaded calling it is simply a matter of creating the credential for the service account and calling the function

Good stuff.

Comments closed

SSPI Context

Published 2015-12-01 by Kevin Feasel

Sean McCown goes into fixing one example of the “Cannot Generate SSPI Context” error:

Now, this was just a quick tutorial on how to manage SPNs.  This hole can go pretty deep.  Here’s a decent link on MSDN for troubleshooting SPNs.  I don’t think I like their troubleshooting because they don’t really do a good job of showing you the commands, but it’s a good explanation of the problem, what an SPN is, etc.  If I remember correctly it’ll also help you choose the right SPN.

This is a classic example of a bad Microsoft error.  In this case, it’s bad because there are multiple root causes for the same error and because the message itself is unhelpful.

Comments closed

Query Store Works With Recompile

Published 2015-11-30 by Kevin Feasel

Kendra Little shows us something awesome about Query Store and OPTION(RECOMPILE):

YAY! For all my queries that were run with RECOMPILE hints, I can see information about how many times they were run, execution stats, their query text and plan, and even information about compilation.

And yes, I have the execution plans, too — the “CAST(qsp.query_plan AS XML) AS mah_query_plan” totally works.

This is great news.  Query Store is going to be a big feature for DBAs.

Comments closed

Storage Spaces Direct

Published 2015-11-25 by Kevin Feasel

Glenn Berry introduces us to Storage Spaces Direct:

One of the more exciting new features in Windows Server 2016 is called Storage Spaces Direct (S2D), which enables organizations to use multiple, clustered commodity file server nodes to build highly available, scalable storage systems with local storage, using SATA, SAS, or PCIe NVMe devices. You can use internal drives in each storage node, or direct-attached disk devices using “Just a Bunch of Disks” (JBOD) where each JBOD is only connected to a single storage node. This eliminates the previous requirement for a shared SAS fabric and its complexities (which was required with Windows Server 2012 R2 Storage Spaces and SOFS), and also enables using less expensive storage devices such as SATA disks.

This sounds like a technology with interesting potential, and not requiring SAS disks will make it more likely to be adopted.

Comments closed