Luc and kriki asked me to post the convergence sessions about sql performance in this forum...
Mr DeVoe started off with a small explenation about the new feature in SQL2005: "Included Columns" and how it could "replace" the SIFTs we define in NAV.
The "included columns" is an extension of an index, where you can define which other columns are included, but are not maintained. You can add as many columns as you want in the "included columns" for an index.
It's all "speculative" (don't know how to say it in another way ), but it should be possible to get rid of the workload to maintain a SIFT. He advised to use it on "small transactual tables" (Sales Header/Line, Purchase....).
How?
- Disable SIFT
- Disable MaintainSQLIndex
- New Nonclustered key + SIFTfields as include columns
- Will NOT be maintained with a NAV backup/restore! A SQL Backup/Restore I don't know, but it seems logical it woul be maintained...
Then, the "chalk & talk" really began, and I noted some small points that were discussed with Michael:
* Putting entry tables (G/L entry, Item Ledger Entry, ...) on seperate disks could help.
* RAID 5: good for reading data, terrible for writing data. Use RAID 10 for that. He hasn't seen a situation where it was necessary to put the tempdb on a seperate volume. He would put it on the same device as the db-files (not Log).
* Index usage: New in SQL2005 are the datamanagement views ... allthough watch out, because these are only interesting when the server has been running for a few weeks/months. These views can show you what has been going on with your server in the past. He referred to technet to look for more info.
After some browsing, I think they were talking about "dynamic management views". May be this URL can get you going:
http://msdn2.microsoft.com/en-us/library/ms188754.aspx
* You have to disable "createstats" in the setup of your database. Maintaining stats on indexes only is enough.
* The new statements (FINDSET, FINDFIRST...) should diminish the number of roundtrips (also explained in Hynek's presentation about cursors).
* If you use fillfactors, you have to set up a maintenanceplan to maintain them.
* Database Mirroring can use a significant overhead!
Comments
RIS Plus, LLC
MVP - Business Apps
The included column can be usefull, but I would only use it as a last solution, simply because it is on SQL level, not on NAV object level.
Always make sure your procedures and documentation are up to date when making changes on SQL level. O:)
A husefull hint when tuning a 3.x system: Try to disable SQL indexes and make selective indexes as new ones in C/SIDE. This keeps the NAV object intact. \:D/
RIS Plus, LLC
MVP - Business Apps
And SQL Server doesn't have a "modified" flag we can filter on, or an export to text to compare, does it ?
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
RIS Plus, LLC
MVP - Business Apps
Even if it is well documented: Where do you put that documentation? Are you sure everyone will read it before doing something on the DB?
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
It would be a pitty to just ignore it, don't you think? When the moment occurs that this is a solution for us, I will just have to "over"document the code and just use it, I guess
May be the NAV development team can add one more key property: SQLIndexIncludeColumns or something ?
MVP's ... you can't pass this to them ?
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
RIS Plus, LLC
MVP - Business Apps
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
RIS Plus, LLC
MVP - Business Apps
May be as part of 5.1 new client it will be no problem, because it will be compatible with SQL2005 only (or not?). :-)
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
RIS Plus, LLC
MVP - Business Apps
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
Besides that, if you calculate one flowfield in NAV the driver tends to calculate all flowfields on SQL so that makes working with covering indexes and/or included columns more difficult :sick:
Also keep in mind that in the index there are as many records as in the table so the number that SQL has to sum all of them. A sift table contains an actual single record with the sum, so for example G/L entries you do not want to replace the sift.
Anyway, that is my view on the subject.
True, so Navision should be able to provide both possibilities.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
How much? I don't know.
Will the system crash and burn if you set too many SIFT indexes like that? I don't know
Am I going to try that? Heck no
RIS Plus, LLC
MVP - Business Apps
Problem however is that NAV calls a SELECT SUM often with more fields as required.
RIS Plus, LLC
MVP - Business Apps