Query handling difference SQL Server 05 and 08

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query handling difference SQL Server 05 and 08

Author

Topic

DanielDucharme
Starting Member

11 Posts

Posted - 2010-04-01 : 18:54:20

I am working with a large database with a number of stored procedures that we have used for years and when I run one procedure on SQL Server Manager 2005 it takes about 35 seconds to run. When I run the same query on the same database only using SQL Server Manager 2008 it takes over 10 minutes (not sure exactly how long because I always stop it after 10 minutes and just remote into a computer with Manager 2005 and do it in 35 seconds). I am wondering why this query is so different depending on which it is run in, see the code below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].spGrantAllToOne] @User varchar(50)
AS

DECLARE @name varchar(100)

DECLARE curMain CURSOR for select name from sysobjects where uid = 1 and type not in ('TR','D','K','F')
FOR READ ONLY
Open curMain
fetch next from curmain into @name
while @@FETCH_STATUS = 0
begin
      SELECT @name, @User
      execute('grant all on [' + @name + '] to [' + @User +']')
      fetch next from curmain into @name
end
close curmain
deallocate curmain

Thanks in advance for any help you can give.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-01 : 18:57:39

The execution plans must be different. For them to be different, the connection properties must be different if you are using the same exact query. Compare the execution plans and let us know.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-01 : 19:00:10

And if they are in different like I suspect due to the connection properties, try recompiling the stored procedure to get rid of the bad plan in cache. I wouldn't suggest clearing the entire procedure cache though as that'll hurt other queries.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DanielDucharme
Starting Member

11 Posts

Posted - 2010-04-02 : 12:37:47

Checking the execution plans they look the same. That being said I noticed that it is just running significantly faster on Manager 2005 doing around 15,000 message lines in 1 minute where in 4 minutes Manager 2008 only managed 2427. That being said I also notice a Spatial results tab showing up on Manager 2008 on each line and then disappearing, this does not occur on Manager 2005. This continued even after I recompiled the stored procedure.

DanielDucharme
Starting Member

11 Posts

Posted - 2010-04-16 : 14:42:38

No other ideas as to why this could be happening?

Kristen
Test

22859 Posts

Posted - 2010-04-16 : 14:57:43

Did you Reindex etc. after moving to SQL 2008?

Suggested migration process here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources