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 7- Sql 2000 upgrade performance problems

Author  Topic 

blakmk
Starting Member

45 Posts

Posted - 2003-03-21 : 11:54:51
Ive just upgraded our database to sql 2000 from sql 7 and noticed severe performance problems with certain dataloading type applications. The application typically does a few select statements and then either inserts or updates data.

Ive got auto update/create statistics on and I noticed that after 6 hours of loading, the statistics havnt been updated from the beginning of the load. The optimizer thinks there are 15000 records in the main table when in fact there are 110000.

I suspect the optimizer is making a wrong call in the execution plan. Has anyone else experienced this problem/ knows a fix?







jharwood
Starting Member

41 Posts

Posted - 2003-03-21 : 12:59:02
When we upgraded, we had a few system stats (_wa) and tuner indexes (hind_..)which were corrupted. Received this script from microsoft to find and delete these objects. After removing them and manually re-creating, we did not see a reoccurrence...hope this helps

set nocount on
select
case
when i.name like 'hind_c_%' then 'drop index ['
else 'drop statistics ['
end,object_name(i.id) + '].[' + i.name + ']' from sysindexes i join
sysobjects o on i.id = o.id
where
i.name like 'hind_%' and
(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND INDEXPROPERTY(i.id,
i.name, 'IsAutoStatistics') = 0))
order by i.name

select
case
when i.name like 'hkhk' then 'drop index ['
else 'drop statistics ['
end,
object_name(i.id) + '].['+ i.name + ']' from sysindexes i join
sysobjects o on i.id = o.id
where
i.name like '_wa%' --and
--(INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR
--(INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND INDEXPROPERTY(i.id,
--i.name, 'IsAutoStatistics') = 0))
order by i.name
set nocount off




Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-21 : 13:15:42
Have you tried rebuilding your indexes? I would highly recommend doing that (DBCC DBREINDEX).

Tara
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-21 : 23:47:19
In addition, you can also run UPDATE STATISTICS on your tables, if you don't want to drop them. The following will work:

EXEC master..sp_MSforEachTable 'UPDATE STATISTICS ?'

It runs a cursor over each table name and substitutes it where the ? appears, this will update the statistics on every table. It might take a while to run, but it will freshen everything up and you should see better performance afterwards. I *think* UPDATE STATISTICS might also drop corrupted stats, I had run this exact statement on a DB that had tons of autostats taking up space, and they just disappeared.

And yes, just because auto update statistics is set to ON doesn't mean that they always refresh. It's always a good idea to run an explicit UPDATE STATISTICS against any table that has seen extensive activity and you want to ensure it performs as best it can. The same applies to DBREINDEX, it can make a huuuuuuuuge improvement in performance when done regularly.

Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-23 : 15:30:19
Rob ,

You sure about the syntax on that
"EXEC master..sp_MSforEachTable 'UPDATE STATISTICS ?' "

I only ask because I've run it on a 700 table database , and all I get is a subsecond response, with "the command(s) compelted successfully".

Pity MS don't document this. So I whip out the Guru'sguide, and based on chapter 15, I wrote the following:

"EXEC sp_MSforEachTable 'UPDATE STATISTICS "?"' ,'?'" which does something ... unfortunately all it seems to do is complain about the tables not exists . I note it preface's all table qualifiers with [dbo]., but that's cool, beacuse they are all owned by dbo. Still - no updated stat's, though.

From BOL , I got , sp_updatestats, which seems to do the trick - it updates all USER table in a database.

Ciao

PS - took 10min 16 seconds on a 40 GB database with 725 tables - guess it way to have 8x P4 1.4 and 4 GB of ram !!!

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Edited by - wanderer on 03/23/2003 15:47:00
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-23 : 15:47:48
Hmmmmm, there might be a difference between our servers that allowed it work for me, but sp_updatestats is a better choice anyway. I didn't realize it was available! Very cool, thanks!

Go to Top of Page

blakmk
Starting Member

45 Posts

Posted - 2003-03-24 : 07:49:31
If I run sp_updatestats with no arguments does it compute or sample the statisitics

Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-04-01 : 04:07:14
According to BOL:

"If 'resample' is not specified, sp_updatestats updates statistics using the default sampling" - resample being the parametre ...



*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-04-07 : 06:13:58
Having sp_helptext'd the sp_updatestats - it is just a cursor running update statistics for each table in a db where type = 'U'.

If the sp_updatestats doesn't help you, consider specializing the sp_updatestats (as I'm going to look into) to accept a parametre that will run update statistics WITH FULLSCAN , or WITH SAMPLE x PERCENT ,rather than just resampling.

HTH

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page
   

- Advertisement -