jasonwisdom
asked on
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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
Jason
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.
Jason
ASKER
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).
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.
ASKER
definitely have checked...there is nothing else running.
Have you been able to capture the execution plan differences between the two different executions?
ASKER
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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
ASKER
The main purpose of this big massive db is to run reports.
Updates are done maybe twice per day - in large batches.
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.
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
Physical disk: Avg. disk queue length, Disk sec/write
SQLServer;BufferManager: Buffer cache hit ratio, Page life expectancy
Processor: % processor time
ASKER
- 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.
- 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.