Posted by HC on 02/04/07 08:33

On Feb 3, 8:32 pm, "HC" <hboo...@gte.net> wrote:
> On Feb 3, 5:14 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
>
>
> > HC (hboo...@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, esq...@sommarskog.se
>
> > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx-Hide quoted text -
>
> > - Show quoted text -
>
> Erland, I copied all the DB files from the MSDE 2000 installation to
> the SQL Express 2005 system. The EXE (my program) is exactly the same
> on both systems, the databases are identical (I did a fresh copy over
> this morning in preparation for more testing so I'm quite certain they
> are the same). The files I copied are the MDF and the LDF files and I
> referenced them both in the sp_attach_db statement (sp_attach_db
> 'dbname', 'c:\dbdatafile.mdf', 'c:\dblogfile.ldf').
>
> I'm sorry to be ignorant but I do not recognize the parameters you
> mention about the update stats, I will check BOL for this. I ran this
> on each db (use <db>, go, SP_UPDATESTATS, go).
>
> The only thing I can think of about the open connection from VB would
> be that the open database connection would keep SQL Server Express "on
> the line", so to speak, and might make it faster for other database
> connections from my app to locate/call the DB. That may be a lame
> guess but it's the best I have now.
>
> Thank you for your help and input. I'm disappointed in myself for not
> knowing more about this stuff, particularly since I do make a living
> using this DB as my data storage; I sincerely appreciate the help.
>
> I have tried a few different things that did not work so I'm about to
> re-do the query plan and review them.
>
> --HC- Hide quoted text -
>
> - Show quoted text -

After reviewing the query plans and the actual execution of them I
think I have an idea of where the next place to look is. Since it
does not involve VB or programming at all, I'm going to start a new
thread to try to keep things neat and tight.

--HC

[Back to original message]