Convergence Diary: SQL Server Performance Troubleshooting and Analysis

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?

  • Allows you to do SQL index Maintenace tasks from within Dynamis NAV
  • Does not require high level of SQL knowledge
  • The tool leverages the power of SL Server 2005 DMV's
  • It's free for all partners!

It consists of 6 built-in tools.

  • Zero SIFT record deletion
  • SQL index degrafmentation
  • SQL Index Statistics Rebuild with FULLSCAN
  • Set SQL Index Fill Factors
  • SQL Index Utilization Analyzer
  • SQL "Missing Index" Analyzer

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

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 Wink.

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 Smile). 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 Indifferent.

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.

Comment List
  • Ola, thanks for sharing!

    Eric, I would still go for the SQLPerform solution myself for the reasons I mentioned above.  It's a maintenance tool, but it still needs some work for sure... .

  • How do you see these tools as compared to the other "tools" available for SQL performance optimization? I am here thinking about the ones from SQL Perform and the other companies specializing in this.

  • >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.

    Ola Hallengren

  • It is free of charge for partners.  And they were looking into it for making it available for customers via customer source as well ... but couldn't confirm if this was possible or not.

  • Funny, these "Performance Optimization Tools" grow like mushrooms ;c)

    Is it also "free-of-charge" - as my "NAV/SQL Performance Toolbox"?

  • I'm willing to think about auto update statistics, I've read a lot of comments saying that it's really not that harmful. However I find the logic flawed to use autostats as well as a routine that manually does it. If auto statistics is so great, then why do we need any tool to update the statistics? Likewise, if you create a tool to update statistics manually, why would you also need to automatically update statistics?

    Then I also strongly disagree with having a tool create indexes on SQL Server that don't exist in the NAV table design, I would never recommend that either.

    This all sounds very negative, but I am really very happy that they are working on this. It is very good to see though that they are addressing this need, very positive.

  • Those tools must be made available on SQL Server, so we can schedule them without having to install the Job Queue. Can you ask while you're there?