new thread -- how to add counters to the server performance monitor

thank you so much, John. Sorry for couldn't response to
you earlier. Been really busy lately.
I don't know whether this is something safe to do because
sometimes certain counter got disabled by the system
automatically because they have errors in them. If re-
active them, may cause problem to the server or slow the
system down.
I could very well be wrong at this. Just want to make
sure with you first.
Please see below quoted from window's help article:
----
Cause: Test routines that run when you start Performance
have detected a problem with installed counters and have
disabled the counters automatically to prevent the
counters from slowing the system. Disabled objects and
counters do not appear in the Add Counters dialog box.
Solution: Using Registry Editor, change the value under
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Servic
e_name\Performance\Disable Performance Counters from 1 for
disable to 0 for enable. Note that counters that have been
disabled after initial testing are likely to contain
errors and may cause system problems. For information
about debugging problems with counter DLLs, see the
Microsoft Web site (http://msdn.microsoft.com/).
Caution
.. Incorrectly editing the registry may severely
damage your system. Before making changes to the registry,
you should back up any valued data on the computer.
----
Thanks.
JJ
>--Original Message--
>JJ
>
>Sometimes, for a wide variety of different reasons, the
>SQL Server Performance Monitor counters will not show up
>as they should. Often, but not always, this problem can
be
>fixed by following these three steps:
>1) At the command prompt, type in the following:
>unlodctr.exe MSSQLServer
>
>2) Then type in the following: lodctr.exe <SQL Server
>path>\binn\sqlctr.ini
>
>3) Reboot the server
>
>Hope this helps
>
>JohnJJ
The article you quote may be the reason the fix does not
always work. I don't think it will do any harm to try it.
The only issue may be when to do the reboot. I know where
I work you can not just perform a reboot, it would need to
be planed, approved and scheduled.
Good luck
John|||thanks again, John!!
>--Original Message--
>JJ
>The article you quote may be the reason the fix does not
>always work. I don't think it will do any harm to try it.
>The only issue may be when to do the reboot. I know where
>I work you can not just perform a reboot, it would need
to
>be planed, approved and scheduled.
>Good luck
>John
>.
>|||Hi John,
under which directory should I run the unlodctr.exe and
lodctr.exe according to you?
1) At the command prompt, type in the following:
unlodctr.exe MSSQLServer
2) Then type in the following: lodctr.exe <SQL Server
path>\binn\sqlctr.ini
Thanks.
JJ
>--Original Message--
>JJ
>The article you quote may be the reason the fix does not
>always work. I don't think it will do any harm to try it.
>The only issue may be when to do the reboot. I know where
>I work you can not just perform a reboot, it would need
to
>be planed, approved and scheduled.
>Good luck
>John
>.
>|||JJ
I found unlodctr.exe and lodctr.exe in C:\\WINNT\system32
on my workstation. Right click on my computer and use find
to check for location on yours.
Regards
John
>--Original Message--
>Hi John,
>under which directory should I run the unlodctr.exe and
>lodctr.exe according to you?
>1) At the command prompt, type in the following:
>unlodctr.exe MSSQLServer
>2) Then type in the following: lodctr.exe <SQL Server
>path>\binn\sqlctr.ini
>Thanks.
>JJ
>>--Original Message--
>>JJ
>>The article you quote may be the reason the fix does not
>>always work. I don't think it will do any harm to try
it.
>>The only issue may be when to do the reboot. I know
where
>>I work you can not just perform a reboot, it would need
>to
>>be planed, approved and scheduled.
>>Good luck
>>John
>>.
>.
>|||yes, mine are in the same location too. for lodctr.exe,
it's also in C:\WINNT\ServicePackFiles\i386. Same size,
same date. How come?
so I should run the code under C:\\WINNT\system32?
Thanks, John.
JJ
>--Original Message--
>JJ
>I found unlodctr.exe and lodctr.exe in C:\\WINNT\system32
>on my workstation. Right click on my computer and use find
>to check for location on yours.
>Regards
>John
>>--Original Message--
>>Hi John,
>>under which directory should I run the unlodctr.exe and
>>lodctr.exe according to you?
>>1) At the command prompt, type in the following:
>>unlodctr.exe MSSQLServer
>>2) Then type in the following: lodctr.exe <SQL Server
>>path>\binn\sqlctr.ini
>>Thanks.
>>JJ
>>--Original Message--
>>JJ
>>The article you quote may be the reason the fix does
not
>>always work. I don't think it will do any harm to try
>it.
>>The only issue may be when to do the reboot. I know
>where
>>I work you can not just perform a reboot, it would need
>>to
>>be planed, approved and scheduled.
>>Good luck
>>John
>>.
>>.
>.
>

New idea to use the EXCEPT operator to paging

Last night, I have this idea, but can not know if it can have a good performance and efficiency when the @.CurrentPage is big.

--------------------

--DATABASE:AdventureWorks

DECLARE @.CurrentPage int
DECLARE @.PageSize int
DECLARE @.OrderExpression nvarchar(100)
DECLARE @.Sql nvarchar(500)

SET @.CurrentPage = 1
SET @.PageSize = 10
SET @.OrderExpression = N' employeeid '

DECLARE @.BigTop int
DECLARE @.SmallTop int
SET @.BigTop = @.CurrentPage * @.PageSize
SET @.SmallTop = (@.CurrentPage -1) * @.PageSize

DECLARE @.StartTime datetime
SET @.StartTime = GETDATE()

SET @.Sql = N' SELECT TOP (' + CAST(@.BigTop AS nvarchar(10)) + ') * FROM humanresources.Employee '
+ ' EXCEPT '
+ ' SELECT TOP (' + CAST(@.SmallTop AS nvarchar(10)) + ') * FROM humanresources.Employee ORDER BY ' + @.OrderExpression

EXEC sp_executesql @.Sql

DECLARE @.EndTime datetime
SET @.EndTime = GETDATE()

SELECT DATEPART(s,@.EndTime-@.StartTime)
SELECT DATEPART(ms,@.EndTime-@.StartTime)

GO

--------------------

It does get progressively slower as the number of records you are dealing with increases.

:(

|||

What's really needed in t-sql is this:

select ROWS 50 to 75 * from some_table order by some_column

It would remove the need for so many additional passes thru the data that other approaches require.

(And if someone knows a trick to do that, speak up now!)

|||

I have done some tests, comparee several different stored procedures, found this has a very good performance when the @.CurrentPage is less than 500 and @.PageSize = 10, in general use, this is enough.

dose anybody have interesting to do more test and post the result?

|||

Another way, by using the operator IN, can have an average speed when the @.CurrentPage is smaller and bigger, but when the @.CurrentPage is smaller, it is slower than not use "IN"
...

...

SELECT * FROM Employee WHERE employeeid IN (
SELECT TOP (@.BigTop) employeeid FROM Employee
EXCEPT
SELECT TOP (@.SmallTop) employeeid FROM Employee ORDER BY employeeid
)

...

...

|||

I made a new test,I copy the HumanResources.Employee table of AdventureWorks database to a new table, and add to about 200,000 rows,

test the performance of the following 3 different ways:

------1 start----------

DECLARE @.CurrentPage int
DECLARE @.PageSize int
DECLARE @.OrderExpression nvarchar(100)
DECLARE @.Sql nvarchar(500)

SET @.CurrentPage = 300
SET @.PageSize = 20
SET @.OrderExpression = N' employeeid '

DECLARE @.BigTop int
DECLARE @.SmallTop int
SET @.BigTop = @.CurrentPage * @.PageSize
SET @.SmallTop = (@.CurrentPage -1) * @.PageSize

/*
SET @.Sql = N' SELECT TOP (' + CAST(@.BigTop AS nvarchar(10)) + ') * FROM Employee '
+ ' EXCEPT '
+ ' SELECT TOP (' + CAST(@.SmallTop AS nvarchar(10)) + ') * FROM Employee ORDER BY ' + @.OrderExpression
*/

DECLARE @.StartTime datetime
SET @.StartTime = GETDATE()

SELECT TOP (@.BigTop) * FROM Employee
EXCEPT
SELECT TOP (@.SmallTop) * FROM Employee ORDER BY modifieddate

DECLARE @.EndTime datetime
SET @.EndTime = GETDATE()

SELECT DATEPART(s,@.EndTime-@.StartTime)
SELECT DATEPART(ms,@.EndTime-@.StartTime)

GO

------1 end----------

------2 start----------

DECLARE @.CurrentPage int
DECLARE @.PageSize int
DECLARE @.OrderExpression nvarchar(100)
DECLARE @.Sql nvarchar(500)

SET @.CurrentPage = 300
SET @.PageSize = 20
SET @.OrderExpression = N' employeeid '

DECLARE @.BigTop int
DECLARE @.SmallTop int
SET @.BigTop = @.CurrentPage * @.PageSize
SET @.SmallTop = (@.CurrentPage -1) * @.PageSize

/*
SET @.Sql = N' SELECT TOP (' + CAST(@.BigTop AS nvarchar(10)) + ') * FROM Employee '
+ ' EXCEPT '
+ ' SELECT TOP (' + CAST(@.SmallTop AS nvarchar(10)) + ') * FROM Employee ORDER BY ' + @.OrderExpression
*/

DECLARE @.StartTime datetime
SET @.StartTime = GETDATE()
SELECT * FROM Employee WHERE employeeid IN (
SELECT TOP (@.BigTop) employeeid FROM Employee
EXCEPT
SELECT TOP (@.SmallTop) employeeid FROM Employee ORDER BY employeeid
)

DECLARE @.EndTime datetime
SET @.EndTime = GETDATE()

SELECT DATEPART(s,@.EndTime-@.StartTime)
SELECT DATEPART(ms,@.EndTime-@.StartTime)

GO

------2 end----------

------3 start----------

DECLARE @.StartTime datetime
SET @.StartTime = GETDATE()
SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY employeeid DESC)
AS Row, * FROM Employee)
AS LogWithRowNumbers
WHERE Row >= 6010 AND Row <= 6020

