SQL tuning - keys

zeon

Hi all,

We have just migrated from 5.01 Native to 5.01 SQL, and probably have a lot of SQL tuning to do. I have a question about low/high selectivity and SQLIndex property.

E. g on the Sales Header table we have a key:

Document Type,Status,Sell-to Customer No.,Settlement Type,Week No.,Month No.

that in my understanding has low selectivity, right? If I want to change that to a key with high selectivity. As I understand it I have two things I could do:

Make a new key with high selectivity, and change my code also, e.g:

Sell-to Customer No.,Month No.,Week No.,Settlement Type,Document Type,Status

or, I could change the SQLIndex property on the current key? That is, add "Sell-to Customer No.,Month No.,Week No.,Settlement Type,Document Type,Status" to the SQLIndex property on the current key "Document Type,Status,Sell-to Customer No.,Settlement Type,Week No.,Month No.". Is that correct?

My question is then - will I have to change the code as well? E.g would I have to change the present code to the sort order of the key in the SQLIndex property?

SalesHeader.SETCURRENTKEY("Document Type",Status,"Sell-to Customer No.","Settlement Type","Week No.","Month No.");
SalesHeader.SETRANGE("Document Type",SalesHeader."Document Type"::Order);
SalesHeader.SETRANGE(Status,SalesHeader.Status::Open);
SalesHeader.SETRANGE("Sell-to Customer No.",Cust."No.");
if SalesHeader.findset then begin
end;

replaced by
SalesHeader.SETRANGE("Sell-to Customer No.",Cust."No.");
....etc

kriki

You need to keep the filters on "Document Type" and Status, otherwise you change the functional logic.

key "Sell-to Customer No.,Month No.,Week No.,Settlement Type,Document Type,Status":
If you never filter on "Month No." in other places, you better use "Sell-to Customer No.,Document Type,Status,Month No.,Week No.,Settlement Type".

But before starting changing code, I would search to eliminate indexes that are not used (the SQL perform tools can help you with that) and creating indexes for queries that are launched a lot and are long. These you can find using sql profiler.

rdebath

Also you need to beware if changing the sqlindex property.

Navision makes the primary key a cluster index (by default) and SQL2005+ assume that the cluster index is good for sequential scans to an much greater degree than SQL2000. Obviously for a big table no index is good for sequential scans but Navision 5.01 forces the existence of a clustered index.

Unfortunately the workarounds that Microsoft have been adding for this make Navision treat SQL more and more like a native database every time. With the exception that if the keys aren't exactly right it has an evil tendency to switch to the cluster index rather than a good one.

stryk

Hi,

since we have "Dynamic Cursors" (DC) in NAV it is necessary to have a good match between the sorting order (ORDER BY) and the filters (WHERE). The ORDER BY is defined by the NAV "Key", and DC tend to optimize a query for that clause.
So you need to make sure, there is an index available which matches (more or less) the order by, at least regarding the fields it contains.
But even if an index matching to ORDER BY (and WHERE!) is available, it might be necessary to also have the same index sorting as the "Key" sorting defines ...

So if you could define an optimized "Key" (regarding selectivity) then you should not change the "SQL Index" (means: leave it blank).

From a SQL perspective zhis is not really nice, but necessary due to the cursor issue.

Kriki mentioned to get rid of "unused indexes", and I strongly agree with this, even though I suggest to use a different tool for that purpose
(reason is: you MUST NOT simply rely on the DMV sys.dm_db_index_usage_stats but you need to do reliable long term monitoring)

Maybe also this could help you a little:
http://dynamicsuser.net/blogs/stryk/archive/2010/02/10/technical-airlift-2009-munich-nav-sql-performance-optimization-indexes.aspx
(Thursday 20th May I'll publish some updated scripts & examples)

zeon

Thanks for your comments and suggestions...

Kriki mentioned to get rid of "unused indexes", and I strongly agree with this, even though I suggest to use a different tool for that purpose
(reason is: you MUST NOT simply rely on the DMV sys.dm_db_index_usage_stats but you need to do reliable long term monitoring)

Well, I was thinking of using this query (see url) as a starting point for finding unused indexes, but as you say I simply cannot rely on using sys.dm_db_index_usage_stats for that purpose...
http://blogs.msdn.com/nav/archive/2008/02/26/index-usage-query.aspx
What tool do you recommend? SQL Server Profiler?

Since the customer we're working on use NAV to perform a few tasks over and over again, I was also thinking about profiling these tasks with the Client Monitor and use that to identify the most problematic server calls...is that the way to go also?

stryk

The problem with the DMV is this: all DMV data - as index usage - is reset when the SQL Server service is restarted.
Hence, for example, if you install an update (which restarts the service), then this analysis will show that all indexes are unused - now if you disable all those indexes you would cause some disaster in NAV, of course.
So you need long-term monitoring to have reliable index-usage stats over a representative period. It's crucial that this "monitoring" includes all critical business processes, e.g. end of month proceedings, etc..
If the index usage stats are not sufficient you might accidentally kill some index which then is necessary, thus you degrade performance.

Second issue is, you must not just drop those unused indexes on SQL site, you have to drop them through NAV by setting "MaintainSQLIndex" to FALSE.

The tool I was referring to is the "NAV/SQL Performance Toolbox" (free of charge, by the way) which establish exactly this crucial long term monitoring; and it does not really matter if the service was restarted during monitoring. Thus, this utility provides sufficient statists, minimizing/discarding the risk to accidentally drop some important index.
Further, this PTB provides some features to quick & easy drop those indexes - NAV & SQL in sync (incl. options for documentation and undoing the changes if required).