DBCC SHOWCONTIG results: Good/bad?

My company is running Microsoft Navision Axapta as our ERP solution.
We've had some performance problems (high disk loads). After reading
"Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
...I decided to run DBCC SHOWCONTIG on the Axapta database.
The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
(500 kb text)
I'm new to administering SQL servers with large datbases and heavy
load. I need som expert opinions! Is this database heavily fragmented
or not? Should I schedule a daily index defragmentation job?
Any feedback appreciated!
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
Look to the best:actual counts. If there was no fragmentation they would be
close to the same.
However, fragmentation is not necessarily a bad thing. In an OLTP database
(loads of updates, few reports) it is good. In an OLAP database (few
updates, bags of reports) it is bad.
You really need to know why your database is running slow by analysing
output from Profiler and System Manager. There's roughly a gazillion things
that can affect performance, fragmentation being only one of them.
Does it affect just SELECT statements? INSERT/DELETE/UPDATE statements?
Both?
If your Indexes are fragmented, try rebuilding the indexes with a lower
FILL_FACTOR setting. That will cause fewer page splits, though SELECT
statements may theoretically increase as SQL will have to read more pages
per index search.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||You really don't expect us to go through all that, do you? ;-)
I suggest you run SHOWCONTIG this way:
DBCC SHOWCONTIG WITH TABLERESULTS
That gives you a resultset. I even use below method (from VB code):
INSERT INTO #tbl (...)
DBCC SHOWCONTIG WITH TABLERESULTS
You have to create the temp tables first, with proper columns, but that gives you the ability to do
SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY LogicalFragmentation DESC, for example.
General tips:
If you don't have > 500 to 1000 pages, don't worry about fragmentation.
If you have > 1 database files, use Logial Fragmentation, scan density will not report correct.
Seems you have some tables without clustered index... Any particular reason?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> My company is running Microsoft Navision Axapta as our ERP solution.
> We've had some performance problems (high disk loads). After reading
> "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> (500 kb text)
> I'm new to administering SQL servers with large datbases and heavy
> load. I need som expert opinions! Is this database heavily fragmented
> or not? Should I schedule a daily index defragmentation job?
> Any feedback appreciated!
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||> Look to the best:actual counts. If there was no fragmentation they would be
> close to the same.
Just watch out if > 1 database file for the filegroup. Jumps between files will count as
fragmentation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver|||You should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
which explains everything you need to know about managing fragmentation.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#0LxzdInDHA.2416@.TK2MSFTNGP10.phx.gbl...
> You really don't expect us to go through all that, do you? ;-)
> I suggest you run SHOWCONTIG this way:
> DBCC SHOWCONTIG WITH TABLERESULTS
> That gives you a resultset. I even use below method (from VB code):
> INSERT INTO #tbl (...)
> DBCC SHOWCONTIG WITH TABLERESULTS
> You have to create the temp tables first, with proper columns, but that
gives you the ability to do
> SELECT with WHERE, ORDER BY etc. Easy to do an ORDER BY
LogicalFragmentation DESC, for example.
> General tips:
> If you don't have > 500 to 1000 pages, don't worry about fragmentation.
> If you have > 1 database files, use Logial Fragmentation, scan density
will not report correct.
> Seems you have some tables without clustered index... Any particular
reason?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
> news:8orppvodf2rqgod40vmtgqbmc4leclcs94@.4ax.com...
> > My company is running Microsoft Navision Axapta as our ERP solution.
> > We've had some performance problems (high disk loads). After reading
> > "Microsoft SQL Server 2000 Index Defragmentation Best Practices" at
> >
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > ...I decided to run DBCC SHOWCONTIG on the Axapta database.
> > The resulting report: http://home.c2i.net/hmhaga/sql/showcontig.htm
> > (500 kb text)
> >
> > I'm new to administering SQL servers with large datbases and heavy
> > load. I need som expert opinions! Is this database heavily fragmented
> > or not? Should I schedule a daily index defragmentation job?
> >
> > Any feedback appreciated!
> >
> >
> > H.M.Haga
> > '98 Subaru Impreza GT
> > '91 Suzuki Bandit 400
> > http://www.imprezadriver.com/phpBB2
> > http://home.c2i.net/hmhaga
>|||On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>You really don't expect us to go through all that, do you? ;-)
No... ;-)
After I read your answers, I signed up for "2072 Administering a MS
SQL Server 2000 Database" :-)
Our server has been running for years AS IS, but after we decided to
put our ERP solution on it - it suddenly requires a lot more attention
- and knowledge!
I've just started to analyze the database, currently it is in the same
state as it was when our vendor installed the solution (Axapta).
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga|||> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
Teaching those courses, I just want to say that the Admin course do not deal with indexes,
fragmentation and such. The programming course does (2073). Just a heads up. :-)
Again, check out the tables with some 500 pages or more, and concentrate on those. Also, consider
why some tables doesn't have clustered indexes. That would be a good start. And read the paper that
Paul referred to. My tips...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hans-Martin Haga" <hmhaga@.c2i.net> wrote in message
news:dlfspv8vi4esvgf2cv2ktgv3ot1ejp9pht@.4ax.com...
> On Mon, 27 Oct 2003 13:39:52 +0100, "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
> >You really don't expect us to go through all that, do you? ;-)
> No... ;-)
> After I read your answers, I signed up for "2072 Administering a MS
> SQL Server 2000 Database" :-)
> Our server has been running for years AS IS, but after we decided to
> put our ERP solution on it - it suddenly requires a lot more attention
> - and knowledge!
> I've just started to analyze the database, currently it is in the same
> state as it was when our vendor installed the solution (Axapta).
>
> H.M.Haga
> '98 Subaru Impreza GT
> '91 Suzuki Bandit 400
> http://www.imprezadriver.com/phpBB2
> http://home.c2i.net/hmhaga|||On Tue, 28 Oct 2003 12:08:46 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote:
>Teaching those courses, I just want to say that the Admin course do not deal with indexes,
>fragmentation and such. The programming course does (2073). Just a heads up. :-)
Thanks! I've just signed 2073 as well, 2 weeks of SQL courses then :)
As for clustered index, neither the database nor the application has
been tuned or optimized in any way after the implementation - so I
guess that is up to me. Our dealer has very limited knowledge of SQL
(it's like a black box), they've put a layer of Axapta application
logic between themself and SQL! ;-)
H.M.Haga
'98 Subaru Impreza GT
'91 Suzuki Bandit 400
http://www.imprezadriver.com/phpBB2
http://home.c2i.net/hmhaga