Posted by Erland Sommarskog on 02/03/07 23:14

HC (hboothe@gte.net) writes:
>> I have installed the Management Express as you suggested and have
>> reviewed the query plan. I ran the same query from the Management
>> Express console, three times each, against the same database on both
>> systems (MSDE and SQL Express 2005). Against the MSDE the system runs
>> the query in Total Execution Time of average 135 milliseconds (I
>> believe the time displayed is in milliseconds). This is across a
>> crossover cable between the two systems. Against the SQL Express 2005
>> system, running on the same system so it should be a Shared Memory
>> connection, it runs an average Total Execution Time of 1546
>> milliseconds, over 10 times as long. I had SP_DETACH_DB'd the
>> databases from the MSDE, SP_ATTACH_DB'd them to SQL Express 2005, run
>> SP_UPDATESTATS against each of the databases on Express.
>>
>> I am going over the query plan and the actual execution now. Any
>> thoughts?

Did you copy the MDF files to the SQL 2005 instance? That is, it can be
a good idea to have the database both on SQL 2000 and SQL 2005 so that
you can scrutinize the differences in the query plans.

And don't forget to run UPDATE STATISTICS WITH FULLSCAN on the table
when you have attached on SQL 2005; the statistics from SQL 2000 is
invalidated.

One thing that I have noticed with SQL 2005 that can be problematic, is
that if it cannot get all memory it would like to have, it can be
very slow. (I have not verified whether this can happen with the
Express edition as well.) If there are other applications running on
the machine - and this should be common with SQL Express - one way to
avoid this problem is to set the configuration parameter "max server
memory" to some amount of memory that is likely to be available. My gut
feeling, though, is that this is a query-plan issue.

> An interesting thing I just noticed:
>
> In my VB6 app using ADO 2.8 when I run the long query process for one
> report it takes, on a test box I've set up for this purpose with XP
> SP2, 512MB RAM, bunch o' GB free disk space, Athlon 2400+ and Express
> 2005 SP1, it is taking about 4 minutes consistently.
>
> HOWEVER, in one portion of my program I open a database connection
> when the user opens a window and I keep that connection alive and open
> as long as that window is open. When that window is open (so, the
> connection is open, too) then the same long-process query consistently
> takes about 40 seconds. I'm not sure what to make of that yet.

Funny. Does tnis happen on SQL 2000 or SQL 2005?

I have no idea what this could be.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]