SQL Server Performance Tuning
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 [...]
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 [...]
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 [...]
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 [...]