SQL Server Performance Tuning

Which is faster when using SQL Server 2000 or 2005, temp tables or the table datatype?

Generally speaking, if the data you are dealing with is not large, then the table datatype will often be faster than using a temp table. But if the amount of data is large, then a temp table most likely will be faster. Which method is faster is dependent on the amount of RAM in your […]

How do I estimate how large a database will grow?

QuestionBeing a developer, I am not an expert at SQL Server (I know just enough to get done what I need); however, many of our clients are asking us for growth estimations of our database. How much MBs/GBs will a database take up? Also, does SQL Server have the ability to estimate how big it […]

My application makes heavy use of temp tables. Should I be creating temp tables as needed, or should I be using a permanent table over and over instead?

Assuming everything is the same, both tables will produce very similar access speeds. If your application makes heavy use of temp tables, you might consider using a permanent table for these reasons:  A regular table already exists. A temp table has to be created, which takes time and overhead. For example, if you need to […]

I don’t know how to transfer an array to a table, and I even if I can, I don’t know if it will be any faster than using INSERT INTO?

Question I have an INSERT INTO SELECT query which appends about 500,000 rows of data to a table. The query takes about 22 minutes to run. I would like it to run in less than 5 minutes, if possible. One solution I thought of was to append the 500,000 rows of data to an array […]

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 […]

Is there any way to allocate processor utilization to SQL Server jobs?

Question In the past couple of weeks, we have been running some jobs that take a lot of the CPU usage. I would like to know if there is any possible way to : 1. Distribute between processors, the scheduled jobs.2. Minimize the maximum amount of CPU usage that a job can take. If there […]

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 |