May 25, 2011 at 3:13 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 25, 2011 at 3:18 am
the way to convert a sql 2000 db to 2005 is to restore the 2000 db to 2005, you do not need to run any of those scripts.
Changing the compatibilty level will just change the commands that can be run against the database, every database on a 2005 server is a 2005 database.
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
I would not run this script on a database ever, auto-shrink will cause you problems and may be a a cause for your performance issues.
May 25, 2011 at 3:28 am
river1 (5/25/2011)
Way is this appening? can someone help me with this?
Autoshrink is going to fragment every single one of your indexes on a regular basis, that's probably why you need to rebuild them. Turn that option off.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2011 at 3:43 am
The auto_shrink is off in most databases but the problem is the same...
May 25, 2011 at 3:55 am
river1 (5/25/2011)
The auto_shrink is off in most databases but the problem is the same...
It should be off for ALL databases, there is no good reason to turn it on.
For the performance issue have a look the actual execution plans to see what the difference is between the two queries
May 25, 2011 at 3:56 am
yes, i know.
May 25, 2011 at 4:37 am
There are instances where a poorly written query performed adequately in 2000 but doesn't perform as well in 2005/2008. Usually fixing the issues with the query fixes the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 25, 2011 at 6:27 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.
May 25, 2011 at 6:56 am
There are changes to the query optimizer between 2000 and 2005. Because of this, you will see some changes in behavior. Most of the time you'll see improvements or no change, but sometimes you will see negative changes. In most of the cases that I've investigated in detail, there were numerous tuning opportunities in the query, even when it was in 2000.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 25, 2011 at 7:02 am
I understand, what i don't undestand is way in SQL Serfver 2000 the update statisctis (auto create and auto update) worked so fine and they don't seem to be fine in SQL Server 2005.
When i update statistics to the table query is very performante, so , in this particular case i don't think that changing the query sintax is necessary.
What i would like to undestand is what to do with this statistics
May 25, 2011 at 7:09 am
The problem will almost certainly be related to the design of the query, you could post the full query and table ddl with indexes to see if any improvement could be made
May 25, 2011 at 7:11 am
Most likely you've hit a point where the stats need to be maintained manually and not count on the server to do this.
The server waits for 20% row change in the table. What's 20% of 1M rows >>> 200K.
After even a few 1000 rows you might need to update the stats depending on your data.
May 25, 2011 at 7:15 am
I undestarnd but still in 2000 that didn't happen and lthe table has only a few more rows...
I will post the query
May 25, 2011 at 7:17 am
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
May 25, 2011 at 7:22 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
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply