SQL Server performance tuning

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 »
David Alcock

Monitoring SQL Server with Dynamic Management Objects – Requests

May 17, 2017 by

In my last post, Monitoring SQL Server with dynamic management objects – Sessions and connections, I introduced the concept of using T-SQL queries to monitor SQL Server using Dynamic Management Views. The article demonstrated how we can use two views, namely sys.dm_exec_sessions and sys.dm_exec_connections to view activity on our instance of SQL Server and we achieved this by utilising some of the functionality available to us within T-SQL like joins, aliases and functions, all of which helped us develop a query to return monitoring information to us.

Read more »
David Alcock

Monitoring SQL Server with Dynamic Management Objects – Sessions and connections

May 12, 2017 by

A fundamental task of Database Administrators is monitoring SQL Server performance. Whilst SQL Server does give us a user interface inside management studio that enables us to view current activity (in the rather aptly named Activity Monitor) this article is going to focus on querying Dynamic Management Objects with T-SQL to return various pieces of useful process information that we can use to monitor server usage.

Read more »
Sifiso W. Ndlovu

How to use Columnstore Indexes to improve your Data Warehouse Staging Environment

May 4, 2017 by

My team and I were recently tasked with refactoring older data marts, particularly those that were created with SQL Server 2008 in mind. As we all know, SQL Server has undergone significant changes since the release of SQL Server 2008. One of those changes relates to the introduction of columnstore as an alternative to the traditional B-tree index (rowstore). Whilst most of the existing documentation relating to columnstore seem to focus on the benefit of columnstore against data warehouse workloads, in this article I argue that the usage of columnstore index should not be limited to facts and dimensions instead let’s introduce it in our data warehouse staging environments too.

Read more »
Neeraj Prasad Sharma
C:\Users\Neeraj\Desktop\TOP\IMAGES\FinalSerialNL.JPG

SQL Server TOP clause performance problem with parallelism

April 13, 2017 by

Basics of TOP Operator

The TOP keyword in SQL Server is a non-ANSI standard expression to limit query results to some set of pre-specified rows. As an argument, it takes 0 to positive Bigint (9223372036854775807) and anything beyond or less gives an error message. TOP without an order by clause, in production, seems buggy because it can produce results in any order depending on the current schema and execution plan. You can also specify a percent with TOP expression which returns only the expression percent of rows from the result set. Float expressions used in TOP percent are rounded up to the next integer value.

Read more »
Minette Steynberg

Understanding the XML description of the Deadlock Graph in SQL Server

March 29, 2017 by

Introduction

In my previous 2 articles, What is a SQL Server Deadlock and Understanding the graphical representation of the SQL Server Deadlock Graph, I discussed what a deadlock is, how SQL Server handles deadlocks, some tips on how to reduce deadlocks and ultimately what information you can glean by just looking at the graphical representation of the Deadlock Graph.

In this article, I will look at what information is available in the XML description of the Deadlock Graph. The XML contains information which is not visible in the graphical representation of the Deadlock Graph, which makes it necessary for us to look at the XML description.

Read more »

Some uncommon but useful T-SQL and Database Engine Enhancements in SQL Server 2016

March 20, 2017 by

SQL Server 2016 is the most advanced version of Microsoft’s Data Platform released yet. This is obviously my favorite one as it has tremendous capabilities and enormous features. These new additions not only enhance the productivity of its users (Database Developer, DBA or Application Developer), but also enable the enterprise to use its data more effectively and efficiently.

Read more »
Simon Liew

The impact of Residual Predicates in a SQL Server Index Seek operation

March 6, 2017 by

Abstract

It is common assumption that an Index Seek operation in a query plan is optimal when returning a low number of output rows. In a scenario involving residual predicates, an Index Seek operation could be reading a lot more rows than it needs into the memory, then each row is evaluated and discarded in memory based on the residual predicate and returns low number of output rows.

This article will explain the concept and the impact of Residual Predicates in a SQL Server Index Seek operation.

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