Page Splits
in [Server Configuration]
|
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 |