DECLARE @.EndTime datetime
SET @.EndTime = GETDATE()

SELECT DATEPART(s,@.EndTime-@.StartTime)
SELECT DATEPART(ms,@.EndTime-@.StartTime)

GO

------3 end----------

When the database has about 200,000 rows,

When @.CurrentPage*@.PageSize <=6000, the first way has good performance

When @.CurrentPage*@.PageSize >6000, the second way has good performance

The 3rd way had the worst performance.

ex. When @.CurrentPage = 100, @.PageSize = 10,

the 1st: 30ms,

the 2nd: 500ms

the 3rd: 4s and 400ms

-------------

But when I made another test that the database has about 30,000 rows,

the 3rd is the fastest, the 1st is the slowest

sorry to all for my bad english ;-p

|||

New test script

------Start--------

DECLARE @.CurrentPage int
DECLARE @.PageSize int

SET @.CurrentPage = 3000
SET @.PageSize = 10

DECLARE @.BigTop int
DECLARE @.SmallTop int
SET @.BigTop = @.CurrentPage * @.PageSize
SET @.SmallTop = (@.CurrentPage -1) * @.PageSize

DECLARE @.StartTime datetime
SET @.StartTime = GETDATE()

IF(SELECT COUNT(SalesOrderId) FROM Sales.SalesOrderHeader) < 40000 /* IF rows less than 40000 */
BEGIN
/*
WITH Rows AS
(
SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderId DESC) AS Row, * FROM Sales.SalesOrderHeader
)
SELECT * FROM Rows
WHERE Row >= @.SmallTop AND Row <=@.BigTop
*/

SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderId DESC)
AS Row, * FROM Sales.SalesOrderHeader)
AS Rows
WHERE Row BETWEEN @.SmallTop AND @.BigTop

END
ELSE
BEGIN
IF @.BigTop < 5000 /* IF @.CurrentPage*@.PageSize < 5000 Rows */
BEGIN
SELECT TOP (@.BigTop) * FROM Sales.SalesOrderHeader
EXCEPT
SELECT TOP (@.SmallTop) * FROM Sales.SalesOrderHeader ORDER BY SalesOrderId
END
ELSE
BEGIN
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderId IN (
SELECT TOP (@.BigTop) SalesOrderId FROM Sales.SalesOrderHeader
EXCEPT
SELECT TOP (@.SmallTop) SalesOrderId FROM Sales.SalesOrderHeader ORDER BY SalesOrderId
)
END
END

DECLARE @.EndTime datetime
SET @.EndTime = GETDATE()

SELECT DATEPART(s,@.EndTime-@.StartTime)
SELECT DATEPART(ms,@.EndTime-@.StartTime)

GO

------End--------

New Features and Comparison.

Morning!
Folks, i want some links where i could find healthy stuff regarding New Features and Compliance levels, performance comparisons, TPC tests etc about SQL 2000 and Yukon specially.
I've to submit a document regarding Top database features that shall be used for a Medical-Billing Software in plan.

Howdy!Keep in mind though, that no matter what DBMS you select, the performance bottleneck would be the quality of back-end design.|||Hi guru, nice to see ya and thanx for the advice.

Back Desgin; you mean Index Planning, access methods and normalization. Where one would find these standards. I mean a GUIDE TO GOOD BACKEND DESIGNS? Any stuff would be appreciative.

Howdy!