|
|
Posted by HC on 02/04/07 02:32
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
[Back to original message]
|