October 17, 2005 at 11:47 am
We have a 110 GB production database and if some one runs a query from QUERY analyzer it virtually becomes single user database! Till the query is not done nobody can access the database. It is a three tier system and we don't see this problem when some one accesses the system from application.
Is this typical!
Really appreciate peoples ideas opinions on this.
Thanks.
Kay.
October 17, 2005 at 12:12 pm
What's the query ran from QA?
October 17, 2005 at 12:13 pm
Is this the same query that's being run from query analyzer as from the application?
If not do are the people writing the ad-hoc queries in query analyzer proficient in SQL?
If you could give some examples it would help.
David
October 17, 2005 at 12:28 pm
Yes. It is the same query that is being run in the application.
Login user has normal select privileges. No update privileges or anything.
Infact the query was captured from SQL Profiler for tuning purposes. If this helps.
Thanks.
Kay
October 17, 2005 at 12:46 pm
Is the query captured from sql profiler part of a stored procedure?
If so have you captured the execution plan for this procedure from sql profiler and compared it to the estimated execution plan produced by query analyzer?
If they are not the same you may find that becuase the procedure form the application is executed a lot that an execution plan from the procedure cache is being used for the stored procedure (if its part of one) and the query when executed as an ad-hoc query in query analyzer is not choosing the same execution plan because the statistics etc are so out of date that the query optimser has decided not use them.
hth
David
October 17, 2005 at 1:12 pm
Is the query captured from sql profiler part of a stored procedure?
No.
"If so have you captured the execution plan for this procedure from sql profiler and compared it to the estimated execution plan produced by query analyzer?"
I captured the execution plans from the application and query analyzer but didn't compare them. I assumed that both will follow the same execution plan. Probably I was WRONG on this assumption!!!
When some of the same queries are run from the query analyzer the database is coming to virtually standstill condition. That's very interesting!
Why is SQL Server is behaving like a single user systems beats my imagination.
Thanks for keeping the thread going.
Kay
October 18, 2005 at 1:30 am
Is the application using a different isolation level than you are using via Query Analyser e.g. READ UNCOMMITTED as opposed to READ COMMITTED?
October 18, 2005 at 2:12 am
How many processors does your server have?
What connection settings/SET options is the app using?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2005 at 8:05 am
Just a thought....
Could it be that the application is only reading the first row of the resultset and then disconnecting or closing? Not that it would be a good idea to do that, but I've seen it done before.
Someone by accident thought they were supposed to use:
Select @@identity from
instead of just:
Select @@identity
Now with 8 million rows in the table, the query would take a very long time to return in QA, but the application just read the first row and got the corect identity value.
jg
October 18, 2005 at 11:46 am
"Is the application using a different isolation level than you are using via Query Analyser e.g. READ UNCOMMITTED as opposed to READ COMMITTED?"
This is normal in any development environment, am i right!!!
Will this give an order of magnitude difference in execution times?
Kay.
October 18, 2005 at 11:48 am
Have you tried the NoLock hint on the query?
October 18, 2005 at 2:16 pm
Is the application using a different isolation level than you are using via Query Analyser e.g. READ UNCOMMITTED as opposed to READ COMMITTED? This is normal in any development environment, am i right!!!Will this give an order of magnitude difference in execution times?
READ COMMITTED will potentially be blocked by transactions updating the table, but READ UNCOMMITTED will not. If there is a lot of update activity, or any long running transactions, then there could be a magnitude of difference in execution times.
Use SQL profiler to profile the queries from the application and Query Analyser.
If the CPU usage and Disk IO are the same, but the total duration is hugely different, then there's a good chance you are suffering from blocking.
If there are big differences in CPU or Disk IO, then SQL Server is generating different query plans, possibly due to some other connection specific options e.g. ANSI_NULLS.
October 18, 2005 at 2:23 pm
BK...
Can you post the query and execution plan? Both from QA and production. You can capture the plans in the profiler.
October 19, 2005 at 12:19 am
This is normal in any development environment, am i right!!!
What do you mean? It should never be considered normal to allow dirty reads.
Will this give an order of magnitude difference in execution times?
Sure, if a query is blocked it is blocked until the blocking query releases it's locks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply