Typical SQL tuning problem.

Mauddib

So Still on Version 3.6 (5,00) here.

The questions I have before you read the following are:

1) What else can I try and
2) How can I tell what index SQL is actually using in the live system, to see if it matches what the execution guide tells me it will be using?

Having a bit of trouble with performance on tables 17.

From form 16 I drill into an account which brings up its postings in table 17. No filters are on in filters or flow filters. So the only filter is the account no.

If you go to the first or last record the system takes an insane amount of time to do this.

Clicking between any other records is ok, its only when going to the first and the last.

In the profiler I can see the statement:

SELECT  * FROM "navision"."dbo"."mycompany$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND  "G_L Account No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_">@P4 ORDER BY "G_L Account No_","Posting Date","Entry No_"'

Clearly this statement returns a null set because I have already gone to the extreme record so there is nothing with >@P4 but how now.

I ran an execute plan on this. I also ran the statement manually in a query. 1, it runs very fast which is odd. Why does it run fast here and not when navision runs it? 2, when I look it uses index $3 which on our system, of all things, appears to be "Document No.", "Posting Date". So why its faster here AND uses a completely weird index I am baffled by.

On the suggestion of another user of this board on another site (dont think he posted it here) I attempted to used a plan guide using the following:

EXEC sp_create_plan_guide
  @name =  N'guide1',
  @stmt = N'SELECT  * FROM "navision"."dbo"."mycompany$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND  "G_L Account No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_">@P4 ORDER BY "G_L Account No_","Posting Date","Entry No_"',
  @type = N'SQL',
  @module_or_batch = NULL,
  @params = N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int',
  @hints = N'OPTION (RECOMPILE)'

This had no discernible effect, nor am I expert enough to find out if it actually did change anything. I am not well versed in the vagaries of the SQL profiler as yet.

At this point I am at a loss. Can anyone suggest anything or if you need more info just ask.

garak

What for a version do you use 4.xx, 3.xx, 5.xx?
And which SQL Server engine?


The Planguide you create with the sql query window.

EXEC sp_create_plan_guide
  @name =  N'Planguide_For_GLEntryLookup',
  @stmt = N'SELECT  * FROM "navision"."dbo"."mycompany$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND  "G_L Account No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_">@P4 ORDER BY "G_L Account No_","Posting Date","Entry No_"',
  @type = N'SQL',
  @module_or_batch = NULL,
  @params = N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int',
  @hints = N'OPTION (RECOMPILE)'

With a own PlanGuid, the server doesn't use a Plan that is in the cache and maybe not optimal, so it's use for the sqlstatement the own PlanGuide. So this is also a option to tune the system. But also here, you must know what you do

These you can see in Managemene Studio under PlanGuides.
If you use SQL 2005 SP2 (Build 9.0.3200) u can also use INDEXHINT / RECOMPILE.
But for this you need >= NAV 4.03 HF 6. And the table $ndo$dbconfig (search for it)

Regards

stryk

Hi!

If the performance of a query is significantly different wether you execute it via NAV or ad-hoc via Management Studio this could indicate a problem related to the "Parameter Sniffing" behavior of SQL Server (search the forum - and/or google it - about "Parameter Sniffing").

"RECOMPILE Hints" could solve this problem, but bear in mind this requires SQL Server 2005 Build 9.0.3200 or higher.
If the "Plan Guide" isn't working you could try to do it via the "$ndo$dbconfig" tabel (see "Installation & System Management (SQL)":

CREATE TABLE [$ndo$dbconfig] (config VARCHAR(1024))
GRANT SELECT ON [$ndo$dbconfig] TO [public]
GO
INSERT INTO [$ndo$dbconfig] VALUES('UseRecompileForTable="G/L Entry"; Company="mycompany";RecompileMode=1;')

To answer your questions:

1) What else can I try

You could try to change the "Clustered Index" of this table to "G/L Account No., Posting Date, Entry No." - in many cases this could help, too.

2) How can I tell what index SQL is actually using in the live system, to see if it matches what the execution guide tells me it will be using?

In SQL Profiler you could monitor the Event "Performance - Showplan XML" which would also display which Indexes were used.

Regards,
Jörg

kapamarou

First, I hope that the key you are using is Maintained in SQL (Just check it to make sure)...

What I would do would be the following. Apply the same filters and sorting Key directly on the table (Run the table from the object designer). If it is fast there then there is something wrong with your form. Before going to the profiler I ALWAYS use Navision's Client Monitor first, to see where my problems are. Use the monitor and check the Time Elapsed entries to see what might be delaying. Then post back your findings and we can investigate further...

garak

a forgott, you can also active in the SQL Profiler the Performance - Showplan XML option. so you can see, what for an index the server use.

Regards

Mauddib

Hi, to answer some of the questions above:

Kapamarou and Herr Stryk, I will try the things you suggest and report back. In the interim here are answers as I was asked:

Garak, I said my version above. “So Still on Version 3.6 (5,00) here.”. That is Version 5 executables running on a 3.6 codebase.

Herr Stryk SQL Server is 9.0.3200. It was actually your post on another site to which I referred in my above post when mentioning the plan guides. Thanks for that.

Herr Stryk again, I did try the clustered index you suggested. It made no improvement on the speed of the read. It did however do HORRIBLE things to the speed of the write. To the point that one user in our test environment posted a sales invoice, it hung for about 5 minutes, then Navision just disappeared off screen with no error message. Crashed out. So I returned the clustered index to Entry No.

Kapamarou yes, the key for the index I am sorting by is maintained. The key it appears to want to use in the execution plan, the strange option of “Document No.”, “Posting Date” is also maintained. Here is the current config of the keys on this table:

1) Entry No.
2) G/L Account No.,Posting Date
3) G/L Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code,Close Income Statement Dim. ID,Posting Date
4) Document No.,Posting Date
5) Transaction No.
6) Close Income Statement Dim. ID
7) Posting Date

Here are the indexes for the keys above in order:

1) “”
2) G/L Account No.,Posting Date
3) “NOT MAINTAINED”
4) Document No.,Posting Date
5) Transaction No.
6) Close Income Statement Dim. ID
7) Posting Date

Mauddib

Ok some little new information.

Firstly to answer the question above about the form. The problem is on both the form and the table directly in Object Designer.

Secondly the live server was restarted over night. The search was slower than ever before using the Plan guide mentioned above. We dropped the plan guide. Things seemed fast, with only tiny delays at the first and last record, at first for all accounts. After a few minutes it was back to being slow before like before the plan guide.

I turned on the Profiler for "Performance - Showplan XML" as suggested. This is a german server so I am guessing that the phrase "Knoten-ID" is telling me which index it used???

If so things here are weird and might be the source of my problem but I do not understand it. As you can see above we do not maintain an SQL index for "G/L Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code,Close Income Statement Dim. ID,Posting Date".

This means on SQL management studio I can see index $0, $1, $3, $4, $5, $6

When I run the query manually in management studio its FAST and the plan it shows me shows "Knoten ID" of 3. This is Document No. Posting Date. Makes no sense to me, but its fast so why not???

However when I run it in navision with "Performance - Showplan XML" I see Knoten ID 2!! There is no $2. So why is it looking for it at all? I can see in the monitor it then starts doing a "Clustered Index Insert". What is this? What is inserting and where? Is this the source of the delays? Why is it looking for an index that is no longer maintained?

stryk

First of all: please don't call me "Herr Stryk" - it's just Jörg :P

Sorry that the "Clustered Index Change" did not work, as mentioned it works in most cases, but not in all (depend on several things).

Did you try the RECOMPILE Hints via "$ndo$dbconfig"? Any success?

If you monitor the "Showplan XML" event with Profiler you should see the same kind of "picture" as in "Management Studio". Here on the right side (the start of the operation) you should see something like "Index Seek" or "Index Scan". If you simply move the mouse-curser on this "picture" a tooltip should pop up, showing the "Object"; here you should see the "$"-something which tells the index name.

stryk

... and: which Build of NAV do you use? With 5.0 ist should be 26810
or higher; with 5.0 SP1 it's 27191 or higher ...

Mauddib

Sorry, its a habit with me. I am currently learning german very slowly. Everyone is Herr and Frau to me at the moment because I find Sie much easier grammatically than du at the moment

At present I really want to know what a clustered index insert is and why it is happening?

The picture I get in the profiler is:

Clustered index insert 75%

---

> compute scalar 0%

---

> Clustered Index Seek 25%

The picture I get in the plan for the same query is:

SELECT 0%

---

> Nested Loops (Inner Join) 0%

---

> Index Seek 50% and Schlüsselsuche 50%

Build was 24199, now on 24652 without any change. Not sure where to get a later build at this time.

Tried the following code with no effect, the profilter still shows the Clustered Index Seek 25% performed on object $0:

INSERT INTO [$ndo$dbconfig] VALUES
('IndexHint=Yes;Company="mycompany";Table="G/L Entry";Key="G/L Account No.","Posting Date";Search Method="-+";Index=1')

As the profilter shows the sort by including Entry No. I tried this too:

INSERT INTO [$ndo$dbconfig] VALUES
('IndexHint=Yes;Company="mycompany";Table="G/L Entry";Key="G/L Account No.","Posting Date","Entry No.";Search Method="-+";Index=1')

Mauddib

And finally I tried:

INSERT INTO [$ndo$dbconfig] VALUES('UseRecompileForTable="G/L Entry"; Company="mycompany";RecompileMode=1;')

With no effect on the speed or on the results in the XML performance monitor. It still takes a long time and it still goes for $0

Mauddib

The only other information I have is that the slow queries are ONLY ever the ones with DESC in it. Such as:

ORDER BY "G_L Account No_" DESC,"Posting Date" DESC,"Entry No_" DESC '

Navision runs the SAME query only without DESC and it is fast and it uses the proper execution plan with Index seek and Schlüsselsuche etc just like in the management studio plan.

When Navision generates a query with DESC however, such as it does when you go to an extreme record, then the problems happen.

I did find another post where someone agrees the latest build kills off the DESC queries. I will try and get my hands on the latest build and will report back

Mauddib

Ok latest information.

I upgraded to the latest build, or what I think is the latest build for 5.0

Build 25359

Someone on another thread mentioned that this removes the problematic DESC queries.

This is not so. It still does them.

What it does do however is reintroduce the SQL keyword "WITH INDEX ($x)".

So what HAS been happening to me is that SQL will see the DESC and will for some reason go straight to the Clustered index, not the maintained index for that key.

What happens NOW is that SQL does work with the maintained index, with a certain amount of time spent on the Clustered index, which matches the execution plan I see in Management studio.

So it APPEARS this may solve my problem but I am only able to test all of this in our test system which is smaller and less active than live. I will not know if this is a success until I upgrade live which is a big job. The new build MUST update the database, almost like moving to a new version. Thankfully we do not HAVE to then upgrade all clients, our current build is still backward compatible with the upgraded database.

IF anyone has any other information, opinions, feedback, do's or dont's on what I have just said then let me know. Am I talking out my rear or does anyone understand what I have just said

stryk

Mauddib wrote:

I upgraded to the latest build, or what I think is the latest build for 5.0 Build 25359

This is not the most recent build for NAV 5.0; that wold be 27241 (see http://dynamicsuser.net/blogs/waldo/archive/2008/12/07/platform-updates-overview-3-70-b-nav2009-rtm.aspx)

Mauddib wrote:

What it does do however is reintroduce the SQL keyword "WITH INDEX ($x)".

Well, in a previous posting you said you have applied such an Index Hint", that is what happens here. (if this is NOT your index hint, or if all queries have this hint, then probably this version is affected by "out-of-the-box-index-hinting" which should be disabled, else performance will completely vanish soon).
With "Index Hinting" you actually overrule SQL Server decisions - which is in 99% a very bad idea. I prefer to set "RECOMPILE Hints" as this gives the SQL Server the chance to "think again", creating a better Execution Plan.
The result should be the same.

Mauddib

Yes it appears that version 25359 is out of the box index hinting as you suggested. It has nothing to do with me or anything I have done or changed at this time.

stryk

You could disable it with this:

CREATE TABLE [$ndo$dbconfig] (config VARCHAR(1024))
GRANT SELECT ON [$ndo$dbconfig] TO [public]
GO
INSERT INTO [$ndo$dbconfig] VALUES('IndexHint=No')

Mauddib

True, but surely I will then be back at square 1 with the same problems as before

stryk

Actually YES. But then again, I would try two things:

First: Applying a "RECOMPILE Hint" via "$ndo$dbconfig" (try mode 1 (Form); if not succesful then 3 (All))

INSERT INTO [$ndo$dbconfig] 
VALUES('UseRecompileForTable="G/L Entry"; Company="MyCompany";RecompileMode=1;')

In the Profiler you should see the quéry ending with "OPTION (RECOMPILE)"

If this does not help: Applying an "Index Hint" via "$ndo$dbconfig" like

INSERT INTO [$ndo$dbconfig] VALUES
('IndexHint=Yes;Company="MyCompany";Table="G/L Entry"; Key="G/L Account No.","Posting Date";Search Method=;Index=')

Regard that "SearchMethod" and "Index" are blank.
In the Profiler you should see the clause "WITH (INDEX($...))".

And just to make sure: these "hints" are read by the C/SIDE client when starting up, hence any changes require a restart of the client!

Mauddib

Sorry to ask this again but I am still curious. What is this "Clustered index insert" that is being performed and takes up 75% of the execution plan of the statement that is causing me all my troubles?

What is such an insert? It is inserting into tempdb.dbo.CWT_primarykey

stryk

Mauddib wrote:

Sorry to ask this again but I am still curious. What is this "Clustered index insert" that is being performed and takes up 75% of the execution plan of the statement that is causing me all my troubles?

What is such an insert? It is inserting into tempdb.dbo.CWT_primarykey

See http://technet.microsoft.com/en-us/library/ms190962.aspx
Actually this is the insertion of a record into a table.

But you should not worry about the percentage shown for that operation (75%) as this just displays the relation between this and other operation, means 75% of the "effort" to process the query was spent for this operation - here it is necessary what this "effort" is, speaking of CPU, Duaration Reads and Writes ...