SQL Server Performance Tuning

Is is more efficient to use SELECT DISTINCT or GROUP BY to perform the same function?

Question The following two queries produce the same results. Is one or the other versions of these two queries more efficient than the other? SELECT DISTINCT productcode FROM sales SELECT productcode FROM sales GROUP BY productcode Answer The goal of both of the above queries is to produce a list of distinct product codes from […]

I want to add an identity or unique identifier column to a table in order to ensure that each row is unique. For best performance, which one should I use?

One of the key aspects of database table design is to ensure what is called entity integrity. What this means is that you need to ensure that each row in your database tables is unique. If you don’t take the proper precautions, it is possible that one or more rows of your table might be […]

Should I use the old Microsoft JOIN syntax, or the ANSI JOIN syntax for the best JOIN performance?

Question Which of the following joins will produce better performance? ANSI JOIN Syntax SELECT fname, lname, departmentFROM names INNER JOIN departments ON names.employeeid = departments.employeeid Former Microsoft JOIN Syntax SELECT fname, lname, departmentFROM names, departmentsWHERE names.employeeid = departments.employeeid AnswerSQL Server supports two variations of performing JOINs: the ANSI JOIN syntax and the former Microsoft JOIN […]

I don’t like to delete records in our production database, even though we will never need them again. Does this affect performance?

Question I don’t like to delete records in our production database, even though we will never need them again. Because of this, I have included an extra column in every table called the “ACTIVE_FLAG” that is set to one character in length that contains either an “a” for active records or a “d” for deleted […]

In my stored procedures I have the option of calling the same data from either a table or a view. For best performance, should I be calling a table or a view?

Anytime you call data from a view instead of a table, there is additional overhead, which can hurt performance. Because of this, optimum performance can be had by calling your data directly from a table, not a view. If you don’t use the stored procedure often, the performance difference will not be noticeable if you […]

Can the use of NULLS in a database affect performance?

Yes, SQL Server’s performance can be affected by using NULLS in your database. There are several reasons for this. First, NULLS that appear in fixed length columns (CHAR) take up the entire size of the column. So if you have a column that is 25 characters wide, and a NULL is stored in it, then […]

Can you provide some ways that I might be able to avoid using cursors in my applications?

QuestionI understand that I should avoid the use of SQL Server cursors in order to increase an application’s performance. Can you provide some ways that I might be able to avoid using cursors in my applications? AnswerA SQL Server cursor should only be considered in situations where you need to scroll through a set of […]

Is there any performance difference between using SET or SELECT to assign values in Transact-SQL?

This is virtually no difference in performance between using SET or SELECT to assign values. In most cases you will want to use the ANSI standard, which says you should use SELECT. See the SQL Server Books Online for the exact syntax for both options.

We have a growing population of Access users. My question is whether there is a way of lowering these user’s priorities on our SQL Server?

QuestionWe have a growing population of Access users, much to my chagrin! My question is whether there is a way of lowering these user’s priorities on the server/database. We are hoping that if the performance using Access is bad, that this will dissuade users from using Access. AnswerAt our company, we forbid users from accessing […]

Our SQL Server is experiencing lots of blocking, but we don’t have any resource bottlenecks.

Question I am the DBA for a SQL Server that is running a two-node active/passive cluster with four CPU’s and 3GB or RAM on each. The server keeps up with demand most of the time. But once in a while we experience blocking issues and new connections are timing out–not a good thing. Our system […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |