Re: Locking ASP.NET Session State with SQL Server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

SQL Server session state is retrieving and saving varbinary/image
data types. This ain't pretty especially with that many page views.
Go watch it work with SQL Profiler turned on.

Believe, ScaleOut is going to drastically improve the performance
of an app like yours.

--
Robbe Morris
EggHeadCafe.com
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp



"george" <george@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:FF220F40-50D3-420D-8A0C-F885950D3CF0@xxxxxxxxxxxxxxxx
Hi Robbe

Thanks for the reponse, looks like a good solution

First I would like to know if anybody else has experienced this problem with
ASP.NET and sqlServer and if there is a solution?

We have 8-10 million page views per day with each of these page views
accessing the state server.
We are not accessing anything comples, just string and integer values.

It seems that the sql server solutions is not very scalable or reliable.

Has anybody else experienced this problem?

Thanks!

"Robbe Morris - MVP C#" wrote:

I steadfastly refused to rely on sql server for managing state.
If your firm can spell out some cash, this is a wonderful
alternative and will likely drastically speed up your
web farm application. They've got great customer service
and their product is quite reliable.

http://www.eggheadcafe.com/articles/scaleout_server.asp

--
Robbe Morris
EggHeadCafe.com
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp



"george" <george@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EE7A45BF-DB93-492F-9F45-3F1F76597D48@xxxxxxxxxxxxxxxx
> We are running a website in a webfarm and using SQL server for session
> state.
>
> The website runs perfectly but after a period of time we get the > following
> error:
>
> System.Web.HttpException: Unable to connect to SQL Server session
> database.
> ---> System.InvalidOperationException: Timeout expired. The timeout > period
> elapsed prior to obtaining a connection from the pool. This may have
> occurred
> because all pooled connections were in use and max pool size was > reached.
> at
> System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
> options, Boolean& isInTransaction) at
> System.Data.SqlClient.SqlConnection.Open() at
> System.Web.SessionState.SqlStateConnection..ctor(String
> sqlconnectionstring)
> --- End of inner exception stack trace --- at
> System.Web.SessionState.SqlStateConnection..ctor(String
> sqlconnectionstring)
> at System.Web.SessionState.SqlStateClientManager.GetConnection(Boolean&
> usePooling) at
> System.Web.SessionState.SqlStateClientManager.GetExclusive(String id) > at
> System.Web.SessionState.SqlStateClientManager.System.Web.SessionState.IStateClientManager.BeginGetExclusive(String
> id, AsyncCallback cb, Object state) at
> System.Web.SessionState.SessionStateModule.GetSessionStateItem() at
> System.Web.SessionState.SessionStateModule.BeginAcquireState(Object
> source,
> EventArgs e, AsyncCallback cb, Object extraData) at
> System.Web.AsyncEventExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
> at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> completedSynchronously)
>
>
> If I monitor the SQL Server/General Stats/User Connections performance
> counters, they show the user connections to be steady at 230 with no
> leaks.
>
> Then, after a period of time (say about 20 minutes) the number of
> connections suddenly increase to 600-800 and the error occurs because > all
> connections are suddenly exhausted.
>
> After a couple of seconds the connections automatically recover, the > error
> stops and the number of connections start to lover back to 230 and stay
> steady.
>
> After about 10-20 minutes it happens again.
>
> I though it may be a scheduled jobs, but I can not produce the error > when
> running any of the jobs.
>
>
> I ran a trace for long running queries using the SQL Profiler and at > the
> exact time the problem happen (ie. when the connections are suddenly
> exhausted) I get the following query repeated over and over:
>
> declare @P1 varbinary(7000)
> set
>
@P1=0x140000000001000A000000FFFFFFFF0D6D656D6265724163636F756E74140001000000FFFFFFFF01000000000000000C02000000446D616E6A616D2C2056657273696F6E3D312E302E323732322E33383936352C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C0501000000236D616E6A616D2E636C6173735F53657373696F6E2B734D656D6265724163636F756E7406000000056D627249440D7072656D69756D4D656D6265720E737570706F72744163636F756E74086C616E67756167650D6E617653756253656374696F6E106E617653756253656374696F6E55524C0000000101010801010200000007B7020000000A0A0A0B0B6D62724E69636B6E616D6501066D616B73696D1073656C65637465644C6F636174696F6E140001000000FFFFFFFF01000000000000000C02000000446D616E6A616D2C2056657273696F6E3D312E302E323732322E33383936352C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C05010000001E6D616E6A616D2E636C6173735F53657373696F6E2B734C6F636174696F6E050000000C61726561526567696F6E49440D61726561436F756E74727949440E6172656150726F76696E636549440A617265614369747949440C61726561537562757262494400000000000808080808020000000A000000A400000092020000FFFFFFFFFFFFFFFF0B0A6D794C616E67756167650102656E136D794C616E6775616765446972656374696F6E01036C7472106D657373656E67657253657276696365140001000000FFFFFFFF01000000000000000C02000000446D616E6A616D2C2056657273696F6E3D312E302E323732322E33383936352C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C0501000000266D616E6A616D2E636C6173735F53657373696F6E2B734D657373656E676572536572766963650500000013646174654C6173744163636573735469636B730B6578706972795469636B730F6E65774D657373616765436F756E74116F6E6C696E65467269656E64436F756E74146F6E6C696E654661766F7572697465436F756E74000000000009090808080200000071A2D1841E7EC90859FC91421E7EC9080000000000000000020000000B10616C50726F66696C6573566965776564140001000000FFFFFFFF010000000000000004010000001C53797374656D2E436F6C6C656374696F6E732E41727261794C69737403000000065F6974656D73055F73697A65085F76657273696F6E05000008080902000000130000001300000010020000002000000008082740030008088F53040008089E5204000808B549040008086D53040008087D4C040008082E4B040008081549040008085E3C04000808F61A01000808346803000808FA6402000808CAA40300080881A803000808E375000008080D3604000808AE2903000808F4920300080849CA02000D0D0B0F696E744D6573736167657353656E74020800000014646174654D65737361676553656E745469636B730CF965613B1E7EC90813706C61795F7365617263684372697465726961140001000000FFFFFFFF01000000000000000C02000000446D616E6A616D2C2056657273696F6E3D312E302E323732322E33383936352C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6E756C6C0501000000296D616E6A616D2E636C6173735F53657373696F6E2B73506C61795F53656172636843726974657269610D00000010646973706C6179417347616C6C6572790867656E64657249440561676549440A626F647954797065494406726F6C6549440B74726176656C4D6F6E74680A74726176656C59656172126661766F7572697465436F6D6D616E6449440F667269656E64436F6D6D616E64494410686973746F7279436F6D6D616E644944086E69636B6E616D650A6F6E6C794F6E6C696E65096F6E6C7950686F746F000000000000000101010100000108080808080801010200000000FFFFFFFFFFFFFFFFFFFFFFFF0400000006000000D70700000603000000087669657753656C6606040000000731646567726565090300000006060000000000010BFF
> declare @P2 bit
> set @P2=0
> declare @P3 int
> set @P3=0
> declare @P4 int
> set @P4=112
> exec dbo.TempGetStateItemExclusive2 @id =
> 'vo0eqk45vmwmfnj04kd4np554d1bef26', @itemShort = @P1 output, @locked = > @P2
> output, @lockAge = @P3 output, @lockCookie = @P4 output
> select @P1, @P2, @P3, @P4
>
>
> The SQL Profiler reports that there are no other queries that are > taking
> 5000ms or more to execute during this time.
>
> When the sql server recovers and the connections are realeased, the SQL
> Profiler reports that there are once again no queries taking 5000ms or
> more
> to execute.
>
>
> I googled for the Stored Procuedure "TempGetStateItemExclusive2" and > found
> someone reporting that ASP.NET session state can lockup under heavey > load
> and
> they pointed to the following MS Hot Fix:
>
> http://support.microsoft.com/kb/843400/en-us
>
>
> Will this hot fix work, how do I download it?



.

Relevant Pages

  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... connections need to be returned to the pool to be ... Enterprise version of SQL Server 2000. ... server user login to be sure that it is not mixed with other running clients). ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I think it will ameliorate the situation if you clean up the client as ... And to come back to my problem: I think with help of the SQL Server admins ... closed connections - but all of these errors are in the version which used ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... And to come back to my problem: I think with help of the SQL Server admins ... closed connections - but all of these errors are in the version which used ... communication between ODBC (OLEDB and Native Client, ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL server connection problem
    ... I've tried to change the user connections to 0, ... "SQL server connection time out. ... The following message appears when restart the server and before login ... I receive Error message: “A connection could not be established to ...
    (microsoft.public.sqlserver.clients)