Adding Indexes to SQL Server 2005 Tables
Sep
24
Written by:
9/24/2009 4:23 PM
Adding Indexes to SQL Server 2005 Tables
I had the opportunity this week to tune a SQL Server 2005 database to improve the performance of a web application. I first created indexes on the temporary tables within a stored procedure that was causing much of the delay and later discovered through the execution plan, there was specific bottle-neck that could be cleared. Here is an illustration of the temp table indexing:
IF OBJECT_ID('#TempTable') IS NOT NULL
CREATE NONCLUSTERED INDEX [#idxTemp_TempTable_Field1_Field2] ON #TempTable
(
[Field1] ASC,
[Field2] ASC,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF,
ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
But by executing the same stored procedure in a query window and selecting ‘Include Actual Execution Plan’ from the toolbar, I was able to see it individual query’s cost within the stored procedure. Then it is possible to look at the most expense queries, their most expensive operations, and add indexes to the permanent tables that reduce execution time.
3 comment(s) so far...
Dental Nursing in UK
Good information am going to share with my colleagues about the site.Thx
By Dental Nursing in UK on
11/26/2009 6:59 AM
|
Re: Adding Indexes to SQL Server 2005 Tables
While adding indexes can improve performance on SELECT queries,A very good Summarised article on INDEXing .. Very helpful for Novices and for experts too to get the gimpse of the ways to add or delete different Indexes on a Database Table.
By Venice Hotels on
8/26/2010 2:35 AM
|
Re: Adding Indexes to SQL Server 2005 Tables
Hey thanx for the post. Indexing has always been a concern when working with databases. Indexing in temporary tables helps in implementing desired index order to the database. This is an alternate solution for simple indexing. Thanx for sharing.
By Conservatories on
6/3/2011 9:34 AM
|