Press "Enter" to skip to content

Category: Tools

The Hunt for Red Logtober

Published 2023-08-02 by Kevin Feasel

Erik Darling has a new stored procedure and a fancy Scottish accent:

SQL Server has incredibly verbose error logs, and sifting through them for the highlight reel can be a pain. They’re often full of messages that you aren’t actionable.

  • Failed logins
  • Successful logins
  • Successful backups

Making matters worse is that when you open large log files, the response time for loading all those messages can be painful, and even time out.

But Erik has a nice stored procedure to filter out the chaff. Read on to learn more about it.

Comments closed

Running Queries across SQL Server Databases

Published 2023-08-01 by Kevin Feasel

Ed Pollack has a query to run in many places:

A challenge that reappears periodically in the world of databases (especially database management) is the need to run code on a subset of databases and to do so in a nuanced manner. Some maintenance or metrics collection processes can be simply run against every database on a server with no ill-effect, but others may be app-specific, or need to omit specific sets of databases.

This article dives into how to create and customize your own solution, tackling everything from filtering databases to validating schema elements to error-handling.

It does surprise me a bit that there’s no officially supported built-in solution for this. I’ve used sp_foreachdb a lot because it’s readily available, free, and works better than sp_msforeachdb, though there are several available options for this task.

Comments closed

Finding a Particular Query Plan in Query Store’s UI

Published 2023-08-01 by Kevin Feasel

Andrea Allred does a search:

I have this problem where I want to see how a newly released query is performing, but it may not be bad enough to make any of the canned reports that SQL Server provides in QueryStore. I was able to look up the plan handle, but always struggled to get to the query id for QueryStore, until now.

Click through for a query to retrieve the query ID and then how to find data on that particular query. I’d also recommend QDSToolbox for more detailed query analysis.

Comments closed

Trying Fabric Data Wrangler

Published 2023-07-31 by Kevin Feasel

Reza Rad looks at a new tool:

There is a tool (or you can consider it as an editor) in Fabric for data scientists. As a data scientist, you must work with the data, clean it, group it, aggerate it, and do other data preparation work. This might be needed to understand the data or be part of the process you do to prepare the data and load it into a table for further analysis. Data Wrangler is a tool that gives you such ability. You can use it to transform data and prepare and even generate Python code to make this process part of a bigger data analytics project.

Data Wrangler has a simple-to-use graphical user interface that makes the job of a data scientist easier.

Read on for a video as well as a demo in written format.

Comments closed

New Script: sp_HealthParser

Published 2023-07-25 by Kevin Feasel

Erik Darling has a new script for us:

I recently found myself in the midst of a client issue that lead me to need a bunch of corroborating data from the system health extended event session.

There’s hardly any good documentation on it, and even fewer well-written queries or resources for parsing data out of it.

So now I’m making it easy for you, because I care about you more that Microsoft does.

Click through for a link to the script and to get an idea of what it provides.

Comments closed

sp_HumanEventsBlockViewer Updates

Published 2023-07-24 by Kevin Feasel

Erik Darling has another update:

In this post, I’m going to talk about a couple cool changes to sp_HumanEventsBlockViewer, a procedure I wrote to analyze the blocked process report via Extended Events, and wish I had given a snazzier name to.

You see, when I wrote it, I pictured it as a utility script for sp_HumanEvents, which will set up the blocked process report and an extended event. But it turns out I use it a lot more on its own.

Read on for Erik’s update, including a neat trick around using an aggregate within a window function to generate ordering.

Comments closed

Updates to sp_QuickieStore and sp_PressureDetector

Published 2023-07-21 by Kevin Feasel

Erik Darling has been busy. FIrst, sp_QuickieStore:

The first thing on the list that I want to talk about is the ability to cycle through all databases that have Query Store enabled.

If you have a lot of databases with it turned on, it can be a real hassle to go through them all looking for doodads to diddle.

Now you can just do this:

Next, sp_PressureDetector:

I added  high-level disk metrics similar to what’s available in other popular scripts to mine. Why? Sometimes it’s worth looking at, to prove you should add more memory to a server so you’re less reliant on disk.

Especially in the cloud, where everything is an absolute hellscape of garbage performance that’s really expensive.

Click through for both sets of updates and thank Erik for his willingness to give so much to the community.

Comments closed

Analyzing Postgres Logs with pgbadger

Published 2023-07-10 by Kevin Feasel

Anthony Nowocien takes us through a useful tool:

This week, #PGSQLPhriday is hosted by Alicja Kucharczyk. Every month, one community member proposes a new subject to this monthly blogging event and let the world (or your family/friends/neighbors if you prefer) know all about pgBadger. It’s a tool to analyze your PostgreSQL logs and present you a nice web report.

If you like some history, it has been developed by Gilles Darold since more than 11 years, as v1.0 came along on June the 10th in 2012. At this time, pgfouine was the main log analyzer and the complete Perl rewrite was greatly performance influenced. In V4, it started to have its current look, by embarking the Bootstrap library and fonts.

Click through to see what pgbadger does and an example of how it makes log analysis understandable.

Comments closed

Deploying a SQL Server Container via go-sqlcmd

Published 2023-07-06 by Kevin Feasel

Jes Schultz builds a container:

In the past, you may have downloaded and installed SQL Server Developer Edition on your computer to test features, write queries, and have a sandbox. Do you want a more lightweight development environment that’s quicker to install and use? Enter Docker or Podman, sqlcmd, and SQL Server containers. 

Our modern sqlcmd gives you the capability to query your existing SQL Server and Azure SQL Database instances. It also introduces new features such as creating containers in one line of code, using an existing backup file to restore a database when the instance is created, and opening Azure Data Studio for data exploration in one command. Here is how you can do this end-to-end. 

Click through for the demo.

Comments closed

Finding Memory Grant Details in sp_WhoIsActive

Published 2023-06-20 by Kevin Feasel

Erik Darling has a video for us. There’s no graf that I can include here, so I’m stuck having to come up with my own explanation…

This is an interesting video covering a fairly new feature in sp_WhoIsActive, as well as giving us some good information around the numbers meaning pages rather than (something)bytes, yet the memory_info column gives us results in kilobytes.

Also, be sure to grab version 12 of WhoIsActive.

Comments closed