9 Best Practices for Writing SQL Queries

Total: 1 Average: 5

If you are reading this article, most probably, you are already familiar with SQL. You know how to write basic SQL queries. There are many ways to execute a SQL query to get desired results on your database.

However, not all SQL queries are created equal. Most can be optimized to follow the best practice. This article focuses on the ways of queries optimizing. After reading, you will know all the dos and don’ts of writing SQL queries.

Read More

Virtual Columns and Functional Indexes

Total: 1 Average: 5

Much too often, we see poorly written complex SQL queries running against the database tables. Such queries may take a very short or a very long time to execute, but they consume a huge amount of CPU and other resources. Nevertheless, in many cases, complex queries provide valuable information to the application/person. Therefore, it brings useful assets in all varieties of applications.

Read More

SQL Server Indexes Management Using Index Manager for SQL Server

Total: 1 Average: 5

SQL Server Index Overview

When talking about SQL Server performance tuning and queries enhancement, the first thing to consider is the SQL Server Index. It serves to accelerate reading data from underlying tables by providing quick access to the requested rows. Thus, it won’t need to scan all the table’s records.

The SQL Server index provides those fast search capabilities due to the B-Tree structure of the index. This structure makes it possible to move through the table rows based on the index key quickly and retrieve the requested records at once. It won’t need to read the whole table.

Read More

Parameter Sniffing Primer

Total: 4 Average: 4.8

Introduction

Developers are often told to use stored procedures in order to avoid the so-called ad hoc queries which can result in unnecessary bloating of the plan cache. You see, when recurrent SQL code is written inconsistently or when there’s code that generates dynamic SQL on the fly, SQL Server has a tendency to create an execution plan for each individual execution. This may decrease overall performance by:

  1. Demanding a compilation phase for every code execution.

  2. Bloating the Plan Cache with too many plan handles that may not be reused.

Read More

Auto Create Statistics and Auto Update Statistics

Total: 8 Average: 4.1

Statistics comprises lightweight objects that are used by SQL Server Query optimizer to determine the optimal way to retrieve data from the table. SQL Server optimizer uses the histogram of column statistics to choose the optimal query execution plan. If a query uses a predicate which already has statistics, the query optimizer can get all the required information from the statistics to determine the optimal way to execute the query. SQL Server creates statistics in two ways:

  1. When a new index is created on a column.
  2. If the AUTO_CREATE_STATISTICS option is enabled.

In this article, Auto Create Statistics and Auto Update Statistics options are analyzed. They are database specific and can be configured using SQL Server management studio and T-SQL Query. Read More

Advanced SQL: CROSS APPLY and OUTER APPLY

Total: 19 Average: 3.8

In this article, we’ll look into the “APPLY” operator and its variations – CROSS APPLY and OUTER APPLY along with examples of how they can be used.

In particular, we will learn:

  • the difference between CROSS APPLY and the JOIN clause
  • how to join the output of SQL queries with table-evaluated functions
  • how to identify performance issues by querying dynamic management views and dynamic management functions.

Read More

Essentials and usage of NOLOCK hint in SQL Server

Total: 9 Average: 4.6

The main idea of the SQL Server locking mechanism is that it controls the consistency of transactions. According to this principle, if a process wants to perform insert, delete or update operations, SQL Server engine locks the row or rows and does not allow another process until the transaction is completed. Under some circumstances, this locking mechanism can lead to performance problems such as high concurrent process pressures. So you can experience the deadlock (Deadlock is a concurrency problem in which two transactions want to access the same data simultaneously) problems in your database. In this article, we will focus on how to avoid lock issues with the help of NOLOCK hint. First, let’s learn the main essentials and details of dirty read methodology because the NOLOCK hint can cause dirty reading.

Read More

Query Store: Showing the Impact of Indexes on Inserts

Total: 7 Average: 4.1

Introduction

It is common knowledge in database circles that indexes improve query performance either by satisfying the required result set entirely (Covering Indexes) or acting as lookups which easily direct the Query Engine to the exact location of the required data set. However, as experienced DBAs know, one should not be too enthusiastic about creating indexes in OLTP environments without understanding the nature of the workload. Using Query Store in SQL Server 2019 instance (Query Store was introduced in SQL Server 2016), it is quite easy to show the effect of an index on inserts. Read More