Lock counters - odd values

Hi,
{SQL Server 2000}
I'm just beginning to look at counters, so sorry if these are obvious.
I've just taken a look at sysperfinfo and found these values:
SQLServer:Locks Lock Requests/sec _Total -249742468
SQLServer:Locks Lock Timeouts/sec _Total 315800
SQLServer:Locks Number of Deadlocks/sec _Total 0
SQLServer:Locks Lock Waits/sec _Total 93
SQLServer:Locks Lock Wait Time (ms) _Total 6787689
SQLServer:Locks Average Wait Time (ms) _Total 6787689
SQLServer:Locks Average Wait Time Base _Total 93
Is it okay to look at _total or is it misleading?
Why is Lock Requests/sec negative?
The wait time looks very large: is this usual?
Over what time period are the averages based? How do you reset the
averages?
Cheers,
JamesI don't know the specifics of this particular counter. But not all perfmon
counters are properly instrumented. For instance, at least one of the SQL
waits counters I often monitor would display a very huge ridiculous number
from time to time, and %Disk Time is often well over 100%.
When I run into a situation like this, I know I can't rely on the counter
and I'll just skip the counter and focus on other counters instead. The good
news is that not getting proper values from one or two counters doesn't mean
you can't get a good assessment of your system.
Linchi
"JimLad" wrote:
> Hi,
> {SQL Server 2000}
> I'm just beginning to look at counters, so sorry if these are obvious.
> I've just taken a look at sysperfinfo and found these values:
> SQLServer:Locks Lock Requests/sec _Total -249742468
> SQLServer:Locks Lock Timeouts/sec _Total 315800
> SQLServer:Locks Number of Deadlocks/sec _Total 0
> SQLServer:Locks Lock Waits/sec _Total 93
> SQLServer:Locks Lock Wait Time (ms) _Total 6787689
> SQLServer:Locks Average Wait Time (ms) _Total 6787689
> SQLServer:Locks Average Wait Time Base _Total 93
> Is it okay to look at _total or is it misleading?
> Why is Lock Requests/sec negative?
> The wait time looks very large: is this usual?
> Over what time period are the averages based? How do you reset the
> averages?
> Cheers,
> James
>|||On Oct 19, 3:28 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> I don't know the specifics of this particular counter. But not all perfmon
> counters are properly instrumented. For instance, at least one of the SQL
> waits counters I often monitor would display a very huge ridiculous number
> from time to time, and %Disk Time is often well over 100%.
> When I run into a situation like this, I know I can't rely on the counter
> and I'll just skip the counter and focus on other counters instead. The good
> news is that not getting proper values from one or two counters doesn't mean
> you can't get a good assessment of your system.
> Linchi
>
> "JimLad" wrote:
> > Hi,
> > {SQL Server 2000}
> > I'm just beginning to look at counters, so sorry if these are obvious.
> > I've just taken a look at sysperfinfo and found these values:
> > SQLServer:Locks Lock Requests/sec _Total -249742468
> > SQLServer:Locks Lock Timeouts/sec _Total 315800
> > SQLServer:Locks Number of Deadlocks/sec _Total 0
> > SQLServer:Locks Lock Waits/sec _Total 93
> > SQLServer:Locks Lock Wait Time (ms) _Total 6787689
> > SQLServer:Locks Average Wait Time (ms) _Total 6787689
> > SQLServer:Locks Average Wait Time Base _Total 93
> > Is it okay to look at _total or is it misleading?
> > Why is Lock Requests/sec negative?
> > The wait time looks very large: is this usual?
> > Over what time period are the averages based? How do you reset the
> > averages?
> > Cheers,
> > James- Hide quoted text -
> - Show quoted text -
Thanks. Can anyone comment more specifically on this case?
Cheers,
James|||On Oct 19, 4:56 pm, JimLad <jamesdbi...@.yahoo.co.uk> wrote:
> On Oct 19, 3:28 pm, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>
>
> > I don't know the specifics of this particular counter. But not all perfmon
> > counters are properly instrumented. For instance, at least one of the SQL
> > waits counters I often monitor would display a very huge ridiculous number
> > from time to time, and %Disk Time is often well over 100%.
> > When I run into a situation like this, I know I can't rely on the counter
> > and I'll just skip the counter and focus on other counters instead. The good
> > news is that not getting proper values from one or two counters doesn't mean
> > you can't get a good assessment of your system.
> > Linchi
> > "JimLad" wrote:
> > > Hi,
> > > {SQL Server 2000}
> > > I'm just beginning to look at counters, so sorry if these are obvious.
> > > I've just taken a look at sysperfinfo and found these values:
> > > SQLServer:Locks Lock Requests/sec _Total -249742468
> > > SQLServer:Locks Lock Timeouts/sec _Total 315800
> > > SQLServer:Locks Number of Deadlocks/sec _Total 0
> > > SQLServer:Locks Lock Waits/sec _Total 93
> > > SQLServer:Locks Lock Wait Time (ms) _Total 6787689
> > > SQLServer:Locks Average Wait Time (ms) _Total 6787689
> > > SQLServer:Locks Average Wait Time Base _Total 93
> > > Is it okay to look at _total or is it misleading?
> > > Why is Lock Requests/sec negative?
> > > The wait time looks very large: is this usual?
> > > Over what time period are the averages based? How do you reset the
> > > averages?
> > > Cheers,
> > > James- Hide quoted text -
> > - Show quoted text -
> Thanks. Can anyone comment more specifically on this case?
> Cheers,
> James- Hide quoted text -
> - Show quoted text -
Hi,
To answer my own question:
BUG: sysperfinfo counters are not time-adjusted
http://support.microsoft.com/kb/555064%20
This basically says that you can't use any /sec counters from
sysperfinfo.
As for the Average Wait Time (ms), you have to divide this by the base
figure of 93, which still gives me an avergae wait of 73 seconds!
I think I'm going to abandon sysperfinfo and just use the figures from
Perf Mon. Hopefully any calculations have already been performed on
them.
James

