Andre (no (AT) spam (DOT) com) writes:
If you say ALTER INDEX ix ON tbl WITH REBUILD, SQL Server should comply
and not try to be smart.

However, it could be a good thing if Microsoft added such parameters to
the maintenance plans to permit you to control this. I don't use maintenance
plans, so I don't know if it's already there.

I have no queries canned, but there are several fragmentation values,
and I would probably use a query that returned several values.

I would recommend that you use queries based on dm_sys_index_physical_stats
rather than DBCC SHOWCONTIG, as the latter is deprecated and will go
away one day.

In the system I work with, we have a home-brewed maintenance job (which is
just a stored procedure, not a maintenance plan). I think the trigger level
is when Scan Density goes below 70%. I don't think it looks at
logical fragmentation, but maybe it should.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I agree with Erland in that if you tell it to run the rebuild it should run
the rebuild. It is up to you to determine if the index should be rebuilt
based on your specific requirements. And I highly recommend you use one of
the scripts you found to customize this process. Don't rebuild the index if
it doesn't need rebuilding.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Andre" <no (AT) spam (DOT) com> wrote

Too funny. I just found a great article, and then saw your response.
Recognize this article?

http://www.sqlmag.com/Articles/Artic...019/99019.html

Didn't I just tell you I appreciate your articles earlier this week? See,
it's true!

Andre

That is funny as I had actually forgotten I even wrote that (they are
usually written months earlier) or I would have pointed you to it . Glad
to see someone read it

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Andre" <no (AT) spam (DOT) com> wrote

It worked great Andrew. I can tell it's working properly too because over
the weekend one time it took only 1 second to run, which was correct because
we hadn't touched the 2008 db. Another day it ran in 4 minutes, when we had
been working in 2008, so thanks again for the article.

Now I have another question about update stats. I've read in BOL that
sometimes updating stats can actually hurt us because of the recompile.
What is your take on this? In SQL 2000 we found that updating stats nightly
was a big help. It was a small cost (running the update) versus the
improvement we saw in our queries.

I'm also really puzzled about the maintenance plans for update stats. I
created a new plan that only had the update stats task, pointing to our main
db and only updates tables. The plan took 3 hr 33 minutes. The query below
(which is what we run in SQL 2k) runs in about 14 minutes. Why the huge
difference?


DECLARE @objid INT -- the object id of the table
DECLARE @objname CHAR(100) -- string to build up table name
DECLARE @strSQL VARCHAR(1000)
DECLARE TableName CURSOR
FOR SELECT DISTINCT NAME FROM sysobjects WHERE TYPE = 'U'

OPEN TableName

FETCH NEXT FROM TableName INTO @objname

WHILE @@fetch_status = 0
BEGIN
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
SET @strSQL = 'UPDATE STATISTICS ' + @objname
EXEC (@strSQL)

FETCH NEXT FROM TableName INTO @objname
END

DEALLOCATE TableName


Andre