cursorfetch problem or query plans need hinting?

chulew

First post so please bear with me

We used to have a lot of Navision performance problems but that changed when we
a. upgraded our san array's disk speeds from 10K to 15K.
b. stopped running perfmon and profiler directly on the SQL 2K5.

However the problems are still around, just less frequently.
I've begun to think it's a lack of physical memory or bad query execution plans. the problems happen in the mornings between 7am - 9am when people would run txns such as applying bank entries to a customer account, or filtering on GL chart of accounts, or searching for posted sales invoices, or going into Item Availability.

So if an end-user complained that they couldn't wait for more than 5 minutes for their Navision txn to complete, we would terminate their Citrix session (which talks to the NAV v4 sp3 update6 32GB RAM 400GB database environment). We would see in SQL activity monitor that the rollback is occurring. Once the rollback was completed, the end-user would try the txn again and it would return results instantly.

this is hard to replicate (imo) but when I use profiler, it indicates that the exec sp_cursorfetch and FETCH API_CURSOR statements have HUGE read values, typically in the hundreds of thousands of rows.

I've read other posts here on clustered indexes being used when a different index should be used (with or without $ndo$dbconfig set properly). Do you think that's what is happening? Could it be that the cursor is using the wrong index so a full cursor scan is happening? Would tools like sql-perform help towards resolution? So far we have tried the basics of re-indexing with fillfactors of 70-90%. We've also tried the delsift zero entries but there are no zero entries for whatever reason.


Thanks for any help.

stryk

Hi!
I'm afraid you're asking too many questions to be all properly answered in one thread ... You should search MIBUSO for "SQL Performance" and you will find gazillions of useful advices and recommendations, concerning hardware/platform/configuration, application, C/AL, indexes SIFT etc..

Actually it always starts with sufficient hardware and configuration, so what are your detailed specifications (see also http://www.stryk.info/Performance%20Checklists%201.06.pdf)?
With NAV 4.0 SP3 it is crucial to use a recent build version. Further SIFT optimization is mandatory!
What about maintenance? Fillfactors of 70% are pointless, just wasting plenty of RAM. If you don't use tools - like the "NAV/SQL Performance Toolbox" (http://www.stryk.info/english/toolbox.html) - to determine the optimal FF. then 90% should do.

Queries which "consume" too many Reads are usually caused by bad or missing indexes, maybe also due to Parameter Sniffing and/or Dynamic Cursor issues. To determine those queries the SQL Profiler is the right tool, but you have to make sure to record the "SP:StmtCompleted" event (the query) and the "SP:RPC Completed" event (the parameter values).
In case of problems with FETCH_API_CURSOR things you have to look at the relate "sp_cursorfetch" query; there you could see the cursor id. Then search the trace for this ID and you should find the related "sp_cursoropen" query. There you could see the statement, then take the RPC parameters - et voila, you've got the query which probably caused the problems. Apply an optimized index, and maybe this could fix it.

The "NAV/SQL Performance Toolbox" would definitely help you to quickly track down those bad queries and support you in fixing them.
But have in mind: "It's not the tool which fixes a problem, it's the person using it!"

Good luck & best regards,
Jörg