InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 20:12:41 GMT20http://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxChris,You are right, and I've altered the question to specify non-heaps.Tue, 10 May 2011 09:53:21 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspx[quote][b]Steve Jones - SSC Editor (5/9/2011)[/b][hr][quote][b]Christian Buettner-167247 (5/9/2011)[/b][hr]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 [b]Pages Allocated/sec[/b] counter.[/quote]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.[/quote]Hi Steve, when the 2nd row from my example heap is updated, the data of the 2nd row actually [b]is[/b] being moved to a new page.Tue, 10 May 2011 01:41:20 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspx[quote][b]SanDroid (5/9/2011)[/b][hr][quote][b]Peter Trast (5/9/2011)[/b][hr]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 :)[/quote]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:[/quote]Yeah, I was only half-kidding :-) I don't like how serious some people get when they are trying to make their opinion the winning point of view, BUT an animated discussion reveals alot of deep info.Mon, 09 May 2011 19:58:52 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxNice question thanks! I agree that more like this would be nice.Mon, 09 May 2011 12:33:33 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxThanks for the question.Mon, 09 May 2011 12:13:28 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspx[quote][b]Peter Trast (5/9/2011)[/b][hr]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 :)[/quote]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:Mon, 09 May 2011 10:54:00 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspx[quote][b]Steve Jones - SSC Editor (5/9/2011)[/b][hr][quote][b]John Mitchell-245523 (5/9/2011)[/b][hr]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 [i]UPDATE[/i] statements cause data to be moved, you may get some misleading results.Have I missed something, or did I misread the question?John[/quote]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.[/quote]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".Mon, 09 May 2011 10:47:54 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspx[quote][b]Steve Jones - SSC Editor (5/9/2011)[/b][hr][quote][b]John Mitchell-245523 (5/9/2011)[/b][hr]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 [i]UPDATE[/i] statements cause data to be moved, you may get some misleading results.Have I missed something, or did I misread the question?John[/quote]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.[/quote]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 :)Mon, 09 May 2011 09:38:11 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspx[quote][b]John Mitchell-245523 (5/9/2011)[/b][hr]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 [i]UPDATE[/i] statements cause data to be moved, you may get some misleading results.Have I missed something, or did I misread the question?John[/quote]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.Mon, 09 May 2011 09:20:15 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspx[quote][b]Christian Buettner-167247 (5/9/2011)[/b][hr]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 [b]Pages Allocated/sec[/b] counter.[/quote]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.Mon, 09 May 2011 09:18:36 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxThis link might also help toohttp://www.sql-server-performance.com/faq/reduce_page_splits_p1.aspxMon, 09 May 2011 04:50:27 GMTKwexhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspx[quote][b]John Mitchell-245523 (5/9/2011)[/b][hr]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 [i]UPDATE[/i] statements cause data to be moved, you may get some misleading results.Have I missed something, or did I misread the question?John[/quote]I agree with you John.Mon, 09 May 2011 03:12:36 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxNice question. The answer seemed too obvious, i was looking for the gotcha. :satisfied:Mon, 09 May 2011 02:39:44 GMTChris Houghtonhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxI discounted Page splits/sec because page splits are also caused by INSERT statements, and therefore if you wanted to measure the rate at which [i]UPDATE[/i] statements cause data to be moved, you may get some misleading results.Have I missed something, or did I misread the question?JohnMon, 09 May 2011 02:25:46 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxThe 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 [b]Pages Allocated/sec[/b] counter.[code]-- CleanUpDROP TABLE dbo.TestGOUSE masterGODROP DATABASE TestGOSET NOCOUNT ONCREATE DATABASE TestGOUSE TESTGOCREATE 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 sizeUPDATE dbo.Test SET String = REPLICATE('A',5000) WHERE ID = 2GO[/code]Mon, 09 May 2011 01:59:43 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxGreat question, thanks.Sun, 08 May 2011 23:41:02 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxTom,Yes, easy and that too have 2 points ;-)Cheers!Sun, 08 May 2011 23:30:08 GMTfree_mascothttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxNice easy question. If there were more like this I would have a better score.Sun, 08 May 2011 11:37:25 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1105111-32-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/Performance/73393/">Performance Counters</A>[/B]Sun, 08 May 2011 05:05:01 GMTSteve Jones - SSC Editor