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....).
- 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!