This session is presented by Michael DeVoe and Robert Miller. They're both really good, so I expect a lot of it.
Apparently, there is a new tool coming available to analyse the performance of a NAV database on a SQL Server: the SQL Server Performance Troubleshooting and Analysis tool. Robert and Michael are both working on it themselves. Apparently this is what the session will be all about.
So, what is it?
It consists of 6 built-in tools.
This soulds cool, but really ... I want to do this from within SQL Server, not from within NAV :|. I should try this out (we're getting a beta version on a USB-solar-calculator-device (yes, indeed )).
Hey, here is a statement. Michael DeVoe sais that Index Hinting by default is a HUGE mistake. It shouldn't have been in there. Quote: "I'd rather trust theSQL Server developer's tool to find me the correct index instead of an application developer". Furthermore, this tool is worthless when index hinting is on, so be sure to switch it off.
To drill down into the tool, let's talk about the zero SIFT record deletion tool (try to put that into a sentence). One note for this tool: make sure you're on 4.0 SP6 Update 6 because of the bug in the SIFT calculation. The tool is completely dynamic and will work on all standard and customized tables.
The next tool, SQL Index Defragmentation Tool, works with the sys.dm_db_index_physical_stats DMV. You use this to defragment indexes, which can have a noticalbe impact on performance.
Furthermore the SQL Index Statistics Rebuild with FULLSCAN tool [gulp :|]. It uses the UPDATE STATISTICS ... WITH FULLSCAN. The FULLSCAN forces SQL Server to read all records in the table when compiling the stats instead of just a sample size. The tool works best when used as a periodic activity in conjunction with "auto update statistics". Michael hopes everyone turns ON auto update stats. Me? I don't know anymore. I'm really starting to doubt that it matters that much.
The SQL index Fill Factors tool will rebuild the clustered index on any NAV table with a fill factor. It allows you to choose a table or a group of tables to rebuild the fill factor on. By the way, fill factors help avoid SQL Index page splits helping increase performance. Processing table such as Sales Line and purchase Line can benefit from Fill Factors, but do not set fill factors on transactional table such as Value entry and Item Ledger Entry. Because of the entry no., all records will be inserted at the bottom of the last page. Setting fill factors will only create unused space.
The SQL Index utilization Analyzer is based on the sys.dm_db_index_usage_stats DMV. It shows you the table name and all non clustered indexes associated with each table. It will allow to set filters such as number of rows and number of seeks and scans and allows you to generate recommendations on what indexes can be dropped due to little or no usage. Before you drop indexes, make sure you got a full backup.
Last tool is the SQL Missing index Analyzer. This tool is based on the sys.dm_db_missing_index_details DMV. It is clear what the tool does, I guess .
A lot of the tools are based on a DMV. DMV's are only available since SQL Server 2005, so this tool will only work for NAV databases on SQL Server 2005. Furthermore is the data in DMV's reset every time you restart the SQL Server Service or reboot the server. Keep this in mind. If your server is only running for 1 day, the data in the DMV's are not really thrustworthy.
Over to Robert Miller with a demo of this tool. He warned us that due to some limitations of ADO (2000 character string buffer), the code isn't that so fantastico. Therefore, they had to write some stored procedures to be able to keep the SQL string limited.
Anyway .. Lets bycicle through the tools (like we say it in Belgium ). He started off with the missing indexes tool. The tool suggests what indexes should be created, lets you select the indexes that you would like to create, and creates the indexes. Good to know: the indexes are NOT added in NAV, only in SQL Server! I find this a little bit awkward. I would never recommend this.
Furthermore, the Index Usage Analyzer tool shows you which indexes might be deleted. The deletion of these indexes are also deleted from within NAV. Again, no synch between the MaintainSQLIndex field and the indexes that were delete in SQL Server .
Hm, not all tools are shown. At least we have a global overview on how it is going to work. Let me check if I can make this available to all of you.
>This sounds cool, but really ... I want to do this from within SQL Server, not from within NAV
>The next tool, SQL Index Defragmentation Tool, works with the sys.dm_db_index_physical_stats DMV. You use this to defragment indexes, which can have a noticeable impact on performance.
I would like to share a solution for doing dynamic index optimization, with the Navision community. It's a stored procedure that does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level and LOB existence. It's using sys.dm_db_index_physical_stats just as the SQL Index Defragmentation Tool.
This reduces the time it takes to do index maintenance considerable. It also reduces the sizes of your transaction log backups during the index maintenance.
The solution has been very thoroughly tested and it has been running on a large Navision installation for about 6 months.