Clustered or not clustered
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) * 8 ) - (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
