I have a SQL Query - sometimes it performs extremely slow (2-3 minutes to return 18 rows), and other times it performs extremely fast (2 seconds for the same).
It is a rather unoptimized query - it selects from a "view" that joins several other views together, along with about 400 columns each treated with a scalar UDF. The base tables making up these views have up to 9 million rows per table.
Okay, the SQL needs to be rewritten. But what I don't get is.. sometimes this query performs BLAZINGLY fast - under 2 seconds. Other times.. it's incredibly slow - 3 minutes, even an hour or a timeout.
I look at performance monitor.. a 4-processor, 3.5 GB RAM machine runs at about 25% processor activity when this is run - RAM usage is about 1.5 GB with 2 GB free.
When the query performs slow.. there are about 150 Index Searches per second.
When the query performs fast.. there are about 200,000 Index Searches per second, and about 8,000 Lock Requests per second.
I've tried defragmenting the disks - that worked twice, but is not working today.
I've closed all other connections to SQL Server - same result.
Any ideas? re-build the indexes? anything at all, OTHER than re-writing the SQL? Why does it perform so fast on certain days?
Thanks a bunch.