SQL Monitor

Custom Metrics for Detecting Problems with Ad-hoc Queries

Whatever development methodology you use, you must check on the quality of the code before releasing a version of a database. This isn’t just a general check for ‘technical debt’ or code smells; you must also make sure that queries are not hogging resources on the server. A common crime is the unnecessary overuse of Read more

SQL Toolbelt

Effective Database Testing with SQL Test and SQL Cover

A well-established technique for improving application code quality, during software development, is to run unit tests, in conjunction with a code coverage tool. The aim is not only to test that your software components behave as you would expect, but also that your suite of tests gives your code a thorough workout. Errors encountered within Read more

Data Masker

Approaches to masking email addresses

A recent Data Governance Survey conducted by Redgate of over 500 SQL Server professionals showed that 61% of respondents were using Production data for non-production workflows; a process that is often seen as necessary for the reliability of development, testing and other similar workflows. However, with data privacy legislation such as the GDPR, POPI and Read more

SQL Monitor

Using SQL Monitor to Detect Problems on Databases that use Snapshot-based Transaction Isolation

If you’re using SQL Server’s Read Committed Snapshot Isolation level (RCSI), to avoid long waits for a blocked resource, caused by transactions being held open for too long, then you’ll want to monitor for possible side effects. Usually, the overhead of using RCSI is not significant compared to the performance benefits of alleviating blocking, but Read more

SQL Prompt

When to use the SELECT…INTO statement

We can use SELECT…INTO in SQL Server to create a new table from a table source. SQL Server uses the attributes of the expressions in the SELECT list to define the structure of the new table. Prior to SQL Server 2005, use of SELECT…INTO in production code was a performance ‘code smell’ because it acquired Read more

SQL Prompt

Beyond Formatting: Improving SQL Code using SQL Prompt Actions

In this article, I’ll discuss how I use the SQL Prompt actions that you can apply as part of the Format SQL command. These actions are designed to help improve the overall quality of your SQL code, in various subtle but meaningful ways, such as qualifying object names, standardizing the use of aliases, adding semicolons Read more

SQL Monitor

Monitoring and Troubleshooting Deadlocks with SQL Monitor

Deadlocks can occur when two or more sessions inside of the database engine are waiting for access to locked resources held by each other. Technically, a deadlock can be viewed as a circular locking chain, because every process (SPID) in the blocking chain will be waiting for one or more other processes in that same Read more

SQL Prompt

Driving up database coding standards using SQL Prompt

Most of us in the data management industry will have learned to adapt, in recent years, to ‘agile’ development and deployment practices. Many organizations have invested heavily in the tools and processes they hope will allow them to deliver new functionality to users more frequently and reliably, while also maintaining quality standards. To achieve this, Read more

SQL Monitor

Redgate’s support for Azure SQL Database Managed Instances

Today Microsoft released the public preview of Azure SQL Database Managed Instances – an exciting new option for running SQL Server workloads in the cloud. I’m pleased to say that initial support for this new offering is already available across the development tools in Redgate’s SQL Toolbelt, as well as in SQL Monitor. This support Read more

SQL Monitor

Investigating problems with ad-hoc queries using SQL Monitor

There is nothing wrong with the principle of using ad-hoc queries; one can use them occasionally and perfectly legitimately. However, when ad-hoc queries run as part of a processes that does database operations iteratively, row-by-agonizing-row, they can be one of the most unremitting ways of sapping the performance of a SQL Server instance. You need Read more