Do you remember the Clustered Index bug of 4.0SP1 (see also http://www.mibuso.com/dlinfo.asp?FileID=594)?

In short: 4.0SP1 forgot to set the index-property “Clustered” on the primary key (the default is that the primary key is clustered). The result is that the tables were heap-tables and that is (generally) bad for performance.

The fix was to put the key-property Clustered in one of the indexes. My little tool (mentioned above) does just that.

But now, this customer was different. The Clustered-property was set, BUT in SQL, the tables were NOT clustered.

I was at a customer to do an audit because they have performance problems. I did find (and fixed) some things, but this is

another story. The important thing of this blog post is the clustered-in-NAV-not-clustered-in-SQL problem.

In the first place. How did I find this out?

Some time ago, I found a little select on http://www.extremeexperts.com/sql/Scripts/TableHasClusteredIndex.aspx, adapted it to fit my needs and ran it on the customers database.

This is the script I use (not for SQL2000 because it uses a CTE [=common table expression ; I really like those!] ):

WITH table_size as ( SELECT object_name(id) AS [TableName], rowcnt AS [Rows], (dpages * 8)/1024 AS [DataSize_MB], ((SUM(used) * - (dpages * 8))/1024 AS [IndexSize_MB] FROM sys.sysindexes WHERE indid IN (0,1) AND OBJECTPROPERTY(id, 'IsUserTable') = 1 GROUP BY id, rowcnt, reserved, dpages ), Table_clustered AS ( Select i.TABLE_NAME, Case objectProperty(object_id(i.TABLE_NAME), 'TableHasClustIndex') When 0 then 'No' When 1 then 'Yes' End as [Has Clustered Index] From INFORMATION_SCHEMA.TABLES i Where objectProperty(object_id(i.TABLE_NAME), 'IsUserTable') = 1 ) select TC.TABLE_NAME,TS.Rows,TS.DataSize_MB,TS.IndexSize_MB from Table_clustered TC inner join table_size TS on TS.TableName = TC.TABLE_NAME where [Has Clustered Index] = 'No' order by TS.Rows desc

It shows me which tables are not clustered and how many records are in the tables, ordered by the number of records to see where the biggest problems are (I don’t care (at the moment) about 0-record-tables).

I showed it the customer, opened the table designer, went to the index properties and found out that the key-property Clustered was set on 1 of the indexes (PS: only 1 clustered index per table is possible). Strange: the NAV 4.0SP1 bug didn’t even set the clustered property…..

I took a small table, made a fob of it then I imported it again: no change.

Then I removed the clustered-toggle, saved (or better: I tried to save) but I got some error because SQL could not remove the clustered constraint from the table because it didn’t exist.

I tried something else: I created a secondary key (I only had 1 key in that table) and made that the clustered index. I saved and it worked. Then I imported the original fob. The primary key became the clustered index. But doing that for about 400 tables? Too much work.

I made a native backup, renamed the company (I was working on a test DB which had the same problem!) and checked the clustered index. Nothing changed.

I restored the native backup I made and checked the clustered index. YES! The restored company had the clustered indexes fixed because the tables for the new company are created from scratch and remember that the Clustered property is set in the NAV-object.

So, it is possible to fix it doing a backup of the company and then restoring it. Quite long, but possible for most databases.

But it does NOT fix the the DataPerCompany=No companies like the Permissions-table.

Those have to be fixed directly in SSMS (at least not 400 tables to fix anymore, but only about 10).

Open the incriminated table => Indexes => Take the primary key => right-click on it => Script Index as => Drop and create to => New Query Editor Window

It creates this code:

USE [CRONUS2009SP1W1]
GO
/****** Object: Index [CRONUS International Ltd_$Item Ledger Entry$0] Script Date: 03/18/2010 21:51:11 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CRONUS International Ltd_$Item Ledger Entry]') AND name = N'CRONUS International Ltd_$Item Ledger Entry$0')
ALTER TABLE [dbo].[CRONUS International Ltd_$Item Ledger Entry] DROP CONSTRAINT [CRONUS International Ltd_$Item Ledger Entry$0]
GO
USE [CRONUS2009SP1W1]
GO
/****** Object: Index [CRONUS International Ltd_$Item Ledger Entry$0] Script Date: 03/18/2010 21:51:11 ******/
ALTER TABLE [dbo].[CRONUS International Ltd_$Item Ledger Entry] ADD CONSTRAINT [CRONUS International Ltd_$Item Ledger Entry$0] PRIMARY KEY NONCLUSTERED
(
[Entry No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO

Change the

“ADD  CONSTRAINT [CRONUS International Ltd_$Item Ledger Entry$0] PRIMARY KEY NONCLUSTERED” to

“ADD  CONSTRAINT [CRONUS International Ltd_$Item Ledger Entry$0] PRIMARY KEY CLUSTERED”

And run the script. It will create the clustered index.

Another way you can use is to create a new company in the DB. This will create the tables from scratch and also create the clustered index on them. After that you can use ara3n’s report (http://mibuso.com/blogs/ara3n/2009/12/08/baking-uptransfering-company-specific-data-through-sql-for-dynamics-nav/#comment-98) to create a script to copy all data from 1 company to the new company (BTW: I am working on my own version based on ara3n’s version but with some extra possibilities. I will publish that later as a download on Mibuso).

This report generates a script that you can open in SSMS and run. After that you can delete the original company and rename the new company to the original one.

Some advice :

-Before doing all this work, be sure to make a good backup. And remember that a backup is only good after you have been able to restore it! A backup that you can’t restore is NOT good!

-Do this when no-one (not even the NAS or webservices) is working.

One last thing: I haven’t found out what caused this in the first place. The customer told me they never have been on 4.0SP1. So if someone has an idea, write a comment.

Filed under: C/AL, NAVISION, PERFORMANCE, SQL | Make a Comment »

The title could also be “Windows 2000, XP SP2 and Vista End of Life Support”, but I found my title funnier.
The fact is that Windows 2000 is close to 10 years old and it is retiring (together with some younger versions).

What is this about : Microsoft stops supporting some windows versions. That means:no more free security updates (if your PC is connected to the internet, can you live without these?)

In short the following Windows versions go out of support:
-Windows 2000 Professional and Windows 2000 Server:13 July 2010
-Windows XP Service Pack 2:13 July 2010 (so best upgrade to SP3 or go directly to the newborn child Windows 7 [if possible])
-Windows Vista without service packs:13 April 2010 (so best upgrade to SP2 or go directly to the newborn child Windows 7 [if possible])

How does this connect to Navision?
I know that some customers are still on old NAV versions (2.60 and maybe older) on Windows 2000 (and maybe older. I hope not!).

If you have older NAV versions and Windows 2000, it might be a problem upgrading Windows 2000 to Windows Vista or Windows 7 (Can you still buy Windows XP or Windows Vista?) because Windows Vista and Windows 7 need at least 4.0SP3 with hotfix (also out of support…).

Some more info:http://blogs.technet.com/lifecycle/archive/2010/02/24/end-of-support-for-windows-xp-sp2-and-windows-vista-with-no-service-packs-installed.aspx

Filed under: NAVISION, WINDOWS | 1 Comment »

It is field 8725:”Signature” and it seems to be used in F8726:”Mobile Order Signature”.

Question: Why a post about this field?

Answer: Because it can (and sooner or later) will cause performance problems.

What happened?

A colleague calls me to tell a customer has performance problems open the sales order form. But not always.

I found out that when opening the form, the form positions on the last record, it is fast and when it positions on the first record, it is slow. Sounds that it is reading the table from the current record to the last. But still : only 60000 records should not be soooo slow (about 5 seconds to open the form) on the server they have. The server is not a monster but is good enough for its workload.

Even after opening the form different times, it remains slow but all the records should be in cache.

So let’s start SQL server profiler and start tracing what’s happening. I found the query. A cursor is created (first bad sign). I hate the FIND(’=<>’)!

And indeed in the WHERE I find “No_” >= ‘…’, so it does a clustered index seek (and if I have the first record, it means it does a scan of the table). But still this doesn’t explain the 5 seconds.

THEN I see DATALENGTH(”Signature”) as a column in the SELECT. The translation for human beings is that Signature is a BLOB! What the <…BEEP…> does a BLOB field in table 36:”Sales Header” and where is it used?

After some searching (exporting all as text and using Navision Object Text Explorer (http://www.mibuso.com/dlinfo.asp?FileID=205) I easily find where it is used (see the first line of this post).

Let’s change the field from BLOB to boolean (After my last adventure with webservices (http://mibuso.com/blogs/kriki/2010/02/17/am-i-a-detective/), I don’t disable the BLOB’s anymore, but change them to boolean.

Let’s retry to open the sales order form with first and last record and all is fast again!

I never noticed this field before (neither did my colleague), so we thought it was new in NAV2009SP1.

No, it already existed in NAV500SP1 (I didn’t check NAV500). But I never had a customer with this problem. Maybe it is caused by the combination of NAV2009SP1 and SQL2008? It is the only customer I have with SQL2008 and so many records in table 36:”Sales Header”.

A remark: I checked other tables with BLOB’s in them (make a form on table “Field” and filter on Type::BLOB). Tables “G/L Account”, “Customer”, “Vendor” have the field Picture BUT I didn’t find any function to put a picture in it. Does someone know where I can find it?

Also table Item has the field.

An advice: if you have a table that is used a lot (like Item or Sales Header), change the BLOB to Boolean and if you need the BLOB anyway, best create an extra table in which to store the BLOB. Most of the time when you GET,FIND the record, you don’t need it. So why do some extra processing for DATALENGTH(”….”)? SQL Server will be grateful if you change the BLOB to Boolean.

Filed under: NAVISION, PERFORMANCE, SQL | Make a Comment »

I found out this virtual training and thought it could be of interest for SQL tuners.
http://www.vconferenceonline.com/shows/spring10/quest/register/multireg.asp

The bad thing is: it starts at 07:45 ET-time.

That means  13:45 CET. And it stops at 23:30!

Anyway, I am following it with the moka (to make italian coffee) nearby.

Filed under: PERFORMANCE, SQL | Make a Comment »

Maybe you ask: what title did he invent now? It has nothing to do with NAV or SQL

Oh, yes! It is about both!

Here it comes.

The crime scene: NAV2009 classic client with NAV Jobqueus that launches a job in a Webservice and goes on with other jobs (thanks to ara3n for his blog that helped me a lot to set it up: http://mibuso.com/blogs/ara3n/2009/01/28/running-job-queue-through-nav-web-service-for-unlimited-companies/). The idea was not really using 1 NAS for multiple companies but more using 1 NAS for multiple jobs at the same moment.

The crime: Each night the same job was started through JobQueue and the webservice did receive the call and started working but never finished its work. So the job died somewhere. Was it suicide, an accident (like a bug in webservices, because when launching the job manually went without errors) or someone murdered the job (delete it’s session ID)?

Suicide was quite improbable. The job has no feelings, no Artificial Intelligence and I didn’t put any suicide logic in it (In some cases I have build suicide logic in a job!).

The big problem was that we didn’t find any tracks (except a missing finishing message). (Maybe someone murdered the job and hid the tracks?) The problem is also that webservices doesn’t write anything to the Windows eventlog in case of a MESSAGE or an ERROR.

So I decided to put some bugs (microphones, not logical errors) in the job in the form of messages to some log table (with after it a COMMIT). The job is full of COMMIT’s to avoid that it blocks too much for too long, so some extra COMMIT won’t hurt (if put in the correct places!). Also I changed the webservice call in the NAS, so the NAS waits for the end of the webservice job and if it errors out, the JobQueue receives the error and writes it in the job log.

I also set up an extra job to be run directly by the NAS as a failsafe measure. In case the webservice crashes, the NAS does the job at a later hour.

The day after: I check the JobQueue log and find the error that let the webservice crash : “Table Item has been changed by another user”. It just became a murder case! But who is the murderer? And who is so crazy to do that every night? Could we have a serial killer in our system?

First: check the item table. No track of changes. Same object datetime. I confront the text-version with the text-version on the development server : the same. Last change was 1 or 2 months ago. Strange!

And the second job, run directly by the NAS not even an hour later has no problems.

With the logs I have I can pinpoint exactly where the problem happened: “tmpItem.DELETEALL(FALSE);”.  And just before that DELETEALL, I have “tmpItem.RESET;” (Did I tell you I am a big fan of explicit initialization of variables? It never leaves doubt if a variable/temptable has a value/data or is blank/empty).

At that same point in the code, I also have some other temptables (item-related) on which I do the same action. Lets inverse the tables and restart NAS and webservices.

First night : all OK.

Second night: Jack The Ripper V2.00 strikes again! The webservice dies again with the same error-message and ON THE SAME TABLE and not on the temptables I put before tmpItem.

Probably not Jack but a bug in Webservices. But why?

So, what is so special about the item table? Something is! I disabled the field “Picture “! It is not used and we have a lot of items and by disabling it, I can get some extra performance.

But what can make the webservice say the item-table has been changed? Nothing is touching it? Or is there? Well, we have an indexrebuild each night some hours after the incriminated job. That might explain why after the restart of the NAS and webservices everything went fine the first night (restart during day. That night FIRST the job and after that the indexrebuild and then the second night : ERROR!).

So I try the deactivate the rebuild for some days. And all that time the webservice works fine!

Conclusion: An accident (a disabled BLOB in a table and a bug in webservices)! And the ‘murderer’ (the indexrebuild) takes the opportunity to murder the accident-victim, turning it into a murder case anyway!

Didn’t this sound like some detective work?

Now back to the real world!

We have to fix this problem. First idea : before the indexrebuild we stop the webservice and after the indexrebuild, we start it again. But the sysadministrator doesn’t like the idea.

Another idea : if we try to make the item table ‘normal’ again? I enable the Picture field AND CHANGE ITS TYPE TO BOOLEAN. Why Boolean? Well both BLOB and Boolean start with a B and a Boolean is small (Boolean becomes TinyInt, so the performance penalty remains small/non existent.

Done that, I enable the rebuild again and now we wait for a few days and check how it goes.

After a few days, everything is still working perfectly.

Customer happy because it works.

My company happy because we fixed it.

I happy because I learned something new.

Microsoft happy because I provided them a workaround.

Filed under: CLASSICCLIENT, NAS, NAVISION, PERFORMANCE, SQL, WebService | 3 Comments »

I read in some blog it was a 64bit only release, but that doesn’t seem to be true (You can download a 32-bit CTP HERE).

Some  other links:

http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx

 http://www.brentozar.com/archive/2010/01/sql-server-2008-r2-release-date-may-2010/

 http://www.brentozar.com/archive/2009/11/sql-server-2008-r2-pricing-and-feature-changes/

http://www.brentozar.com/archive/2009/09/sql-server-2008-r2-frequently-asked-questions/

Filed under: SQL | 4 Comments »

Have you been at a customer where ONLY Windows logins are allowed?

So launching NAV (or SQL Server Management Studio) to connect to their database is impossible.

Can you connect to their database if you don’t have a remote terminal or citrix to some computer in their domain?

Yes, you can!

Microsoft has a little (sysinternals) tool (shellrunas.exe) that makes it possible.

You can download it from http://technet.microsoft.com/en-us/sysinternals/cc300361.aspx.

To install it, just run “Shellrunas /regnetonly”.

This adds the option “Run as different user (netonly)” under the rightmouse click on a file in explorer. So when you want to run you finsql.exe, fin.exe, Ssms.exe, … use this trick and you can use Windows-logins from your own portable even if you are NOT in your customers domain!

Filed under: NAVISION, Varia, WINDOWS | 1 Comment »

I just read something very interesting:

Backup compression is available in SQL Sever 2008 R2 Standard!  Read more

That is very good news. No more need for Enterprise version for backup compression!

This is the feature I like the most in SQL 2008!

It speeds up backups (most processors are idle anyway) and it takes less time to move the backups over the network.

Filed under: Uncategorized | 3 Comments »

And now that I am busy with recursion:

-- recursive CTE to generate numbers like the NAV date table

SELECT DATEADD(DAY,-1,[The Date])
FROM DateTable
WHERE DATEADD(DAY,-1,[The Date]) >= @from
)
SELECT *
FROM DateTable
ORDER BY [The Date]
OPTION (MAXRECURSION 0)
See also the forum

Filed under: NAVISION, SQL | 1 Comment »

If we want to loop on integers in NAV, we can use the integer table and put some filters on it.
The problem is that in SQL, we don’t have such a table.

It is possible to create the table with a recursive CTE (Common Table Expression).

If you want, you can also put the select into a stored procedure and use it.

This is the select:
-- recursive CTE to generate numbers like the NAV Integer table

SELECT [The Integer] - 1
FROM numbertable
WHERE [The Integer] - 1 >= @from
)
SELECT *
FROM numbertable
ORDER BY [The Integer]
OPTION (MAXRECURSION 0)

See also the forum.

Filed under: NAVISION, SQL | Make a Comment »