localhost/reports not found

Everything works ok at work and so I decided to install it at home on Windows 2003.

For some odd reason I can run my report this way:

http://localhost/ReportServer?%2fReport+Project1%2fReportFirstTest&rs:Command=Render

But if I try this..

http://localhost/reports

all I get is "The page cannot be found"

I can also get to the report by clicking on links found on the page I get usinghttp://localhost/ReportServer

I'd guess an IIS setting got messed up somehow, anyone know?

1. Make sure that Home.aspx is a default webpage for the related web site in the IIS, or try thishttp://localhost/reports/home.aspx ; 2. Make sure that the asp.net 2.0 version is selected for this web site; 3. It's better to have an own pool for asp.net 2.0 applications in the win 2003.

Local temporary table schema

It seems odd that you would need to do this, since your code for the current
connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT *
FROM Tempdb..sysobjects
WHERE name LIKE '%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#Temp')
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4
939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table in S
QL
> Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> multiple connections use the same temp table name, there doesn't seem to b
e a
> way to differentiate between them. Each table name is appended with a bunc
h
> of underscores and a random number (i.e.
> " #MyTable________________________________
_________________________________
________________________________________
_0000000002B0"),
> but that doesn't seem to help much.
>
> Thanks,
>
> Rich WoodIs there a way to get the schema (columns) of a local temporary table in SQL
Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
multiple connections use the same temp table name, there doesn't seem to be
a
way to differentiate between them. Each table name is appended with a bunch
of underscores and a random number (i.e.
" #MyTable________________________________
___________________________________
_______________________________________0
000000002B0"),
but that doesn't seem to help much.
Thanks,
Rich Wood|||It seems odd that you would need to do this, since your code for the current
connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT *
FROM Tempdb..sysobjects
WHERE name LIKE '%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#Temp')
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4
939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table in S
QL
> Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> multiple connections use the same temp table name, there doesn't seem to b
e a
> way to differentiate between them. Each table name is appended with a bunc
h
> of underscores and a random number (i.e.
> " #MyTable________________________________
_________________________________
________________________________________
_0000000002B0"),
> but that doesn't seem to help much.
>
> Thanks,
>
> Rich Wood|||Arnie Rowland wrote:

> It seems odd that you would need to do this, since your code for the curr=
ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=
54-45EB-8978-6391D4939CD1@.microsoft.com...
n SQL[vbcol=seagreen]
if[vbcol=seagreen]
o be a[vbcol=seagreen]
unch[vbcol=seagreen]
________________________________________
____0000000002B0"),[vbcol=seagreen]
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
> <META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2>It seems odd that you would need to do t=
his, since
> your code for the current connection had to know how to create the #Temp
> Table.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>However, if you must, you could use this=
as a
> starting point:</FONT></DIV>
> <DIV><BR><FONT face=3D"Courier New" size=3D2>SELECT * </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>FROM Tempdb..sysobje=

cts
> </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>WHERE name LIKE '%#Temp%'</FONT>=
</DIV><FONT
> face=3D"Courier New" size=3D2>
> <DIV><BR>SELECT * </DIV>
> <DIV>FROM Tempdb..syscolumns </DIV>
> <DIV>WHERE id =3D object_ID('tempdb..#Temp')</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, YACE* <BR>"To =
be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>*Yet Another certification Exam</FONT></=
DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>"Rich Wood" <</FONT><A
> href=3D"mailto:RichWood@.newsgroup.nospam"><FONT face=3DArial
> size=3D2>RichWood@.newsgroup.nospam</FONT></A><FONT face=3DArial size=3D2>=
> wrote in
> message </FONT><A
> href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"><FONT fa=
ce=3DArial
> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com</FONT></=
A><FONT
> face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> Is=
there a way to
> get the schema (columns) of a local temporary table in SQL <BR>> Serve=
r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if <BR>> mul=
tiple
> connections use the same temp table name, there doesn't seem to be a <BR>=
>
> way to differentiate between them. Each table name is appended with a bun=
ch
> <BR>> of underscores and a random number (i.e. <BR>>
> " #MyTable________________________________
________________________________=
________________________________________
__0000000002B0"),
> <BR>> but that doesn't seem to help much.<BR>> <BR>> Thanks,<BR>=
>
> <BR>> Rich Wood</FONT></BODY></HTML>
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use=20
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't kno
w
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It seems odd that you would need to do this, since your code for the curre
nt connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-075
4-45EB-8978-6391D4939CD1@.microsoft.com...|||Arnie Rowland wrote:

> It seems odd that you would need to do this, since your code for the curr=
ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=
54-45EB-8978-6391D4939CD1@.microsoft.com...
n SQL[vbcol=seagreen]
if[vbcol=seagreen]
o be a[vbcol=seagreen]
unch[vbcol=seagreen]
________________________________________
____0000000002B0"),[vbcol=seagreen]
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
> <META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2>It seems odd that you would need to do t=
his, since
> your code for the current connection had to know how to create the #Temp
> Table.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>However, if you must, you could use this=
as a
> starting point:</FONT></DIV>
> <DIV><BR><FONT face=3D"Courier New" size=3D2>SELECT * </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>FROM Tempdb..sysobje=

cts
> </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>WHERE name LIKE '%#Temp%'</FONT>=
</DIV><FONT
> face=3D"Courier New" size=3D2>
> <DIV><BR>SELECT * </DIV>
> <DIV>FROM Tempdb..syscolumns </DIV>
> <DIV>WHERE id =3D object_ID('tempdb..#Temp')</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, YACE* <BR>"To =
be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>*Yet Another certification Exam</FONT></=
DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>"Rich Wood" <</FONT><A
> href=3D"mailto:RichWood@.newsgroup.nospam"><FONT face=3DArial
> size=3D2>RichWood@.newsgroup.nospam</FONT></A><FONT face=3DArial size=3D2>=
> wrote in
> message </FONT><A
> href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"><FONT fa=
ce=3DArial
> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com</FONT></=
A><FONT
> face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> Is=
there a way to
> get the schema (columns) of a local temporary table in SQL <BR>> Serve=
r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if <BR>> mul=
tiple
> connections use the same temp table name, there doesn't seem to be a <BR>=
>
> way to differentiate between them. Each table name is appended with a bun=
ch
> <BR>> of underscores and a random number (i.e. <BR>>
> " #MyTable________________________________
________________________________=
________________________________________
__0000000002B0"),
> <BR>> but that doesn't seem to help much.<BR>> <BR>> Thanks,<BR>=
>
> <BR>> Rich Wood</FONT></BODY></HTML>
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use=20
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't kno
w
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It seems odd that you would need to do this, since your code for the curre
nt connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-075
4-45EB-8978-6391D4939CD1@.microsoft.com...