Database file grown and performance decreased after Optimize

zaurb

Hi!

We run Windows 2003 Server with SQL Server 2000 and Navision 3.70. All this is in virtual environment VMWare ESX Server and EMC SAN.
We have a production database which is 123 Gb size and a test database which is a copy of production database.
We have run a Table Optimizer from Navision on test database and we've noticed that the database size increased and became 127 Gb after optimizing only one table: Item Ledger Entry.
Before Optimizing 'Item Ledger Entry' table was:
Nr. of records: 4592120
Record Dim.: 5582
Dimension (KB): 25031520

After optimization it became:
Nr. of records: 4592120
Record Dim.: 2.131
Dimension (KB): 9556144

Also we have noticed the performance decrease as the tables take more time to open.

For a few weeks we've suffering of a general performance issue even if we regularly run a maintenance plan:

--Step 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0F31AD1C-FC96-468D-ACEE-62AF37D991CD -Rpt "d:\Microsoft SQL Server\MSSQL\LOG\Mantenance ME_Prod - FT_Prod - F_Prod2.txt" -DelTxtRpt 4WEEKS -WriteHistory  -CkDB  '

-- Step 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0F31AD1C-FC96-468D-ACEE-62AF37D991CD -Rpt "d:\Microsoft SQL Server\MSSQL\LOG\Mantenance ME_Prod - FT_Prod - F_Prod0.txt" -DelTxtRpt 4WEEKS -WriteHistory  -RebldIdx 10 -RmUnusedSpace 50 10 '

Perfomance counter Avg. Disk Read/Queue Length bounces to a value of 50,000-60,000 every now and then...

I have two questions:
1) Why did the database grow so much after Optimizing only one table and what if happens if we Optimize others?
2) What could be the reason of such a performance decrease and how may we track it back.

Looking forward to your comments!

Thank you!

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]

It looks that the table itself went from 25GB to 9.5 GB.
Better don't use the optimize in NAV, but use the indexrebuild of SQL with 90% fillfactor (or leave 10% free space).
This does a 'safer' job using the correct fillfactor you want.
The optimize is only useful for removing the empty sift-records. But there are tools who do a good job for that.
I have the idea that you let your DB shrink and grow. This is not a good idea. You should NEVER shrink your DB or logfile. This can create fragmentation on the disk (and also internal fragmentation for the log file).
So best is now to create a DB large enough so it has not to increase size of some time to contain all the data. After that, do a defrag of the disk.
For the logfile, I advice to check my blog: http://mibuso.com/blogs/kriki/2008/12/23/some-tips-tricks-for-the-transaction-log/.
After creating a big-enough logfile, do also a defrag of the disk.

PS: probably you will need to stop the sqlservice for defragging the DB and the log.
PS2: your logfile should be on a dedicated RAID1 (or RAID10) disk. And ABSOLUTELY NOT on a RAID5.