Performance Counters

  • Comments posted to this topic are about the item Performance Counters

  • Nice easy question.

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

    Tom

  • 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."

  • Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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

  • 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

  • Nice question. The answer seemed too obvious, i was looking for the gotcha. :satisfied:

  • 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

  • This link might also help too

    http://www.sql-server-performance.com/faq/reduce_page_splits_p1.aspx

  • 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.

  • 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 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

  • 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".

  • 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:

  • 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