Press "Enter" to skip to content

Category: Administration

Kafka In Front of ELK

Published 2019-06-10 by Kevin Feasel

Daniel Berman sets up a simple Elasticsearch-Logstash-Kibana (ELK) stack and throws Kafka in front of it:

To perform the steps below, I set up a single Ubuntu 16.04 machine on AWS EC2 using local storage. In real-life scenarios you will probably have all these components running on separate machines.

I started the instance in the public subnet of a VPC and then set up a security group to enable access from anywhere using SSH and TCP 5601 (for Kibana). Finally, I added a new elastic IP address and associated it with the running instance.

The example logs used for the tutorial are Apache access logs.

This is a great walkthrough on setup and basic configuration. If you don’t have something in place to manage logs, the ELK stack is fine.

Comments closed

Finding Recently Created Objects

Published 2019-06-10 by Kevin Feasel

Max Vernon has a script to help us find what new objects now exist on your database:

The code below provides a list of all SQL Server objects created in the past “x” number of days. Dynamic T-SQL is used to construct a query for each database, including system databases. Each query provides the schema, name, and date created for each object listed, along with the object type description.

This looks quite useful for auditing. You might want to filter out tempdb on a real system, though.

Comments closed

Tracking Database Changes with DDL Triggers

Published 2019-06-07 by Kevin Feasel

Lori Brown shows how you can use DDL triggers to track database or instance-level changes:

I have been working on some improvements to some of the regular ways we monitor for important changes.  We always have to be on the lookout for unexpected changes being made in the SQL instances that we monitor since often times we are not the only team who has sysadmin access to the instance.  We are always the best trained to take care of and configure things but we sometimes find that someone makes a change either to the SQL or database configuration without telling us.  We want to know when things like this happen!

I’m a big fan of these. Of course you need to get the code right, as a bad trigger can be devastating but you can get a lot of useful information out of it and figure out who’s hand was in the cookie jar.

Comments closed

Persistent Memory and SQL Server

Published 2019-06-07 by Kevin Feasel

Ned Otter gives us the rundown on Persistent Memory and how it can make life smoother:

SQL 2017 on Windows Server 2016 behaves the same as SQL 2016 on Windows Server 2016 – “tail of the log” is supported. However, there is no support for PMEM with SQL 2017 on supported Linux distributions (except as a traditional block store). Using PMEM with SQL 2019 on Linux supports what’s known as “enlightenment”, which allows us to place data and log files on DAX formatted volumes, thereby reducing latency considerably. SQL 2019 on Linux also support “tail of the log”.

This is one of those areas where understanding Linux versus Windows administration really pays off, at least until Windows Server supports something like enlightenment.

Comments closed

Problems With Renaming SQL Server

Published 2019-06-07 by Kevin Feasel

Jamie Wick goes through some of the problems you might experience when renaming a SQL Server instance:

Monitoring Agents – The first (and easiest) thing to be fixed were the monitoring agents (ie. MS Systems Center Operations Manager). All that took was a quick Redeploy and they came back online.

SQL Agent Jobs – The next issue was SQL Agent Jobs. Many of the maintenance jobs (eg. syspolicy_check_schedule_xxxx) have the server name hardcoded into them. This can be easily remediated by editing the job step properties and replacing the old server name with the new one.

Read on for more problems you might run into. None of these is insurmountable but they are annoying enough that I wouldn’t want to rename servers willy-nilly.

Comments closed

Getting Version Info From dbatools

Published 2019-05-31 by Kevin Feasel

Jess Pomfret shows how you can get your operating system and SQL Server versions from the dbatools Powershell module:

With these dates on the horizon it’s a good time to look at our estate and make sure we have a good understanding of the versions we currently support. I’m going to show you how to do that easily with a couple of dbatools functions. Then, bonus content, I’ll show you how to present it for your managers with one of my other favourite PowerShell modules ImportExcel.

Jess gets bonus points for avoiding the dreaded pie chart at the end.

Comments closed

Parameter Sniffing in the Wild

Published 2019-05-31 by Kevin Feasel

Erik Darling is a parameter sniffing anthropologist:

A while back, I put together a pretty good rundown of this on the DBA Stack Exchange site.

In the plan cache, it’s really hard to tell if a query is suffering from parameter sniffing in isolation.

By that I mean, if someone sends you a cached plan that’s slow, how can you tell if it’s because of parameter sniffing?

Read on to see what Erik does to discover parameter sniffing problems.

Comments closed

Auditing Azure Analysis Services

Published 2019-05-30 by Kevin Feasel

Kasper de Jonge shows how you can audit an Azure Analysis Services cube:

So the question was: how can I see who connected to my AS Azure database and what queries where send? Initially I thought of ways I used to do this in the on premises world. Capture profiler traces or XEvents by writing code and then store it somewhere for processing. It looks like was not alone in these, even the AS team itself had ways to capture XEvents and store them: https://azure.microsoft.com/en-us/blog/using-xevents-with-azure-analysis-services/

But it turns out it is much more smooth, simple and elegant by leveraging Azure’s own products. In this case we will be using Azure Log Analytics. It already documented in the official documentation here.

Click through for a demo.

Comments closed

Plan Cache Sizes

Published 2019-05-29 by Kevin Feasel

Erin Stellato shares information on plan cache sizes:

If you have an ad hoc workload, you’ll often hit the max number of entries before you hit the space limit, particularly if you have the optimize for ad hoc workloads server option enabled, which stores the plan stub for an ad hoc query on initial execution, rather than the entire plan (the plan stub consumes less space, so this is an attempt to reduce bloat).

Click through for the numbers and a couple of options you have around plan cache size.

Comments closed

More on Index Fragmentation

Published 2019-05-29 by Kevin Feasel

Tibor Karaszi revises and extends some remarks on index fragmentation:

In my last blog post, I wanted to focus on the sequential vs random I/O aspect and how that part should be more or less irrelevant with modern hardware. So I did a test that did a full scan (following the linked list of an index) and see if I could notice any performance difference on my SSD. I couldn’t.

That isn’t the end of the story, it turns out. Another aspect is how the data is brought into memory. You might know that SQL server can do “read ahead”, meaning it does larger reads per I/O instead if single-page I/O. I.e., fewer but larger I/O operations. This sounds fine, but what happens when we have fragmentation?

Read on for a situation in which fragmentation does matter.

Comments closed