Press "Enter" to skip to content

Category: Administration

Installing Prometheus Exporter for Windows Clients

Published 2022-04-20 by Kevin Feasel

Jamie Wick exports some data:

Prometheus is an open-source monitoring solution that our Linux team has been using for several years. More recently, we began using it for our Windows-based servers too. (I’ll post a writeup about Prometheus in the future)

One of the obstacles to implementing Prometheus monitoring on our Windows servers was finding and installing an agent. We ultimately decided to use the windows_exporter agent available in the Prometheus Community on GitHub. The exporter is free to use under an MIT license and supports an extensive list of WMI metrics that are grouped into Collectors.

Read on for more info, including ways to avoid common errors.

Comments closed

SQL Audit for STIG Compliance

Published 2022-04-19 by Kevin Feasel

Tracy Boggiano has proof of existence:

Recently I spent months of my lift working on STIG and CIS compliance at my job and one of those tasks was setting up SQL Audit for STIG.  Now, that might seem like a trivial task after all don’t you just have to create an audit and audit specification and let it run.  If only it were that easy, some of the specifications can have a significant performance impact on your system depending on the type of activity happening and if you happened to lucky enough to have a monitoring software setup your will be logging even more data that doesn’t make sense to log.  In addition, on my system we are using SQL replication and that activity due to volume doesn’t make sense to log.  So, let’s walk through my setup and how I got there, the how I got there being the most important part so you can figure out how to use filters to setup a SQL audit that does that kill your performance.

Read on for the audit specification and server audit scripts, as well as some details on how to read from server audits.

Comments closed

Azure Delete Locks

Published 2022-04-05 by Kevin Feasel

Denny Cherry has some advice:

When I’m working in a client’s Azure environment, and they don’t have a delete lock on their production environment I always work on getting them to have one.

This doesn’t always play nicely with everything in Azure, so read on for Denny’s advice when working with Azure Migrate.

Comments closed

Monitoring Kubernetes in Production

Published 2022-04-04 by Kevin Feasel

Samir Behara provides some guidance:

Kubernetes is an open-source container orchestration system for automating the deployment and management of containerized applications. Kubernetes provides capabilities like service discovery, horizontal autoscaling, and load balancing, while ensuring that application configurations are declarative and that systems are self-healing.

In this article, I will explain how to monitor your Kubernetes cluster and implement automated health checks, and discuss the various monitoring tools available.

Read on for some thoughts. In addition to Samir’s links and ideas, I’d also throw in some tools like Rancher to make management a little easier.

Comments closed

Automating Remote Execution via Powershell and SQL Server

Published 2022-03-31 by Kevin Feasel

Eitan Blumin dives into Powershell:

As part of my journey to get better at Powershell, I gave myself a task: Create a project utilizing Powershell at its core, to solve a complicated problem as elegantly as possible.

A complicated problem indeed presented itself: How to remotely control multiple computers without having to log into them? Make them perform any task that could possibly be needed? Control them from one central location? And make the solution as easily scalable as possible?

Read on to see what Eitan has learned and applied.

Comments closed

Troubleshooting Out-of-Memory Errors in SQL Server’s Database Engine

Published 2022-03-31 by Kevin Feasel

Dimitri Furman shows off a DMV:

As part of our efforts to improve database engine supportability, we have added a new dynamic management view (DMV), sys.dm_os_out_of_memory_events. It is now available in Azure SQL Database and Azure SQL Managed Instance and will become available in a future version of SQL Server. If an out-of-memory (OOM) event occurs in the database engine, this view will provide details to help you troubleshoot the problem, including the OOM cause, memory consumption by the database engine components at the time of event, potential memory leaks (if any), and other relevant information.

Read on to learn more about it, as well as a corresponding Extended Event.

Comments closed

Finding Free Space in a SQL Server Filegroup

Published 2022-03-29 by Kevin Feasel

John McCormack does some digging:

I just realised that in all my scripts that I use on a regular basis, I didn’t have one for working out free space in SQL Server filegroups. It’s not something that comes up too often but it’s handy to know. For methods of working out space in individual files, you could refer to this post on mssqltips.

Click through for the query and congrats to John on 100 posts.

Comments closed

The Practical Costs of Index Fragmentation

Published 2022-03-29 by Kevin Feasel

Tibor Karaszi digs into index performance:

See numbers and diagrams at the end, or at the top. I measured a few cases: the difference between no external fragmentation and severe external fragmentation (over 99%). I have both a narrow index and a wide index, and I read one (1), 10,000 and 100,000 rows using index searches (“range scan”). There were obviously no difference reading 1 row so I exclude that from my discussion below. For the other cases the extra time with an extreme level of external fragmentation is (from lowest impact to highest) 7%, 10%, 13% and 32%. The highest number (32%) is when reading many rows from a narrow index, i.e. many rows per page. Again, this is with an extreme level of fragmentation.

What’s interesting is that for the most part, there’s a negligible difference between ~0% internal fragmentation and ~99% internal fragmentation. The follow-on question is, how much are defrag operations costing you in performance and when is the benefit worth the cost?

Comments closed