SQL Server 2000

24 Jan 2006 2:28 PM

Joseph

My users are complaining that running queries are taking too long. What
normally runs in less than 2 minutes is taking over 30 minutes. This only
just started happening. The user interface is MS Access 2000 and XP. The
actual data resides on my SQL Server 2000.

I am completely new to this company and environment and I do not know how to
troubleshoot. When I use Query Analyzer to run the exact same query, it only
takes a few seconds. All assistance/ideas are welcome.

24 Jan 2006 2:34 PM

Tibor Karaszi

I'd start with checking if you get the same execution plan between the fast (QA) and slow (Access)
query. You can get the execution plan In the Profiler tool.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quoteHide quote

"Joseph" <Jos***@discussions.microsoft.com> wrote in message
news:D017FEEE-6902-4697-B02A-67C4F8326461@microsoft.com...
> My users are complaining that running queries are taking too long. What
> normally runs in less than 2 minutes is taking over 30 minutes. This only
> just started happening. The user interface is MS Access 2000 and XP. The
> actual data resides on my SQL Server 2000.
>
> I am completely new to this company and environment and I do not know how to
> troubleshoot. When I use Query Analyzer to run the exact same query, it only
> takes a few seconds. All assistance/ideas are welcome.

24 Jan 2006 2:54 PM

Steve L

Do you defrag your indexes etc.... - try running a profiler as well
(replay one, but add the duration column in) to see if anything jumps
out at you. Also check for any blocking.

Also, review your indexes, the structure of them needs to be tweaked
once tables grow to certain sizes as the old indexes become ineficient.
You could also try looking for a lot of table scans as well - these are
very bad for performance on large datasets

Steve L
Dominicus Data Systems