May 8, 2011 at 5:05 am
Comments posted to this topic are about the item Performance Counters
May 8, 2011 at 11:37 am
Nice easy question.
If there were more like this I would have a better score.
Tom
May 8, 2011 at 11:30 pm
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."
May 8, 2011 at 11:41 pm
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 9, 2011 at 1:59 am
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
May 9, 2011 at 2:25 am
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
May 9, 2011 at 2:39 am
Nice question. The answer seemed too obvious, i was looking for the gotcha. :satisfied:
May 9, 2011 at 3:12 am
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
May 9, 2011 at 4:50 am
This link might also help too
http://www.sql-server-performance.com/faq/reduce_page_splits_p1.aspx
May 9, 2011 at 9:18 am
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.
May 9, 2011 at 9:20 am
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
Hard to tell here. I did ask for movement because of size differentials. I suppose an insert could be argued to cause this and I'll concede here. I've altered the question to say insert and update.
May 9, 2011 at 9:38 am
Steve Jones - SSC Editor (5/9/2011)
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
Hard to tell here. I did ask for movement because of size differentials. I suppose an insert could be argued to cause this and I'll concede here. I've altered the question to say insert and update.
Glad you asked the question, John, and that Steve updated before I read it. I would have had the same question. We have had alot of really good questions the last few weeks, although I am not sure if questions that DON'T start a debate are really so great after all 🙂
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 9, 2011 at 10:47 am
Steve Jones - SSC Editor (5/9/2011)
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
Hard to tell here. I did ask for movement because of size differentials. I suppose an insert could be argued to cause this and I'll concede here. I've altered the question to say insert and update.
Steve- Great question. Glad you updated it. Wish I would have re-read the question after selecting the link in my email. The "Update" and "new structures" wording made me believe the answer was "Extents Allocated/sec".
May 9, 2011 at 10:54 am
Peter Trast (5/9/2011)
We have had alot of really good questions the last few weeks, although I am not sure if questions that DON'T start a debate are really so great after all 🙂
LOL Peter... did you foget the j/k after that?
Seriously though I have to agree.
Being able to debate and prove an answer or statement is correct without using misquoted data or invalid jumps in logic is very important when teaching professional adults.
My MCT course had a statement that stuck with me;
"Children will believe in anything, Adults require some proof."
:laugh:
May 9, 2011 at 12:13 pm
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply