SQL Server convert from 2000 to 2005

Last Post 25 May 2011 04:44 PM by gunneyk. 12 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
24 May 2011 08:23 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.



rm
New Member
New Member

--
24 May 2011 09:46 AM
1. Why set db to auto shrink?
2. How many changes in underlying tables? How often do you reindex/update stats in the db?
river1
New Member
New Member

--
25 May 2011 12:20 AM
the auto_shrink is to be removed....

Has to how many changes in underlying tables, i am not choore of what you are asking, but this is a OLTP database so updates and inserts to all tables are normal.

I reindex from time to time (1 month to 1 month).

But, the problem is taht, when the database was in SQL Server 2000 this problem didn't appen. Now, it's happening and i can only resolve it after reindex again all the tables. Can someone help me with this?
river1
New Member
New Member

--
25 May 2011 12:22 AM
It looks like every time that need data is inserted after all the indexs are rebuild , this new data is slow in querys but data that is allready in the database (before the index) is not slow.

This didn't happen in 2000
river1
New Member
New Member

--
25 May 2011 04:25 AM
I went deeper and found more intersting things....

I was thinking that this had to do with rebuid the indexs, but may be not.

every time a run this command, no problems:

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


I discovered that the table that had the problem has the liquidacao table.


If i rebuild it's indexs using:

DBCC DBREINDEX ('liquidacao', '', 0)

the problem is solved (no perfomance issue).


But if i try to do it , index by index name with the same command, the problem is not solved...

The view only get good performance if i do the index to all the indexs at the same time.

strange...


Then i went to see if it was really necessary to reindex the table or if it was only necessary to update it's statictis.

I used the command:

UPDATE STATISTICS liquidacao WITH FULLSCAN

After using the command the problem desapeared....

So, i think that this is not a index problem but a statistics problem. Maybe every time i do dbcc reindex to the table, the command it self rebuild the index and update the statistics automaticly.

But it seems that the problem is with statisctis.


This didn't happen when the database was in 2000.

and in 2000 and 2005 the database as the option auto create statistics and auto update statistics =true...

I would like to know why this was good in 2000 and now it's now in 2005, even with the same automatic statistics on.










rm
New Member
New Member

--
25 May 2011 05:02 AM
Ensure you get all indices when rebuild individually, should have same result either way if rebuild all of them.
river1
New Member
New Member

--
25 May 2011 05:07 AM
Ok, but in my case if i only update statistics to that table, then the perfomance returns to good. So, i think that this is a statistics update problem.

What i don't understand is way...

I have the database with the option auto create and auto update statistics to on, but it seems like it does not update the sattistics to this particular table...

In SQL Server 2000 this didn't happen...


Do you have any ideia of what can be done?
river1
New Member
New Member

--
25 May 2011 05:23 AM
query (view)

SELECT Liquidacao.CODLIQ, Liquidacao.NUMLIQ, Liquidacao.LANCLIQ, Liquidacao.DATALIQ, Liquidacao.VALORTRIB, Liquidacao.ANOLIQ,
Liquidacao.DESCPERIODO, Liquidacao.CODTLIQ1, Liquidacao.IMPTRIB, Liquidacao.CODDLIORIG, Liquidacao.TIPODLI, Liquidacao.CODCRORIG,
ISNULL(dbo.IMPOSTODESD.NUMIMPDESD, Imposto.NUMIMP) AS NUMIMP, Contribuinte.NOMECTB, Contribuinte.NIFCTB,
CCCredito.VALORCCCREDITO, SGTALiqData.NUMDU, SGTALiqData2.OrdemNotas, SGTALiqData.CODCR, SGTALiqData.DATAEXPIRACAO,
Liquidacao.CODANULA, Liquidacao.CODIMP, Liquidacao.CODIMPDESD
FROM dbo.LIQUIDACAO AS Liquidacao LEFT OUTER JOIN
dbo.IMPOSTO AS Imposto ON Liquidacao.CODIMP = Imposto.CODIMP LEFT OUTER JOIN
dbo.CCCREDITO AS CCCredito ON Liquidacao.CODLIQ = CCCredito.CODDLI AND CCCredito.TIPOLANC = 0 INNER JOIN
dbo.SGTALIQDATA AS SGTALiqData ON Liquidacao.CODLIQ = SGTALiqData.CODLIQ INNER JOIN
(SELECT MIN(CODLIQ) AS OrdemNotas, NUMDU
FROM dbo.SGTALIQDATA AS SGTALIQDATA_1
GROUP BY NUMDU) AS SGTALiqData2 ON SGTALiqData2.NUMDU = SGTALiqData.NUMDU LEFT OUTER JOIN
dbo.IMPOSTODESD ON Liquidacao.CODIMPDESD = dbo.IMPOSTODESD.CODIMPDESD LEFT OUTER JOIN
SGCT.dbo.CONTRIBUINTE AS Contribuinte ON Liquidacao.CODCTB = Contribuinte.CODCTB
river1
New Member
New Member

--
25 May 2011 05:23 AM
remember one thing.

after i update the statistics the query is very fast.

like

select * from diarioliq
where dataliq = '2011-05-16' = 1 sec.

if no statistics are updated then

select * from diarioliq
where dataliq = '2011-05-16' = after 5 minuts i stop query....


My problem is that after some data is inserted this will hapeen again but only for dates above update statistics day.

For example, suppose i update statistics today (25-05-2011), whitin 10 days the query is slow again but like this:


select * from diarioliq
where dataliq = '2011-05-16' = 1 sec.

...........................................
...........................................

select * from diarioliq
where dataliq = '2011-05-25' = 1 sec.


select * from diarioliq
where dataliq = '2011-05-26' = minuts.. need to stop the query

river1
New Member
New Member

--
25 May 2011 07:13 AM
Auto update statistics seems to only trigger after a table as more 20% of modifications since last time it was triggered.

What about SQL Server 2000? did it performe the same way (only on 20%) ? or did it triggered sooner?
rm
New Member
New Member

--
25 May 2011 07:37 AM
Rebuild index will update stats, and set auto update stats on desn't mean you don't need schedule reindex/update stats regularly.
river1
New Member
New Member

--
25 May 2011 07:40 AM
Ok, but what about the diffence in behavior between SQL Server 2000 and SQL Server 2005 in terms of auto update statistics? Does the trigger fire more times in SQL Server 2000 that in SQL Server 2005?
gunneyk
New Member
New Member

--
25 May 2011 04:44 PM
2000 used a single counter for any mods to the table that dictated when the stats were updated. In 2005 each column has it's own counter so its more accurate as to when something is out of date or not.
You are not authorized to post a reply.

Acceptable Use Policy