May 24, 2011 at 10:26 am
Hi,
I have a database that was converted from SQL Server 2000 to SQL Server 2005 with the following script:
if not exists (select sname from SCRIPTS where sname ='SCRIPT_table_ALL_2009-07-10_ACT_31')
begin
declare @erro as int
ALTER DATABASE SGT SET SINGLE_USER WITH ROLLBACK IMMEDIATE
set @erro = @@error
if @erro = 0
begin
if(select count(*) from sys.databases where name = 'SGT' and compatibility_level=80) <>0
begin
EXEC sp_dbcmptlevel SGT, 90
set @erro =@@error
end
if @erro=0
begin
if(select count(*) from sys.databases where name = 'SGT' and is_auto_shrink_on=0) <>0
begin
ALTER DATABASE SGT SET AUTO_SHRINK ON
set @erro =@@error
end
end
if @erro=0
begin
if(select count(*) from sys.databases where name = 'SGT' and page_verify_option<>2) <>0
begin
ALTER DATABASE SGT SET PAGE_VERIFY CHECKSUM
set @erro =@@error
end
end
if @erro=0
begin
if(select count(*) from sys.databases where name = 'SGT' and is_auto_close_on<>0) <>0
begin
ALTER DATABASE SGT SET AUTO_CLOSE OFF
set @erro =@@error
end
end
if @erro=0
begin
declare @table varchar(256)
declare tables cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
exec('DBCC DBREINDEX ('+@table+', " ", 0)')
fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
end
if @erro=0
begin
declare @table2 varchar(256)
declare tables2 cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables2
fetch next from tables2 into @table2
while @@fetch_status = 0
begin
exec('UPDATE STATISTICS '+@table2+' WITH FULLSCAN')
fetch next from tables2 into @table2
end
CLOSE tables2
DEALLOCATE tables2
end
ALTER DATABASE SGT SET MULTI_USER
end
else
select 'No foi possvel colocar a base de dados em Single_User_Mode.
Existem ligaes activas base de dados. Feche as mesmas e volte a executar o script.'
if @erro=0
begin
insert into SCRIPTS values ('SCRIPT_table_ALL_2009-07-10_ACT_31', '2009-07-10', 'ACT', 31, 'From_2000_to_2005', getdate())
end
else
select 'Problemas durante a execuo do script, por favor contacte o Administrador de sistemas.'
end
else
select 'O script que est a tentar correr contra a base de dados , já foi corrido anteriormente. Por favor contacte o Administrador de Sistemas.'
GO
Problem: when the script was executed against the database all the querys run fine, after some time, a view started to have performance problems....
I executed the script on day 09 of this month.
If i select the view with date prior to 09, then the view is still very performante, but if a execute it with a date like 09 or 10 or upper, i get performance issues.
i.e - select * from diarioliq where dataliq = '2011-05-08' Good perfomance
select * from diarioliq where dataliq = '2011-05-10' bad perfomance
The only way that i can get the view having goog performance again is by executing the command:
Reindex command:
declare @table varchar(256) declare tables cursor for select table_name from information_schema.tables where table_type = 'base table' open tables fetch next from tables into @table while @@fetch_status = 0 begin exec('DBCC DBREINDEX ('+@table+', " ", 0)') fetch next from tables into @table end CLOSE tables DEALLOCATE tables
Way is this appening? can someone help me with this?
Thank you.
May 24, 2011 at 10:39 am
#1 turn auto_shrink OFF and reindex the whole db.
#2 select * from diarioliq where dataliq = '2011-05-10' bad perfomance
That is likely because the stats are out of date.. literally. If the stats were updated on the 9th, then the server thinks there is 0 rows for the 10th and that can give a very bad plan.
See if re-updating the stats fix the issue.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply