August 20, 2019 at 12:00 am
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
Very interested to try your technique. should line 386 end in air1?
August 20, 2019 at 1:24 pm
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
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)
Mike Byrd
August 20, 2019 at 2:22 pm
I'll ping Mike for updated images.
August 21, 2019 at 6:41 pm
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
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 🙂
August 21, 2019 at 7:16 pm
I'm not seeing your attachment. 🙁
Mike
Mike Byrd
October 3, 2021 at 4:12 am
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
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.
-- 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
Here is what I have, need to make the ExtendedEvent Include the db_name so I can do it for more than 1 db.
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