Post reply

Slow connection.

  • August 23, 2004 at 11:56 am

    #161412

    Hi all,

           i'm new to SQL server. The version we use is SQL Server 2000 on a Win2K machine. I am experiencing a slow start up when we open a connection for the first time or its slow if it is not used for a while. I myself have set up a datasource for the Access MDB's i've created using ODBC and we had a contractor here to help set up our website which uses JDBC and ODBC as well I believe, and access for the first time is painfully slow. As for the Access DB's, sometimes I cannot get onto them at all. That said however there is a DB that the Admin team use for their work and they experience no problems at all, but it is in constant use. Aparantly it uses the same SQL server as the Datasources I created but unfortunatly I was not at the Co when these were set up so I cannot say for certain how this was done. As a newbie where would I check to see why its slow to get access to the datasource for the web-connection and the Datasources for Access applications ?

     

    Thanks in advance,

    Mitch...

  • August 24, 2004 at 7:15 am

    #520314

    Your database doesn't by any chance have Autoclose set to 'Yes', does it?

  • August 24, 2004 at 10:04 am

    #520351

    Good question, I have no idea. Where would I look to find this out ? Is this a property I need to change/toggle for each DB in SQL Server ?

     

    Thanks for your reply,

    Mitch....

  • August 24, 2004 at 11:13 am

    #520371

    Yes, this is a per-database setting. In Enterprise Manager, right-click the database, and select properties. Select the Options tab, and you'll find the Auto close option from there. I believe you can also monitor this through the SQL server error log; it will show when the db is being closed and opened. If you see this happening regularly, you can pretty much assume this option has been enabled. Except for the most constrained servers, this is not a good option to turn on.

    --Mitch

  • August 24, 2004 at 11:19 am

    #520374

    In EM:

    Server->Database Name->Right Click on Properties->Options. Make sure Auto Close is unchecked.

    In QA: Select DATABASEPROPERTYEX ('Pubs','IsAutoClose')

    1-stand for Property Enabled

    0-Check Off-Property Disabled

     

  • August 24, 2004 at 11:20 am

    #520375

    Oh yeah; you can also run

    SELECT DATABASEPOPERTYEX('dbname', 'IsAutoClose')

    against the database in question, and it'll return 0 for NO, and 1 for YES.

    To change this option, you can use Enterprise Manager (as described in my previous post), or you can run

    SP_DBOPTION @dbname='dbname', @optname='autoclose', @optvalue='false'

    According to Books Online, this option is automatically turned off for all SQL Server Editions, except for MSDE.

    -- Mitch

    --Mitch

  • August 24, 2004 at 11:34 am

    #520377

    Thanks guys... having looked the Auto close option is unchecked so...

    Is there anything else I should look for ?

     

    Mitch..........

  • August 24, 2004 at 6:29 pm

    #520435

    IS your SQL Server starved of memory? or is it starving the OS of memory?

    In Enterprise Manager, right click your server, choose properties, and hit the Memory tab. Keep the sliders in the Green areas. If you have IIS or another application on the server as well, you may want to set SQL Server to use even less memory. Otherwise everything else gets pushed out to the windows page file on disk. You can also hit the "Reserver Physical Memory for SQL Server button".

    Next to check is how long does it take YOU to connect with Query Analyzer? Open it up and login. It should be instantanious. Do a Select top 1 * from sometable to see if sql server is slow at accessing the datafiles. Again that should be instantaneous.

    Hint: You should have at least 1GB ram on the server, and be running on a server with SCSI disks and RAID.

    Julian Kuiters
    juliankuiters.id.au

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply