Performance Question

Flare

Hi,

we work with navision 40SP1
Since we updated to 40SP1 we have some performanceissues.
The serverhardware is really fine and no leaks there.

First i will do is to set the indexes to clustered, i hope it will boost the performance a bit.

I reorganize data and index pages with a maintance plan daily. (fillfactor 30% )

Must i create a second maintanace plan for update statistcs ?

Greets Flare

SQLGuru

Jesus, 30%?!?!
This means that you leave 70% free space on all pages.
I would consider this way too much!

My tool SQLSunrise calulates fillfactors for a maintenance intervall pretty precisely, and for big tables the average fillfactor lies around 97%. Which is pretty obvious, if you look at it like this: 3% space in a table that has 10 million records would mean enough space for 300000 new records (if the keys are distributed evenly), which should be enough for one week, i guess...?

Please adjust your fillfactors to around 95, and this will boost again.

kriki

[Topic moved from Navision forum to SQL Performance forum]

Waldo

Flare wrote:

Must i create a second maintanace plan for update statistcs ?

To give you a much-too-late answer: yes you should create a maintenance plan to update statistics (indexonly is enough).

Best is to first rebuild the indexes, then update statistics.

toor

Best is to first rebuild the indexes, then update statistics.

I don't think that you need to update the statistics after rebuilding the indexes. The statistics will be updated while rebuilding the indexes.

Am i wrong?


tobias

kriki

toor wrote:

Best is to first rebuild the indexes, then update statistics.

I don't think that you need to update the statistics after rebuilding the indexes. The statistics will be updated while rebuilding the indexes.

Am i wrong?


tobias

Good question. I think they are updated while rebuilding. I think this because with when creating a Database Maintenance plan, if you select to rebuild, you CAN'T select also to create statistics.

Waldo

Hm, you can update statistics with the "indexonly" property.
To be sure, I would just do it... . It takes 30 seconds on our 40Gb database ... not worth not to do it

kriki

Waldo wrote:

Hm, you can update statistics with the "indexonly" property.
To be sure, I would just do it... . It takes 30 seconds on our 40Gb database ... not worth not to do it

And what is the percentage of records that SQL samples on your machine?
And what kind of machine is it?

Waldo

It's a 4CPU 6GB server.
first question :
I do a sp_createstats 'indexonly' without the fullscan parameter.

This is the "help" text:
The SQL Server 2005 Database Engine makes sure a minimum number of values are sampled to ensure useful statistics

It's always better to do a fullscan, then you're sure the stats are 100% useful, but if you don't specify it, SQL Server 2005 will make sure the stats are useful ... so in 30 seconds you have useful stats ... .

That's why I say it's better do it, than to guess it's updated through index rebuild.

You agree?

kriki

Waldo wrote:

That's why I say it's better do it, than to guess it's updated through index rebuild.

You agree?

Yes.

The reason I asked was that SQL2000 proposes 10% of sampling and it take about 10-15 minutes for a DB of about 20GB.

Nobody

Data sampling is great for auto-updating systistics on the fly, but it is good to schedule a Stat update with FULLSCAN as part of a regular maint. schedule.