asked
improve query average duration
The average duration for this query is 5 seconds, it's the highest average duration on my database. I'm wondering how to improve performance on this query. I need the data as is, I'm just wondering how to improve the index. SHould I: -remove ContractID from the INCLUDE in the Index? -make this Index the Clustered index for this table? query text: SELECT "tblContracts"."CustomerID", "tblContracts"."StartDate" FROM "dbo"."tblContracts" "tblContracts" ORDER BY "tblContracts"."PublicId" DESC explain plan shows Index Scan (NonClustered) tblContracts.tblContracts$PublicID Cost 100% the index is: CREATE UNIQUE NONCLUSTERED INDEX [tblContracts$PublicId] ON [dbo].[tblContracts] ( [PublicId] ASC ) INCLUDE ([ContractID], [CustomerID], [StartDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
to answer Usman,
http://www.quest.com/whitepapers/SQLServer_SQLTuning_final.pdf states: Since SQL Server 2000 now supports forward and backward scrolling through indexes with equal performance, you may see ORDERED FORWARD or ORDERED BACKWARD in the query plan. This merely tells you which direction the table or index was read. You can even manipulate this behavior by using the ASC and DESC keywords in your ORDER BY clauses.
You are pulling all the rows from tbl_contracts, there is no filter or where clause in the query, so SQL Server will always do index scan on [tblContracts$PublicId]. The performance of the query depends on the number of rows it return. Also, check for the fragmentation level of the index, fragmentation results in additional page reads and increases I/O.
I would be careful of changing the index based on just this query. You are right in noting that ContractId is not necessary to fulfill the query - removing it from the index will reduce I/O usage for this query. However, there is a possibility that this index is being used by another query that may well use the column ContractId. Removing that column could then cause *that* query to be slow. I don't know what edition of SQL Server you are running, but if you have Enterprise Edition you could take a look at row or page compression to further reduce the I/O load for this. Again, this is not a silver bullet and needs to be fully considered before being implemented.
I agree with Anuj that returning all the rows would take some time depending upon the number of rows in the table. There would always be an index scan in that case. But the problem I see is the sorting ORDER of the index. You are ordering the data in DESCending order in your query but the index made is in ascending order. Change the sorting order of the index and see the difference. As far as removing some column OR changing the clustered index is highly dependent upon your data access pattern and design. Without knowing much it is hard to recommend anything.
13 People are following this question.
