Windows Vista Forums

SQL CommandTimeOut problem

  1. #1


    microsoft.public.virtualisation Guest

    SQL CommandTimeOut problem

    Hi,

    We recently Virtualised our servers. Basically we had two physical
    servers and now we have two vertual servers.

    We run a couple of Web applications on the VM server. We had
    everything running fine for a while - until we got a timeout problem
    with a search function. Basically a page doing a search call to SQL
    server and that took more than the default SQL Command Execution
    timeout which is set to 30 secs my MS.


    To resolve that problem, we set CommandTimeout property of the
    SQLCommand object as well as the
    SQLConnection object - to 0 - this makes the timeout to be unlimited.


    The code snippet below shows what we did:
    Note: that the CommandTimeout is set at both the SQLConnection and
    SQLCommand levels !!

    >>> code snippet follows ...
    aConnection = New SqlConnection
    ("Database=myDB;Trusted_Connection=True;Connection Timeout = 0;")

    mCommand = New SqlCommand(strStoredProcedureName, aConnection)
    mCommand.CommandType = CommandType.StoredProcedure
    mCommand.CommandTimeout = 0

    <<<...

    The question is: this stuffed up the Virtual Server memory. The
    connection seems to consume the memory
    really big time. From 4GB RAM down to 1GB RAM left in no time whenever
    we run our application making
    use of the above SQL Connection Statements.

    We are not sure what caused the problem - but we decided to roll-back
    to our old code and
    remove the above SQL CommandTimeout Statements and left it to the
    default 30secs.

    This seemed to run the application and RAM consumption as normal and
    happy.degradation which is of course

    We suspected the above CommandTimeout code snippet to have the caused
    the problem.

    Any changes to any other code did not cause any memory problems.

    ==>> Also - the problem was apparent only in the Virtual Servers
    network setup.
    ===> That is the application could run fine when hoseted on a local
    IIS (personal web server)
    ===> making calls to the same SQL server ...


    Will someone please tell me what exactly happened.

    Can the CommandTimeout setting affect the RAM so much?

    Please also CC me your reply to => billion.mohammed@newsgroup

    Thanks.

    Billion.

      My System SpecsSystem Spec

  2. #2


    Geoff N. Hiten Guest

    Re: SQL CommandTimeOut problem

    Sure. I can tell you what happened.

    Each "connection" to your web server creates a session. Sessions consume
    memory. Before, sessions timed out and ended. Now, Sessions just hang.

    User reconnects and gets a new session. My guess is you are running about
    three times the concurrent sessions you did before, with many of the
    sessions hung waiting on SQL.

    Solution: Find and fix the timeout, don't just mask it by setting the
    timeout to unlimited.

    --
    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP


    "microsoft.public.virtualisation" <billion.mohammed@newsgroup> wrote in
    message
    news:e7571df6-58a7-4c37-a3f7-716f1fd11f51@newsgroup

    > Hi,
    >
    > We recently Virtualised our servers. Basically we had two physical
    > servers and now we have two vertual servers.
    >
    > We run a couple of Web applications on the VM server. We had
    > everything running fine for a while - until we got a timeout problem
    > with a search function. Basically a page doing a search call to SQL
    > server and that took more than the default SQL Command Execution
    > timeout which is set to 30 secs my MS.
    >
    >
    > To resolve that problem, we set CommandTimeout property of the
    > SQLCommand object as well as the
    > SQLConnection object - to 0 - this makes the timeout to be unlimited.
    >
    >
    > The code snippet below shows what we did:
    > Note: that the CommandTimeout is set at both the SQLConnection and
    > SQLCommand levels !!
    >

    >>>> code snippet follows ...
    >
    > aConnection = New SqlConnection
    > ("Database=myDB;Trusted_Connection=True;Connection Timeout = 0;")
    >
    > mCommand = New SqlCommand(strStoredProcedureName, aConnection)
    > mCommand.CommandType = CommandType.StoredProcedure
    > mCommand.CommandTimeout = 0
    >
    > <<<...
    >
    > The question is: this stuffed up the Virtual Server memory. The
    > connection seems to consume the memory
    > really big time. From 4GB RAM down to 1GB RAM left in no time whenever
    > we run our application making
    > use of the above SQL Connection Statements.
    >
    > We are not sure what caused the problem - but we decided to roll-back
    > to our old code and
    > remove the above SQL CommandTimeout Statements and left it to the
    > default 30secs.
    >
    > This seemed to run the application and RAM consumption as normal and
    > happy.degradation which is of course
    >
    > We suspected the above CommandTimeout code snippet to have the caused
    > the problem.
    >
    > Any changes to any other code did not cause any memory problems.
    >
    > ==>> Also - the problem was apparent only in the Virtual Servers
    > network setup.
    > ===> That is the application could run fine when hoseted on a local
    > IIS (personal web server)
    > ===> making calls to the same SQL server ...
    >
    >
    > Will someone please tell me what exactly happened.
    >
    > Can the CommandTimeout setting affect the RAM so much?
    >
    > Please also CC me your reply to => billion.mohammed@newsgroup
    >
    > Thanks.
    >
    > Billion.

      My System SpecsSystem Spec


SQL CommandTimeOut problem
Similar Threads
Thread Forum
the problem with ndis.sys Blue Screen of Death problem solved? Vista General
Re: Windows Mail Attachement Problem and Adobe Player Problem with IE8 Vista mail
Generic McAfee Problem Message in Vista Problem Reports Vista performance & maintenance
audio problem with my sony vaio laptop and x64 problem Vista hardware & devices
Vista Upgrade Problem - Windows Explorer Loop problem Vista installation & setup