Long running query

Hello group,
We have a query that calculates certain dates, etc. It runs very slow on
this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
Windows 2003 server and SQL 2000. If you copy the database over to another
server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
and run the same query it runs in 2-3 minutes. This is a huge gap of time
and I don't see a whole lot of performance changes using perfmon on the
troubled server.
Any ideas what I can use to troubleshoot this? Do you think this may be
hardware related or database or ?
TIA,
Jason
There can be many reasons. Did you verify using sp_who that this query is
not getting blocked? Did you monitor the physical disk counters to see if
there are any issues with disk subsystem performance?
What about the execution plans? Did you compare the execution plan of the
queries between your server and workstation? This will help in determining
any missing indexes or out of date stats.
Worth checking the fragmentation on your bigger tables and also updating the
statistics.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jason King" <jasonk@.bham.wednet.edu> wrote in message
news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello group,
> We have a query that calculates certain dates, etc. It runs very slow on
> this server (3 hours!) which is a Gateway quad 600mhz with 2GB RAM running
> Windows 2003 server and SQL 2000. If you copy the database over to another
> server or XP workstation (even running a single 2.6 GHZ CPU with 1gb ram)
> and run the same query it runs in 2-3 minutes. This is a huge gap of time
> and I don't see a whole lot of performance changes using perfmon on the
> troubled server.
> Any ideas what I can use to troubleshoot this? Do you think this may be
> hardware related or database or ?
> TIA,
> Jason
>
|||Thanks. We found the problem. It was due to passing NULL parameters within
the SP for retrieving certain dates. If we call the dates first then execute
the procedure without any NULL parameters, it runs very fast.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uIDsfRnOFHA.3280@.TK2MSFTNGP10.phx.gbl...
> There can be many reasons. Did you verify using sp_who that this query is
> not getting blocked? Did you monitor the physical disk counters to see if
> there are any issues with disk subsystem performance?
> What about the execution plans? Did you compare the execution plan of the
> queries between your server and workstation? This will help in determining
> any missing indexes or out of date stats.
> Worth checking the fragmentation on your bigger tables and also updating
the[vbcol=seagreen]
> statistics.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jason King" <jasonk@.bham.wednet.edu> wrote in message
> news:%23ETRjMgOFHA.3356@.TK2MSFTNGP12.phx.gbl...
running[vbcol=seagreen]
another[vbcol=seagreen]
ram)[vbcol=seagreen]
time
>
sql