Effective Troubleshooting

wakestar

Hi All

A general question about troubleshooting sql-performance...

Let's say you have a database with lots of users and a lot of Full Scans / s... according to the Windows Performance Monitor.
What's the fastest / most effective way to find out, where the trouble actually comes from?
I guess the first thing to find out is which navision tables are causing most of the Full Scans? But how?

thanks

kriki

A full scan is not always bad. If you open a setup-form, SQL will do a full table scan. But there is only 1 record, so no problem.

The problem is if there are lots of records and SQL has to do a full tablescan. Those you can find using traces where the time is quite high and it does a lot of reads.
But it only shows you the query and not the values and neither the object that generated the scan.

garak

u can use at first the SQL Profiler.

It's under Start -> Programs -> SQL 20005 -> Perfomance -> SQL Profiler.

In the SQL profiler you activate following Events

Showplan XML (to see which index is used and if he scan or seek)

Stored Procedures
- PRC:Completed
- SP:Completed
- SP:StmtCompleted

TSQL
- SQL:BatchCompleted
- SQL:BatchStarting

Filter (for example):
Session -> the Sessionid of the user to see only the activity of the user or
Reads -> over 1000 //view all Reads over 1000 pr
Duration -> 2000 ms //view all processes over 2seconds

now you can see, which "commands" are the scan commands

stryk

Exactly \:D/

Please regard that the "SP:StmtCompleted" usually shows the TSQL satements,but the WHERE clause just contains some placeholders, e.g. @P1, @P2 etc..
The real values for these parameters are shown in the "RRC:Completed" event, thus to re-run that query in SSMS you need to combine both operations (sound trickier than it is - we're not talking about rocket science here 8) )

When it about to filter for poor queries, I would start with this:

Reads >= 1000
Duration >= 50 (or 100 if you want, but IMHO 2000 is too high, you'd miss plenty of issues then)

Then you should count how often the same/similar bad query is executed, to determine if it is a frequent problem or a singular event (sometimes scans are provoked just by users applying "stupid" filters).

And - do you know this one? http://msdynamicsbooks.com/product_info.php?products_id=130&language=de&osCsid=c43beba9c2b0f50dffa2e1a65db49539
Maybe this helps!

stryk

P.S.: For a first investigation you should NOT include the "Showplan XML" in the trace. The filters - Reads / Duration - won't apply to this event, thus you'll record tons of lines. Once you've tracked down the "bad queries" you could re-run them in SSMS and there you could also check the Execution Plan.

garak

Before you start the profiler you can also start the "perfmon" (Start -> run -> perfmon) with the needed indicators (do you need help for usinig the correct indicators?) and store the recording in a file (or table).
Then start also the SQL profiler with the filter that you need (see the examples from me and stryk).

If you have the datas from the sql profiler, store the tracefile (File -> Store -> trace file). Store your setting also as template (so you must not "reconfigure" the profiler when you need it some times ago).
Now, and thats why i also like both tools, you can import the perfmon datas into you sqlprofiler. Now you see on top the "bad" sql query and bottom the permon indicators ....... \:D/

garak

ah, and forgott. It must not ever be a problem of your NAV-Sql queries (wrong / no high selectivity index, no good statistics, wrong C/AL code, and so on) it could also be a problem of your hardware (or both). so this you can also check with the perfmon and, for example with the IOMeter tool (iometer.org).

The "only" problem that you can have is: You see the sql queries in the sql profiler but you doesn't know in which NAV-Object will this stuff "created" (if not u run the C/AL, but other user work in the system and you sniff only the traffic/commands)

So, forgott i something? :-k
Regards

wakestar

thanks guys, I'll go through that stuff

@Jörg A. Stryk: I have the book since yesterday on my desk ... so I haven't read everything yet

another question:
are there any microsoft average reference values for the sql server access methods statistics (Full Scans / s, Page Splitts / s) based on a Cronus Database with let's say 50 Users?

Or in other words: How can you determine whether 10 full scans / s are bad in a certain scenario?

stryk

I don't think that such a reference exists ...

I think the best is to monitor your system (e.g. Full Scans etc.) for a certain period to get some sort of "baseline", an daily average or something.
Then you could check with the Profiler, whenever this average is exceeded, if there are "huge Read queries" which usually indicate scans.
Once you could fix such problematic queries you could compare the baseline figures with the new measurement.

wakestar

stryk wrote:

I think the best is to monitor your system (e.g. Full Scans etc.) for a certain period to get some sort of "baseline", an daily average or something.

That's the point, how do you argue with a customer who thinks that the baseline is too high?
Who's problem is it? Is it the navision standard c/al code (which was originally made for native databases), is it my customized code or is it just the fact that a *lot* of users are working with the system at the same time?

stryk

Well, the Windows Perfomance Monitor, e.g. "Full Scans", "Page Splits", "Lock Waits" etc. could just indicate a change, there is no chance to see what is actually causing these figures.

The only chance to "look behind the scenes" is using the SQL Profiler: there you could see which queries are causing I/O or CPU load - that's what you could measure with perfmon - and then the challenge is to ...

... determine if it is frequent/periodic problem caused by code or process; or if it was a singular event due to specific user interaction
... reproduce the problem
... find and implement appropriate solution (Index/SIFT/VSIF optimization, Query Hinting, code/process optimization, etc.)

Therefore it is crucial to be able to translate the recorded TSQL back into the original C/AL code, thus you have to know how C/SIDE is querying the SQL Server etc.. Eric's BLOG might help here: http://dynamicsuser.net/blogs/waldo/archive/tags/What+impact+does+my+C_2F00_AL+have+on+SQL/default.aspx
Then you could tell if it's a standard or customized issue ...

<sort-of-advertisement>I develop a utility called the "NAV/SQL Performance Toolbox" (PTB) http://www.stryk.info/english/toolbox.html which is provided by my Dynamics Partner network. This PTB has some features to easily create/investigate traces etc.. If you would like to know more about it, please send me an e-Mail off this topic.</sort-of-advertisement>

garak

@wakestar: also you can use the "Microsoft_Navision_SQL_Resource_Kit". It's on the ToolsCD (viewtopic.php?f=23&t=27702 and viewtopic.php?f=34&t=28923)

Regards

stryk

The DB Resource Kit has no feature for query analysis, and honestly, the Client Monitor is not really feasible for serious troubleshooting ...
(IMHO the Resource Kit is not feasible at all )

garak

right, but in "alliance" with the Profiler and the Perfmon it can be helpful (for example the enhanced Clientmonitor with using the excel Pivot Table option).

@Wakestar: Here some perfmon indicators that u could use (sorry it's in german)
As i know, stryk describes this also in his excelent book.

Regards

stryk

Wakestars problem is to determine where the poor queries are originating from.
To use the Client Monitor you need exactly to know where it is coming from, as you have to execute it on the relevant NAV Client, processing the problematic code; thus, the Client Monitor is pointless for an initial investigation.

Hence, it is necessary to track down the poor queries first with Profiler, then fix it (if you would like using the CM then).

garak

I know Jörg, thats was the reason for the first answer.

We have misunderstood. What i mean is the tip with the Clientmonitor, SQLResourcekit are for the next, intensive, job to find the "bottlenecks" when he will run a complex NAV transaction like posting some production orders with charge and lot nos and Dimensinons and customized code ....

It's true for the first, if you don't know which commands stress the system, it's the best way to use the sql profiler in alliance with the perfmon. Then u can see when the system has the problem and which user(s) runs this transactions. So he can ask the user(s) what he do at this time and check the NAV processes.

Regards and greetings from the balcony ;-)