28 September 2017
28 September 2017

SQL Monitor Quick Tip: Using the SQL static code analysis performance rules

Static code analysis parses the source code, checking the syntax for compliance with a built-in set of rules. These rules are designed to encourage good coding practices and, applied during development and testing work, help you minimize the number of ‘code smells’ that creep into your application and database builds.

For SQL, these ‘smells’ could include problems with table design, such as a missing clustered index; naming problems, such as use of reserved words; or problems with the syntax used in queries, or in routines such as functions and procedures, which could cause performance or security issues.

With Redgate’s acquisition of SQL Code Guard, SQL Monitor 7 has now integrated some of its static code analysis capabilities.

This quick tip will review how SQL Monitor 7 has incorporated SQL Code Guard’s built-in set of Performance Rules for static code analysis. These rules are designed to highlight SQL syntax that could potential cause performance problems, and so indicate ways to improve the overall quality and performance of the workload, over time.

The performance rules

The performance rules cover a range of general best practices for SQL performance, ranging from the need to qualify object names, to avoiding over-reliance of hints, to misuse of cursors:

Figure 1

If your SQL code flouts any of these rules, SQL Monitor will now highlight it automatically. If you haven’t noticed this until now, don’t worry, it’s quite subtle! Let’s look at a couple of simple examples. You’ll need to have SQL Monitor 7 installed, or alternatively you can visit the online demo.

Unqualified objects

Navigate to the Overview screen for one of your monitored SQL Server instances, and look at the query details for one of the Top 10 Queries. If you see any query text with a blue wiggly line underneath it, hover over that line, and a ToolTip will indicate the performance rule that has been contravened. In this case, it’s rule PE002, because the highlighted code fails to specify the schema name for a table.

Figure 2

Failing to qualify the owner of an object can cause performance problems for several reasons. For example, we force the engine to check for the object in two places, first in the authenticated user’s default schema and then in the dbo schema, instead of just one. Also, SQL Server can fail to reuse a perfectly valid execution plan, if for example a query is executed first with and again then without object qualification.

The required action is simply to rewrite such queries to schema-qualify all objects.

Over-reliance on hints

Figure 3 shows the Query Details screen, in SQL Monitor, for a Top 10 query that flouts another static code analysis performance rule, this time relating to the use of hints.

Figure 3

In this example, the developer has chosen to force the query optimizer to implement the INNER JOIN as a Merge join. Given a free hand, for example if we remove the join hint, the optimizer chooses a Nested Loops join.

Occasionally, during development, you will encounter cases where the optimizer appears to have erred in its decision making, and that better performance can be achieved by forcing it to make a different choice, via a query or table or join hint.

It’s rare that hints offer substantial performance benefits. Often their use results simply from the developer not performing enough iterations during testing to rule out statistical variation in performance.

Conversely, it’s common to find cases where hints cause performance problems, especially because over time they prevent the optimizer from making different choices, based on changes in the data, in the distribution of that data, or as a result of improvements in the optimizer with subsequent service packs or new releases.

What’s next?

SQL Code Guard’s static analysis rules are not designed to offer a comprehensive query analysis tool, but to provide a useful first step in determining if there is something obviously wrong with your SQL, which needs further investigation.

At the time of writing, SQL Monitor surfaces only SQL Code Guard’s static analysis performance rules, but there are many other rules that could be included. We want to get your feedback on how useful the feature would be in diagnosing poor performance. Are these rules useful? Is there anything you think we could add/remove? We’d welcome your feedback.

Further reading

Tools in this post

SQL Monitor

SQL Monitor is a SQL server monitoring tool that transforms the way you look at your database. It cuts your daily check to minutes, with a web-based overview of all your SQL Servers.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Integrating SQL Monitor alerts with other systems

    One of SQL Monitor’s most valuable capabilities is raising alerts when bad things happen in SQL Server. The alerts are listed within SQL Monitor but can also be sent out as emails. However, many of our customers have said they’d like to see those alerts in their other systems too. The latest version of SQL

  • SQL Saturday

    SQL Saturday Holland

    SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Redgate are proud to be attending as a Gold sponsor, so stop by and meet the team. Admittance to this event is almost free, all costs are covered by donations and sponsorship except for a €15 lunch fee which

  • Article

    How to monitor backups and other SQL Agent jobs using SQL Monitor

    How many times, when reading a report of some highly public case of data loss or corruption, do you inevitably stumble across a line like: ‘Unfortunately the backup job had not been running for the last three months, and no one noticed’? It’s not enough just to set up jobs to run database backups, or

  • Article

    Using SQL Monitor with SCOM

    In my last blog post, I talked about how to get SQL Monitor to send alerts to Slack and how to send SNMP Traps. It’s easy to make SQL Monitor send SNMP traps, but SCOM is more challenging than most management tools to configure to receive these traps. This walkthrough shows how to configure SCOM

  • University

    Take the SQL Monitor course

    This course takes you from installation and configuration, all the way up to getting the most out of the advanced features in SQL Monitor to help you proactively monitor your SQL Server estate.

    Learn how to explore in-depth issues in your environment, run an analysis, manage alerts, create custom metrics, and more.

  • Forums

    SQL Monitor Forum

    Real-time SQL Server performance monitoring, with alerts and diagnostics