May 25, 2011 at 7:23 am
Update the stats on that table daily... maybe 1-2 hours before business hours start and that'll solve the issue.
May 25, 2011 at 7:24 am
river1 (5/25/2011)
I undestarnd but still in 2000 that didn't happen and lthe table has only a few more rows...
SQL 2000 to SQL 2005 was a major change. not a couple additional features, a significant rewrite. Stuff happens and it's not unexpected. Don't waste time on 'but it worked on SQL 2000', focus on finding what does work.
It's quite common for the auto update stats to not be adequate (and it tended to kick in more often on 2000 than on 2005) and the solution in that case is to schedule manual stats updates.
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 7:25 am
river1 (5/25/2011)
My problem is that after some data is inserted this will hapeen again but only for dates above update statistics day.
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 7:36 am
GilaMonster (5/25/2011)
river1 (5/25/2011)
I undestarnd but still in 2000 that didn't happen and lthe table has only a few more rows...SQL 2000 to SQL 2005 was a major change. not a couple additional features, a significant rewrite. Stuff happens and it's not unexpected. Don't waste time on 'but it worked on SQL 2000', focus on finding what does work.
It's quite common for the auto update stats to not be adequate (and it tended to kick in more often on 2000 than on 2005) and the solution in that case is to schedule manual stats updates.
Excellent advice.
"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:39 am
river1 (5/25/2011)
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
A couple of things... You don't have any WHERE clause, so unfiltered data can lead to scans and other poor behavior. The query uses MIN with a GROUP BY in the derived table, have you tried using the MIN and an ORDER BY? You might get better behavior.
"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 8:24 am
Gail Shaw thank you for the link.
You say that the update statisctics trigger on SQL Server 2005 fires at 20%.
Do you mean that this auto update statistics trigger only updates tables that have 20% more rows that the lest time they were updated by this trigger?
May 25, 2011 at 9:06 am
By the way, i have one table (that is on the join) which as a column (the primary key) with an DESC index.
A DESC index is what you call on your archical as a ascending column?
May 25, 2011 at 9:23 am
you may be better off by first looking at the execution plan to see where the bottle-neck is, and treat this as you would any other badly performing query, rather than starting by looking at the statistics and differences between 2000 and 2005.
you may find that you are missing some crucial indexes or part of the query could be written more effectively.
May 25, 2011 at 9:28 am
Thanks for the tip but in this case i don't know if it is necessary, because when you update the statistics the query becames very performante.
May 25, 2011 at 11:37 am
river1 (5/25/2011)
Do you mean that this auto update statistics trigger only updates tables that have 20% more rows that the lest time they were updated by this trigger?
No. 20% changes, not 20% additions.
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 11:39 am
river1 (5/25/2011)
A DESC index is what you call on your archical as a ascending column?
No, not at all. I explained it in the blog post. An ascending column is one where each new row has a value for that column higher than any other row. Identity and data inserted are classic ascending columns.
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 11:39 am
and in 2000? the behavior was the same? or it was neded less chages to fire the trigger?
May 25, 2011 at 11:44 am
river1 (5/25/2011)
and in 2000? the behavior was the same? or it was neded less chages to fire the trigger?
Do you plan on regressing to sql 2K???
Focus on what you need to do NOW to fix the issue. That means setup a daily job that runs a few hours after business hours start and run update stats.
Thank you very much, done. Next issue plz.
May 25, 2011 at 11:56 am
river1 (5/25/2011)
and in 2000? the behavior was the same? or it was neded less chages to fire the trigger?
There were subtle differences that you can easily find if you do some reading.
But that's besides the point, the issue is how to get things fine again, and your solution, as I earlier mentioned, is to schedule a job that runs a stats update for that table. Probably daily.
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 12:03 pm
Thank you gail.
I have created indexes on all the tables that make joins in the query (some did not had).
Now the query is permorming much better.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply