Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
This hotfix solved our NAV 2009sp1 SQL performance problems
JamieHurst
Member Posts: 25
We've been struggling with performance and I think we finally found a fix worth sharing.
Environment:
- NAV 2009 SP1, classic only (Build 31793)
- 150 GB database on SQL 2008 r2
- 6 companies
- Licensed for 116 users, average about 90 concurrent (we love ExpandIT)
- Lots of add-ons and customizations
Until now, we've achieved incremental improvement, thanks to the advice of two major US partners and the SQL Perform tool. But still, every month or two performance would grind to a halt and just going from one record to the next on a Card form would take a second or two.
Strangely, when we experienced a slow-down, only our biggest company was affected, the others were fine. And, it was EVERYTHING in this one company: Items, Vendors, Customers, Jobs, G/L Accounts, Fixed Assets, SOs, POs, TOs, WOs -- even stuff we don't use very much, like Standard Text. Meanwhile, the same things were perfectly normal in the other companies.
Have I built up the suspense long enough?
It turns out the "Record Link" table was killing us. It needed a compound index which isn't there by default. We have about 4,500 records in this table, mostly for our one "problem" company (it's a "DataPerCompany=No" table). And, just about every table read automatically checks Record Link so the client can show that little "LINKS" notification in the status bar. So an inefficient index in this single table can affect performance all over the place.
Anyway, here's the hotfix:
http://support.microsoft.com/kb/2648727
Environment:
- NAV 2009 SP1, classic only (Build 31793)
- 150 GB database on SQL 2008 r2
- 6 companies
- Licensed for 116 users, average about 90 concurrent (we love ExpandIT)
- Lots of add-ons and customizations
Until now, we've achieved incremental improvement, thanks to the advice of two major US partners and the SQL Perform tool. But still, every month or two performance would grind to a halt and just going from one record to the next on a Card form would take a second or two.
Strangely, when we experienced a slow-down, only our biggest company was affected, the others were fine. And, it was EVERYTHING in this one company: Items, Vendors, Customers, Jobs, G/L Accounts, Fixed Assets, SOs, POs, TOs, WOs -- even stuff we don't use very much, like Standard Text. Meanwhile, the same things were perfectly normal in the other companies.
Have I built up the suspense long enough?
It turns out the "Record Link" table was killing us. It needed a compound index which isn't there by default. We have about 4,500 records in this table, mostly for our one "problem" company (it's a "DataPerCompany=No" table). And, just about every table read automatically checks Record Link so the client can show that little "LINKS" notification in the status bar. So an inefficient index in this single table can affect performance all over the place.
Anyway, here's the hotfix:
http://support.microsoft.com/kb/2648727
0
Comments
Never stop learning
Van Vugt's dynamiXs
Dutch Dynamics Community