Performance drecrease

Hello, I'm executing a sql sentence on my sql server 2000 sp3, and some times It takes about 3 sec to execute It... and some times It takes about 5 min and later 3 sec again and so on ....

Could you please explain me this behavior ? This is so confusing cause It souldn't be happening and I'm working alone in this server... then no other process is running here...

any suggestion?

Thanks,

is it the same query/procedure ... Update the statistics and check the indexes also... if need rebuilt that

Madhu

|||

Hello Julian,

As suggested by Madhu,please reindex and update stats before running the query again.

Is it a select statement with full outer joins and functions like convert etc.

There is a bug in SP3 that causes such behaviour with optimizer.

This has been fixed in SP4 + 2187.

http://support.microsoft.com/default.aspx/kb/830298/EN-US/

regards

Jag

|||

Hello Jag...

What does + 2187 mean?

|||

refer this:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9C9AB140-BDEE-44DF-B7A3-E6849297754A&displaylang=en

Madhu

|||It means SP4 plus a hotfix to bring it up to build 2187|||Cheers Glen

Performance- Buffer Manager:Page Lookups/Sec

We have a BI app that were testing, running many different reports. Under
some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
high. I was wondering if this normal behaviour, and if not, what can be
done to ameliorate it.
Thanks in advance
Not sure if you know this or not, but the page lookups/sec counter is:
Number of requests to find a page in the buffer pool.
So depending on what you are doing this could be normal. It appears that you
are retrieving data and SQL Server first trying to find the data in the
memory.
HTH,
DeeJay Puar
MCDBA
"MAS" wrote:

> We have a BI app that were testing, running many different reports. Under
> some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
> high. I was wondering if this normal behaviour, and if not, what can be
> done to ameliorate it.
> Thanks in advance
>
>

Performance- Buffer Manager:Page Lookups/Sec

We have a BI app that were testing, running many different reports. Under
some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
high. I was wondering if this normal behaviour, and if not, what can be
done to ameliorate it.
Thanks in advanceNot sure if you know this or not, but the page lookups/sec counter is:
Number of requests to find a page in the buffer pool.
So depending on what you are doing this could be normal. It appears that you
are retrieving data and SQL Server first trying to find the data in the
memory.
HTH,
DeeJay Puar
MCDBA
"MAS" wrote:

> We have a BI app that were testing, running many different reports. Under
> some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
> high. I was wondering if this normal behaviour, and if not, what can be
> done to ameliorate it.
> Thanks in advance
>
>

Performance- Buffer Manager:Page Lookups/Sec

We have a BI app that were testing, running many different reports. Under
some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
high. I was wondering if this normal behaviour, and if not, what can be
done to ameliorate it.
Thanks in advanceNot sure if you know this or not, but the page lookups/sec counter is:
Number of requests to find a page in the buffer pool.
So depending on what you are doing this could be normal. It appears that you
are retrieving data and SQL Server first trying to find the data in the
memory.
HTH,
DeeJay Puar
MCDBA
"MAS" wrote:
> We have a BI app that were testing, running many different reports. Under
> some conditions the Buffer Manager:Page Lookups/Sec counter spikes very
> high. I was wondering if this normal behaviour, and if not, what can be
> done to ameliorate it.
> Thanks in advance
>
>sql

Perfmon/Profiler confusion

I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).
Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).
|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.

> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a proc
> is recompiled before it even starts executing.
>
|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.

Perfmon/Profiler confusion

I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.
> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a proc
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.

Perfmon/Profiler confusion

I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.

> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a pro
c
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.

PerfMon- SQL Server:Databases counter

I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***
I checked this on Windows 2003 system monitor as well, and the problem is
still there. I am not sure if there's any other way out.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Toni" <teibner@.SQLallina.com> wrote in message
news:u3SamtejFHA.1416@.TK2MSFTNGP09.phx.gbl...
I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***

PerfMon- SQL Server:Databases counter

I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***I checked this on Windows 2003 system monitor as well, and the problem is
still there. I am not sure if there's any other way out.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Toni" <teibner@.SQLallina.com> wrote in message
news:u3SamtejFHA.1416@.TK2MSFTNGP09.phx.gbl...
I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***

PerfMon- SQL Server:Databases counter

I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.developersdex.com ***I checked this on Windows 2003 system monitor as well, and the problem is
still there. I am not sure if there's any other way out.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Toni" <teibner@.SQLallina.com> wrote in message
news:u3SamtejFHA.1416@.TK2MSFTNGP09.phx.gbl...
I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.developersdex.com ***