execution plan is same even though it takes 10 times longer to run

Abbi

20 pts.

Tags:

Execution Plan

Indexes

Performance/Tuning

I have a query that accesses a table with a non-clustered index. When I drop the index and run the query, it takes 1.25 minutes to run. With the index, it takes 2 seconds to run. Yet, the execution plan is the same with or without the index. How can that be? Thank you!

Asked: March 3, 2008 5:07 AM Last updated: March 10, 2008 4:49 PM

Answer Wiki

Last updated: March 3, 2008 6:15 AM GMT

Denny Cherry68,405 pts.
History
Contributors
- Ordered by most recent
- Denny Cherry68,405 pts.

Thanks. We'll let you know when a new response is added.

It shouldn’t be the same query plan. Can you post both of the execution plans? Also what database platform is this.

Discuss This Question: 3 Replies

Abbi Mar 3, 2008 7:18 PM GMT

The platform is SQL Server 2005 using the SQL Query Analyzer. The table that contains the index is called within a function within the Stored Procedure.

20 pointsBadges:

report
Jerry Lees Mar 4, 2008 7:16 PM GMT

SQL indexing is a wonderful thing! I've saved many of thousands of dollars by talking DBA's and Web Developers into "just trying" an index when a server is running slow for no apparent reason and there is not any sign of a resource problem on the server. Mrdenny will definately be able to help you in this area if you post both execution plans and let him/us know what database platform you're using. Be sure and check out my blog here on ITKE for more Network administration and VBSCRIPT tips. The VBScript Network and Systems Administrator's Cafe

5,335 pointsBadges:

report
Ross.bradbury Mar 10, 2008 4:49 PM GMT

The execution plan hides the cost of calling the function. I believe functions that access data are relatively new to SQL Server and the execution plan still tends to consider the function as being something simple, like adding some numbers that were passed in. Try executing the code that is in the function for some example values that would be called during your normal procedure, and you should then be able to see a difference in that execution plan when the index is in place and when it is not. I try to avoid user defined scalar functions that access data because you can't see an accurate execution plan from statements that use them. Hopefully this helps a bit.

40 pointsBadges:

report