Why is my SQL query sometimes slow, sometimes very fast??

Hi all,

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jasonwisdomAuthor Commented:
again.. re-writing the SQL is absolutely out of scope -
The request I've been given is to make this query run as fast as it has.. all the time.

Thanks a lot for your help.
It could be a lot of things.  When it is slow, there could be other queries running on your system that are blocking that query, or just tying up resources.  When it is fast, there may not be other queries running.  Also, when it is slow, it may have a different execution plan than when it is running fast.  The statistics could be different.  
Significant differences in query performance are typically result of using different execution plans. SQL Server recompiles queries from time to time for various reasons. Another thing is the use of data cache. If the pages needed by your query are in data cache it works much faster than if they must be fetched from disk. If it is your case, running the same query twice with no memory stress should give much better execution time for the second run.
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Scott PletcherSenior DBACommented:

1) Add index(es) to speed searches of the table(s)
2) Regularly update statistics on the base tables specifying high sample rate (50%+, 100% if possible)
3) Add /3G switch to increase mem available to, and dedicated to, SQL.
4) Perhaps create a materialized view, if allowed -- the viability and advisability of doing this could vary dramatically based on the actual view, the volume of modifications to the base table(s), etc..
You can also try to use the Analize Query in Database Engine Tuning Advisor under the Query menu. It will run an analysis for you on the query and then it will estimate the improvement ratio in %. If that ratio is substantial (it can go up to 90% or even more) apply the recommendations.

If you run the query on your local server then it's a very strange behaviour prpviding that you don't run something in the background that can affect your server. If you run the query on a remote server then vary reasons can affect the performance, even network hickups, or concurent use of the query/views/tables.

Another thing might be the using of cached but inapropiate execution plan for the current running. Sometimes using an existing execution plan actually has a negative impact on performance, in these cases you need to create a stored procedure to run the quey but using the WITH RECOMPILE option on the procedure definition.
I forgot to mention that the recommandations form Analize Query in Database Engine Tuning Advisor are actually about applying indexes based on your query code and works from MS SQL Management Studio.
jasonwisdomAuthor Commented:
What is the /3G switch, and how can I allocate it?  Would I suggest that a query use this 3G by saying "SELECT * FROM vwView /3G", and then specify say 1GB of ram for this select statement?

How can I update statistics using 50% or 100% of the table's data?  

I've considered Indexed Views in the past.. but they never worked with all of Microsoft's ANSI NULLS and QUOTED IDENTIFIER conditions - they caused more problems than they solved.


There are no other queries running on the server, and the only other program is an antivirus program.

Running the same query 2 or 5 times yields the exact same execution time.

Show Execution Plan indicates all searches are through Index Seeks.  The bottlenecks are joins and order by...about right when 3 layers deep of views are joining tables together and the entire thing has a 6-column "order by" clause.

Anyway.. I don't see adding any more indexes.  This is a step we spent considerable time on.. and they look to be pretty optimize.

I've de-fragged the drive, re-organized indexes, re-built indexes, and restarted the server.
Also done many other things.. but the 3 ideas at the top of my reply I haven't done.  Would you kindly elaborate?

Thank you all for offering help.

jasonwisdomAuthor Commented:
Update Statistics was already optimized... I have a nightly maintenance plan which has an Update Statistics task.. and it uses a Full Scan rather than a percentage, to generate statistics.

So.. every night, there would be REORGANIZE INDEXES followed by UPDATE STATISTICS, full scan for the entire database.

(Once per week there will be REBUILD INDEXES followed by UPDATE STATISTICS).
Please check to make sure that there are not other queries running on your system that is blocking your query.....there is a good chance that is what it is.
jasonwisdomAuthor Commented:
definitely have checked...there is nothing else running.
Have you been able to capture the execution plan differences between the two different executions?
jasonwisdomAuthor Commented:
no.  I do have the execution plan from "slow" method... index seeks are 0 or 1%, 2 join operations are 23% each, and order by is 51%.

It's not a gradual thing.  It's very consistent.  

For example, my one query takes either 2:10-2:40 to run.... or 0:02-0:03 to run.
There is no 1:16 or anything like that.

It's either super-fast, or super-slow.
Try running sql profiler on that machine and capture the execution plans...it will allow you to see if there are any differences between the 2 executions.
jasonwisdomAuthor Commented:
First.. I get to get it to work in fast mode!
I have the "slow" plan.  I don't have the "fast" plan. and Profiler cannot bring about  the fast plan.

This is not a SQL tuning issue.. the reason why this SQL query runs fast one day, then slow for the next week... that's the issue here.
Right...I don't think it is a "tuning" thing....I think that SQL is generating different execution plans between the fast and slow executions.  If you run Profiler for awhile (days) and look for only instances where that procedure is called, you should be able to capture the exeuction plans and duration for all of the calls.  You can then examine them.  

Have you tried adding "WITH RECOMPILE" at the beginning of the procedure?   IT would cause a new compilation everytime...which may solve your problem

jasonwisdomAuthor Commented:
I'm not sure I'm communicating the situation as best as I can.

this is not about a single query (Dynamic SQL btw no stored procedure).

EVERY query runs fast... or every query runs slow.

I could do select * from a view, select * from a table, or run one of the SQL scripts that are called.  When the database is fast, everything is fast, and when the database is slow, everything is slow.

It has only been fast maybe 1-2% of the time.  

My question is.. how do I "make" the sql server  run that fast, 100% of the time?
How do I Move it, from slow mode to fast mode?

If I happen to see the server running fast.. then yes I can look at an execution plan.
But if I see it running slow...i cannot get a "fast" mode execution plan.

any other ideas?
I see....do you happen to be on a SAN?  Make sure you have write-caching on if you do.

Since you are on a machine w/ no other apps running, it may not be too bad to diagnose.  Do you have any jobs in the background running frequently?
Is your system OLPT or analysis one? I mean is the main user task to modify data (order entry, warehouse, accounting or so on) or running big reports. If the first (OLTP) try to set max degree of paralelism do 1, restart the server monitor your query.
Be careful if you take maradams advice.  Setting the maxdop at a server level will cause all of your queries to use a certain number of processors.  Otherwise you'd have to use the OPTION(MAXDOP 1) hint on each individual query.
OLTP queries very seldom need paralel processing. And on the other hand if you set max degree of paralelism to 1 query optimizer does not even try to find paralel plan, what is expensive. My practice also shows that paralel plans with typical OLTP queries are much worse than serial ones in majority of cases. So I said: try. Of cource giving maxdop option to a single query for testing is a good idea.
jasonwisdomAuthor Commented:
The main purpose of this big massive db is to run reports.

Updates are done maybe twice per day - in large batches.
Ditto the question about dedicated storage.

Or is it possible we're just looking at the effect of memory cache, where databases are much bigger than RAM?  You run a series of report that make use of related data so the cache is very effective, but if I run some other reports mixed in with them that use other data they effectively flush the cache.  The difference can be dramatic.
So you should rather leave max degree of parallelism at default level (0). I guess your problem is flushing pages from data cache due to memory stress and increased disk activity as a result of that. Look at performance monitor to find hardware bottlenecks during:

Physical disk: Avg. disk queue length, Disk sec/write
SQLServer;BufferManager: Buffer cache hit ratio, Page life expectancy
Processor: % processor time
jasonwisdomAuthor Commented:
- No SAN switch.  A RAID 1 set, 160 GB, 10000 rpm/sec.

- 0 Average Disk Queue Length, throughout the entire query.
- 86% Cache Hit Ratio
- 30% average Processor Time (2 Dual-Core processors, 4 total.. usually one is @ 5% and the others are @ 50%)

- 130 Index Searches/sec
- 0 Page Scans/sec
- Memory:  2GB used, 1.5GB free (3.5 GB total)
- Disk Pages - 0/sec (once per minute or so there is a spike, 100 pages/sec.. the very next second it drops to 0)

I have not been capturing Disk Writes/Sec or page life expectancy.  
tempdb is optimized to the hilt.
data is on its own physical disk set.
jasonwisdomAuthor Commented:
In case anybody is curious, I figured this out with the help of Microsoft in a Support Incident.

Apparently an Index was SLOWING it down.  

The index had Columns A, B, and C.

The query was selecting Columns A and C.

There were hundreds of millions of rows.

Creating two indexes, A and C and then B and C, sped this query up to under 1 second.

I still don't know what caused "fast" vs. "slow" mode.  
But this is what worked.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.