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