Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Performance Counters Expand / Collapse
Author
Message
Posted Sunday, May 8, 2011 5:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
Comments posted to this topic are about the item Performance Counters






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1105111
Posted Sunday, May 8, 2011 11:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 7,804, Visits: 9,556
Nice easy question.

If there were more like this I would have a better score.


Tom
Post #1105135
Posted Sunday, May 8, 2011 11:30 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Tom,

Yes, easy and that too have 2 points

Cheers!


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1105212
Posted Sunday, May 8, 2011 11:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
Great question, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1105219
Posted Monday, May 9, 2011 1:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
The answer does not sound right to me.
For heaps, there are no page splits, therefore the only counter that gives me the answer for both, indexes & heaps, is the Pages Allocated/sec counter.


-- CleanUp
DROP TABLE dbo.Test
GO
USE master
GO
DROP DATABASE Test
GO


SET NOCOUNT ON
CREATE DATABASE Test
GO
USE TEST
GO
CREATE TABLE dbo.Test (id TINYINT NOT NULL, String VARCHAR(5000))
-- Enable or disable the below statement to test for Heap or for Clustered Index
--CREATE CLUSTERED INDEX idxid ON dbo.test (id)

-- Insert test data:
-- The first row will remain on the same page
-- The second row will be inserted into the first page, but we will update the row later
-- to a size that does not fit on that page anymore, and therefore will trigger a new page allocation.
INSERT dbo.Test VALUES (1,REPLICATE('A',5000))
INSERT dbo.Test VALUES (2,REPLICATE('A',1))
GO
-- Pause here for a second or so - we want to measure the Update, not the above inserts.

-- Now move the record to a new page by increasing the size
UPDATE dbo.Test SET String = REPLICATE('A',5000) WHERE ID = 2
GO



Best Regards,
Chris Büttner
Post #1105253
Posted Monday, May 9, 2011 2:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:24 AM
Points: 5,430, Visits: 10,099
I discounted Page splits/sec because page splits are also caused by INSERT statements, and therefore if you wanted to measure the rate at which UPDATE statements cause data to be moved, you may get some misleading results.

Have I missed something, or did I misread the question?

John
Post #1105266
Posted Monday, May 9, 2011 2:39 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:55 AM
Points: 558, Visits: 1,488
Nice question. The answer seemed too obvious, i was looking for the gotcha.
Post #1105270
Posted Monday, May 9, 2011 3:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:12 PM
Points: 2,278, Visits: 3,798
John Mitchell-245523 (5/9/2011)
I discounted Page splits/sec because page splits are also caused by INSERT statements, and therefore if you wanted to measure the rate at which UPDATE statements cause data to be moved, you may get some misleading results.

Have I missed something, or did I misread the question?

John


I agree with you John.


Mohammed Moinudheen
Post #1105282
Posted Monday, May 9, 2011 4:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 1:55 AM
Points: 298, Visits: 236
This link might also help too
http://www.sql-server-performance.com/faq/reduce_page_splits_p1.aspx
Post #1105318
Posted Monday, May 9, 2011 9:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
Christian Buettner-167247 (5/9/2011)
The answer does not sound right to me.
For heaps, there are no page splits, therefore the only counter that gives me the answer for both, indexes & heaps, is the Pages Allocated/sec counter.


While you are correct that page splits do not occur for heaps, it also means that there is no movement to new pages. This isn't pages used for data, the question asked for pages on which data is moved.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1105501
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse