SQL Server Statistics - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server Statistics

Author

Topic

Angelo
Starting Member

1 Post

Posted - 2002-01-23 : 08:22:37

Hello People,

I have a problem, I'd like an answer about a question.

Fisrt situation:
4 servers Windows 2000 Advanced Server and SQL Server 2000, 1GB RAM, 2 processors 500Mhz
The disks are in a Hitache storage connected by fiber channel. The storage has 14TB.
I have a table with the following structure:

NUM_REMESSA int NULL
CGCMF bigint NULL
CGCTE char 14
DATA int NULL
SERIE char 2
SUBSERIE char 3
NOTA int 4 NULL
CFOP smallint 2 NULL
ALIQUOTA smallint NULL
CONTEUDO_REGISTRO char 126
NRO_REG_ARQ_TEXTO int NULL
TR tinyint NULL
COD_CONTRIB char 14
TIPO_CONTRIB tinyint NULL
TIPO_OPERACAO_DF char 1
COD_NAT_IMAG_DF smallint NULL
COD_MODALIDE_FRETE bit NULL
COD_PROD_PADRAO char 8

indexes
IDX0_RDFMOVTO clustered located on PRIMARY NUM_REMESSA
IDX1_RDFMOVTO nonclustered located on PRIMARY CGCMF, DATA, SERIE, SUBSERIE, NOTA
IDX2_RDFMOVTO nonclustered located on PRIMARY CGCTE, DATA, SERIE, SUBSERIE, NOTA
IDX3_RDFMOVTO nonclustered located on PRIMARY TR

This table has 373.063.877 regs.
size = 152.515.408Kb and
index size = 57.774.368Kb

In this server when I run the following command
UPDATE STATISTICS RDFMOVTO
WITH SAMPLE 15000000 ROWS, ALL

it takes 7 hours to execute.

Second Situation.
4 DELL servers, Windows 2000 Data Center Server and SQL Server 2000 Enterprise Edition, 16 GB RAM, 8 processors 700Mhz
The disks are in a Hitache storage connected by fiber channel. The storage has 14TB.

It's the same table.
When I run the same command
it takes 4 hours to execute.

Well, I'd like to know if it's possible to get a better answer from SQL Server
and decrease this time to execute this command.

Thank's a lot.

Ângelo Luiz Cecon
MCT, MCSE, MCDBA

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-23 : 12:13:23

My first thought is that the database tables are heavily fragmented. Are these highly transactional tables? I can't see that network or CPU would be the issue since you've got plenty of each. I can only guess that the data is spread out in bits and pieces on the drives of the storage box and it's causing execessive head seeking.

Have you tried a DBCC REINDEX and a DBCC SHRINKDATABASE? This will probably take a long time, but it should make the UPDATE STATISTICS go faster in the future. I wouldn't be surprised if it still takes up to 1 hour to do it, these are pretty big tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources