Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34

    Unanswered: Is it feasible to restart mssql server 2008 service periodically???

    Hello all,

    Is it feasible to restart mssql server 2008 service periodically every 10min???
    what will be the effect on database, If it is heavy loaded environment.
    Please suggest your views.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Feasible implies some sort of practicality. I would only rate such a scheme as "possible" in the same way it is "possible" for you to only walk on your hands. The sheer number of support problems this will generate for the applications will be enormous.

    I do have to ask, what made this plan sound good? What was the alternative?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What on earth for?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't think of any reason that would cause me to deliberately restart a SQL Server every ten minutes.

    What problem are you attempting to solve? There has to be a better way.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34
    We are in the process of fine tunning the database server, I came across scheduller, that restarting sql server service every 10min, I have disable scheduller.

    Client said, we suppose to do this for discarding sleeping state connections.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You were definitely correct to remove the scheduler task to restart SQL Server every 10 minutes. That was the correct thing to do.

    Why is there a concern with sleeping spids? These consume around 128 Kb of memory, but otherwise cause the server no problems. I don't understand why the client wanted to manage these connections.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34
    Thank you pat. & all for your valuable comments.

    What should be the possible areas to fine tune MS-SQL database?

    We are currently working on physical file fragmentation.

    We performed following:
    - Index rebuilding by which remove almost fragmentation.
    - Index rebuilding and reorganization job scheduler. (rebuilding once in month, reorganization once in week)
    - Instance property parameter:
    - Server memory Option. (min memory:0 max memory 30GB) total RAM of sym: 32GB
    - Other momory Option. (Index creation memory: dynamic, minimum memory per query:1024)
    - @Processors: Unchecked Boost SQL server priority.
    - In database property -> options -> auto create statistic: false
    auto shrink: false
    auto update statistics: false
    In Netrok:
    - Regedit -> local machine -> system -> current control set -> services ->
    TCPIP -> service provider
    1st priority : local 5
    2nd priority : Host 6
    3rd priority : DNS 7
    4th priority : netbt priority - 8

    - sysEdit -> In notepad:
    IRQ18=4096 ( Netowrk addapter name )
    (NOTE: Before going into production database servers, get proper understanding on testing servers.)

    - Run profiller and apply all recommended settings.
    - We found top 20 queries and as per recommendation, Indexes created.

    @OS: page vertual memory increased. min 1000 and maximum 30000.

    Valuable comments will be appreciated.

    Thank you. Have a nice time.

  8. #8
    Join Date
    Aug 2008
    Posts
    147
    Focus on identifying the bottlenecks and alleviating the pressure.
    1.Analyse waits at the instance level.
    SQL 2005\2008\2012 use sys.dm_os_wait_stats ; SQL 2000 use DBCC SQLPERF(WAITSTATS) Focus on I/O, although there are other issues
    2.Match waits with queries
    use System monitor - e.g if most waits were I/O based use the different I/O queues
    3.Determine course of action
    What is the cause of the problem?
    4.SQL Server Instance/Database Configuration & Optimization
    5.Design your db for performance
    6.Set up an appropriate maintenance plan for data
    7.Poor indexes and queries are not the only cause of performance problems.
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  9. #9
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34

    Duplicate indexes found.

    Thank you JackVamvas,

    I am taking your suggestions forward.

    1.Analyse waits at the instance level.

    This is top 10 records o sys.dm_os_wait_stats tables:

    wait_type waiting_tasks_count wait_time_ms
    CXPACKET 7074 4177665
    PAGEIOLATCH_SH 286879 2548541
    LAZYWRITER_SLEEP 5800 394982
    FT_IFTS_SCHEDULER_IDLE_WAIT 11 354620
    LCK_M_S 17 248090
    REQUEST_FOR_DEADLOCK_SEARCH 43 205649
    LOGMGR_QUEUE 570 205041
    SQLTRACE_INCREMENTAL_FLUSH_SLEEP 52 204593
    CHECKPOINT_QUEUE 32 188417
    FT_IFTSHC_MUTEX 8 183318

    I went through web about wait_type, but confuse what to do with this. At least give me idea.
    (http://www.sqlskills.com/blogs/paul/...here-it-hurts/)

    4.SQL Server Instance/Database Configuration & Optimization.
    ==>> Please, can you be more specific about above point.

    6.Set up an appropriate maintenance plan for data.
    ==>> Which maintenace plan you are talking about, i have created rebuilding and reorganizing indexes + db backup.

    7.Poor indexes and queries are not the only cause of performance problems.
    ==>> Agree.

    It's great opportunity for me to learn something from you guys.
    Have a nice time.
    Last edited by jignesh_foryou; 01-30-13 at 07:52.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •