Use SQL database backups to reduce the performance impact of heavy reporting

Depending on your particular environment, database reporting can have a heavy impact on the database performance, can execute queries which run for dozens of minutes or both. This is usually the case with reports which require complex queries having multiple calls to SQL Server’s aggregate functions to be executed against very large data sets. The effect on the database performance is particularly severe in scenarios where the production database stores data which is changed often – as data modification operations require exclusive locks, in order to preserve the integrity of the database, SQL Server will go ahead with the data modification operation until the query initiated by the report is still running. This increases the chances of a deadlock occurring; especially in cases where another set of data modification instructions, dependent on the ones which are waiting for the reporting to finish has already been applied. Therefore, reporting can cause performance degradation in a production environment. So, how can reporting be optimized to prevent such a heavy load on production databases?

April 4, 2013

SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis

Nobody likes to read a wall of text, even when it’s just plain text. When it comes to reading code, the problem is even bigger. Code can have different formatting styles, which could make a job either easier or more difficult. It can make code difficult to decipher and understand. A clean and neat SQL is read faster than an inconsistently written SQL; SQL reviewing and troubleshooting is more efficient; joint development efforts are more effective; handing off projects from one team to another is smoother.

How to make SQL code readable, so that it is enough just to skim through the code and get the general idea.

It’s the SQL formatting that makes the difference.

The best solution is to create a personalized style guide for SQL. The problem, but at the same time the advantage that can be utilized, is that there are neither style nor formatting standards, and it’s all a matter of preference. There’s no “best” SQL formatting style, so it’s up to user and team to create set of formatting rules that will be enforced to all SQL code that is worked on. The best option that can be done for SQL readability is to have the whole team always enforce the set standard – that way, everyone will be familiar with the formatting used in the company, and will be able to quickly read the code Here are some basic guidelines that can be used when setting the SQL formatting rules:

  • Use comments to describe what SQL does. If existing SQL code is modified, add the author’s name, the date, describe the modifications, and avoid questions. Don’t overdo it and comment on the obvious
  • Put each major SQL statement on a new line:
USE Adventureworks2012;

SELECT
         S.Businessentityid,
         E.Jobtitle
FROM Sales.Salesperson
WHERE S.Businessentityid > 10
  • Put SQL keywords such as SELECT and FROM, built-in function names such as SUM, AVG, CASE, and data types such as INT, CHAR, NTEXT in the uppercase:
CREATE TABLE Sales.Customer(
        Customerid INT IDENTITY(1, 1) NOT NULL,
        Personid INT NULL,
        Storeid INT NULL,
        Territoryid INT NULL,
        Modifieddate DATETIME NOT NULL,
        CONSTRAINT Pk_Customer_Customerid PRIMARY KEY 
        CLUSTERED(Customerid ASC)
            WITH(PAD_INDEX = OFF, 
            STATISTICS_NORECOMPUTE = OFF)ON [Primary])

ON [Primary]
  • Use CamelCase capitalization and do not separate name parts with underscores: TableName, instead of Table_name, or use lower case and underscores to separate name parts: table_name
  • Set standard abbreviations for frequently used objects, such as tbl for tables, or sp for stored procedures
  • Use single quotation for characters, strings, binary and Unicode
  • Set a rule for naming aliases
  • Use indenting to align wrapped long lines
  • Use parentheses in complex mathematical expressions
  • Be consistent with indentation – use either tab or space
  • Don’t avoid using line breaks to improve readability
  • Code grouping – keep the lines that execute a certain task in separate code blocks
  • Limit line length – wrap the lines longer than approximately 80 characters

And here is what should be avoided:

  • Deeply nest the statements
  • Use ambiguous names – be consistent, use simple and clear naming. Avoid excessive abbreviations
  • Write a flow of execution which is difficult to follow

Once the rules are set, find an easy way to enforce them. Manually applying all the rules is a time-consuming process where mistakes are very likely to happen.

Use our SQL code formatter as a solution here.

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in which formats and refactors SQL utilizing over 200 formatting options. It can be used to distribute and enforce SQL formatting rules among team members.

  1. In SQL Server Management Studio or Visual Studio’s main menu, click ApexSQL menu
  2. From the ApexSQL Refactor menu, click the Options command to initiate the Options window:

C:\Users\Zivko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\FormatT01xa.png

  1. Set the specific option as described in the article below
  2. Preview the option’s effect on the current query or a built-in example:

C:\Users\Zivko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\Template02xa.png

  1. In SQL Server Management Studio or Visual Studio’s main menu, select the ApexSQL menu, and choose the ApexSQL Refactor. Click the Format SQL command, and apply the default or custom formatting to SQL code in the current Query window

Capitalization

Before setting the capitalization standards, make sure that a database doesn’t have case-sensitive collation, as this can cause big problems when playing with capitalization. The rules should be set for:

  • Reserved/key words (e.g. SELECT, DECLARE, CREATE, ALTER). Upper case is recommended
  • Data types (int, nvarchar, varchar)
  • Object names – identifiers (table, view and stored procedure names)
  • System and built-in functions (SUBSTRING, ABS, LEFT)
  • Variables

C:\Users\Zivko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\OptionCapitalization03xa.png

Style 1 – Keywords, types and identifiers in lowercase:

create table humanresources.department(
             departmentid smallint identity(1, 1) not null,
             name dbo.Name not null,
             groupname dbo.Name not null,
             modifieddate datetime not null,
             constraint pk_department_departmentid 
primary key clustered(departmentid asc)
                 with(pad_index = off, allow_page_locks = on)on [primary])

on [primary];

Style 2 – Keywords in upper case, types in lowercase, identifiers in proper case:

CREATE TABLE Humanresources.Department(
             Departmentid smallint IDENTITY(1, 1) NOT NULL,
             Name dbo.Name NOT NULL,
             Groupname dbo.Name NOT NULL,
             Modifieddate datetime NOT NULL,
             CONSTRAINT Pk_Department_Departmentid 
PRIMARY KEY CLUSTERED(Departmentid ASC)
                 WITH(PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON)ON [Primary])

ON [Primary];

Indentation

Indenting makes SQL easier to follow, as it makes it visually structured.

It’s recommended not to indent the first line in a multiple line statement, so it would be clear where the statement starts. Make sure that the SQL left margin is indented per the section nesting.

Be consistent with indenting – set up several spaces to use for a tab, or always use the same number of spaces. Using tabs instead of spaces is easier, as it requires less clicks, but when cutting and pasting of SQL code is started, spaces are easier to handle.

First, set whether to use the space or tab for indenting:

C:\Users\Zivko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\General04xa.png

ApexSQL Refactor provides multiple options for indenting arithmetic, logical and comparison operations, schema statements, data statements, and column lists. Check out the Expressions, Statements, Joins, Lists, Flow control and Obfuscation tabs to see all available options:

C:\Users\Zivko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\Expressions05xa.png

Style 1 – each clause begins on a new line, none is indented:

select s.businessentityid, e.jobtitle
from sales.salesperson as s
inner join humanresources.employee as e on e.businessentityid = 
s.businessentityid

Style 2 – each clause and each set of arguments begins on a separate line. Each statement subordinate to SELECT is also indented:

SELECT
         S.Businessentityid, E.Jobtitle
  FROM
         Sales.Salesperson
        AS S
         INNER JOIN
         Humanresources.Employee
                       AS E
              ON E.Businessentityid = S.Businessentityid;

Style 3 – indent all list items equally, together with all columns and table names

SELECT
         S.Businessentityid,
         E.Jobtitle,
         S.Salesquota,
         S.Salesytd,
         S.Saleslastyear
  FROM
         Sales.Salesperson
         AS S
         INNER JOIN
         Humanresources.Employee
         AS E
         ON E.Businessentityid = S.Businessentityid;

Parentheses

Parentheses can be used in different contexts – in SELECT statements, function parameters, DDL statements, mathematical expressions, etc.

The opening parenthesis in an expression can be placed on a new line and followed by an immediate line break. The closing parenthesis can also be placed on a new line, with or without a line break afterwards::

C:\Users\Zivko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\ApexSQLDefault06xa.png

Style 1 – no line breaks after opening or closing parentheses:

CREATE TABLE Humanresources.Department (
        Departmentid SMALLINT IDENTITY ( 1, 1 )
                                   NOT NULL,
        Name dbo.Name NOT NULL,
        Groupname dbo.Name NOT NULL,
        Modifieddate DATETIME NOT NULL,
        CONSTRAINT Pk_Department_Departmentid 
        PRIMARY KEY CLUSTERED ( Departmentid ASC )
           WITH ( PAD_INDEX = OFF, 
           STATISTICS_NORECOMPUTE = OFF, 
           IGNORE_DUP_KEY = OFF, 
           ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON ) ON [Primary] )

ON [Primary];

Style 2 – a line break after the opening parenthesis:

CREATE TABLE Humanresources.Department (
            Departmentid SMALLINT IDENTITY ( 1, 1 )
                                       NOT NULL,
            Name dbo.Name NOT NULL,
            Groupname dbo.Name NOT NULL,
            Modifieddate DATETIME NOT NULL,
            CONSTRAINT Pk_Department_Departmentid 
            PRIMARY KEY CLUSTERED ( Departmentid ASC )
               WITH ( PAD_INDEX = OFF, 
               STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, 
               ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON ) 

    ON [Primary]);

Style 3 – line breaks after both opening and closing parentheses:

CREATE TABLE Humanresources.Department
(
      Departmentid SMALLINT IDENTITY 
      ( 
            1, 1
      ) NOT NULL,
      Name dbo.Name NOT NULL,
      Groupname dbo.Name NOT NULL,
      Modifieddate DATETIME NOT NULL,
      CONSTRAINT Pk_Department_Departmentid 
      PRIMARY KEY CLUSTERED 
      ( 
            Departmentid ASC 
      )
      WITH 
      ( 
            PAD_INDEX = OFF, 
            STATISTICS_NORECOMPUTE = OFF, 
            IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS = ON, 
            ALLOW_PAGE_LOCKS = ON
      )
) ON [Primary]

Comments

Comment types that can be used in SQL are:

  • Block comments /*…*/
  • Inline comments —

Here are a couple of guidelines for comments that should be followed:

  • Write comments clearly and consistently
  • Don’t add too many comments
  • Check whether or not a developer’s name and revision history is really needed

C:\Users\Zivko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\Comments07xa.png

-- Select top sales agents 
SELECT
         S.Businessentityid,
         E.Jobtitle,
         S.Salesquota,
         S.Salesytd,
         S.Saleslastyear
  FROM Sales.Salesperson
  WHERE S.Businessentityid > 10
  ORDER BY
         S.Salesquota;
 /*add them to the bonus table*/

Using ApexSQL Refactor, one type of comment can be changed to another one:

/* Select top sales agents*/ 
SELECT
      S.Businessentityid,
      E.Jobtitle,
      S.Salesquota,
      S.Salesytd,
      S.Saleslastyear
FROM Sales.Salesperson
WHERE S.Businessentityid > 10
ORDER BY
      S.Salesquota;
 /*add them to the bonus table*/ 

Creating a SQL formatting standard usually takes a lot of testing and tweaking. Once the rules are satisfying, save them to a formatting profile and distribute to all team members:

  1. In the Options window, click the Export button to save the formatting rules to an XML file:

C:\Users\Zivko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\Export08xa.png

  1. Specify the file name and location
  2. Copy the XML file to a team mate’s machine
  3. In the Options window, click the Import button
  4. Navigate to the XML file

Now all colleagues will have the same formatting.

With SQL, its readability is as important as the fact that it can be executed. Clear formatting guidelines and automatic implementation of formatting rules will ensure that all team members and anyone who inherits the code can read it easily. Use ApexSQL Refactor to automatically implement all SQL formatting rules

April 4, 2013

SQL code refactoring – ways to improve database performance (part III) – Add surrogate key

Determining just the right primary key for tables is one of the most important parts of a robust, high-quality database design. The key candidates and the keys themselves need to be picked with caution, as suboptimal choices can snowball out of control and leave the bloated, slow databases which require heavy maintenance and require massive amounts of work to meet changes in the business requirements. Therefore, due to the importance of the primary keys for the future behavior of the database, their impact on the database performance needs to be weighted as well. So, from a performance standpoint, should replacing complex natural keys with a surrogate key be considered?

April 4, 2013

SQL code refactoring – ways to improve database performance (part II) – Split table

In most cases, splitting a table into two tables containing different columns is required in order to address database design changes, business requirements or even adding domain restrictions retroactively (for example, isolating currencies in a separate table and enforcing referential integrity via a foreign key to ensure that only valid currencies can be stored in the database). However, splitting tables may actually offer additional performance benefits to the database.

April 4, 2013

SQL code refactoring – ways to improve database performance (part I) – Encapsulate code as a stored procedure

Database performance is a challenge for every developer and DBA. Even when some improvements are made, there is always a question – is there anything else that can be done.

There might be. Some of the performance improvement techniques are not code related – a proper indexing strategy, adding memory, using different disks for data files, log files, and database backups, using faster disks, optimizing tempdb performance.

April 4, 2013

Get an alert when a certain record changes in SQL Server

Auditing a database is the first step towards staying updated about database changes. However, if specific, highly sensitive data needs reviewing, an immediate notification of any change is preferable.

Sending email alerts to one or more recipients is one of the most common ways of immediate notification. Sending emails for every change in a database is not advised. It will, in most cases, quickly fill your inbox, and add many unnecessary actions, which can also affect the overall application performance. Therefore, it is important to choose the changes that you want to be alerted about, carefully.

April 4, 2013

Recover SQL data from a dropped table without backups

If you’ve accidentally executed a DROP statement and you have a full database backup, given that no changes occurred after the table was dropped, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the DROP SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data

April 4, 2013

Recover a SQL Server database using an old backup and the current transaction log file

How to recover a SQL Server database using an old backup and the current transaction log file DBAs have various disaster recovery plans. One of the questions they have to answer before setting up a right plan is how much data can they afford to lose. Many of “accidental DBAs” become aware that making regular full database backups might not be enough, only after it’s too late.

April 4, 2013

Recover data lost due to a TRUNCATE operation without backups

If you’ve accidentally executed a TRUNCATE statement and you have a full database backup, given that no changes occurred after the table was truncated, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the TRUNCATE SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data.

April 4, 2013