Convergence: C&T with Michael DaVoe: SQL2005 & NAV

WaldoWaldo Member Posts: 3,412
edited 2007-03-31 in SQL Performance
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!

Eric Wauters
MVP - Microsoft Dynamics NAV
My blog

Comments

  • DenSterDenSter Member Posts: 8,307
    They would indeed be included in a SQL Server backup, as that owuld be part of the SQL Server table design. Included columns looks very promising. I think someone had mentioned it before, and I didn't understand what it was until Mike's demo. Theoretically it will save SQL Server from having to read anything from the SIFT tables or the actual table, because the included columns are part of the index, and the info is right there. I haven't tried it in production yet though.
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    The most usefull thing I got out of this session was the advise to always go for SQL 64bit. I am always lost in the SQL licensing model.

    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/
  • DenSterDenSter Member Posts: 8,307
    That's always the risk isn't it... they find an alternative solution for SIFT indexes, and before you know it, someone will try replacing all of them and cause all kinds of upgrade and object maintenance problems.
  • WaldoWaldo Member Posts: 3,412
    It give an extra difficulty when you (for example) have to take over a customer from a partner, which did some customization on SQL level.
    And SQL Server doesn't have a "modified" flag we can filter on, or an export to text to compare, does it :wink: ?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • DenSterDenSter Member Posts: 8,307
    Exactly. This is very dangerous territory, which has to be documented very well.
  • krikikriki Member, Moderator Posts: 9,115
    My advice is : don't use it! The change you run into problems sooner or later is 100%!
    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?
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • WaldoWaldo Member Posts: 3,412
    The option is just so nice to work with ... . :|

    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 :wink: ?
    MVP's ... you can't pass this to them :D ?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • DenSterDenSter Member Posts: 8,307
    Well I would be interested to see what it would look like if all SIFT indexes are implemented that way, and maybe that could be THE way on SQL Server.
  • WaldoWaldo Member Posts: 3,412
    I think we all agree it should be added into C/SIDE ...

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • DenSterDenSter Member Posts: 8,307
    Yes definately. I doubt that it will be though, because that would make NAV not compatible with SQL Server 2000.
  • kinekine Member Posts: 12,562
    DenSter wrote:
    Yes definately. I doubt that it will be though, because that would make NAV not compatible with SQL Server 2000.

    May be as part of 5.1 new client it will be no problem, because it will be compatible with SQL2005 only (or not?). :-)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • DenSterDenSter Member Posts: 8,307
    I don't know, and if I did I wouldn't be saying anyway 8)
  • WaldoWaldo Member Posts: 3,412
    DenSter wrote:
    I don't know, and if I did I wouldn't be saying anyway 8)
    :evil:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    The 5.1 client is not a dev-environment as in the classic client. The classic client will remain as dev-environment for both C/Side and SQL.

    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.
  • krikikriki Member, Moderator Posts: 9,115
    DenSter wrote:
    Yes definately. I doubt that it will be though, because that would make NAV not compatible with SQL Server 2000.
    Well, Navision on SQL2000 could just ignore it.

    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • DenSterDenSter Member Posts: 8,307
    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.
    The thought behind though is that the additional fields are part of the index, so SQL Server doesn't have to go to the actual table and retrieve the records, and that in itself should save time.

    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 :mrgreen:
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    I know the idea, it is the same as a covering index but with less cost.

    Problem however is that NAV calls a SELECT SUM often with more fields as required.
  • DenSterDenSter Member Posts: 8,307
    I thought though, that when you turn off MaintainSQLIndex, turn off MaintainSIFT, that the query would try to sum the sumindexfield only, in the table directly, and that SQL Server would then recognize that the field is covered in the index and sum from there. So what you're saying then would mean that if you add one of the sumindexfields to the index, you should add them all? Have you tried profiling that?
Sign In or Register to comment.