Posted by HC on 02/06/07 16:46

On Feb 5, 4:20 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> HC (hboo...@gte.net) writes:
> > It turns out, as I'm sure you know, that AUTO_CLOSE defaults to on
> > ONLY for SQL Express (not for other versions of SQL 2005 (from my
> > reading of BOL)). Turning it off was a huge help to me and my
> > program.
>
> As far as I know, AUTO_CLOSE is also on by default for MSDE. Probably
> some time in the dim and distant path, you learnt about this, turned
> it off, and by now you have forgotten all about it.
>
> Happens to me too. Just watch this thread. It took me a couple of
> posts to recognize the problem.
>
> --
> 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

I know what you're saying but the auto_close is off. I checked my
databases and it's off and I'd remember this kind of hell. :) My
only offering is that I designed and built the databases using the SQL
Server Enterprise Manager that I have with SQL Server 2000 Enterprise
Edition so perhaps it is a function of whatever creates the database,
not in which data engine the database is created. At least with MSDE
2000.

I tried testing this by scripting an existing database and then using
OSQL to execute the script and make a new database on MSDE on my dev
machine (that I use all the time) but it still didn't mark the DB as
auto_close. But, you are right, MSDE is supposed to, by default, mark
the databases as auto_close on (I checked BOL). I'm not sure, but it
seems odd to me that the default behavior the BOL says I should see
isn't happening which makes me think I'm doing something wrong.

In any case, setting AUTO_CLOSE off on the DB's on Express (of which
all of mine were marked on) saved a ton of time on the queries. The
simple stuff (one join, one criterion) took 1,100 to 1,500 ms or so
repeatedly, now it takes less than 300 ms.

The problem I'm working on now is why my 2Ghz Centrino on a GB of RAM
running XP SP2 and MSDE 2000 on a 5,400 RPM disk kicks out my simple
join query, inside my program but with similar speed differences from
SQL Management Express, in about 3-5 hundreths of a second (0.03 -
0.05 seconds) but my Vista system running 2GB of RAM, Athlon 64 X2
dual core 3800+, on a 7,200 RPM disk and using SQL Express 2005 kicks
the same query, on the same data, with the same program, out in about
one tenth of a second (0.10). This isn't a huge problem in itself.
What makes this a problem are two things: 1) why any perf difference
to the negative on a faster machine with the new DB system (Express
vs. MSDE), and 2) the same tables used in that little query are used
in my big nasty query and the average time on my XP system with MSDE
is 17.433 seconds over 8 runs of the big query and on the Vista system
with Express the average time is 46.66 seconds.

Here's what I did, ran the same process on each of two machines, one
is the XP system I mention above, the other is the Vista system I
mention above. I ran the process 8 times on each system, each one
right after the other. I have incorporated a timer function in my
program that tracks how long it takes to run the process (grab the
data and pop it on screen). The time is capable of recording in
milliseconds. On the MSDE system (XP) I ran the process only 8 times
with the only index on either of the tables being the clustered
primary key index. On the Express system (Vista) I ran the process 24
times, 8 each of the following: no indexes other than the clustered
primary key index, 8 each of a clustered index on MedID (the primary
key from the table with 2,738 rows) and non-clustered indexes on
primary key and ResID, and 8 each of a clustered index on ResID, and
non-clustered on MedID and primary key. After each change of the
indexes I ran first the UPDATE STATISTICS tablewithchanges WITH
FULLSCAN and then executed SP_UPDATESTATS for that database. Each of
the three groups of 8 runs averaged about the same time, with the
lowest average of 45.7 and the highest of 46.8 seconds. Approximately
3 times longer than the time the XP and MSDE system took.

There are, perhaps, things I can do to make my process smaller or
faster, I suppose, but before I try changing what I'm doing I'd like
to understand what the difference is between MSDE and Express that is
causing the problem. My expectation from Express was that I would use
it with the databases I have currently and the same program and have
the same or better performance than what I had before. What I have is
a system that, while fast enough for the little query (one tenth of a
second is not bad) is unusable for my clients when that difference is
magnified to 45+ seconds for another. To summarize, I'm disappointed
that a process that was fast on prior version is slow on a newer
version and it makes me think, particularly in light of the info you
gave me on the AUTO_CLOSE, that there is yet something I don't know or
understand and that bugs me.

Anyway, I'm not sure there's a question in there, sorry.

Thank you, ad nauseum, for sticking with me and helping me with the
initial problem. That fix is huge to my ability to move to Vista and
Express.

--HC

[Back to original message]