MSSQL7 and WinXP

Hi!
We're closer to a solution to our problem with slow machines on a MSDE
database (two weeks ago).
We now have one standalone machine that runs MSSQL7, SP4 (SP4 was
installed after it did not work without it).
The computer has been freshly set up with WinXPPro, MSSQL7 and our
application. All current Windowsupdates applied afterwards.
OK, what we have now:
1) Extremely quick resutls when using osql
2) Extremely slow startup time of our application. (Delphi, using ADO as
connection)
The reason for 2) is (or rather one effect of it) can be seen in the error
log:
--8<--
2004-06-17 15:14:19.43 spid5 Startet Datenbank 'Patient'.
2004-06-17 15:14:19.43 spid5 Opening file C:\MSSQL7\data\Patient.mdf.
2004-06-17 15:14:19.43 spid5 Opening file
C:\MSSQL7\data\Patient_log.LDF.
2004-06-17 15:14:20.01 spid14 Closing file C:\MSSQL7\data\Stammdaten.mdf.
2004-06-17 15:14:20.01 spid14 Closing file
C:\MSSQL7\data\Stammdaten_log.LDF.
--8<--
Sections like this one are repeated *quite* often, read: immediately
afterwards. Several times. For approx.10 seconds per database. (which
makes about 10-20 retries).
We then noticed that this machine has hyperthreading enabled, which we
disabled. The entries in the log file were reduced to a more sensible
amount, still it happens more often than on other machines with our
software and MSSQL7 or MSDE.
Question: What else can we do? Is there something with respect to
hyperthreading that can still interfere? Have others also seen multiple
repeating entries in the log files and have a clue as to where they come
from?
Thanks for reading,
Stefan
See the BOL for information concerning the AUTO_CLOSE option. It appears to
me that you have that enabled (the default for MSDE) and the database server
is closing the database after all connected clients are disconnected.
Jim
"Stefan M. Huber" <looseleaf@.gmx.net> wrote in message
news:opr9qpvxj0s9ddfw@.news.individual.de...
> Hi!
> We're closer to a solution to our problem with slow machines on a MSDE
> database (two weeks ago).
> We now have one standalone machine that runs MSSQL7, SP4 (SP4 was
> installed after it did not work without it).
> The computer has been freshly set up with WinXPPro, MSSQL7 and our
> application. All current Windowsupdates applied afterwards.
> OK, what we have now:
> 1) Extremely quick resutls when using osql
> 2) Extremely slow startup time of our application. (Delphi, using ADO as
> connection)
> The reason for 2) is (or rather one effect of it) can be seen in the error
> log:
> --8<--
> 2004-06-17 15:14:19.43 spid5 Startet Datenbank 'Patient'.
> 2004-06-17 15:14:19.43 spid5 Opening file C:\MSSQL7\data\Patient.mdf.
> 2004-06-17 15:14:19.43 spid5 Opening file
> C:\MSSQL7\data\Patient_log.LDF.
> 2004-06-17 15:14:20.01 spid14 Closing file
C:\MSSQL7\data\Stammdaten.mdf.
> 2004-06-17 15:14:20.01 spid14 Closing file
> C:\MSSQL7\data\Stammdaten_log.LDF.
> --8<--
> Sections like this one are repeated *quite* often, read: immediately
> afterwards. Several times. For approx.10 seconds per database. (which
> makes about 10-20 retries).
> We then noticed that this machine has hyperthreading enabled, which we
> disabled. The entries in the log file were reduced to a more sensible
> amount, still it happens more often than on other machines with our
> software and MSSQL7 or MSDE.
> Question: What else can we do? Is there something with respect to
> hyperthreading that can still interfere? Have others also seen multiple
> repeating entries in the log files and have a clue as to where they come
> from?
> Thanks for reading,
> Stefan
>
|||On Fri, 18 Jun 2004 09:44:47 -0700, Jim Young <thorium48@.hotmail.com>
wrote:

> See the BOL for information concerning the AUTO_CLOSE option. It appears
> to me that you have that enabled (the default for MSDE) and the database
> server is closing the database after all connected clients are
> disconnected.
Thanks for the pointer. However, I wonder, why this happens when only one
single client wants to connect at startup. I'll check that next time I am
at work. Thanks.
Stefan
"Mobile launchers are more difficult to detect because they move
around, unlike fixed launchers."
-- Katie Couric, NBC News

MSSQL Slow Queries on One Database only all others fine.

Hello Everyone,

I have a strange problem that I cannot seem to solve. I have two server running Windows 2003 MSSQL on one IIS on the other.

Out of the blue I have slow queries that cause asp script timeouts and I cannot figure it out. I have moved some test code to other sites on the server and still have these long delays.

I have tried dropping the tables, then the database entirely and restoring and still slow. I run the same code on my test server at home running both iis and sql on the same server and it is a little slow but not too bad and the test server is a duron 1.6.

Here it is:
Code: ( text )

<!-- #include file="adovbs.inc" --> <% response.write("<table width=100% border=0>") response.write("<tr><td align=left>") Set dbconn = Server.CreateObject("ADODB.Connection") dbconn.Open database_three dbconn.CommandTimeout = 0 Set group_master = Server.CreateObject("ADODB.Recordset") Set section_master = Server.CreateObject("ADODB.Recordset") Set document_master = Server.CreateObject("ADODB.Recordset") group_master.open "SELECT * FROM group_master",dbconn,adOpenForwardOnly,adLockReadOnly Do while not group_master.EOF group_number = clng(group_master("group_number")) group_title = group_master("group_title") group_description = group_master("group_description") group_stat = group_master("group_status") response.write("<table width=100% border=0>") If group_stat = "A" Then response.write("<tr><td align=left colspan=3 CLASS=activegroup>" & group_title & "</td></tr>") If group_stat = "O" Then response.write("<tr><td align=left colspan=3 CLASS=obsoletegroup>" & group_title & "</td></tr>") response.write("<tr><td align=justify colspan=3 CLASS=groupdescription>" & group_description & "</td></tr>") section_master.open "SELECT * FROM section_master where group_number=" & group_number,dbconn,adOpenForwardOnly,adLockReadOn ly If not section_master.EOF Then Do while not section_master.EOF sec_no = clng(section_master("section_number")) If section_master("section_status") = "A" Then response.write("<tr><td></td><td align=left colspan=2 class=activesection><font color=black><strong>" & x & ".</strong></font> " & section_master("section_title") & "</td></tr>") If section_master("section_status") = "O" Then response.write("<tr><td></td><td align=left colspan=2 class=obsoletesection><font color=black><strong>" & x & ".</strong></font> " & section_master("section_title") & "</td></tr>") response.write("<tr><td></td><td align=justify colspan=2 class=sectiondescription><font color=white><strong>" & x & ".</strong></font> " & section_master("section_description") & "</td></tr>") document_master.open "SELECT * FROM document_master where group_number=" & group_number & " and section_number=" & sec_no & " ORDER BY document_seq",dbconn,adOpenForwardOnly,adLockReadOnly If not document_master.EOF Then Do while not document_master.EOF If document_master("document_status") = "A" Then response.write("<tr><td></td><td></td><td align=left CLASS=activedocument><font color=black><strong>" & formatnumber(document_master("document_seq"),2) & "</strong></font> " & document_master("document_title") & "</td></tr>") If document_master("document_status") = "O" Then response.write("<tr><td></td><td></td><td align=left CLASS=obsoletedocument><font color=black><strong>" & formatnumber(document_master("document_seq"),2) & "</strong></font> " & document_master("document_title") & "</td></tr>") response.write("<tr><td></td><td></td><td align=left CLASS=documentdescirption><font color=white><strong>" & formatnumber(document_master("document_seq"),2) & "</strong></font> " & document_master("document_details") & "</td></tr>") document_master.MoveNext Loop document_master.Close Else document_master.close End If ' Make sure there are documents to loop thru. section_master.MoveNext Loop section_master.Close Else section_master.Close End If ' Make sure there was sections to look at. group_master.MoveNext Loopresponse.write("</td></tr></table></td></tr></table>") dbconn.Close Set dbconn = Nothing Set group_master = Nothing Set section_master = Nothing Set document_master = Nothing %>

Keep in mind nothing about my code has changed so it must be something on the server like maybe a security setting or routing issue, I just can't figure out what could have MAGICALLY changed. I am not the only admin on the servers but everyone says they changed nothing. Any help would be GREATLY appreciated.Is it possible that you just have more data then before?
Do you have indexes on your tables?|||Yeah I do have index's. I also ran the optimizer.

The data has not changed, it is only updated as we add documents and none have been added for a couple of months.

Its not really that much data.