Press "Enter" to skip to content

Category: Administration

SQL Server Baselines with the TIG Stack

Published 2021-07-15 by Kevin Feasel

Mark Wilkinson combines Telegraf, InfluxDB, and Grafana:

Lots of folks wonder why I would go through the trouble of building out a system when so many vendors have already solved the problem of collecting baseline metrics. The answer at the time was simple: cost. With my setup I could monitor close to 600 instances (including dev) for $3,000 USD per year. That includes data retention of ~2 years! Are there some administration costs as far as my time is concerned? Of course. In the begining things were a little rough as I learned more about InfluxDB, but once things were configured correctly the most work I’ve had to do is to expand the size of the data drive as we started collecting more metrics.

Click through for more info and check out the GitHub repo.

Comments closed

Attaching a Database sans Transaction Log File

Published 2021-07-12 by Kevin Feasel

Chad Callihan lost a transaction log file in a boating accident:

What if you’re moving a database to a new server by detaching and re-attaching database files and someone (not you of course) loses the log file? What if an old database needs to be brought online but the person coming to you only has an mdf file? Can you still attach the database in these scenarios? Let’s find out.

Read on for the answers.

Comments closed

Extending MDF Files without an Outage

Published 2021-07-09 by Kevin Feasel

David Klee creates some files:

Do you have quite large MDF files on your database? By large, I mean hundreds of gigabytes (or larger). Have you ever noticed that your SQL Server disk stall metrics for these data files are much higher than the storage latency metrics exhibited on the underlying operating system layer? It could be that your SQL Server data files are being hammered too hard and you don’t have enough data files to help the SQL Server storage engine distribute the load. We do this for tempdb, right? Why don’t we do this enough for our user databases as well? It’s easy for a brand-new database from day zero, but what about existing databases that have grown out of control with a single data file attached? Let me show you how to adjust this for existing databases without an outage!

Check it out. This is a part of database administration I’d never really thought much about, so it often ended up being a blind spot for me.

Comments closed

Enabling Trace Flags in SQL Server

Published 2021-07-08 by Kevin Feasel

Robert Sheldon performs some level-setting:

SQL Server includes a set of configurable options known as trace flags. You can use trace flags to set server characteristics and control different types of operations. SQL Server offers a wide range of trace flags that let you modify the platform’s default behavior to meet specific requirements. Trace flags can help you when performing such tasks as testing stored procedures, diagnosing performance issues, or debugging complex computer systems. Microsoft Support might also recommend using certain trace flags to address behavior that’s impacting specific workloads. This article explains how to enable SQL Server trace flags.

Click through for the article.

Comments closed

Against sp_hexadecimal and sp_help_revlogin

Published 2021-07-07 by Kevin Feasel

Andy Mallon says it’s time to give up a couple of procedures:

We recently ran into some performance problems with our login sync, which is based on sp_hexadecimal and sp_help_revlogin, the documented & recommended approach by Microsoft.

I’ve been installing & using these two procedures since I started working with SQL Server, back at the turn of the century. In the nearly two decades since, I’ve blindly installed & used these procedures, first on SQL Server 2000, and then on every version since… just because that’s the way I’ve always done it. But our recent performance problems made me rethink that, and dive in to take a look at the two procedures to see if I could do better, which made me realize, OHBOY! WE CAN DO BETTER!!

Read on to understand how.

Comments closed

Is sysname Case-Insensitive?

Published 2021-07-06 by Kevin Feasel

Solomon Rutzky tries Betteridge’s Law of Headlines:

Over time I’ve used a variety of SQL Server versions with a variety of instance-level collations. This has lead me to conclude that sysname had to be all lower-case in some earlier versions if the instance-level collation was either binary or case-sensitive. However, it was not clear exactly which versions and what scenarios truly affected the behavior of sysname name resolution, so I played it safe and continued to always specify that particular data type in all lower-case, even leaving comments in scripts that it must remain as all lower-case (just in case someone goes through and tries to make it upper-case to be consistent with the other data types).

But then, while researching another topic, I recently found the following in some old documentation ( Breaking Changes to Database Engine Features in SQL Server 2005 ):

Read on for the results of Solomon’s archaeological expedition.

Comments closed

Optimizing for Ad Hoc Workloads

Published 2021-07-06 by Kevin Feasel

Chad Callihan explains the importance of a feature:

Anytime you run a query, SQL Server needs to build an execution plan to use as directions for best executing that query. These execution plans can be stored in your plan cache to be reused in the future if that same query is ran. Instead of resources going into rebuilding the plan each time, SQL Server can use the same plan as the previous execution. This is great for queries that run over and over. On the other hand, what if you have a large number of queries that will run once but never again? Plans never to be reused are taking up valuable space in plan cache. If this looks like your workload, consider enabling the Optimize for Ad hoc Workloads feature.

Click through to learn more about the feature. I haven’t seen many (any?) cases where Optimize for Ad Hoc Workloads doesn’t help at least a little on net.

Comments closed

SQL Server: Side-By-Side Install or Direct Upgrade?

Published 2021-06-30 by Kevin Feasel

Kenneth Fisher lays out preferences between two upgrade paths:

Most discussions like this start with the fact that an in-place upgrade is far easier but riskier if anything goes wrong. Side-by-side is more work and you run the risk of forgetting something. But if something goes wrong you just move back to the old instance.

I thought about something that isn’t usually part of the discussion this week while working on a side-by-side migration.

Read on for Kenneth’s insight. My general preference is side-by-side updates on a new server, as that helps get rid of operating system bit rot as well.

Comments closed

Using Ola’s Maintenance Solution on RDS

Published 2021-06-24 by Kevin Feasel

Jack Vamvas takes us through a couple of nuances around using Ola Hallengren’s SQL Server Maintenance Solution on Amazon RDS:

I’ve used the Ola Hallengren Maintenance Solution across various SQL Server environments . I was recently asked by a colleague about how adaptable they are to the AWS RDS SQL Server environment. 

I checked the Ola Hallengren FAQ and there is a comment :

Read on to learn the details.

Comments closed