SQL Server Performance Tuning

My SQL Server seems to take memory from the operating system, but never releases it. Is this normal?

If you are running SQL Server 7.0, SQL Server 2000, or SQL Server 2005, and have the memory setting set to dynamically manage memory (the default setting), SQL Server will automatically take as much RAM as it needs (assuming it is available) from the available RAM of the server. Assuming that the operating system or other […]

My application is very INSERT heavy. What can I do to speed up the performance of INSERTs?

Here are a variety of tips that can help speed up INSERTs. 1) Use RAID 10 or RAID 1, not RAID 5 for the physical disk array that stores your SQL Server database. RAID 5 is slow on INSERTs because of the overhead of writing the parity bits. Also, get faster drives, a faster controller, […]

Does running the SQL Server Index Wizard or the Database Engine Tuning Advisor find all of the tables in my database that need indexing?

Question Does running the SQL Server Index Wizard in SQL Server 7.0 or 2000, or the Database Engine Tuning Advisor in 2005, find all of the tables in my database that need indexing? Answer No. While these are handy tools to help identify some of the more obvious missing indexes, they can still miss a […]

Is there any significant performance difference when joining tables across different databases on the same server?

This is very easy to test yourself. For example, make a test copy of one of your databases. Then create a query that JOINs two tables from within the same database. Next, create a second JOIN query similar to the first, but modify the second query so that it JOINs a table from the original and test […]

Is it possible to keep the entire database in memory?

In a very real sense, SQL Server automatically attempts to keep as much of the database in memory as it can. By default, when SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory available. If there is more memory free, SQL Server recommits memory to […]

What happens when SQL Server executes a stored procedure or query ?

SQL Server performs a couple of internal steps before executing a query or stored procedure. The steps that interest us here are compilation and execution. When SQL Server receives a query for execution, its execution plan may already be present in memory (the procedure cache); if not, SQL Server will have to compile the query […]

What happens during the rebuild of a clustered index?

Before SQL Server 2000 Service Pack 2 (SP2) the rebuild of a clustered index automatically forced all nonclustered indexes of that table to be rebuilt as well. This behaviour has changed with SP2. Now whether a nonclustered index has to be rebuilt depends on how the clustered index was inititially created. What does this mean? […]

What is the difference between DELETE and TRUNCATE? Is one faster than the other?

DELETE logs the data for each row affected by the statement in the transaction log and physically removes the row from the file, one row at a time. The recording of each affected row can cause your transaction log grow massively if you are deleting huge numbers of rows. However, when you run your databases in […]

Why can it take so long to drop a clustered index?

Generally speaking, indexes can speed up queries tremendously. This comes at the cost, as changes to the underlying data have to be reflected in the indexes when the index column(s) are modified. Before we get into the reasons why dropping a clustered index can be time-consuming, we need to take a short look at the […]