SQLServerCentral.com Content tagged Performance Tuning

Using NOLOCK and READPAST table hints in SQL Server

2008/07/15

Table locking hints provide developers much tighter control of their transactions. Look at the benefits and disadvantages of using the NOLOCK and READPAST table hints in SQL Server

Is a Temporary Table Really Necessary?

2008/05/30

In this article Randy Dyness shows you how to avoid temp tables whenever to maximize the performance of your queries.

Lengthy SQL Server Queries Will Consume Your CPU

2008/05/23

The SQL (Structured Query Language) language is a declarative language that became the "Data Language" used for describing "what I need" and "where to fetch it from" in most organizations. OOP (Object Oriented Programming) languages became the most common practice among developers widely adopted by R&D organizations around the world. So how do we bridge the gap?

Execution Plan Basics

2008/05/22

Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn't my index getting used? In order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? We are very pleased to be allowed to publish the first chapter of Grant Fritchey's excellent new book on execution plans.

Making the Most Out of the SQL Server 2005 Performance Dashboard

2008/05/20

The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005 that became available shortly after the release of Service Pack 2 for SQL Server 2005.

Best Practices for Troubleshooting Slow Running Queries

2008/05/08

In this article we will share some of the common reasons for slow-running queries and what your approach should be for identifying and fixing them.

SQL 2005 vs. SQL 2008 Part 1 - (Backup File Sizes & Times)

2008/05/05

SQL 2008 has some key improvements over SQL 2005. This article deals with backup compression and faster backups in SQL 2008

DATABASE I/O STATISTICS (SQL Server 2000)

2008/05/01

Identify your busiest databases based on I/O statistics.

Top Queries in SQL Server 2005

2008/04/09

This article illustrates how to query dynamic management views to find the TOP queries based on average CPU and average I/O.

SQL Server Index Checklist

2008/04/03

Indexing a SQL Server database in some respects is considered both an art and a science. Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production? Are these the same types of steps or not? Do any best practices really exist when it comes to indexing? Where does indexing fall in the priority list from an application or production support perspective?

Writing Faster T-SQL

2008/03/31

How many times have you wished that your queries performed better? Performance Tuning is a bit of an art, but learning about new techniques and which things work help grow your knowledge. Jacob Sebastian brings us the first part of a series on writing better performing queries.

More RBAR and "Tuning" UPDATEs

2008/03/14

SQL Server expert Jeff Moden discusses a common problem seen in many update statements.

Monitoring Stored Procedure Performance with sys.dm_exec_cached_plans

2008/03/13

This article will show you how to use the sys.dm_exec_cached_plans DMV to monitor the performance of stored procedures

Not In v Not Equal

2008/03/12

Is it better to use NOT IN() or <> in a T-SQL query? Ken Johnson had the question put to him and decided to investigate them both. Read about how these two functions perform.

Why Object Qualification is important.

2008/03/10

You have all read about why object qualification is important. You also must have heard of why stored Proc should not have sp_ as prefix to the name. Now let us proove if this is all true.

Linked Server Optimization

2008/03/07

This article demonstrates the negative affect that linked servers can cause in queries and offers alternatives to speed up performance.

SQL Server non-clustered indexes for query optimization

2008/03/05

Optimize SQL Server non-clustered indexes and queries by considering index fields, compound indexes and SQL Server statistics' impact on non-clustered indexes.

Check List for Performance Optimisation in SQL Server Analysis Service 2005

2008/02/29

This article is a step-by-step checklist to help ensure that you are obtaining the maximum performance possible from SQL Server Analysis Services.

Graphical Query Plans

2008/02/20

Execution Plans

2008/02/18

How SQL Server Chooses the Type of Join

2008/02/13

How many times have you had a stored procedure run quicker in Query Analyzer than it does in the application? Mike Dillon tracked down one possible cause in this article.

Designing High Performance Stored Procedures

2008/01/30

Stored procedures can be an effective way to handle conflicting needs, but it's not always so obvious how to write them so they both perform well and scale.

Importance of UPSERT

2008/01/29

A simple UPSERT can reduce reads on tables. This in turn will increase the performance of a DB.

Using Included Columns in SQL Server 2005

2008/01/28

One of the new features found in SQL Server 2005 is the ability to add additional columns, called Included Columns, to a non-clustered index. This article will explain the advantages of using included columns and what impact they will have on your database.

Measure TSQL Statement Performance

2008/01/22

Every developer needs to ensure that each TSQL statement is optimized. This article will give you a few different ideas on how to identify slow running queries and provide you with some tips on monitor your query performance while you make iterative changes to each query to try and improve performance.

SQL Server clustered index design for performance

2008/01/16

Find why SQL Server clustered index design should be narrow and static and how clustered indexes affect many-to-many tables to improve database performance.

Evaluating How Filtering Options Affect T-SQL Performance

2008/01/14

The many popular rules concerning T-SQL filtering operators can't be trusted implicitly; instead, you should evaluate your options explicitly.

SQL Server: Uncover Hidden Data to Optimize Application Performance

2008/01/11

Many application performance problems can be traced to poorly performing database queries; however, there are many ways you can improve database performance. SQL ServerTM 2005 gathers a lot of information that you can use to identify the causes of such performance issues.

Measure TSQL Statement Performance

2008/01/08

Predeployment I/O Best Practices

2008/01/04

The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005.