Understanding SQL Server Query Optimization Statistics

SQL Server query optimizer uses statistics to create query plans that improve query performance. For most queries, the query optimizer already generates the necessary statistics for a high quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results. By default, SQL Server maintains statistics for index columns. You can also keep statistics on selected non-indexed columns, if desired. Statistics on non-indexed columns count toward the limit of non-clustered indexes (249) that you can have on a table.

Checkout my article here, in which you’ll learn what query optimisation statistics are and how SQL Server query optimizer uses statistics. You will also learn how to create and manage statistics, and what are the different ways to determine when statistics were last updated in SQL Server.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Advertisements

SQL Server Transactions and Locking (Part 2)

Checkout the part-2 of my two part article series on SQL Server Transactions and Locking here, in which you will learn how to manage transactions, how to set the transaction isolation level, and how some isolation levels use locking, while others use row versioning.

This article is published on SQL-SERVER-PERFORMANCE.COM.

SQL Server Transactions and Locking (Part 1)

In many situations, data modification requires several steps. For example, you may need to change the values ​​in two separate tables. You can use transactions to complete these two operations as a unit, or if an error occurs, does not change any of the tables. Another key consideration is that most databases must be compatible with many operations simultaneously. You have several options to configure how data is locked while it is being modified by a user.

Checkout the part-1 of my two part article series on SQL Server Transactions and Locking here, in which you will learn fundamentals about SQL Server transactions, how they work and how to manage them, and how to write procedures that use SQL Server transactions.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Find Duplicate Indexes on SQL Server (Script)

Like other mainstream commercial database systems, Microsoft SQL Server allows you to create multiple indexes on the same column of a table. This increases the likelihood of having duplicate indexes in the database because SQL Server does not prevent you from creating duplicate indexes, infect we can create up to 999 duplicate indexes on each table inside database. Having duplicate indexes on tables columns can significantly hurt the performance of your database because SQL Server has to maintain each duplicate index separately (such as updating these duplicate indexes during DML operations and calculating and updating statistics for these duplicate indexes). Moreover, SQL Server query optimizer will consider each of them when it optimizes queries, which can cause serious performance impact.

Check out my article here in which you will learn about what duplicate index is, how they are created, and what system catalogs we can use to find and remove these duplicate indexes from SQL Server databases.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Capture SQL Server Deadlocks using Extended Events

Microsoft SQL Server provides a variety of different options to monitor deadlocks. This includes Trace flags 1204 and 1222, SQL Profiler trace events, Extended Events and Event Notifications. Of these options Extended Events are the most efficient, lowest impact method for capturing deadlock information. Extended Events infrastructure is built directly into SQL Server and so can easily be managed with T-SQL.

Check out my article here in which I discussed how we can use Extended Events to capture deadlocks on SQL Server.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Tips For Minimizing Deadlocks in SQL Server

Database servers are configured in such a way that they can service multiple requests from multiple users. Obviously this increases the likelihood of conflicts because multiple processes can request access to same resources at the same time. This type of conflict is also known as block or blocking. Blocking usually resolves itself after the locking process releases the resource for waiting process. Sometimes, blocking creates a more serious condition, called a deadlock or fatal embrace, which occurs when the first process is waiting for the resource that is locked by the second process and the second process, is waiting for the resource that is locked by the first process.

Deadlocks are caused by poor database design, inappropriate isolation level, inefficient code etc. Check out my article here in which I discussed different ways to minimise deadlocks on SQL Server.

This article is published on SQL-SERVER-PERFORMANCE.COM.