SQL Server Performance

Performance Tuning a Demo Server

Discussion in 'ALL SQL SERVER QUESTIONS' started by N1njaB0b, Jan 28, 2014.

  1. N1njaB0b New Member

    We have a SQL instance specifically set aside for doing sales demos. It has the same tables and structure as our production database, but the data within it has been mocked up. Since this instance is rarely used, when someone attempts to use the database (when doing a sales demo) it is sluggish, and reports often time out the first few times that you use them, likely because the cache is empty and it's reading the data from disk.
    Right now, the only advice we can give to the sales team to avoid embarrassingly slow response times and timeouts is to "exercise" the application for a while just before the demo so that the data gets cached and the SQL server instance can be more responsive. In the meantime, since a hardware upgrade is not currently an option, I would like to do what I can to improve the situation. I am working on cleaning out as much extraneous table data as possible. I'm not sure if it will help, but I'm also looking at taking some unused databases from a different instance on the same server offline, in the hopes that it will free up memory. Any other advice for how we can improve performance for this rarely used demo instance?
  2. Luis Martin Moderator

    SQL Instance for sales demos is in the same server as production?
  3. N1njaB0b New Member

    No, the dev instance and the demo instance share the same server. Production is on a separate server.
  4. Luis Martin Moderator

    Well beside maintenance plan (update statistics, etc.) my suggestion is to use some time before doing a demo.
  5. N1njaB0b New Member

    Setting some of the databases on the dev instance to auto_close seemed to help. I saw the plan cache utilization drop significantly after doing that. I know auto_close is typically a no-no, but on this dev server I feel it's appropriate.

Share This Page