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!
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.
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
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.
Discuss This Question: 3  Replies