Post reply

Is it posible to run 1000 active sessions in SQL server 2005?

  • October 9, 2007 at 3:32 am

    #182517

    We receive the following errors when we try running with 1000 active sessions of simple query as select * from sysprocesses :

    2007-09-25 15:46:08.72 spid220 Error: 701, Severity: 17, State: 123.

    2007-09-25 15:46:08.72 spid220 There is insufficient system memory to run this query.

    My configuration:

    Windows 2003- 8 CPU 32G RAM

    SQL SERVER 2005:

    1.Use AWE to allocate memory

    2.Maximum server memory -20G

    3.Minimum memory per query -512k

    4.Maximum worker threads -32M (maximum)

    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

  • October 9, 2007 at 5:17 am

    #740296

    the memory you allocated is used for the buffer cache

    Check other parameters of sp_configure

  • October 9, 2007 at 6:21 am

    #740317

    try using the recommended views and not sysprocesses which is a sql 2000 table/view. is this error on the server or the client

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 9, 2007 at 6:27 am

    #740321

    This is the server error

  • October 9, 2007 at 6:34 am

    #740323

    Which parameter for isntance?

  • October 9, 2007 at 6:36 am

    #740325

    and you're running the client, management studio, on the actual server?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 9, 2007 at 6:37 am

    #740327

    sorry, are all other settings out of the box ?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 9, 2007 at 7:58 am

    #740366

    For one, worker threads shouldn't be set to the max. The default of 255 will handle 1000 sessions.

    Second, as Colin asked, what else have you changed?

  • October 9, 2007 at 8:19 am

    #740380

    Look my parameters in the attached file:

  • October 9, 2007 at 2:29 pm

    #740532

    can you post the results of:

    select count(*) from sys.dm_tran_locks


    * Noel

  • October 10, 2007 at 3:05 am

    #740672

    select count(*) from sys.dm_tran_locks

    I receive 0

  • October 11, 2007 at 4:34 am

    #741124

    One other thing to remember. You are running 32-bit code, which means all your user connection memory has to live within 2GB, along with all other SQL memory apart from the buffer pool. You will eventually run out of space.

    As you have a 32 GB box, consider using 64-bit W2003 and 64-bit SQL. This will allow SQL to use far more memory for things other than the buffer pool. In a 64-bit environment on a 32 GB server you should be able to run 1000 connections OK, and with some tuning run them very efficiently.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • October 11, 2007 at 6:16 pm

    #741414

    Hi,

    make sure both the config_value and run_value for the max server memory are same.. , seems like the config_value is set to only 6.4 gb while run_value is 20..

  • March 19, 2008 at 1:17 pm

    #791680

    I agree with Steve. You are tying up a lot of memory in worker threads. Lack of worker threads is not your problem and the number you have set is a theoretical maximum which is unrealistic in any scenario.

  • March 19, 2008 at 7:38 pm

    #791836

    YOu said AWE is enabled. In the boot.ini, /PEA and /3GB? Has anyone added anything to the /USERVA switch in boot.ini?

    DAB

Viewing 15 posts - 1 through 14 (of 14 total)

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