November 19, 2012 at 8:55 am
I'm guessing this is more of a beginners thing. I'm having trouble tracing down what I'm doing wrong. my simple goal is to create a two part job that will reindex only the items that need it and will email the report after it's done so. i have a job working that will already email us the condition of our indexes, I'm trying to build a better index job that is as simple as possible. i've seen some of the large scripts. I'm trying to cut it down some. any help is appreciated. I'm still looking items up and trying to piece something together. I'm still new to the DBA world.
select 'ALTER INDEX ALL ON dbo.' + 'SELECT
(i.object_id)
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, ''limited'') p
inner join sys.indexes as i on p.object_id = i.object_id and p.index_id = i.index_id
where i.index_id > 0 and avg_fragmentation_in_percent > 5 and page_count > 25
order by avg_fragmentation_in_percent desc' + 'REBUILD WITH (FILLFACTOR = 80;'
i am running this prospectively on ms sql server 2005 / 2008 / 2008 r2.
.
November 19, 2012 at 9:03 am
to be specific, the above scripts goal was to create a script that will create a set of scripts. i would prefer it to be reactive and repair as it goes, but isn't a requirement.
.
November 19, 2012 at 9:35 am
No need to reinvent the wheel.
http://ola.hallengren.com/Versions.html
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2012 at 9:42 am
the main idea was to cut script time since these are very large and slower boxes. if cutting out all the extra script needed doesn't really provide any boost in performance, I'll stick with the ones you linked. i've looked into 4.1.
what i have so far is below. basically create the able, then get the items i want, the drop. this is still manual at this stage.. *and creating 9 alters since it's extending the list due to the amount of indexes not just table names :/ so some duplicates*
SELECT Object_name(i.object_id)
AS TableName, i.index_id, name
AS IndexName
into DBA_Reindex
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'limited') p
inner join sys.indexes as i on p.object_id = i.object_id and p.index_id = i.index_id
where i.index_id > 0 and avg_fragmentation_in_percent > 5 and page_count > 25
order by avg_fragmentation_in_percent desc
select 'ALTER INDEX ALL ON dbo.' + TableName + ' REBUILD WITH (FILLFACTOR = 80);'
from dbo.DBA_Reindex
drop table DBA_Reindex
.
November 19, 2012 at 9:49 am
The time for a reindex is almost entirely the actual rebuilds, you're not going to save much, if anything by rolling your own version, you're going to spend a lot of time getting the bugs out of it for little gain.
Unless you have a specialised index rebuild requirement that the available scripts don't address, just use one of them, don't write your own.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2012 at 9:51 am
i appreciate it, this was mostly to help understand how it works as well as get it working with as few options as possible. I will work with the other scripts instead and just play with this in my spare time.
.
November 19, 2012 at 3:20 pm
fluffydeadangel (11/19/2012)
my simple goal is to create a two part job that will reindex only the items that need it ...
select 'ALTER INDEX ALL ON dbo.' ... 'FILLFACTOR = 80' ...
You should limit your rebuilds to specific index(es) on a table that need it, not automatically do all indexes.
Also, you need to use the existing FILLFACTOR on the index when doing the rebuild, not arbitrarily use FILLFACTOR = 80, which could be extremely too low for some indexes. Tune the existing FILLFACTORs as you go along, rather than overriding them with a single, catch-all value.
Btw, the wheel's been "reinvented" literally hundreds of times, or we'd all be riding around on wooden wheels, all the same size, with no rims! 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply