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 (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84878 Visits: 19224
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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17834 Visits: 12338
Nice easy question.

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

Tom

free_mascot
free_mascot
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4883 Visits: 2248
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-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35405 Visits: 13270
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4143 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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19292 Visits: 16237
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
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 1707
Nice question. The answer seemed too obvious, i was looking for the gotcha. Satisfied
M&M
M&M
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4215 Visits: 3912
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
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 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 (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84878 Visits: 19224
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