Prev: Problem installing SQL Server Express 2008
Next: Need field uniqueness but don't want index
From: cbrichards on 11 Feb 2010 00:36
I am attempting to analyze the number of page splits occurring on a server.

DECLARE @PageSplits bigint
DECLARE @CntrValue bigint

select @PageSplits = sum(leaf_allocation_count)
from sys.dm_db_index_operational_stats (NULL,NULL,NULL,NULL)

select @PageSplits = sum(leaf_allocation_count) - @PageSplits
from sys.dm_db_index_operational_stats (NULL,NULL,NULL,NULL)

SELECT @CntrValue = cntr_value
FROM master.dbo.sysperfinfo
WHERE counter_name = 'Page Splits/sec'
AND object_name like '%Access methods%'

SELECT @PageSplits As PageSplits, @CntrValue As CntrValue

@PageSplits = 27
@CntrValue = 10,419,270

In reading BOL the leaf_allocation_count in DMV sys.
dm_db_index_operational_stats has the following definition: "Cumulative count
of leaf-level page allocations in the index or heap. For an index, a page
allocation corresponds to a page split."

Since, in the above execution, the statement getting the sum of the
leaf_allocation_count takes about a second, I do not see why there is such a
difference in the page splits per second in the DMV and the page splits per
second from sysperfinfo.

I am sure I will look back on this as a stupid question, but why the huge
difference?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1

From: TheSQLGuru on 11 Feb 2010 09:34
get counter values
wait 1 minute (while under load)
get counter values
do a diff between two sets of counter values (perf mon one)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"cbrichards" <u3288(a)uwe> wrote in message news:a371f5350114d(a)uwe...
>I am attempting to analyze the number of page splits occurring on a server.
>
> DECLARE @PageSplits bigint
> DECLARE @CntrValue bigint
>
> select @PageSplits = sum(leaf_allocation_count)
> from sys.dm_db_index_operational_stats (NULL,NULL,NULL,NULL)
>
> select @PageSplits = sum(leaf_allocation_count) - @PageSplits
> from sys.dm_db_index_operational_stats (NULL,NULL,NULL,NULL)
>
> SELECT @CntrValue = cntr_value
> FROM master.dbo.sysperfinfo
> WHERE counter_name = 'Page Splits/sec'
> AND object_name like '%Access methods%'
>
> SELECT @PageSplits As PageSplits, @CntrValue As CntrValue
>
> @PageSplits = 27
> @CntrValue = 10,419,270
>
> In reading BOL the leaf_allocation_count in DMV sys.
> dm_db_index_operational_stats has the following definition: "Cumulative
> count
> of leaf-level page allocations in the index or heap. For an index, a page
> allocation corresponds to a page split."
>
> Since, in the above execution, the statement getting the sum of the
> leaf_allocation_count takes about a second, I do not see why there is such
> a
> difference in the page splits per second in the DMV and the page splits
> per
> second from sysperfinfo.
>
> I am sure I will look back on this as a stupid question, but why the huge
> difference?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1
>


 | 
Pages: 1
Prev: Problem installing SQL Server Express 2008
Next: Need field uniqueness but don't want index