Sql-server – SQL Server 2000 Page Splits per second higher han 12 million

page-splitssql-server-2000

I was reading about index fill factors & performance, and out on a whim I tried the query given by the webpage on the company's production SQL Server 2000 server:

SELECT *
FROM MASTER.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access methods%'

Imagine my shock when I saw the result is 12164778 (yes, that's larger than 12 million).

Is it actually possible to have 12 million page splits per second? Or am I reading the numbers incorrectly? Or is there a mistake in the query?

Best Answer

Please read Calculating Counter Values to understand what performance counter values are and how you use them. Per second values are computed from two samples delta divided by time.

Jason Strate has a SQL Server specific discussion here.

Related Solutions

Sql-server – quickish, straighforward way to measure Transactions Per Second on SQL Server 2000

I would suggest using the SQL Server Counters in the Windows PerfMon Utility.

This is for SQL 2005 but the instructions provide the gist of things for you.

Sql-server – Table Size Analysis on SQL Server 2000

You're not using WITH ROLLUP correctly. WTIH ROLLUP creates a subtotal and/or total based upon what you tell it. You didn't specify what to call the subtotal and/or total so it is calling it NULL.

This query generates a subtotal report:

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Output:

Item                 Color                QtySum                     
-------------------- -------------------- -------------------------- 
Chair                Blue                 101.00                     
Chair                Red                  210.00                     
Chair                ALL                  311.00                     
Table                Blue                 124.00                     
Table                Red                  223.00                     
Table                ALL                  347.00                     
ALL                  ALL                  658.00

Best Answer

Related Solutions

Sql-server – quickish, straighforward way to measure Transactions Per Second on SQL Server 2000

Sql-server – Table Size Analysis on SQL Server 2000

Related Question