SQL Server performance tuning

Ahmad Yaseen

How to control online Index Rebuild Locking using SQL Server 2014 Managed Lock Priority

July 18, 2017 by

When you perform a SQL Server Online Index Rebuild operation, introduced for the first time in SQL Server 2005, the index will not be taken down. But at a specific point, in which the new index new is built and switched from the old structure of the index, a special kind of lock, Schema Modification (SCH-M), will be granted. This lock may cause blocking if your database server is busy.

Read more »
Vitor Montalvão

SQL Server system databases – the msdb database

July 14, 2017 by

Introduction

This article is the third I am writing about Microsoft SQL system databases.

In this article, I will focus only on the msdb database, one of the four system databases that exist in any MSSQL instance:

Read more »
Ahmad Yaseen

SQL Server SET Options that Affect the Query Result – SET CONCAT_NULL_YIELDS_NULL, SET NUMERIC_ROUNDABORT, SET QUOTED_IDENTIFIER, SET NOCOUNT, SET XACT_ABORT

July 4, 2017 by

In the previous article of this two-part series SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT, we described the first four SQL Server SET options and showed practically how setting these options ON and OFF affects the SQL Server Database Engine behavior and the query result. To recall, SQL Server SET options are a group of session-level options that control how the SQL Server behaves on the database session level, and the option value can be changed using the SET T-SQL command for the current session that you execute the SET command on.

In this article, we will describe another five SET options and see how turning it ON and OFF will change the SQL Server behavior and the query result.

Read more »
Ahmad Yaseen

SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT

June 30, 2017 by

SQL Server provides us with a number of options to control SQL Server behavior on the connection level. These session-level options are configured using the SET T-SQL command that change the option value for the session on which the SET command is executed. Changing the default value of these session-level configuration affects how the session queries will be executed affecting the query result. The performed change on a session-level option will be applied to the current session until its value is reset or until the current user’s session is terminated.

Read more »
Jefferson Elias

How to use SQL Server Extended Events to parse Deadlock XML and generate statistical reports

June 29, 2017 by

Introduction

Context

In previous article entitled “How to report on SQL Server deadlock occurrences“, we’ve seen how to extract deadlock information from either system_health or specialized Extended Event and store this information into a table in a SQL Server database. We also discussed a simple report which leads to the creation of a deadlock occurrences timeline.

Read more »
Vitor Montalvão

Configuration, operations and restrictions of the tempdb SQL Server system database

June 28, 2017 by

Introduction

tempdb is one of the 4 system databases that exists in all SQL Server instances. The other databases are master, model and msdb. In case of using Replication, a fifth system database named distribution will also exist. You can find all existing system databases in SQL Server Management Studio (SSMS) under the Databases / System Databases folder:

Read more »
Ed Pollack

Collecting, aggregating, and analyzing missing SQL Server Index Stats

June 27, 2017 by

Description

Indexing is key to efficient query execution. Determining what indexes are needed to satisfy production needs is often a game of cat and mouse in which we are forced to react to performance problems after they are brought to our attention. Being able to proactively monitor index needs and respond effectively before complaints are received can save us immense time while preventing costly performance messes.

Read more »
Ahmad Yaseen

SQL Server 2016 Memory-Optimized Tables – The Checkpoint operation

June 21, 2017 by

The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically.

Read more »
Nikola Dimitrijevic

All about locking in SQL Server

June 16, 2017 by

Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency. Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect. A lock as an in-memory structure is 96 bytes in size.

Read more »
Daniel Tikvicki

How to monitor total SQL Server indexes size

June 12, 2017 by

SQL Server indexes overview

Just like a book index, SQL Server index has a similar purpose, to provide faster searching, gathering and sorting of information from a database, without need to go through all of the objects over and over. Instead, an index provides the gateway between the table rows and query engine, in order to achieve better both database and query performance.

Read more »
Susantha Bathige

Understanding Skewed Data in SQL Server

May 22, 2017 by

Introduction

I recently did some research to analyze skewed data distribution in SQL Server. This article is the outcome of my analysis to share with SQL Server community.

SQL Server understands its data distribution using statistics. The SQL Server query optimizer then uses the statistics to calculate estimated row counts when executing the queries submitted by users. The accuracy of estimated row count is crucial to get optimal execution plans. The SQL Server query optimizer is a complex mathematical component and it does a decent job to create better execution plans during a very short period of time.

Read more »
Page 1 of 912345...Last »