SQLServerCentral.com Content tagged T-SQL, Performance Tuning

Is a Temporary Table Really Necessary?

2006/06/02

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

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.

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.

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.

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.

TinyInt, SmallInt, Int and BigInt

2007/12/10

SQL Server guru David Poole takes a look at the different integer data types and the impact of each of those on your database.

Performance Effects of NOCOUNT

2007/12/07

Most SQL Server programmers know to use the SET NOCOUNT command to prevent the number of rows message from being returned to the client. But how does this affect performance? Does it make sense to qualify the owner on your objects? SQL Server guru David Poole brings us some performance analysis of how your stored procedures perform.

Introduction to Parameterization in SQL Server

2007/08/14

In a previous article I showed how to look at what query plans are in SQL Server's memory. In this article I'll look at how they got there and how and when they get reused. This is commonly called compiling a query plan. More specifically I'll look at how and when SQL Server can parameterize dynamic SQL

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.

Writing Faster T-SQL

2008/03/31

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.

Using Indexed Computed Columns to Improve Performance

2007/01/29

I recently read a blog post on doing case-insensitive text searches on SQL Server 2005. The post said that an index on a computed column might be used even if the computed column itself wasn't used in the WHERE clause. I was curious to test that and see how far I might take it.

TinyInt, SmallInt, Int and BigInt

2007/12/10

SQL Server guru David Poole takes a look at the different integer data types and the impact of each of those on your database.

Performance Effects of NOCOUNT

2007/12/07

Is a Temporary Table Really Necessary?

2006/06/02

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

Troubleshooting SQL Server with the Sysperfinfo Table

2006/04/21

Joe discusses why he prefers command line tools over GUI, then launches into a how-to about querying sysperfinfo to resolve performance issues. Couple nice sample queries included!

Measuring SQL Performance

2005/12/23

One thing that often amazes me is that many SQL Server developers do not actually measure the performance of their queries. Many of them thought that you needed external tools, more or less complicated, to run against your server, and they did not have the time or inclination to learn and try these. This article will describe a couple of much easier methods of measuring performance of queries.

Performance Implications of Nullable Columns

2005/11/18

To null or not to null: that is the question. The specific focus here is performance implications of NULL versus NOT NULL columns.

Controlling Transactions and Locks in SQL 2000 and 2005 - Part 3

2005/10/14

In the preceding articles of this series, Lock Granularity, Transactions, and ACID were introduced. Common lock types, such as Shared, Exclusive, and Update were explored, as well as using SP_Lock to obtain current system lock information. In this article, the normal internal SQL locking methods will be manipulated using Lock Hints in order to obtain finer lock control.

Execution Plans

2005/10/07

How many of you use Execution Plans to tune your queries? Do you understand the impact of different indexes? Mr. Vijayakumar looks at his experiments with different types of indexes and their effects on the execution plan use. A great article for those of you that want to learn more about how you can tune your server for better performance.

Dynamic SQL or Stored Procedure

2005/08/26

We've had a lot of coverage of dynamic sql (including another great one from Robert Marda later this week) but this one is a little different. Done in a question/answer format, Andy tries to explain to junior developers why dynamic sql is to be avoided, how to do so, what to do when you can't.

SQL Maintenance Plans

2005/03/04

Do you use the maintenance plans or hate them? Wish they would do more? Curious about how they work under the hood? Cmon, you gotta read this one! Trust us, it's not another "how-to" article! Well, maybe just a little bit!

Stored Procedures and Caching

2004/11/19

One of the biggest performance gains built into SQL Server is the stored procedure. In this article by Brian Kelley, he shows you how to fully utilize, debug and monitor the caching of such objects.

Seven Showplan Red Flags

2004/07/15

SQL Server showplan is a great tool for analyzing queries and determining whether or not more performance tuning needs to be done. However working with showplan can be confusing and it takes some practice to learn the ins and outs of what is occurring. Joseph Sack looks at showplan and seven of the main things that he is aware of when looking at the results. If you've rarely or never used showplan, this is a great article to get started in learning how an experienced DBA views the output.

Case Sensitivity in Selects - Part 1

2004/06/03

Your SQL Server has a case insensitive collation, but you need a case sensitive join? Do you know what happens? There are a couple options but do you know the performance implications of using each? Or a way to mitigate the performance degradation that can result?

Overview of Performance

2004/05/11

How can you tell if your code if performing well? Do you know what you want to look for when starting to analyze code? Here's an article from Leo Peysakhovich that looks at some of the things that you can use at a high level to improve the performance of your code.