November 30, 2012 at 3:30 am
This has being a common observation since last few days. The server through which i am connected is taking long time to respond like long time to execute a View(which has join from more than 10 table), but that is not the issue because on other day it takes around 10-20 seconds. I thought that some other user might be blocking my view but when i check with sp_who2, it showed that i was the only user who was accessing that object(view).
So what are the area on which we look when the view takes a long time to execute then expected.
November 30, 2012 at 4:01 am
Shadab Shah (11/30/2012)
This has being a common observation since last few days. The server through which i am connected is taking long time to respond like long time to execute a View(which has join from more than 10 table), but that is not the issue because on other day it takes around 10-20 seconds. I thought that some other user might be blocking my view but when i check with sp_who2, it showed that i was the only user who was accessing that object(view).So what are the area on which we look when the view takes a long time to execute then expected.
Does the view takes long time top get executed or it's getting the result back to your client PC is taking longer?
It can be due to many different reason, including slow network...
November 30, 2012 at 5:56 am
Have you looked at the execution plan of the query to see what part seems to be a bottleneck? It could be possible that an index that used to be there was removed or maybe even statistics out of date. The execution plan should help narrow it down.
November 30, 2012 at 8:04 am
Eugene Elutin (11/30/2012)
Shadab Shah (11/30/2012)
This has being a common observation since last few days. The server through which i am connected is taking long time to respond like long time to execute a View(which has join from more than 10 table), but that is not the issue because on other day it takes around 10-20 seconds. I thought that some other user might be blocking my view but when i check with sp_who2, it showed that i was the only user who was accessing that object(view).So what are the area on which we look when the view takes a long time to execute then expected.
Does the view takes long time top get executed or it's getting the result back to your client PC is taking longer?
It can be due to many different reason, including slow network...
Hi Eugene, hhmmm.... slow network....i think that was not the issue because when this operation was going on i open another window and executed some heavy queries which were running with in time. Could you guess some other problem due to which this error occurr...
November 30, 2012 at 8:08 am
Chris Harshman (11/30/2012)
Have you looked at the execution plan of the query to see what part seems to be a bottleneck? It could be possible that an index that used to be there was removed or maybe even statistics out of date. The execution plan should help narrow it down.
Well the view which i am telling over here , took long time to run for the first time. Then i started Execution Plan and again tried to run this view, this time it was matter of fraction of seconds. Can't really figure it out why there are problems after some random time interval
November 30, 2012 at 8:10 am
Shadab Shah (11/30/2012)
Chris Harshman (11/30/2012)
Have you looked at the execution plan of the query to see what part seems to be a bottleneck? It could be possible that an index that used to be there was removed or maybe even statistics out of date. The execution plan should help narrow it down.Well the view which i am telling over here , took long time to run for the first time. Then i started Execution Plan and again tried to run this view, this time it was matter of fraction of seconds. Can't really figure it out why there are problems after some random time interval
something might be locking tables your view is based on...
November 30, 2012 at 8:16 am
What is Task Manager telling you?
Is all your hardware functioning properly?
Check out Gail Shaw's article on finding performance issues.
November 30, 2012 at 8:59 am
The problem with the "why is my server running slow" question is that it could just be way too many things.
Some suggestions have already been made such as statistics, blocking, changed query plans, hardware, rogue apps running in the background, newly released code that turns out to be more resource intensive than expected, a server/database config. change and on and on...
It can be difficult to troubleshoot that kind of stuff without the benefit of the bigger picture.
We did a release of some code into live a few days ago which was taking ages to run. Was not the case in dev or qa. Turns out "someone" had set the Max DOP to 1 in Live to accommodate a number of queries which didn't perform so well with parallel plans. Changing the setting solved the problem with a maxdop hint being added to the queries that needed the setting.
Peformance tuning can sometimes be more of an art than a science, I would say, as there is rarely one clearly defined way to detect the issue and resolve it.
November 30, 2012 at 9:24 am
Since you're seeing intermittent behavior, I would suggest looking for resource contention and blocking. Next time it's running slow, run a query against sys.dm_exec_requests to see what the process is waiting on. That will help you determine what the issue might be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply