SQL Server Performance

Optimise Statistics and Indexes daily

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sekharabcs, Oct 23, 2009.

  1. sekharabcs New Member

    Hi All,
    We have 2 jobs running in production sql server daily after business - Optimize Statistics and Optimize Indexes. Is it the best practise to have these jobs run daily?
    I have taken the Index Defragmentation stats and got around 16 indexes which are having AvgPageFragmentation >60 and page count less than 10. Among these I have only one index which has AvgPageFragmentation > 80 and Pagecount > 5000.
    Are the above values feasible for my SQL Server? The reason for my doubt is our front end application runs very slow. So, I am looking into the possibilities.
    One of the reason is IIS, SQL Server, Reporting Services are running on single server and its configuration is Xeon 2.33 GHz CPU and 4 GB Ram. I think this could be one of the reason for the application to be slow. We are planning to purchase a new hardware to migrate SQL Server alone to a new server. In such case, I may come back to you guys for specifications for new server for SQL server.
    Thanks,
    Sekhar
  2. techbabu303 New Member

    Your indexes are small , anything beyond page count 10K and having AVG fragmentation > 30% needs attention.
    You needs to answer couple of questions to best guide you
    1. How big is your database ?
    2, How many users use your application ? Is your application web or client server with LAN ?
    Having IIS application pool and sql server on same machine is not good option any day if you have more than 100 users.
    3. Have you indetified long running queries ?
    Regards,
    Sat
  3. satya Moderator

Share This Page