//
you're reading...
SQL Server

Troubleshooting Performance Problems in SQL Server

This is going to be a multi-part post regarding basic performance troubleshooting in SQL Server. It applies mainly to SQL Server 2008 and beyond but the methodology and general approach to troubleshooting can be applied to any version of SQL Server.

Introduction

it is all to common to experience slowdowns of a SQl Server Database. The causes can range from poor i/o to poorly designed queries to a misconfigured system. To the best of our abilities we want to proactively prevent such slowdowns, however that is not always possible. When such slowdowns occur you want to be able to quickly identify the cause and take corrective actions. The following posts will provide some guidelines for identifying  common performance issues using the standard tools available (SQL Profiler, Performance Monitor, DMVs, Extended Events and Data Collectors). The main goal of which is to provide a basic methodology for diagnosing and troubleshooting performance problems.

Methodology

Obviously there can be many reasons for a slowdown in SQL Server, but I generally focus on three main symptoms to start diagnosing problems:

· Resource bottlenecks: CPU, memory, and I/O bottlenecks

· tempdb bottlenecks: Because there is only one tempdb for each SQL Server instance, it can be a performance and a disk space bottleneck.

· A slow-running user query: The performance of an existing query might degrade over time, or a new query might be taking longer than expected.  There can be many reasons for this here are some of the big ones:

o Changes in statistics

o Missing indexes (over indexing as well)

o Blocking

o Lock escalation

 

Next Post will examine Resource Bottlenecks: CPU

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: