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

Waldo

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!

DenSter

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_Brummel

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/

DenSter

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.

Waldo

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 ?

DenSter

Exactly. This is very dangerous territory, which has to be documented very well.

kriki

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?

Waldo

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

DenSter

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.

Waldo

I think we all agree it should be added into C/SIDE ...

DenSter

Yes definately. I doubt that it will be though, because that would make NAV not compatible with SQL Server 2000.

kine

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?). :-)

DenSter

I don't know, and if I did I wouldn't be saying anyway 8)

Waldo

DenSter wrote:

I don't know, and if I did I wouldn't be saying anyway 8)

:evil:

Marije_Brummel

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.

kriki

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.

Mark Brummel wrote:

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.

DenSter

Mark Brummel wrote:

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

Marije_Brummel

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.

DenSter

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?