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

• gen4ik

  SSC Journeyman

  Points: 87

  More actions

  October 9, 2007 at 3:30 am

  #182633

  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)

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737424

  More actions

  October 9, 2007 at 5:56 pm

  #740597

  You shouldn't change max worker threads.

  Do you mean 1000 people querying the server at the same time or 1000 connections? Both are possible, just depends on the server, app, queries, etc.

• gen4ik

  SSC Journeyman

  Points: 87

  More actions

  October 10, 2007 at 3:11 am

  #740673

  1000 people querying the server at the same time with the same query without any delay ,continuous ,we are talking about fthe following query "select * from master..sysprocesses"

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  October 10, 2007 at 6:50 am

  #740762

  I'm sure I've already seen this post elsewhere on the site. Why on earth would you want to do this anyway - it's also a sql2000 table not a sql2005 table.

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