July 14, 2005 at 3:35 am
Dear Friends,
Please let me know the answer to this Question
Q) 4 people are accessing the same query.3 people
do not have any performance problems.But the 4th person
has performance problem with the Query.
What could be the reason for the performance problem
of the query for the 4th person ?
regards
shown
July 14, 2005 at 3:47 am
Depends,
How is the query being executed? IN an application? Thru query analyzer?
are the queries the same, or different, I mean are all the joins, Where clause items the same?
Have you looked at the query plan between the 4 different queries and noted why they are different?
Run a sql profile, and have the same 4 queries being executed ,and see if you see the slower one using more CPU, DiskIO, Reads, etc.
July 14, 2005 at 5:11 am
Mr Ray,
The Question was asked to me for an Interview,they didn't
say much details about the query.
For u'r knowledge it was actually run from an application
and the queries where the same.
Think they were not asking regarding the usage of
different clauses .
regards
shown
July 14, 2005 at 5:21 am
In general terms :
Possible locking / contention issues.
Is there a limit on the number of connections and the last app is waiting silently for a free one?
Network performance? Are they all connecting from the same site, same routers, same protocols?
Workstation performance - hardware, software versions, connection configuration, other apps running?
Are they all using the app in identical ways? Is the problem really with the query or with processing a returned recordset, for example?
When presented with such a general question, I think you need to explore all the possibilities and not just assume that the problem is the database. Lateral thinking!
July 14, 2005 at 5:55 am
Even I don't have much knowledge about the query.
But they where asking in a general way, which means
What would we do generally to such a query which perform well
to certain people and not to some others.
regards
shown
July 14, 2005 at 6:01 am
That's what I was trying to suggest. The probelm might be with the database but I expect that they wanted you to show that you could think beyond the obvious and your own specialism. If I were interviewing you, I probably would follow up, whatever your answer, with '.. and if that didn't work?' and 'who else would you ask for help?'.
July 14, 2005 at 6:10 am
That was fine.
But could U tell what U would look for If such performance
problems happens with U.
regards
shown
July 14, 2005 at 6:19 am
I would want to know a lot more before even formulating a strategy. First of all, what is the query. A simple SELECT * FROM dbo.[ListOfUsers] is not likely to be a problem but a big query with lots of joins and linked servers would make a SQL cause much more likely. There isn't one strategy. That's the point. You need to be flexible and think across the whole system - including the network - not just your bit of it. In my experience, problems like that frequently are network or workstation related rather than database related ... but, of course, the first thing to ask is 'Has anything like this happened before and did we find out what caused it?'.
July 14, 2005 at 6:49 am
I think I got it cleared by u'r answers.
regards
shown
July 14, 2005 at 7:18 am
One thing not mentioned, is there a difference in parameters being passed by the various users? Are the first three calling proc A and passing in a parameter that returns three rows and the fourth is calling proc A with a parameter that returns three thousand? I'd put Profiler to work on the issue. Without accurate data as to the reason why the slowdown is occuring, the rest is speculation.
"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
July 15, 2005 at 5:04 am
The last person needs aditional resources dynamically assigned by the server: locks for example.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply