I’ve been reading through many posts, and have been looking at the Tuning Navision for better performance document. Running Fin 2.6E on SQL 2000. If I look at the recommendations listed, I can see that basically all of my problems are probably related to either database design issues (keys, flowfields) or code. After performing the recommended server performance counter to monitor, I have found that have what are seemingly a lot of lock requests per second. I can also see other counters such as full scans/second and page splits/second being high at times. Does anyone have any general recommendations about the types of coding mistakes that cause these conditions to happen? This has become important recently, since during peak usage times, we get very slow performance in the Sales Order area, where we do most of our work. Througout other times of the day, the performance is more than acceptable.
In addition to that. Can anyone confirm significant performance improvements by optimizing indexes in SQL on certain tables (such as sales header/line, item and others that are often accessed)?
I did experiment a lot few years ago with indexes cretated by Navision in SQL 2k. There are tonns of tips and tricks you can find in Internet on this subject. For example apart of optimization of general SQL performansce (some general parameters, moving data files to separate disk groups etc.) I re-created often used indexes through Enterprise Manager directly. For instance when you re-create an index, you could specify WITH SORT_IN_TEMPDB option of the CREATE INDEX statement, which directs the database engine to use tempdb as a work area for the sorts required to build an index. This option reduces the index creation time and boosts performance when tempdb is on a different set of disks than the user database, but increases the amount of disk space used to create an index. This gives some effect when you have significant table size like few hundreds of thousands records.
I’ve been using NetIQ’s Diagnostic Manager for SQL, and let me tell you, SQL is tracking tons of different things, and this app lets you see it. I’ve seen some of our big tables, as well as those elusive flow field tables that may be up to 80% fragmented. I’m going to experiment this weekend with this to see what kinds of improvement I might get by using the SQL defragment option. Based on the Tuning Navision for better performance document, if you have a lot of page splits/second, then the recommendation is to defrag SQL indexes, and review keys. It is also interesting that the document recommends that you rebuild/defrag indexes DAILY on certain tables (Sales Header, Sales Line). Andrei or anyone else, do you have any easy batch scripts that you use to do regular SQL maintenance like this?
There are some other issues involved here also. As everyone knows, one of the greatest causes of conflicting lock requests in Navision on SQL is the use of flowfields. What fewer people understand is that this is caused by the way that SIFT is implemented in SQL Server, i.e. through separate tables instead of index extensions. For example, a single write to a database table with 5 indexes on it will of course create 6 database writes without SIFT. If 3 of these indexes have SIFT maintained on them, however, and each index has 5 parts to its key (all SIFT maintained), this same write will result in 21 writes to the database. That’s 3.5 times as many writes. It also generates a fair bit of other overhead because every write has to first reference the index, then find the corresponding value in the SIFT table, then perform the write. As this all occurs as part of the transaction, it thus prolongs the lock. This is why Navision has been progressively deactivating certain SIFT fields with each new release - to make it perform better on SQL Server. You can achieve similar relief by deactivating portions of your own SIFT activity. If you only use certain combinations of the key parts, or you generally limit your trendscape analysis to certain periods, you can deactivate the portions of SIFT you are not using and significantly reduce the write load on your system. This covers the write side. There’s even more that you can do on the read/analysis side. But this depends on your knowledge of SQL Server’s optimizer and the way it is used in a typical SQL application versus the way that it is used in Navision. However, I fear that I may be boring the hell out of this thread, so I’ll leave it here for now. Waynewil
quote:
Originally posted by THaug Andrei or anyone else, do you have any easy batch scripts that you use to do regular SQL maintenance like this?
You can use the script below (script rebuilds all indexes with a fillfactor of 80 in every table in the current database): DECLARE @TableName sysname DECLARE cur_reindex CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’ OPEN cur_reindex FETCH NEXT FROM cur_reindex INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT ‘Reindexing ’ + @TableName + ’ table’ DBCC DBREINDEX (@TableName, ’ ', 80) FETCH NEXT FROM cur_reindex INTO @TableName END CLOSE cur_reindex DEALLOCATE cur_reindex GO During the rebuilding of a clustered index, an exclusive table lock is put on the table, preventing any table access by your users. During rebuilding a nonclustered index, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it; you should schedule DBCC DBREINDEX statement during CPU idle time and slow production periods.
We are also encountering blocking problems when posting sales and purchase orders in peak times. I would be interested in reading the Running Fin 2.6E on SQL 2000 document. Where can I find a copy?
waynewil, I’m personally not bored by the discussion, so you can go and discuss more if you like. Andrei, thanks for the script. I’ve been doing a Maintenance Plan to optimize the tables every week. Speaking of SIFT/FlowField issues, besides going in to every table and checking them for FlowFields individually, is there a way to possibly search through the database schema, if you will, to find all FlowFields in the database? Andy, if you use the e-mail function here and send me your e-mail address, I will send you a copy.
THaug, The fastest way to see your overall SIFT use when running Navision on SQL Server is to use the SQL Server Enterprise Manager, Tables, and look for tables with a naming convention of $Tableno$indexno, e.g. CRONUS Canada, Inc_$37$0 is a SIFT table for index 0, table 37. Keep in mind, index 0 really means the first SIFT maintained index. Take a screen snapshot of this listing and you will have a comprehensive list of SIFT use throughout your system. To correlate to what you are seeing through the Navision interface, go into Keys and look for the first SIFT maintained index = 0, second = 1, and so on. To confirm this, simply open the SIFT table and look at its contents versus the Navision index configuration (i.e. which fields and SIFT buckets are being maintained, etc.). As for improving performance other than managing your SIFT usage and of course index optimization, i.e. coding performance problems, there is one sure-fire way to do this if you’ve got the time. After you use your Navision performance monitor, you’ll be able to see the general location of your problems. Next, add a few lines to your Navision code to measure start/stop times between key blocks of code. This will give you your specific code block problems. Finally, create a SQL query/procedure to duplicate the logic your problem code block. Being a declarative language as compared to procedural, this can be done in SQL very quickly. Now use your SQL Server query analyzer tool to examine/optimize the execution plan. When you’ve completed this, you will have determined SQL Server’s maximum capability for the code block in question. If the time for this differs significantly from the time required for the same code block in Navision (i.e. C/AL), you know that your Navision approach is flawed and needs to be reworked. Hope this helps, Waynewil