Poor performance due to wrong setup on sql

willy
willy Member Posts: 67
edited 2009-06-17 in SQL Performance
Hi!

I'm lookin into the solution for one of our customer. I am not very familiar with setting up and configurate NAV on sql server, but this time I have got to find out why the customers database is running very slow.

They complain about bad performance, sometimes the whole system hangs for about 10 seconds and all searching and posting are going extremly slow.

I'm looking for some tips where to begin to find out where the problem(s) are.
Hoping someone can give me some tips.

What they have got is the following setup:

A windows 2003 server R2 Service pack 2.
Sql 2005 workgroup edition, 1,86 GHz and 3,5 GB Ram

Running on nav 4.0 sp3 runtime files (build 23305), with av 4.0 SP 1 database

The database is 7051712 KB, used is 6837440 (97% used).
The log file is about 1,7 GB

File setup for database: (database files are on a seperate disc,while the log file is on another disc)
mdf file:Size = 297, File Growth= 10%, Unrestricted Growth=true, Max Size= not set
ndf file: Size 6588, FileGrowth = 10%, Unrestricted Growth=true, Max Size= not set

Log file:
Size= 1588, FileGrowth= 10%,Unrestricted Growth=true, Max Size= 21474833647

The database is about 5 years old. There has been no defragmentation or reindexes lately.
No update stats have been driven.

They have 6 physical disk drives. A is disc for OS and software, B is for sql database files, C is for sql log.
Running RAID 1 to 3 identical disc.

When it comes to customisation, They have got a lot of "special" code. Especially the job and sales module are heavily customized.

I

Answers

  • TonyH
    TonyH Member Posts: 223
    How old is the database?


    Have you defragged/reindexed recently? Update stats?

    whats the drive configuration? RAID what?

    Any addons or custom code? There are so many things that could be causing slow performance.

    Example could be an item catalog of a million records and a user is searching on the non indexed description field in a random sort order with find as you type turned on. That would cause degradation even if the hardware is top notch.

    There are people on her who can give you some sound advise and they are a lot better than i am, but they are gonna ask you lots of questions and make you go back to the posts that already exist.

    t
  • garak
    garak Member Posts: 3,263
    edited 2009-06-17
    1. deactivate the File Growth= 10%, set a decimal value like 500 MB (it's depends on how often the database expand) or deactivate it an you expand the database ever manuell (create a warning so you becames a message)
    2. Exapand the database to a size, so that are ~ 20% free space
    3. activate the Maintanance Plans like sp_updatestats go sp_updatestats 'indexonly' (every day) and RebuildIndex (for exmple every 2nd day or every Sunday, it's depends on how often datas are changed / inserted) <- This will expand also the Log File
    4. create a backup szenario like every half hour a Log Backup and in the evening a full backup (search here also the Perfomance forum, there are some examples)
    5. Check the "AUTO" settings on database. Set them to NO (like AutoClose -> no).
    6. If you say,there are customized code, check this code and the "table structure" of the "slow" tables. Maybe there are wrong indexes and the SIFTS and the SIFT BUCKETS are not optimized. This is the step with the longest time..... (viewtopic.php?f=34&t=30468)
    7. To check why the system is so slow, activate the SQL Profiler (after step 1..4) and "sniff" the sql commands (viewtopic.php?f=34&t=32657). viewtopic.php?f=34&t=32666
    8. Test this all before on a Test Database / Test System.
    9. Search this "SQL Perfomance" Subforum. there are also a lot of good examples. And a tip, buy Jörgs (viewtopic.php?f=34&t=22727) book. It's a good thing and you can get the money back from your chef ;-)
    10. For the RAID Question read this viewtopic.php?t=24623 viewtopic.php?f=34&t=25862
    11. Good luck.

    regards
    Do you make it right, it works too!
  • stryk
    stryk Member Posts: 645
    @garak: Indeed! 8)

    Adding just this:
    The NAV build used is way too old and insufficient, with NAV 4.0 SP3 (runtime) you need at least Update 6.9 (26565) better higher, most recent is 6.20 (29113), see http://dynamicsuser.net/blogs/waldo/archive/2009/06/12/platform-updates-overview-3-70-b-nav2009-sp1-ctp2.aspx

    As garak mentioned: search the forums for "SQL Performance" and you'll get TONS of advices!
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • willy
    willy Member Posts: 67
    Hi Guys! Thanks for all feedback :)

    I've got some good advice on how I can start analyzing the performance and I hope we will get a pleased customer soon...

    I found quite a lot of nice post on the forum and are setting up a little plan what to check and measure.

    Looking forward to read the book from Stryk. What I found out so far, is that I have a lot to learn about sql and performance - but it's quite interesting, so I look forward to learn some more and dig into it.

    Closing the request and going to work with the case now.. Thx!!
  • garak
    garak Member Posts: 3,263
    Please an enjoy the learning: NAV Tuning for a better work with SQL ;-)
    Do you make it right, it works too!

Categories