SQL Performance and Tuning Considerations, concluded - Microsoft SQL Server Considerations

(Page 3 of 5 )

This section covers some tuning considerations that are specific to Microsoft SQL Server databases.

SQL Server Query Performance Considerations

Here are some considerations for running queries in Microsoft SQL Server databases:

• Access via stored procedures.  Placing frequently used SQL queries in stored procedures can yield significant performance gains. In fact, in older versions of Microsoft SQL Server, it was essential to do so in order to achieve reasonable performance because there was no SQL cache to hold recently used SQL statements. In SQL Server 2005, the SQL cache allows the DBMS to recognize a statement similar to one that has already been run and to bypass statement preparation and optimization steps when the statement is reused from the cache. Nonetheless, any statement that is accessed through a stored procedure is precompiled and optimized, so it will always perform well (even if it is not found in the cache at the time it is run). This is one reason why seasoned developers strive to do most or all of their SQL Server database access using stored procedures.
  
• Avoid nulls in unique indexes.  Unlike most other SQL implementations, unique indexes in SQL Server do not handle null column values well. You can define a unique index that includes a column that is allowed to be null, but the second index entry that contains a null is considered a duplicate of the first, and therefore results in a duplicate key error. Most other SQL implementations avoid this issue by not indexing null values.
  
• Consider clustered indexes.  A clustered index causes the table rows to be physically ordered by the indexed column(s). This can help considerably in search and join operations, but it’s at the expense of table maintenance, particularly when new rows force existing rows to be moved to maintain their sequence.

Next: Displaying Execution Plans Using SQL Query Analyzer >>
 

More MySQL Articles
More By McGraw-Hill/Osborne

Comments On: SQL Performance and Tuning Considerations, concluded

· This article is an excerpt from the book "SQL DeMYSTified," published by McGraw/Hill...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 11 of the book SQL DeMYSTiFied, written by Andy Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249). Check it out today at your favorite bookstore. Buy this book now.