bad performance, help needed

Hi,
We have a working application that has been ok for some time but recently as
our data and users increased we started to get timeouts as some queries
take more than minute to execute. This happens only occasionally on a very
specific query that joins couple of big tables.
Now the queries are fairly complex and unoptimized but that is not what
bothers me. (also changing queries didn't help, may be something to do with
amounts of data involved?)
If I run the same query as SA it runs in a second. If I run it as a normal
user that has been given select permissions on the relevant tables it takes
ages. Are there any settings for normal users regarding limits on resource
or something like that?
We only support two users SA and TheUser as authentication is done server
side and everything is done through stored procedures spare the read
queries. TheUser is granted permissions to read some tables and to execute
the stored procedures nothing else.
Using the SQL Trace thing... I can see that the offending query does approx
350000 reads and 20 writes. I don't know what that means but thought it
might help you help me ;)
Thank you,
YordanIt is impossible to say without actually seeing the query and the DDL for
the tables involved but it sounds liek it is not using an index and scanning
the table at least once, probably more. Can you post the other info?
Andrew J. Kelly SQL MVP
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Yordan,
Are you logging into Query Analyzer using SA and TheUser to make the
comparisons in time? Same T-SQL code? Are there any differences in the
execution plans chosen?
HTH
Jerry
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||If timeouts occur "only occasionally", then perhaps your problem is with
blocking:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Use SQL Server Profiler to verify that your SP is making good use of
indexes. Also, as your table gets larger, fragmentation may become an issue
that needs periodic maintenance.
How To: Use SQL Profiler
http://msdn.microsoft.com/library/d...
ethowto15.asp
Planning and Creating Indexes
http://www.microsoft.com/technet/pr...s/c0618260.mspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Yordan...
How often do you reindex your database tables?
Yosh
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Tables are not indexed... (yeah, I know)
I'll probably index to improve but I don't understand the difference the
actual user makes.Why would it work with SA? Is there any quota on resources
spared on one user? Session? I'd like to understand that...
Also same query runs fine most of the time but occasionally is taking >
minute.
its something of the sort (the actual query is too long):
select some stuff from table1 outer left join (select * from table 2) ON
(conditions) where other conditions
table1 is approx 40,000 rows
table2 is approx 600,000 rows
-Yordan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OQ1SjFdzFHA.2652@.TK2MSFTNGP14.phx.gbl...
> It is impossible to say without actually seeing the query and the DDL for
> the tables involved but it sounds liek it is not using an index and
> scanning the table at least once, probably more. Can you post the other
> info?
> --
> Andrew J. Kelly SQL MVP
>
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Thanks for the links. I'll try this tomorrow as I'm home now.
To be honest it does feel like a block because I've seen few before. Its
just I don't see how it can be this time.
Thanks,
Yordan
"JT" <someone@.microsoft.com> wrote in message
news:e%23daTHdzFHA.692@.TK2MSFTNGP10.phx.gbl...
> If timeouts occur "only occasionally", then perhaps your problem is with
> blocking:
> INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
> http://support.microsoft.com/defaul...kb;EN-US;224453
> Use SQL Server Profiler to verify that your SP is making good use of
> indexes. Also, as your table gets larger, fragmentation may become an
> issue that needs periodic maintenance.
> How To: Use SQL Profiler
> http://msdn.microsoft.com/library/d...enethowto15.asp
> Planning and Creating Indexes
> http://www.microsoft.com/technet/pr...s/c0618260.mspx
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
>
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||I make my app dump the query in text. (so T-SQL code is the same).
Then I use the Query Analyzer to log-in and execute it. Using TheUser
outcomes are identical. With SA I've tried only the Analyser.
The difference in time is obvious. The exact times I get from the server
manager - using Trace (not sure about the name).
Thanks,
Yordan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OnLPIGdzFHA.3256@.TK2MSFTNGP09.phx.gbl...
> Yordan,
> Are you logging into Query Analyzer using SA and TheUser to make the
> comparisons in time? Same T-SQL code? Are there any differences in the
> execution plans chosen?
> HTH
> Jerry
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Here is an easy way to tell if it is a blocking problem. The next time a
query seems to be hung up, then run sp_who2. If a process has another
processes's SPID listed in the [blkby] column, then it is being blocked.
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%23u6dfXdzFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Thanks for the links. I'll try this tomorrow as I'm home now.
> To be honest it does feel like a block because I've seen few before. Its
> just I don't see how it can be this time.
> Thanks,
> Yordan
> "JT" <someone@.microsoft.com> wrote in message
> news:e%23daTHdzFHA.692@.TK2MSFTNGP10.phx.gbl...
>|||Is it possible that since you run the same exact query as SA the execution
plan is already cached from the app and SQL server returns the result much
faster a second time
if the app user runs the same query twice does it take a long time both time
s?
http://sqlservercode.blogspot.com/
"Yordan Gyurchev" wrote:

> I make my app dump the query in text. (so T-SQL code is the same).
> Then I use the Query Analyzer to log-in and execute it. Using TheUser
> outcomes are identical. With SA I've tried only the Analyser.
> The difference in time is obvious. The exact times I get from the server
> manager - using Trace (not sure about the name).
> Thanks,
> Yordan
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OnLPIGdzFHA.3256@.TK2MSFTNGP09.phx.gbl...
>
>