Long running SP's - sp_prepexec

Hi All:
We have a booking engine for resorts reservations(cold fusion MX and
sql server), our sql server is performing real slow, cpu utilization
is abt 70 -95 % all the time.
Result of Long running SPs contained something like this :
declare @.P1 int set @.P1=NULL declare @.P2 varchar(8000) set @.P2=NULL
exec sp_prepexec @.P1 output, 210, 145, '02/14/04', '02/22/04', 600, 1,
@.P2 output select @.P1, @.P2 151.34 usr001 NULL server_name NULL 65
2003-08-06 11:35:47.733 2003-08-06 11:38:19.077
Please let me know what could be the problem.
Regards
Bhakta RamRam,
Not easy to pinpoint the problem rightaway, especially if its the adhoc
query issue.You can find troubleshooting details here:
HOW TO: Troubleshoot Application Performance with SQL Server
http://www.support.microsoft.com/?id=224587
HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/default.aspx?scid=kb;EN-US;243588
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"BHakta Ram" <bhaktaram@.yahoo.com> wrote in message
news:57bb2f4.0308070727.4a66e989@.posting.google.com...
> Hi All:
> We have a booking engine for resorts reservations(cold fusion MX and
> sql server), our sql server is performing real slow, cpu utilization
> is abt 70 -95 % all the time.
> Result of Long running SPs contained something like this :
> declare @.P1 int set @.P1=NULL declare @.P2 varchar(8000) set @.P2=NULL
> exec sp_prepexec @.P1 output, 210, 145, '02/14/04', '02/22/04', 600, 1,
> @.P2 output select @.P1, @.P2 151.34 usr001 NULL server_name NULL 65
> 2003-08-06 11:35:47.733 2003-08-06 11:38:19.077
> Please let me know what could be the problem.
> Regards
> Bhakta Ram

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

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,
JasonThere 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
> 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[vbcol=seagreen]
>

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,
JasonThere 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
> 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
> >
> >
>