locked
Server with large number of databases RRS feed

  • Question

  • I support a SQL 2005 server with 500+ databases.  Only approximately 100+ of those are open/active at a given time.  The server is dedicated to one application. We are experiencing performance issues with this server. This article...

    "There may not be enough virtual memory when you have a large number of databases in SQL Server" (http://support.microsoft.com/kb/316749/EN-US)

    was originally about SQL Server 7.0 and 2000, but there is an update at the end which indicates the issue pertains to SQL 2005 as well.

    The example is 500 DBs.  Does anyone know if the issue pertains to the number of databases created on the instance, or the number in use?  It's probably the number in use, but I just want to make sure.

    Also, do you know if the issue pertains to SQL Server 2008r2 as well?

    Thanks!

    Monday, April 9, 2012 2:20 AM

Answers

  • Hi,

    Every database on server consumes certain amount of system resources ( CPU, Mem, Disk ) whether being active or inactive.

    You can check , test & implement below if it goes with your environment.

    1. If your application supports 64 bit, switching to 64 bit environment would be helpful.

    2. if you had more than 4 GB of RAM , configure AWE for sql server to utilize more memory.

    3. Configure min & max memory setting appropriately from your application.

    4. Keep a check on number of concurrent user connections. Ask user to close their connections once they are done with their activity since every open connection to database whether that session is being used or not consumes system resource.

    Hope this helps.


    Regards, Vishal Srivastava

    • Proposed as answer by Peja Tao Tuesday, April 10, 2012 5:31 AM
    • Marked as answer by Peja Tao Monday, April 16, 2012 1:46 AM
    Monday, April 9, 2012 7:02 AM
  • @District9

    In addition, please 'Enable the Lock Pages in Memory Option' in Windows server after using the following performance counter to expose the memory that the buffer pool allocates:

    • Performance object: SQL Server:Memory Manager
    • Counter: Total Server Memory (KB)

    You could also refer to this article.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Peja Tao Monday, April 16, 2012 1:46 AM
    Tuesday, April 10, 2012 5:55 AM

All replies

  • Hi,

    Is this a 32 bit system? When a database is created and online even if it does not have active connections SQL allocates memory for each database and in SQL 2005 and SQL 2008 more memory is required per database as SQL stores more metadata information per database such as information in the DMV's. Therefore if anything the later versions of SQL will be worst in 32 bit systems. This is again one of the reasons why you should try to move all your SQL instances to 64 bit.

    This code snippet which was originally posted by Paul Randal will show you how many pages are being stored in the buffer pool per database to give you an idea of what is happening per database.

    use master
    go
    SELECT(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) 
    AS 'Page State',(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' 
    ELSE DB_NAME (database_id) END) AS 'Database Name',COUNT (*) 
    AS 'Page Count'FROM sys.dm_os_buffer_descriptors
    GROUP BY [database_id], [is_modified]
    ORDER BY [database_id], [is_modified];
    GO

    Have you attempted to test and implement any of the recommendations in the KB article such as the startup parameter?


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Monday, April 9, 2012 4:41 AM
  • What does the below return?

    1.Analyze waits at the instance level.
    2.Correlate waits with queues.
    3.Determine a course of action.
    4.Drill down to the database/file level.
    5.Drill down to the process level.
    6.Tune indexes/queries.

    WITH Waits AS
    (
      SELECT
        wait_type,
        wait_time_ms  /1000. AS wait_time_s, 
             100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
             ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
          FROM sys.dm_os_wait_stats
          WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
    ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
    ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
    ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
    ,'XE_DISPATCHER_WAIT','SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'XE_DISPATCHER_JOIN')
    )
          --filter out additional irrelevant waits

    SELECT
      W1.wait_type,
      CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12,2))AS pct,
      CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
    FROM Waits AS W1
      JOIN Waits AS W2
        ON W2.rn<= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
    ORDER BY W1.rn;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, April 9, 2012 5:08 AM
    Answerer
  • Hi,

    Every database on server consumes certain amount of system resources ( CPU, Mem, Disk ) whether being active or inactive.

    You can check , test & implement below if it goes with your environment.

    1. If your application supports 64 bit, switching to 64 bit environment would be helpful.

    2. if you had more than 4 GB of RAM , configure AWE for sql server to utilize more memory.

    3. Configure min & max memory setting appropriately from your application.

    4. Keep a check on number of concurrent user connections. Ask user to close their connections once they are done with their activity since every open connection to database whether that session is being used or not consumes system resource.

    Hope this helps.


    Regards, Vishal Srivastava

    • Proposed as answer by Peja Tao Tuesday, April 10, 2012 5:31 AM
    • Marked as answer by Peja Tao Monday, April 16, 2012 1:46 AM
    Monday, April 9, 2012 7:02 AM
  • @District9

    In addition, please 'Enable the Lock Pages in Memory Option' in Windows server after using the following performance counter to expose the memory that the buffer pool allocates:

    • Performance object: SQL Server:Memory Manager
    • Counter: Total Server Memory (KB)

    You could also refer to this article.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Peja Tao Monday, April 16, 2012 1:46 AM
    Tuesday, April 10, 2012 5:55 AM