sql server 2000 after update statistics job on ta

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 Development (2000)

sql server 2000 after update statistics job on ta

Author

Topic

sarikondak
Starting Member

7 Posts

Posted - 2008-08-02 : 08:43:23

Sql Server 2000

One of our client reported the slow performance of one our programs.

We have analyzed the trace files supplied by the client. We have found , that the procedure XXXX is taking about 5 minutes each time it is executed. I have seen the execution plan on the database and found that the Optimizer is taking table scan on one table YYYY and that causes delay.

I hve done DBCC DBREINDEX on YYYY table and SP took less than two seconds. The YYYY table contains more than 15 million records

When I run update statistics after reindexing again it got back to 5 minutes.

Our client does Update statistics every alternative day on all database tables hence client wants a solution.

The SP is taking table scan after update statistics.

Thanks in advance for your help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-02 : 12:48:54

After you rebuild index, you don't need to update stats as it will do. Also you need index if it is doing table scan.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-02 : 14:04:05

>> Our client does Update statistics every alternative day on all database tables hence client wants a solution.

Ensure update statistics with full scan, but they still need reindex in schedule.

sarikondak
Starting Member

7 Posts

Posted - 2008-08-04 : 00:28:49

Thank you very much SODEEP and RMJAO.

But Update statistics with full scan is taking very long time on particular XXXX table. Below are the timings

Update statistics on YYYY table took 30 minutes
DBREINDEX on YYYY table took 3 hours and 20 minutes
Update statistics With FULLSCAN on YYYY table ran for 9 hours and I have to stop the physically.

I have further done following
DBCC UPDATEUSAGE ('DBNAME')
It arrange lot of table but the particular table in context results are given below

DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 3):
USED pages: Changed from (35983) to (36068) pages.
RSVD pages: Changed from (36022) to (36102) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 4):
USED pages: Changed from (27250) to (27292) pages.
RSVD pages: Changed from (27398) to (27446) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 5):
USED pages: Changed from (16646) to (16691) pages.
RSVD pages: Changed from (16725) to (16773) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 6):
USED pages: Changed from (17603) to (17635) pages.
RSVD pages: Changed from (17678) to (17718) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 7):
USED pages: Changed from (15247) to (15255) pages.
RSVD pages: Changed from (15302) to (15318) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 8):
USED pages: Changed from (17586) to (17683) pages.
RSVD pages: Changed from (17646) to (17750) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 9):
USED pages: Changed from (25998) to (26040) pages.
RSVD pages: Changed from (26028) to (26084) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 75):
USED pages: Changed from (10064) to (10761) pages.
RSVD pages: Changed from (10065) to (10762) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 76):
USED pages: Changed from (12954) to (13672) pages.
RSVD pages: Changed from (12961) to (13674) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 82):
USED pages: Changed from (45691) to (45766) pages.
RSVD pages: Changed from (45696) to (45778) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'YYYY' (index ID 0):
USED pages: Changed from (662464) to (662466) pages.
RSVD pages: Changed from (663148) to (663157) pages.

Reindex, at least it was returing after 3 hours but fullscan is taking very abnormal time. Still I will try and update you.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 00:37:25

DBCC UPDATEUSAGE does nothing for performance.

We don't do full scan for update stats as it takes way too long on large tables. We typically do 25%.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 00:37:34

sarikondak
Starting Member

7 Posts

Posted - 2008-08-04 : 04:20:46

Thank you Tara Kizer

I will try 20% sample scan

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-04 : 09:42:19

quote:
Originally posted by sarikondak

Sql Server 2000

One of our client reported the slow performance of one our programs.

We have analyzed the trace files supplied by the client. We have found , that the procedure XXXX is taking about 5 minutes each time it is executed. I have seen the execution plan on the database and found that the Optimizer is taking table scan on one table YYYY and that causes delay.

I hve done DBCC DBREINDEX on YYYY table and SP took less than two seconds. The YYYY table contains more than 15 million records

When I run update statistics after reindexing again it got back to 5 minutes.

Our client does Update statistics every alternative day on all database tables hence client wants a solution.

The SP is taking table scan after update statistics.

Thanks in advance for your help

You do not need to update stats after you rebuild index.

sarikondak
Starting Member

7 Posts

Posted - 2008-08-04 : 22:53:56

Hi Tara Kizer,

I ran following
update statistics YYYY WITH sample 20 percent and it did not stop even after 17 hours. I have to manually stop.

Hi Sodeep,

Thanks for the advise. My worry is, as a maintenance plan , my clients at the end, they run update satistics on each table and in a loop, and table YYYY also falls in it. So when the program calls the procedure XXXX, it does not take indexes ( though the where condition meets one of the indexes) and goes for table scan.

Where as when I run manually reindex and run this procedure it hits index and takes less than a second. The worry is that Update Satistics is misleading optimizer. I can ask the client to put Reindex on YYYY table at the end, but they are asking why update statistics fails.

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-04 : 23:44:12

>> they are asking why update statistics fails.

If don't update statistics with full scan option, sql just samples data (about 5% of data) for statistics. That sampling may way off from real data distribution.

sarikondak
Starting Member

7 Posts

Posted - 2008-08-05 : 00:35:09

Thank you very much. It sound logical to explain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources