| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Optimization Techniques in SQL Server

Page history last edited by Dr. Ron Eaglin 14 years, 2 months ago

Optimization Techniques in SQL Server 

 

There are a lot of ways to optimize a database for performance after the initial database design. A major technique for improving performance is the addition of indexes. By default any primary key field is indexed and thus has very efficient access. By creating indexes on fields that need to be used frequently for joins and lookups - the performance of the database can be greatly enhanced. For SQL Server the Microsoft Knowledge base ( http://support.microsoft.com/kb/832526). To understand execution of queries, the place to start is the Execution Plan - which is simply a graph of how the software executes a query. To do this take a simple query (maybe a single table join with a few pieces of data) and work out the results by hand. You can probably figure out that there are multiple ways to do this, and that you can time easch step to figure out how to do it faster. It is the same here. A short lecture on how to do execution plans in SQL server is shown here.

 

 

Download Link 

 

 

SQL Server has a type of view called indexed views, which are also called materialized views. These views are discussed in detail in Microsoft Technet ( http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx)  and database students should read this source in its entirety.

Comments (0)

You don't have permission to comment on this page.