Post reply

A Self-Tuning Fill Factor Technique for SQL Server – Part 1

  • August 20, 2019 at 12:00 am

    #3670380

    Comments posted to this topic are about the item A Self-Tuning Fill Factor Technique for SQL Server – Part 1

    Mike Byrd

  • August 20, 2019 at 1:09 pm

    #3672746

    Very interested to try your technique. should line 386 end in air1?

  • August 20, 2019 at 1:24 pm

    #3672752

    Had some trouble getting it to work though.

    First of all, as stated in the above comment, line 386 has the wrong alias and should be air1 indeed. And I'd like to suggest three small adjustments on lines 463, 476 and 535 to put a QUOTENAME around the @indexname ...

    ALTER INDEX ' + QUOTENAME(@indexname) +' ON [' + @schemaname

    It appeared that someone in our organization came up with the excellent idea to put dots (.) in a couple of indexnames, obviously resulting in a failing command.

    Looking forward to see what's going to happen over time and can't wait to read part 2.

    Cheers

  • August 20, 2019 at 2:19 pm

    #3672786

    Actually the script has evolved some since I wrote the article.  Have attached latest script (currently in production on one of client's databases).  (Change file extension from txt to sql)

    • This reply was modified 5 years, 3 months ago by  Mike Byrd.
    • This reply was modified 5 years, 3 months ago by  Mike Byrd.

    Attachments:
    You must be logged in to view attached files.

    Mike Byrd

  • August 20, 2019 at 2:22 pm

    #3672790

    I'll ping Mike for updated images.

  • August 21, 2019 at 6:41 pm

    #3673228

    I'm trying to use this on SQL Server 2014 Std. I'm using the most recent script listed above. However it keeps crashing with:

    Msg 25704, Level 16, State 1, Line 8

    The event session has already been stopped.

    Is this happening to anyone else? I'm going to try the original script in a moment.

  • August 21, 2019 at 7:04 pm

    #3673236

    This is the script I reworked to run on SQL Server 2014 STD taking into account everything mentioned in this thread up to this point. It uses the code base from the original posting.

    UPDATE: Forgot to rename the extension from .SQL to .TXT 🙂

    • This reply was modified 5 years, 3 months ago by  ThatSteveCena. Reason: Forgot to fix file extension
    • This reply was modified 5 years, 3 months ago by  ThatSteveCena.

    Attachments:
    You must be logged in to view attached files.

  • August 21, 2019 at 7:16 pm

    #3673237

    I'm not seeing your attachment. 🙁

    Mike

    Mike Byrd

  • October 3, 2021 at 4:12 am

    #3935963

    Hey Mike,

    Thank you for your work here.  Really super helpful.  Thanks to Jeff Moden for these ideas too!!!!

    I think I might try to make it use Ola Hallengren's Index Optimize instead of the ALTER INDEX.

    There is another script that may be incorporated that shows low density in indexes:

    Old reference here from Paul Randal:  https://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/

    G.B. Torres
    gene.torres@comcast.net

  • October 3, 2021 at 5:14 am

    #3935966

    This one is missing the boiler plate for the schema and AgentIndexRebuilds.  Maybe it is just for the ongoing work to rebuild the indexes.

    Also does not include the SQLskills_TrackPageSplits.

    https://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/

    -- Drop the Event Session so we can recreate it

    -- to focus on the highest splitting database

    IF EXISTS (SELECT name FROM sys.server_event_sessions WHERE name = 'SQLskills_TrackPageSplits')

    DROP EVENT SESSION SQLskills_TrackPageSplits ON SERVER

    -- Create the Event Session to track LOP_DELETE_SPLIT transaction_log operations in the server

    DECLARE @Database SYSNAME = (SELECT DB_NAME())

    DECLARE @cmd NVARCHAR(4000)=N'

    CREATE EVENT SESSION [SQLskills_TrackPageSplits]

    ON SERVER

    ADD EVENT sqlserver.transaction_log(

    WHERE operation = 11 -- LOP_DELETE_SPLIT

    AND database_name = ''' + @Database +

    ''' )

    ADD TARGET package0.histogram(

    SET filtering_event_name = ''sqlserver.transaction_log'',

    source_type = 0, -- Event Column

    source = ''alloc_unit_id'');'

    PRINT @cmd

    EXECUTE sp_executeSql @cmd

    -- Start the Event Session Again

    ALTER EVENT SESSION [SQLskills_TrackPageSplits]

    ON SERVER

    STATE=START;

    GO

    G.B. Torres
    gene.torres@comcast.net

  • October 3, 2021 at 5:30 am

    #3935967

    Here is what I have, need to make the ExtendedEvent Include the db_name so I can do it for more than 1 db.

    • This reply was modified 3 years, 1 month ago by  gene.torres.

    Attachments:
    You must be logged in to view attached files.

    G.B. Torres
    gene.torres@comcast.net

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply