Post reply

Tunning Up a SQL Database

  • January 18, 2007 at 7:11 am

    #116015

    This started on a Monday, SQL Server is straining the Server box,

    I tried checking up on all the stored procs and changes that happened the previous week onto the Database, but nothing really major, So I tried archiving most of the data that's not needed,

    Took down the MDF file from 1.2 GB to 633MB, and switched off the auto growth function on the Data File and Log File,

    But the Server is still hammered,

    I read a few articles on using the Index Tunning Up Wizard, but still

    Curious if its safe,

    Can anybody suggest anything, before i make my rough changes

  • January 18, 2007 at 7:35 am

    #683997

    • turn auto grow back on - why on earth would you think turning this off would help?
    • get a dba
    • go on a training course
    • buy sql 2000 performance tuning guide.
    • Rebuild indexes
    • update stats if not a full index rebuild
    • dbcc updateusage
    • Run perfmon to find your bottleneck - disks, cpu, memory

    leave index tuning wizard alone.

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • January 19, 2007 at 1:14 am

    #684203

    First things, since you shrank the database. Grow it back to a reasonable size and rebuild all the indexes. Shrinking a database shuffles data pages around, fragmenting indexes very very badly. It's a sure recipe for poor performance.

    When you say hammering the server, how do you mean? High cpu usage, high disk usage, slow response?

    you can run profiler for a little while to see what queries are running slow/using high cpu/doing lots of reads. Might help pinpoint the problem.

    And don't trust the index tuning wizard.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 19, 2007 at 3:20 am

    #684216

    Thanx a lot guys for the responses,

    When saying Hammering, Yes I ment that the CPU usage is quit high, and users that are working on the database are experiencing time-outs,

    But the for the advise, now I understand when its said that a person needs experience before you can say you are a DBA,

    Have not resolved it yet, but still looking into it,

  • January 19, 2007 at 4:10 am

    #684233

    Check to see if it is SQL that's taking the CPU. There shouldn't be anything else running on a SQL Server, but is worth checking. Use task manager for this.

    Rebuild all your indexes this evening. Don't do it while users are using the DB, as reindexing is disk and cpu intensive and locks tables.

    Make sure there are no SQL Agent jobs running that shouldn't be running.

    If that hasn't fixed things, use perfmon to see where your bottlenecks are. Might be that you have disk or memory bottlenecks that you haven't noticed.

    See if you have excessive locking (sp_lock)

    Use profiler to find any long-running queries or cpu intensive queries.

    Sorry the help is vague. There are a lot of possible causes for the symptons you describe

     

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 19, 2007 at 6:09 am

    #684261

    There seem to be so many posts regarding server running flat out ( I have one such too, but I know the main problem )  it can be quite a difficult task to track down the issue and of course you need to be able to recognise the issues too .. and this can be tricky. I'd probably suggest you give Idera diagnostic manager a try - this tracks loads of stats, worst performing, blocking, locking .. just lots and lots in easy to follow and understand presentations. As it monitors you can do real time monitoring ( ok so you need to know what you;re looking at/for )

    This product is one I'd always recommend, having used it at most places for many years.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 7, 2007 at 2:41 am

    #688220

    Hey Guys,

    Thanx a lot for everyones inputs, We got to solve out what the problem was, The thing is that our Server "A" sends and receives data to another Server "B" of which "B" was having problems lately with disk utilizations as they say and that was what was affecting our Server "A" Box, Reason being I actaully went through all the steps of checking what has changed these passed few days since the server started running slow,

    So thats what was killing our Server,

    and Hey Thanx a lot to all you guys

  • February 7, 2007 at 4:04 am

    #688234

    That's cool and thanks for posting - so often we never get to know what a problem was or if it had been resolved, appreciated.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply