SQL Tuning for NAV

Matt_Keyes

Hello,

We have a customer that migrated to a new SQL server (2005) from a SQL 2000 server in NAV 4.0 SP3, and now process are taking longer than they used to on the SQL 2000 server.

I am doing my own analysis and research, but if anyone has any checklists or suggestions to check, it would be much appreciated.

Thanks!

JoshuaSaves

Examine all the Keys. All subindex fields in SQL are tables. The more tables that have to be written to, the longer each transaction takes. The keys and code for 4.0 sp3 is still optimized for the native database. SQL doesn't like Boolean, Option, Dates in the keys. Often SQL will analyze the cost of the recordset and simply read the entire dataset. You can make use of the SQLIndex property in the keys to create a custom index that SQL will like better.

For custom code, make use of the new FINDFIRST and FINDLAST commands. This only returns a single row of Data. Also if you are fetching a set of data that is read only, use the FINDSET(FALSE). This produces a firehose recordset which is very fast.

There is alot more to tunning a SQL database. May I recommend that you contact Daniel Rimmelzwaan. You can find him here in this forum.

Matt_Keyes

Thanks for the reply.

The catch is that they used to be on SQL 2000 with no problems. Migrating to 2005 has slowed them down.

We are prone to thinking it may be a hardware problem, but performance tuning tips are appreciated. I will look at contacting Daniel Rimmelzwaan. EDIT: I can't seem to find his username when trying to send a message.

Thanks again!

kine

DenSter ;-)

DenSter

That would be me

davmac1

I had a customer who migrated to a new server where there was a network speed problem because of incompatabilities with their network cards and the latest Windows Server service pack.
If it is a hardware related problem, the speed issue should also show up in general network related task as well.

Poebbles

Have you installed all hotfixes for NAV 4.03 SP3? I'll think hotfix 6 or 7 will solve your problem.. If not you can use the perfmon of SQL Server and watch the HitCacheRatio. This Ratio must always be over 0.90 or 90% If Ratio is under 90 you have to insert more memory for server...
Another point is to split the tempdb
http://support.microsoft.com/kb/328551
Or Check the Disk performance

stryk

Hi!

Well, it is fact that the engine of SQL Server 2005 works differently than in SQL Server 2000.
Unfortunately, SQL 2005 reacts more "sensible" on sub-optimal queries and indexes, having a far higher tendency to perform "Index Scans" instead of Seeks - this is why after migration the system behaves slower (and get more blocks).

Here Index Optimization is crucial, starting with reducing SIFT structures and "streamlining" Indexes. For several tables the Clustered Index might be changed, dispensable indexes have may be deactivated (Caution: Only deactivate when KNOWING, not GUESSING!).

Fill-Factor optimization or Index Segregation, Index Hints or Plan Guides may also be feasible - if you KNOW what to implement (else you'll screw up your system).

Hence, there's a lot of tuning required, especially with SQL 2005 as it acts somewhat "sissy". Once it was tuned, it'll unleash it's full power!

P.S.: With SQL 2005 the priorisation of several operators has been changed. With build 9.0.3200 a new traceflag (4119) was introduced, which resets the priorisation for specific query-patterns to old SQL 2000 behaviour. See: http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2007/11/19/new-trace-flag-in-update-4-for-sql-2005-sp2.aspx

@Poebbles: you're mixing up several things, and you're simplifying this way too much - if it was THAT easy ...

garak

Yeah, the engine of SQL Server 2005 works very differently than in SQL Server 2000. Often you can see this, weh analyse the same query on SQL2000 and 2005. The 2000 makes an Clustered Index Seek with using the right index but 2005 -> he means to must use an scan :-(

With using index hint or recompile, that will not make any difference to this query.

Often, this is whe you use queries with Wildcards (Like '%blb').

I've tested the 4119 Traceflag, ok it works, but how long :-8 and ist only an "Hotfix" in my mind.

But before using this Update, its necessary to analyse if there are some querys with this wildcards. For anal. u can use the SQL Server Profiler.

http://www.mibuso.com/forum/viewtopic.php?t=24105

@Poebbles:
adding memory why the cache hitratio is down, must not be the key.
The CacheHitratio will go down, the reading data from physical disk will go up when using 2005 and an query with these wildcard (see link). These go on with using 4 GB, 8 GB or higher (tested). And my testserver is an good one and the index tree is optimised ;-)

But when you mean, more RAM is better for SQL, than your'e right. But not every problem can be fixed with adding ram. When you analyse the Server you must take a look to more indicators (Pages, CPU, Queue Lengt, Cache Hit Ratio, Disk read, Write, sniffing Query with an duration >= XYZ msec, blocking Sessions and users, etc.)

Often analysing spend a lot of time ...... :-s and the customer scream for an solution and can't wayt ....

stryk

With using index hint or recompile, that will not make any difference to this query.

Even though general "index hinting" (as with NAV 4.0 Sp3 U6 "out-of-the-box") is a very bad idea, specific Index hinting could help, of course!

If you see, that with 2005 a wrong index is used and scanned, it may be an option to hint the right index, e.g. the one which was used in 2000!
Usually the problem with 2005 is, that it performs "Clustered Index Scans" instead of "Index Seeks". By hinting the right index you have a high chance it will prcoess the query correctly!

Same for using OPTION(RECOMPILE): Many CI Scans are due to "Parameter Sniffing" in 2005. By forcing RECOMPILE SQL 2005 actually always gets another chance to operate correctly!

Regards,
Jörg

garak

yes thats right an specific hintig can help.
General activation Indexhinting = YES is an bad idea (we all know this).
general Indexhintig = No can also be an bad idea, because in some tables an index hint for specific index, for example $1 in 27, should be activated (after testing).

But before activating, i ever make an trace of the "customer" queries an server.

With the sentense "...make no any difference to this query" i mean the problem with using wildcard (Like and >= <,>,<=).

I wounder, why on partnersource, there is no public comment about some problems with 2005 and there solution :-(

stryk

garak wrote:

I wonder, why on partnersource, there is no public comment about some problems with 2005 and there solution

Yeah, it's a pity ... the only official MS acrticle about that issue is this http://support.microsoft.com/kb/942659/en-us but without mentioning this refers to a specific NAV problem ...

garak

jupp, this artikle looks like it was created why navision makes queries like this ;-)

For new members they ask what is index hinting, here an good article

http://blogs.msdn.com/microsoft_dynamic ... 40718.aspx

kriki

[Topic moved from Navision forum to SQL Performance forum]