Query Run Very Slow on SQL 2005

Question

Query Run Very Slow on SQL 2005

jsanchez-851191

SSC Veteran

Points: 221
More actions
August 27, 2009 at 5:44 pm

#204106

Hi,
I restored a Backup SQL 2000 Database in SQL 2005. Change Compatibility Level to "SQL Server 2005 (90) and execute the followings commands:
DBCC UPDATEUSAGE (0)
DBCC CHECKDB()
ALTER DATABASE ACO_PROD SET PAGE_VERIFY CHECKSUM
EXEC sp_UpdateStats
But i when run the queries, its run very slow.
I Sending the Execution Plan, SQL 2000 and 2005 of same query
Jose Sanchez

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

Hi Jose,

From comparing the two execution plans, it looks like the main culprit is the Nested Loops join instead of a hash join at the second last operation (i.e., at the top left of the diagram). While I can't tell exactly if all the tables are accessed in the same fashion otherwise (although it's likely, because the plan is essentially the same otherwise).

In your 2000 plan, you have a table scan, pulling back a huge number of rows (judging by the thickness of the arrow) into the hash match. In 2005, the nested loop join will look up the Impuestos table for every row coming out of the Hash Match (Inner join).

I would suggest two things. First, it's a good idea to rebuild all your statistics when upgrading from 2000 to 2005. Secondly, it looks like you could do with an index on the Impeustos table to prevent the table scan. This appears to be the case with both 2000 and 2005, but without seeing the code and table structure, or a text plan, it's hard to make a suggestion of what the index should be.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

sp_updatestats only updates stats that SQL thinks are outdated. When upgrading you need to update all statistics. Use UPDATE STATISTICS.

If it's still slow after that, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jsanchez-851191 SSC Veteran Points: 221 More actions · Answer 3

Hi,

Thanks for your responses.

Executed the update statistics on Database; but query runs slow ...

The execution time of query on SQL 2000 is 6 seconds, on SQL 2005 23 minutes.

I sending the T-SQL and the Scritps to create the tables and indexs.

Thanks a lot

Jose Sanchez

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Execution plan please. Just the SQL 2005 one is fine.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jsanchez-851191 SSC Veteran Points: 221 More actions · Answer 5

jsanchez-851191

SSC Veteran

Points: 221

August 28, 2009 at 1:33 pm

#1046388

I sending The execution plan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

CAn you please post the exec plan from the SQL 2005 server as detailed in the article I referenced above. You're missing indexes, I can't tell which ones without seeing the details of the exec plan

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jsanchez-851191 SSC Veteran Points: 221 More actions · Answer 7

jsanchez-851191

SSC Veteran

Points: 221

August 28, 2009 at 3:53 pm

#1046424

Execution Plan from SQL2005

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Ok, you need a couple indexes and a partial query rewrite. I have no idea how SQL 2000 was executing this, seeing as it would have had to do table scans and hash joins. Unless some indexes were dropped during the upgrade.

Try the following.

Index on dbo.ImpuestosASCO on (CodigoImpuesto)

Index on dbo.impuestosACF on (AscId, CodigoImpuesto)

Index on dbo.Impuestos on (AscId, CodigoImpuesto)

and change the NOT IN to a NOT EXISTS, that will remove the need for a string conversion and concat and allow index usage.

WHERE

NOT EXISTS (SELECT 1 FROM dbo.Impuestos WHERE dbo.impuestosACF.AscId = dbo.Impuestos.AscId

AND dbo.impuestosACF.CodigoImpuesto = dbo.Impuestos.CodigoImpuesto)

If it's still slow after you make those changes, post the new plan.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jsanchez-851191 SSC Veteran Points: 221 More actions · Answer 9

Thanks a lot Gail,

Executed the query and returned the information very fast

Mark Harrison-282986 SSC Enthusiast Points: 197 More actions · Answer 10

I think that the problem might simply be the NOT EXISTS. I converted a SQL Server 2000 database to 2005 shortly after 2005 was released and ran into a huge problem with NOT EXIST queries (not mine, I never use them).

The query was not fast under SQL Server 2000 (about 12 minutes execution time) but the query never finished under SQL Server 2005. Well, it was never allowed to finish. I cancelled it. Anyway, I re-wrote the query and everything was fine.

A Microsoft support request was made and a bug filed but this sounds like it was never resolved. A bit disappointing but I'm not surprised. It was a badly written query.