Full Scans on table Object Tracking

paurola

I was monitoring SQL Server in a load test for NAV 2009R2 Web Service when I noticed a Full Scan happening every two seconds. I started digging into it and found out that the table being scanned is "Object Tracking".

The table is quite small, there are somewhat 4000 rows in that table (one line for each NAV object) so I do not think that this is a problem and it is obvious that NAV Services really need to read all the records in that table repeatedly. I find this quite interesting though, because Full Scans are something that are usually "bad" for SQL Server.

I'm sharing this because I'd like to hear if you have any thoughts on this.

Marije_Brummel

Full scans are not bad for SQL Server.

If the table is small and the system needs to read the table all the time it is the execution plan you want.

Full scans are bad on large tables if you only need a set of that table.

stryk

How did you find out about this "Full Scan"? Did you run any SQL Profiler Traces? As Mark mentioned, I suggest to check the QEP to see the actual I/O and CPU time of that query.

paurola

stryk wrote:

How did you find out about this "Full Scan"? Did you run any SQL Profiler Traces? As Mark mentioned, I suggest to check the QEP to see the actual I/O and CPU time of that query.

Jörg, actually I ran the Performance Monitor Counters according to the advice in your book...

Performance Counter for Full Scans was showing that there are full scans going on so I created a trace to see what query was causing them. I found out that the table being scanned is Object Tracking so I really did not get worried, cause I think it is obvious that NAV Service has to keep track on (possibly changed) objects. I thought that this was interesting enough to start a thread.

IMHO: The NAV/SQL Performance Field Guide is a great book and should be mandatory reading for every NAV dba or developer.

kine

It looks like NST is in pooling mode, check the event log when starting the NST. Looks like the change tracing is not working because wrong setup (no permissions).

stryk

paurola wrote:

Jörg, actually I ran the Performance Monitor Counters according to the advice in your book...

Performance Counter for Full Scans was showing that there are full scans going on so I created a trace to see what query was causing them. I found out that the table being scanned is Object Tracking so I really did not get worried, cause I think it is obvious that NAV Service has to keep track on (possibly changed) objects. I thought that this was interesting enough to start a thread.

Thanks for your praise

Well, have in mind that with NAV there will always be "Full Scans": we have plenty of tiny tables where all records could be easily stored within one single data page (8 KB). If such a table is read - e.g. a Setup table etc. - this counts as a full scan.
Hence, a "Full Scan" itself is not necessariyl a problem - as Mark said, the problem arises if a large table/index is fully scanned.

To find out if and how this "Object Tracking" is affected I suggest to run a SQL Profiler trace, e.g. checking the number of "Reads" (= number of pages read for this query). A high number of Reads indeed could indicate partial or full scans. If theat is the case you probably could solve this by either proper indexing or changing the C/AL code ...

mdPartnerNL

I bit off topic but your book looks to be a good read.

As with forum topics and books information gets outdated real fast. I once was fixing a problem with a solution in a topic to later find out this solution was outdated.

Is your book usefull if you have 2009 R2 ? I really don't want to read about the indexing problems in the past with SQL. Those were expected and we waited until the release of NAV 2009 SQL to step over.

stryk

mdPartnerNL wrote:

I bit off topic but your book looks to be a good read.

As with forum topics and books information gets outdated real fast. I once was fixing a problem with a solution in a topic to later find out this solution was outdated.

Is your book usefull if you have 2009 R2 ? I really don't want to read about the indexing problems in the past with SQL. Those were expected and we waited until the release of NAV 2009 SQL to step over.

I have to admit that the current version of my book is somewhat old - it's from 2009. I'm actually updateting it already, but I'm lacking time to finalize it ... :oops:
Of course, most if its content is still valid, but few new things are missing ...

kriki

stryk wrote:

I have to admit that the current version of my book is somewhat old - it's from 2009. I'm actually updateting it already, but I'm lacking time to finalize it ... :oops:
Of course, most if its content is still valid, but few new things are missing ...

In 2 years, a lot of changes occur in NAV-technology and one (even one as Joerg) learns a lot more about SQL server in 2 years.