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

SQL Prompt

Consider using [NOT] EXISTS instead of [NOT] IN (subquery)

It used to be that the EXISTS logical operator was faster than IN, when comparing data sets using a subquery. For example, in cases where the query had to perform a certain task, but only if the subquery returned any rows, then when evaluating WHERE EXISTS (subquery), the database engine could quit searching as Read more

SQL Provision

Masking Dates in a Non-Production Database

Since 2004 I’ve helped more than 800 companies incorporate data masking into their database deployment strategy for non-production databases, mainly using the Data Masker tool, which is part of SQL Provision. The goals are always the same. First, understand what sensitive data, or Personally Identifying Information (PII), exists in the source database. This might not Read more

SQL Monitor

What is the Return on Investment (ROI) of a SQL Server monitoring tool?

The increasing size of SQL Server databases, alongside the growing complexity of SQL Server estates, is making more organizations realize the need for a tool that enables proactive monitoring. Hand-rolled scripts can provide basic information, like wait stats and memory utilization, but that’s often not enough. With the database a key element of business operations, Read more