SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Counters


Performance Counters

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: Administrators
Points: 250318 Visits: 19813
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
My Blog: www.voiceofthedba.com
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39660 Visits: 12890
Nice easy question.

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

Tom

free_mascot
free_mascot
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12131 Visits: 2250
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."
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111778 Visits: 13338
Great question, thanks.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7189 Visits: 3889
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
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61384 Visits: 17246
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
Chris Houghton
Chris Houghton
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 1715
Nice question. The answer seemed too obvious, i was looking for the gotcha. Satisfied
M&M
M&M
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9736 Visits: 3916
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.

M&M
Kwex
Kwex
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 Visits: 240
This link might also help too
http://www.sql-server-performance.com/faq/reduce_page_splits_p1.aspx
Steve Jones
Steve Jones
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: Administrators
Points: 250318 Visits: 19813
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search