SQL Server Performance

test dbase instance running slower

Discussion in 'Performance Tuning for DBAs' started by JohnCUK, Oct 25, 2006.

  1. JohnCUK New Member

    We have a live MIS dbase running in the main (primary/first) SQL instance within SQL 2000, we also have a test MIS dbase running in a second instance, created to allow the copying of LIVE data over to the TEST dbase, using backup and restore, to test MIS systems outside of the live environment.<br /><br />The client software connects to the SQL backend via ODBC data source settings in user machine control panel.<br /><br />The issue is.. the main instance/dbase is running fine, with client connectivity within 1 second of user client login, however, changing the ini settings within the client software (the front end), to point to the second DSN ODBC connection, then running the client, and logging in, results in 3 - 5 second delay, and overall performance seems much slower, and quite annoying.... [}<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I have checked all the settings of both instances, both clients, both ODBC DSN sources, all of which make sense, and are the same in detail, so I cannot figure out why the test dbase has this 'LAG'<br /><br />any ideas? any process I can go through to check for config issues I have missed?<br /><br />thanks in advance..<br /><br /><br />regards<br />John.
  2. thomas New Member

    How much memory are you allocating to each instance?

    Is there anything running on the test instance? A restore, for instance?

    Ideally you'd have the Test instance on a separate physical server. I'm not a big fan of instances.
  3. JohnCUK New Member

    Hi Thomas,

    Yeah well.. it would be great to have the 2 physical servers, however, we are running SBS2003 Premium, on 1 single server, not much redundancy, but not much in the way of budget for new equipment either... unfortunately...

    As far as memory is concerned, both instances are configured identical properties, we are running a twin processor XEON 2.GHz server, with 2GB RAM, and SATA I (1.5Gb/s) Hard drives.

    Using task manager, both instances 'currently' are utilising:
    live instance: 564,376k
    test instance: 30,044k

    I would imagine that the great variation is due to the fact that the live dbase instance is being used by 30+ users, and has been up for x days, and the test dbase instance is being used by only myself, for the same x days...

  4. thomas New Member

    Are they both set to dynamically allocate RAM?

    Of course, they will both compete for resources, and this is a key danger of running Test and Live instances together on a server, is that the Test users will hog resources and jeopardise performance for Live users.

    I suspect in this case memory is likely to be an issue (but processor and disk I/O may also be bottlenecks). Check the perfmon counter Buffer Cache Hit Ratio for each instance. If it's < 95% you're likely to be short of RAM.
  5. JohnCUK New Member

    Yes, they are both set to dynamically configured.

    The testing user (i.e. me) is using very limited resources.

    MSSQL$TESTDB:Buffer ManagerBuffer cache hit ratio
    SQLServer:Buffer ManagerBuffer cache hit ratio
    are both consistantly 99%

    It's something that has bugged me for some time, but as this is only a testing environment, I can live with it for testing purposes, still would be nice to finally find the reason for the lagging.


  6. joechang New Member

    from the information presented
    it seems more like that the prod db is warmed up, data in cache, connections open
    and the test db is cold, not data in memory, no connection memory allocated

    keep using the test db and see if it is still slow,
    generally, ignore reasonable slow startup behavior
  7. satya Moderator

Share This Page