Press "Enter" to skip to content

Category: Tools

SSMS Layout and Tips

Published 2024-08-05 by Kevin Feasel

Tom Zika talks about SQL Server Management Studio:

I’d like to pin my Object Explorer and Registered Servers to the right and set them to auto-hide for these reasons:

  • I spend most of my time in the Query window and I like that it’s left-aligned
  • I have to resize the Object Explorer based on the level of nesting or length of the object name, sql jobs, etc
  • The Solution Explorer in Visual Studio is also on the right side

Read on for quite a few tips. Tom has a good tip regarding tabs and I’d bring up a separate tool for consideration: Tabs Studio. I paid for a license of it out of my own pocket when I was a database engineer and absolutely loved the control it gave me over displaying SSMS tabs, including having literally dozens of them on the screen at once.

1 Comment

Execution Plan Analysis in Postgres with StatsViaExplainAnalyze

Published 2024-08-02 by Kevin Feasel

Deepak Mahto shares a tool with us:

Tuning SQL is an integral part of most developer’s or DBA’s primary responsibilities. Understanding and dissecting problematic execution plans are critical steps in the overall tuning process.

One key ingredient of SQL performance tuning, along with the runtime plan, is object statistics. Looking only at the execution plan is often insufficient for making solid recommendations; we need a fair understanding of current object statistics.As the saying goes,

Behind every optimized SQL query lies a deep understanding of the underlying object stats.

Click through to see examples of the StatsViaExplainAnalyze tool and how you can get a copy of it from GitHub.

Comments closed

Building a Series of T-SQL Statements via Regular Expression

Published 2024-08-01 by Kevin Feasel

Andy Levy now has two problems:

Earlier this year, I embarked on a bit of a project to tidy up the indexes in a sizeable database. This database has over 900 tables, and there are quite a few indexes which I have long suspected don’t need to exist for a variety of reasons. Some indexes are redundant, others don’t get used, still others could be combined into a single index. By reducing the number of indexes, we can improve write performance in the database, and the size of the database itself. But we have to do it carefully.

Read on for an example of how you can use a regular expression to take text in one form (DatabaseName.SchemaName.IndexName, for example) and convert it into a T-SQL script.

Comments closed

Contoso Data Generator v2

Published 2024-07-26 by Kevin Feasel

Marco Russo announces an updated product:

I am proud to announce the second version of the Contoso Data Generator!

In January 2022, we released the first version of an open-source project to create a sample relational database for semantic models in Power BI and Analysis Services. That version focused on creating a SQL Server database as a starting point for the semantic model.

We invested in a new version to support more scenarios and products! Yes, Power BI is our primary focus, but 90% of our work could have been helpful for other platforms and architectures, so… why not?

Read on to see how you can use this and generate as much data as you want.

Comments closed

Unit Testing a Database

Published 2024-07-17 by Kevin Feasel

Olivier Van Steenlandt builds some tests:

In the past few years, I learned much about collaborative data warehouse development and deployment automatization by using Database Projects (SSDT) and Azure DevOps (and other tools).

I had my fair share of learning curves, making mistakes, and having great learning opportunities. Lately, I started my next journey to learn about Unit Testing for data warehousing/database development.

In this data cookbook (blog post series), we will discover the wonderful world and different flavors of unit testing from a data perspective. In the coming weeks/months, new data recipes (blog posts) will be released bi-weekly.

This first post provides an overview of the topic and includes links to three tools, though SQL Test is an implementation of tSQLt. Of the three, Visual Studio tests are the best of the bunch, though they’re more integration tests than unit tests.

Comments closed

Tips to Identify Poorly-Performing Code

Published 2024-07-16 by Kevin Feasel

Rich Benner shares a few tricks:

So, you have a database or block of code. You’ve been told to “make it fast” but you’re not sure where to start. I’ve got you. We’re going to create a process to follow to ensure we can tune effectively and prove that we’ve made things faster. It’s all about having a structure when performance tuning.

Read on for Rich’s recommendations on how to identify poorly-performing code.

Comments closed

Statistics TIME and IO Analysis and Statistics Reporter

Published 2024-07-16 by Kevin Feasel

Rod Edwards does a bit of analysis:

Again, this is another one of those little bits of functionality that has been present since year dot. Unfortunately, it seems to be forgotten by many in the great game of query investigation.

Being able to see the IO patterns of a query that we’re running to help us see where the heavy lifting is occurring is really useful in allowing us to get the bottom of problems quickly, or see the benefits of our tuning efforts (or otherwise!).

One neat thing I did not know about before was a product called Statistics Reporter, an extension for SQL Server Management Studio that parses the results from time and I/O statistics results. I like Richie Rump’s Statistics Parser website, but the idea of having something built-in is pretty nice.

Comments closed

Modifying Column Return Order in sp_QuickieStore

Published 2024-07-05 by Kevin Feasel

Josephine Bush demands order:

I love QuickieStore, but I wanted some columns to be at the front end of the results returned. Namely, I wanted top_waits, query_sql_text, and query_plan right after database name. This way I don’t have to scroll over to see those values.

Unfortunately, it would appear that there’s no advanced functionality for column ordering like we have for sp_whoisactive. But that didn’t deter Josephine, and you can grab a copy of an updated script that includes columns in this different arrangement.

Comments closed

Exporting Multiple Databases Concurrently via SqlPackage

Published 2024-06-18 by Kevin Feasel

Jose Manuel Jurado Diaz has a script for us:

This week, I’ve been working on a service request case where we need to export multiple databases using SqlPackage. Following, I would like to share my lesson learned to export  simultaneous several databases, saving the export files to the F:\sql folder and the logs of the operations to the F:\sql\log folder.

Few recommendations when performing these exports:

Click through for those recommendations and the script. The cynic in me would add a third tip: make sure your databases are small, or else SqlPackage won’t work so well.

Comments closed

Task Scheduling in PostgreSQL with pg_cron and pg_timetable

Published 2024-06-17 by Kevin Feasel

Radim Marek compares two extensions:

Working with PostgreSQL, and virtually any database system, extends far beyond merely inserting and retrieving data. Many application and business processes, maintenance tasks, reporting, and orchestration tasks require the integration of a job scheduler. While third-party tools can drive automation, you can also automate the execution of predefined tasks directly within the database environment. Although system-level cron might be a starting point, the power of the database system lies in its ability to store all the necessary information alongside your data/schema. In this article, we will explore pg_cron and pg_timetable as two distinct PostgreSQL-specific tools for scheduled task automation.

Read on to learn more about each.

Comments closed