Indexes

Kimberly Killian

What is causing database slowdowns?

February 2, 2016 by

Why is my database so slow? This query used to be so much faster. Why does it take so long to rebuild my index? How come it was fine last month? Every day I am asked these types of questions by clients. Every day! A lot of database developers and application developers do not realize that indexes are ever changing entities within your database or rather they need to be monitored closely and managed periodically to remain efficient. I cannot even count the times someone tells me “but we have index’s on this or that column and it was fine last month” and so on. All while they fail to realize or even tell me that the database just took on, updated or deleted 1,000,000 records for example, which would definitely change the footprint of the data, making the index’s unsound or in need of help. Even adding 50 new users that use the data differently could require new indexes. That being said, I decided to automate a quick and easy data gathering and reporting job that helps to answer these questions. Most of the time query performance questions can be answered by determining the fragmentation levels of index’s, if there are missing index’s, duplicate index’s, unused index’s and what are the heavy hitters in regards to queries and are queries running in memory or to disk and how many executions. My favorite thing to do with SQL Server is automate, automate and automate the tasks that are asked of me over and over.

Read more »
Miroslav Dimitrov

Tips and tricks for SQL Server database maintenance optimization

January 11, 2016 by

Database maintenance is very important, a critical part of our database administrators’ daily tasks. However, this aspect is frequently underestimated which could lead to performance problems and respectively angry, unhappy customers. In this article, we will take a look at the different maintenance operations we have in SQL Server and how we can optimize them and take the maximum out of each.

Read more »
Ed Pollack

Filtered indexes: Performance analysis and hidden costs

December 11, 2015 by

Introduction

Filtered indexes are well documented, as they have been around in SQL Server for almost six years now. Despite their longevity and usefulness, discussions of them tend to be very simple overviews using simple queries and not digging too deeply into more precise costs and benefits. This article is inspired by a production problem that cropped up recently involving a filtered index that illustrated that general knowledge of their function was not as complete as it should have been.

Read more »
Daniel Calbimonte

DTA, a great tool to automate indexes

December 4, 2015 by

Introduction

In a previous chapter, we learned how to use the Tuning Advisor to analyze queries and receive recommendations about indexes, partitions and statistics. In this new chapter, we will learn how to use the command line tool called DTA. The DTA is the command line of the Tuning Advisor.

The DTA is a very powerful tool that can be used to automate some tuning tasks. It can be used combined with the SQL Agent, SSIS, or customized and external tools like programs made in C# or Java.

In this article, we will show how to use this tool.

Read more »
Daniel Calbimonte

A great tool to create SQL Server indexes

December 1, 2015 by

Introduction

The indexes in many cases are great solutions to solve performance problems. For some problems, they are magical and very cheap solutions. In this chapter, we will show a demo of the SQL Server Tuning Advisor, which is a tool that comes with the SQL Server Installation. We will also use the SQL Server Profiler to generate the information for the Tuning Advisor.

Read more »
Sifiso W. Ndlovu

Columnstore Index in SQL Server

May 28, 2014 by

Prerequisite

The discussion pertaining to SQL Server 2012 columnstore indexing is better explained through theoretical and practical measures. Thus, for the practical measure part – I will be using the AdventureWorksDW2012 sample database. The rest of the prerequisites for a comprehensive understanding of this topic are as follows:
Read more »
Milena Petrovic

Frequent query recompilations – a SQL query performance killer – detection

April 11, 2014 by
In the previous part of this article, we presented query recompilation basics. We explained when a query is compiled, when recompiled, and what preferred behavior is. We also showed the how to trigger a recompilation using T-SQL and provided examples. In this part, we will focus on detecting frequent query recompilations and frequently recompiled queries. Read more »