Press "Enter" to skip to content

Category: Tools

The Origins of SentryOne Plan Explorer

Published 2020-06-19 by Kevin Feasel

Jason Hall gives us a bit of history:

Greg saw a need in his own work, and I was seeing a need in the field with our customers, for a way to go beyond identifying high-impact queries. DBAs and developers needed a way to tune queries surfaced by SentryOne SQL Sentry’s Top SQL without fiddling with a lot of extra tools to get there. We were already building integration with SQL Server Management Studio (SSMS), which included graphical query plans, so the original thought was to extend that integration from SentryOne with a link that opened plans in SSMS from Top SQL in SQL Sentry.

It seemed like an elegant solution that would allow us to reuse some code, but it wasn’t long before Brooke Philpott discovered that we wouldn’t be able to get what we needed this way. That particular part of SSMS wasn’t exposed to us in the manner we needed. Par for the course, we weren’t going to let that stop us from filling the need. Greg and Brooke dug into the problem to discover a mix of documentation, flow controls, and ingenuity that would provide the foundation for building our own query plan visuals.

Read on for the story and a bit about how the product has morphed through the years.

Comments closed

mssql-cli Generally Available on MacOS and Linux

Published 2020-05-08 by Kevin Feasel

Alan Yu announces the general availability of mssql-cli outside of Windows:

We are excited to announce that mssql-cli is now generally available on macOS and Linux.

mssql-cli is an open source and cross-platform command-line tool (CLI) to manage SQL Server on-prem and on the cloud. We are a proud member of the dbcli family of open source command line tools to manage relational databases.

If you are a user of sqlcmd, you will love the interactive and modern design components in mssql-cli. With this release, you will also be able to use mssql-cli in non-interactive scenarios such as scripting and automation. Read on to learn more about how mssql-cli will help improve your productivity through a modern CLI experience.

If you love the command line (or simply need to SSH into a box from time to time), give this product a try.

Comments closed

Poor SQL Formatter’s SSMS Plugin

Published 2020-04-24 by Kevin Feasel

Dave Bland shows us the Poor SQL formatter for SQL Server Management Studio:

So what is PoorSQL Formatter?  Well, as you probably know, formatting is very important when writing T-SQL code.  While we try to format our code as we write it, sometimes the code we need to review was written by someone else and maybe doesn’t follow formatting standards. This is where PoorSQL Formatter will come in handy.  It will help us take the poorly written code and format it in a manner that is more in line with T-SQL formatting best practices.  The best part, it will do the formatting in seconds.

I’ve used that website many a time. It’s not as detailed as I want it to be, but if you just need a quick and dirty way to format a query so that it’s understandable, this does the trick.

Comments closed

The Basics of tSQLt

Published 2020-04-15 by Kevin Feasel

Jess Pomfret walks us through the basics of tSQLt:

Getting started with tSQLt is really easy- you download a zip file, unzip the contents and then run the tSQLt.class.sql script against your development database.

There are a couple of requirements, including CLR integration must be enabled and your database must be set to trustworthy.  These could open up some security considerations, but for my development environment it’s no issue.

This is where I’d say putting the database in a container would be extremely helpful, as then you can destroy it afterward. I’m not sure if that’d work, as SQL Server on Linux doesn’t support unsafe or external access assemblies (and I’m not sure what tSQLt requires there).

Comments closed

Replaying Workloads to a Different Database with WorkloadTools

Published 2020-03-31 by Kevin Feasel

Gianluca Sartori takes us through workload replay ability in WorkloadTools:

One of the features I was asked to implement for WorkloadTools is the ability to replay commands to a database name different from the one recorded in the source workload.

This is something that I had been planning to implement for a while and it totally makes sense. Usually, you have two identical environments for the workload capture and replay, both with the same databases. Sometimes it makes sense to have two different databases as the source and target for the workload, for some particular reasons: resources constraints, ease of testing and so on.

WorkloadTools now supports replaying commands to a different database, using the DatabaseMap property of the ReplayConsumer.

Setting this up is pretty simple, though Gianluca does lay out a caveat.

Comments closed

sqltxls Now Supported Cross-Platform

Published 2020-03-26 by Kevin Feasel

Randolph West has made sql2xls work on Linux and MacOS:

Last year I released sql2xls, a free open-source tool which lets you throw a bunch of scripts into a folder, run them automatically against SQL Server and get the results back in a nicely-formatted Excel file, one tab per script.

A year later, I am happy to announce that the tool now runs cross-platform. You can compile and run it with .NET Core 3.1 LTS to run on Windows, macOS, and Linux. It uses the new Microsoft.Data.SqlClient library from NuGet, as well as the latest beta of ClosedXML.

Read on for a few more tips and go check the project out.

Comments closed

Real-Time Replay with WorkloadTools

Published 2020-03-04 by Kevin Feasel

Gianluca Sartori shows us how to perform a real-time replay with WorkloadTools:

Before we jump to how, I’d better spend some words on why a real-time replay is needed.

The main reason is the complexity involved in capturing and analyzing a workload for extended periods of time. Especially when performing migrations and upgrades, it is crucial to capture the entire business cycle, in order to cover all possible queries issued by the applications. All existing benchmarking tools require to capture the workload to a file before it can be analyzed and/or replayed, but this becomes increasingly complicated when the length of the business cycle grows.

I’m not sure how frequently I’d use real-time replays, but it’s nice to know that it’s pretty easy to pull off with WorkloadTools.

Comments closed

Disaster Recovery for Your Workstation

Published 2020-02-20 by Kevin Feasel

Randolph West explains that disaster recovery isn’t just for your servers:

I just completed a chapter for another book where I spoke about the Recovery Point Objective (how much data you are prepared to lose) and Recovery Time Objective (how long you have to bring your environment up again) after a disaster, and while I never get tired of repeating myself, that’s SQL Server. What happens if your development environment — or workstation — experiences a catastrophic failure?

Or what if, say, you’re on a cruise ship in the middle of the ocean with Internet access and a phone (but no laptop) and your on-call person just died? (I’ll leave this as an exercise for the reader to decide if this really happened.)

The answer is, if we do a careful bit of planning using the same disaster recovery principles we already know, the impact could be minimal. Note that this post assumes that you have Internet access and are using Microsoft Windows as your environment.

Click through for some useful suggestions.

Comments closed

Diagnosing and Resolving CPU Issues

Published 2020-01-29 by Kevin Feasel

Jeff Iannucci has a story to tell:

Anyhow, [Erik] Darling’s tool is designed to provide five data sets showing a quick view of memory and processor usage based on several dynamic management views (DMVs). It’s gives you a quick look into what’s going on when the CPU and/or memory of your SQL Server instance are feeling discomfort.

Why do I mention this? Because little did I know the very next day I would be presented with a fine opportunity to use this tool. And by opportunity I mean a server on fire. (Well, not literally, but still…)

Click through for a description of sp_pressure_detector and a testimonial of its utility.

Comments closed