SQL Server Performance

Keep Alive Setting?

Discussion in 'General DBA Questions' started by andysk, May 28, 2007.

  1. andysk New Member

    Hi,

    In SQL Server 2000, is that any option to set keep session/connection alive for at least 2 hours (without any activities from/to the server)? Or the setting on Windows server?

    I have tried to search on the forum for this similar topic, but it keeps give me a timed out error.

    Thanks in advanced.

    Regards,
    Andy
  2. satya Moderator

    I think it is on windows settings, based on the protocol settings.
    Is clustering involved?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. andysk New Member

    Hi Satya, sorry i don't get your meaning. can you be more specific?
    I have the problem with the user that usually leave the application open to do some are manual task, and then after they back to the application (around 15 minutes - 2 hours later), the application has been terminated from server.

    Regards,
    Andy
  4. satya Moderator

    KeepAlive setting is related to the protocol used to connect the server.

    If you have any specific terminate mode from the application then it will be disconnected, as SQL will not disconnect any connect unless the service is restarted or mentiond explicitly with KILL statement.

    DO you have any problem with this client termination?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. andysk New Member

    Satya, Actually it happened in almost all client. So how do i troubleshoot this? What setting that i should modify in SQL Server and Windows Server ? I try to avoid the setting on client, since it will consume a lot of job.

    Regards,
    Andy
  6. satya Moderator

    What kind of application you are using, is it a thirdparty or in-house developed?
    Do you have explicit terminate connection statement within your application once the process is completed?q

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. andysk New Member

    It is a in-house developed application.
    In my application, there is a global connection (suppose to always connected to the server), and one or two more connections on entry form/screen.
    For global connection, will only close whenever user quit from application
    For each entry form/screen, connection(s) will close whenever user quit from entry form/screen.

    There is no other terminate connection except those stated above, so i suspect of keep alive setting or something else.
  8. MohammedU New Member

    What is the language used for developing the application?
    There is no setting in sql server and it is in the frond end application check with application developers...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  9. satya Moderator

    Thanks, monitor & check the number of connections against this database from that application using SP_WHO2. If you don't have any explicit terminate method then it should be alive on SQL SErver to process the tasks.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. andysk New Member

    Mohammed, Our front end application was built using Ms. Visual Foxpro and use ODBC connection to SQL Server.

    Satya, sometimes terminated connection will still in the list of active connections (SP_WHO2), eventhough the connection is already terminated. Based on the experience of some users, they will get the connection terminated whenever they let application open without activities around > 10 minutes, so now some users will logout from application if they should go to do something else (nothing to do with the application).

    If my guess was right, this should be something to do with Connection Timeout setting on SQL Server or KeepAlive setting on Windows Server 2003.
  11. MohammedU New Member

    Connection timeout is when some one try to connect sql server it will try until specified time then it will timeout...
    Where as yours is connection closing which is alread esblished successfully...

    When you run sp_who2 it will return all connections where as if pass the parameter 'active' then it will return only active connections...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. andysk New Member

    quote:Originally posted by MohammedU

    Connection timeout is when some one try to connect sql server it will try until specified time then it will timeout...
    Where as yours is connection closing which is alread esblished successfully...

    When you run sp_who2 it will return all connections where as if pass the parameter 'active' then it will return only active connections...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    It's something that after succesfully connected and don't have any activities for certain period, and the server will automatically kick you out, i don't know what i should call it.
  13. satya Moderator

    SQL Server will not terminate connection explicitly unless you issue a KILL statement, if it terminating connection then you have to use PROFILER to see what is happening in this regard.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  14. andysk New Member

    Hi, just let you know. Finally, I have found the answer for my questions on :
    -http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21474
    -http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21476
    -http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21795

    After i downgraded Windows 2003 R2 to Windows 2000 Advanced Server, all my problem on above threads are solved.

    Windows 2003 R2 detected Dual-Core processor as 4 processors, While Windows 2000 Advanced Server detected as 2 processors. Something wrong with Windows 2003 R2?

    Thanks a lot for all of your help.

    Andy
  15. MohammedU New Member

    Was HiperThreading enabled on the server?
    It could be HyperThreading issue...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  16. andysk New Member

    Hi Mohammed, how do i check whether HyperThreading enabled or not.

    Regards,
    Andy
  17. MohammedU New Member

    You can check when you restart the computer in the BIOS...

    In 2005 you can use DMV but not in 2000...

    YOu can use the following if it is HP server...

    -- HKEY_LOCAL_MACHINESYSTEMCompaqServerStd_EquipComponentCpu

    set nocount on
    -- create table #CpuNum (id int identity, name sysname)
    create table #Cpu (name sysname)
    declare @id int, @cpu Varchar(6), @CpuCnt int , @Lcpu int, @Pcpu int
    select @id = 0, @cpu = 'Cpu'
    while @id <=100
    begin
    select @cpu = @cpu + case when len(@id) = 1 then '00'+convert(Varchar,@id)
    when len(@id) = 2 then '0' +convert(Varchar,@id)
    when len(@id) = 3 then +convert(Varchar,@id)
    end
    -- insert into #CpuNum (name) select @cpu
    insert into #Cpu
    EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
    @key='SYSTEMCompaqServerStd_EquipComponentcpu',
    @value_name = @cpu

    select @id = @id + 1, @cpu = 'Cpu'
    end
    select @Pcpu = count(*) from #Cpu

    create table #LCpuCnt
    (
    [Index] varchar(5),
    [Name] varchar(20),
    Internal_Value varchar(10),
    Character_Value varchar(120)
    )

    insert into #LCpuCnt exec (' master.dbo.xp_msver ''ProcessorCount''')

    select @Lcpu = Internal_Value from #LCpuCnt

    if @Lcpu = @Pcpu select 'HT is not enabled'
    if @Lcpu > @Pcpu select 'HT is enabled...' + 'Actual CPU countis :'+convert(Varchar,@Pcpu)+ '....Logical CPU count is:' +convert(Varchar,@Lcpu)

    drop table #Cpu, #LCpuCnt


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  18. satya Moderator

  19. andysk New Member

    Mohammed,

    The result from your code is :
    HT is enabled...Actual CPU countis :1....Logical CPU count is:2 (Windows 2000 Advanced Server + SQL Server 2000, which is the right one)

    Why Windows 20003 detected as 4 processors? Or how do i turnoff HT?

    Regards,
    Andy

Share This Page