MS-Access / Getting Started

---

Performance and Execution Plans

Access is a bit like a black box in that you can extract some information on what it is doing behind the scenes, (but unfortunately, not a lot of information). By comparison, SQL Server is a much more open environment, and one way to investigate and resolve performance problems is the use of execution plans.

To begin, you should appreciate that not all of the information that you see in the plans is easily understood, simply because it is very comprehensive. But within this complex environment is some very valuable information that can help you to solve serious performance issues. In this section, you will construct examples to show how you dig into the strategies used by SQL Server when processing your SQL.

You start by creating a copy of some existing data in NorthWind, as shown in the following script (Indexing.sql):

-- Create a test table
SELECT * INTO TestOrders
FROM Orders
GO
-- display any indexing on the table
sp_helpIndex [TestOrders]
GO
-- This should confirm that we have no indexes

The Toolbar in Management Studio when working with a query window. This has several options for obtaining more information on the execution of the SQL.

Click the Include Actual Execution Plan icon. You can then execute a piece of SQL and view the plan output.

Executing the following T-SQL creates a CLUSTERED INDEX on the table; all tables should have a clustering key as this is used by other indexes, which are called NONCLUSTERED indexes. The clustered index controls the physical ordering of the data; this can impact the speed of retrieval on searches by the key. When you create a table and add a primary key, SQL Server makes that the clustered index (unless you already have another clustered index on the table). The SQL is shown in the following code:

-- Next create a clustered index
CREATE CLUSTERED INDEX idxTestOrdersClustered
ON TestOrders(OrderID)
GO

Next, you create an index on the OrderDate field, which is used for searching. The SQL is shown in the following code:

CREATE INDEX idx_TestOrders_OrderDate
ON TestOrders(OrderDate)
GO

We mentioned that the clustering key is stored in the NON-CLUSTERED indexes, and it is only the CLUSTERED index that references the actual physical data. This strategy means that often a search requires a Key Lookup to locate the data. The big advantage of this strategy is that if the physical data needs to be moved by SQL Server, for example, to store more data in the record, then only the CLUSTERED index needs to be altered to record where the data row has been moved; there is no impact on all the other indexes. As updates are very common in databases, this strategy minimizes the impact of updates.

Writing your SQL efficiently and giving some thought to what you want to index will improve your applications performance. Here are some tips for how to do that:

• Add indexing to Foreign Keys (Access does this automatically; SQL Server does not). This improves join performance.
• Select only the required fields in your SQL and avoid using * to select all fields.
• Make your WHERE clauses as specific as possible.
• Don't over-index the database; indexes have a cost as SQL Server needs to maintain the data in the indexes.
• Choose to index columns that are often searched and contain many different values (they are selective). If your index is not selective, SQL Server might avoid using the index.
• Run maintenance plans to rebuild indexes with space for new growth.

The ability in SQL Server to analyze specific queries and look for where indexing is not being used can help you plan your indexes. It is a good idea to start with very few indexes and then gradually add them to improve performance. SQL Server uses a costbased optimizer that maintains statistics on the indexes, which can then be used to decide on a minimum I/O cost based strategy for returning your data.