Not In Vs Not Exists

The Not in and Not exists clauses are quite similar except for one major difference which can be illustrated using the example below:

EMP_NBR	EMP_NAME	MGR_NBR
1	DON	5
2	HARI	5
3	RAMESH	5
4	JOE	5
5	DENNIS	NULL
6	NIMISH	5
7	JESSIE	5
8	KEN	5
9	AMBER	5
10	JIM	5

Now, the aim is to find all those employees who are not managers. Let’s see how we can achieve that by using the “NOT IN” vs the “NOT EXISTS” clause.

SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );

COUNT(*)
———-
0

SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );

COUNT(*)
———-
9

Now there are 9 people who are not managers. So, you can clearly see the difference that NULL values make and since NULL != NULL in SQL, the NOT IN clause does not return any records back.

There is no value entry within the Filter

Well this is the error we received at one of the customer sites when they were executing the famous adjust cost batch job. Did some real head breaking into this one and discovered that when the Inventory setup has been set as:
1. Average Cost Calc. Type = Item
2. Average Cost Period = Month

The error was something like:
There is no Value entry within the Filter
Filters : Item No: 1100053749, Valution Date: 01/01/09..31/12/08

On debugging we found the error was being caused by an FINDSET; statement which was issues in the following Codeunit:

Codeunit : 5895, Inventory Adjustment
function : AvgValueEntriesToAdjustExist(OutbndValueEntry;ExcludedValueEntry,AvgCostAdjmtEntryPoint)


Got around the issue after a detailed profiling of the code and found that when the valuation date changes and a new filter is being evaluation in the code it somehow caused caused a wrong filter on the Value Entry Table the code snipped was as shown below

IF "Valuation Date" > CalendarPeriod."Period End" THEN BEGIN
CalendarPeriod."Period Start" := "Valuation Date";
AvgCostAdjmtEntryPoint.GetValuationPeriod(CalendarPeriod);
END;

How i managed to get around the issue was to add one line here as shown below:
IF "Valuation Date" > CalendarPeriod."Period End" THEN BEGIN
CalendarPeriod."Period Start" := "Valuation Date";
AvgCostAdjmtEntryPoint."Valuation Date" := "Valuation Date";
AvgCostAdjmtEntryPoint.GetValuationPeriod(CalendarPeriod);
END;

I finally concluded that the problem was encountered when a value entry record was found for a new financial year (2009 in this case) and there were no records for the previous year end (December 2008).

If we look at the code GetValuationPeriod(CalendarPeriod) in Table 5804 "Avg. Cost Adjmt. Entry Point" at the end of the function the following lines were setting the values to be used to set the filter :

//*****************************************************************************
IF FiscalYearAccPeriod."Starting Date" IN [CalendarPeriod."Period Start"..CalendarPeriod."Period End"] THEN
IF "Valuation Date" < FiscalYearAccPeriod."Starting Date" THEN
CalendarPeriod."Period End" := CALCDATE('<-1D>',FiscalYearAccPeriod."Starting Date")
ELSE
CalendarPeriod."Period Start" := FiscalYearAccPeriod."Starting Date";
//*****************************************************************************
All we did was to set the valuation date so that it does not evaluate to less then the Financial Period."Start Date"

Navision Filters

We all know the basics of Filters now lets turn to some advanced ways of using and understanding filters.

? which can be used while building a filter to substitute one unknown character pretty much like the Wildcard Characters used in DOS days

@ which can be used to ignore the case of the text being searched for
thus @co* would search for anything beginning with Co , cO, CO or co followed by any character as indicated by an asterix.

There has been some other types of filters which are referred to in Navision code with commands like FIND('=><') OR FIND('=<>')

1. '=><' when the above three expressions are combined it would stand for. If equal '=' rec is not found, search for a record which is smaller '<' and if smaller rec is not found search for a rec which is bigger '>' basically means find anything which was used in olden days to check if the rec filter was empty or not.

2. '=<>' this would translate to find something "equal to" or "not equal to" which means "find anything" similar to the command above

Today we also have ISEMPTY commmand to determine if a recordset is empty.

Procedure to Update Statistics for All indexes of a Table

sp_updatestats (Transact-SQL)
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.


Find the user defined procedures to run it for a table.

/*
select 'usp_update_statistics [' + name + ']' + char(13) + char(10)
+ 'go' + char(13) + char(10)
from sysobjects where type = 'U'
and not ascii( right ( [name], 1 ) ) between 48 and 57
*/


Alter procedure usp_update_statistics
@Table_Name varchar(255)
as
declare @Index_Name varchar(255)
declare @SQL nvarchar(500)


declare index_cursor cursor for ---Getting index name
select name from sysindexes
where id = object_id(@Table_Name)
and indid > 0
and indid < 255
and (status & 64)=0
order by indid

open index_cursor

fetch next from index_cursor
into @Index_Name

while @@fetch_status=0
begin
set @SQL = ''
set @SQL = @SQL + 'update statistics [' + @Table_Name + '] ( [' + @Index_Name + '] )'
set @SQL = @SQL + ' WITH FULLSCAN, NORECOMPUTE'
exec sp_executesql @SQL

print @SQL
print ''

fetch next from index_cursor
into @Index_name
end
close index_cursor
deallocate index_cursor
print 'Complete.'

sp_cursorfetch

Had this terrible issue with Navision Value Entries form. Whenever the form was opened from the Item Card Navision would freeze did a profile in SQL for all the SQL statements and found that the system was taking time to execute a command which started like sp_cursorfetch.

Some RND and found that the sp_cursorfetch is implemented by the database library to manage the cursors at the server side. Before a cursor fetch can be issue a cursor open statement has to be declared which would contain the base statement for the cursor. Once i found the statement being used for the cursor i did a execution plan display and found that it was not using the correct index.

I then updated the statistics for the index using the Update Statistics command we all know that statistics decide the selectivity of an index once this was done the cursor started using this index and the issue was resolved.

Format Strings

Format function with Navision is really powerful all we need to do is to understand in detail how the format string is built

Below is one example what i wanted to do was to generate a file name in the format DDMMYY-HHMMSS.txt also i wanted DD (Date) to be two characters and MM (Month) to be padded with 1 zero if it was 1 character in length. Amazingly the entire thing could be achieved just by one amazing function FORMAT

FORMAT(CURRENTDATETIME,0,'<FillerCharacter,0><Day,2><Month,2><Year><Hours24,2><Minutes,2><Seconds,2>')

All the different parameters that can be used with FORMAT function are well documented in the online help.

Browse For Folder in Navision

Navisions implementation of Common Dialog Box does not cater to Browse for folder. There is a small workaround for this although it is not very neat but it does the work. Below is the code for the same this is a function which returns the FolderName and DefaultFolderName is the parameter to the function.


IF DefaultFolderName = '' THEN
DefaultFolderName := 'C:\Folder'
ELSE
DefaultFolderName := DefaultFolderName + '\Folder';

FolderName := CmmDlg.OpenFile('Select Folder'
, DefaultFolderName
, 4
, 'All File (*.*)|*.*'
, 0 );

//Truncate the file name from the path
Ctr :=STRLEN(FolderName);
WHILE Ctr > 0 DO BEGIN
IF COPYSTR(FolderName, Ctr, 1) = '\' THEN BEGIN
FolderName := COPYSTR(FolderName, 1, Ctr -1 );
EXIT;
END;
Ctr -= 1;
END

The only thing we are doing here is that we are providing a default filename in the browse window thus the open button is enabled without waiting for the user to select a file name.

Arabic Data

Came across this client who wanted to capture the item descriptions in Arabic in addition to the english descriptions. Changing the System Keyboard alone does not help for this the following steps are involved.

1. Install Supplemental Language Support for this go to Control Panel -> Regional and Language options -> Languages Tab select the Install files for complex script and right to left languages.
2. In the input languages click on the details button and add arabic keyboard support.
3. Change the Language for Unicode Programs this can be changed from Control Panel -> Regional and Language options -> Advanced Options Tab.
4. Once the language has been changed the database collation should be change to support the new code page. For this use the alter database option -> Collation Tab and select the collation for the arabic language.

Now you are all set to save english and arabic in the Navision database.

OnCreateHyperLink and OnHyperLink

Ever wondered about these triggers in navision and what they are meant for. Well Navision offers a facility to create links to the different objects within it. Using this feature a hyperlink could be created for a Form in Navision from the desktop. Creation of the hyperlinks is simple open the desired Form and then use the File -> Send to option to place a hyperlink on the desktop.

When the send to desktop option is used the first thing the option does is to call the OnCreateHyperlink trigger with the URL as the parameter. The URL parameter can be modified in the trigger giving one a control over what needs to be placed in the URL string. The OnHyperLink trigger is called when the form is accessed using this link and the URL is passed in the trigger.

PrintOnlyIfDetail Property in Navision Reports

Figured out that this propery does not work if for a Dataitem if the child DataItem does not have a section defined in the section view. The way around was to create a section and use CurrReport.ShowOutPut(False) to suppress the section as well as make the PrintOnlyIfDetail property work in the desired manner.

Adjust Exchange Rates Dimension Error

The adjust exchange rates batch job calculates and post the exchange gain/loss entries due to transactions in foreign currency. In doing so the batch job is required to pass a no of entries into the G/L.

In cases where the G/L has been tightly bound by dimension rules it can sometimes fails in this case it is necessary to understand the entries that this batch job passes and accordingly have the rules in place.

The Batch job as mentioned in the help creates one entry per currency per posting group of the banks defined. Because it consolidates the entries it cannot pick the dimensions from the source transactions. Thus it picks the dimensions which are defined as the default dimensions on the bank card. Thus we need to make sure that the dimension rules are met by these default dimensions. The entries are passed to the control accounts defined in the posting groups for the banks and the exchange gain or loss accounts.

In case of adjusting the Customer and Vendor Accounts it passes entries to the control accounts and a balancing entry is passed to the Exchange gain or loss accounts as configured in the the currency setup.

Columns in a Primary Key Index

Query to get the columns in a primary key for a table

select object_name(SI.id) tableName, SI.name indexName, SC.name
from sysindexes SI
inner join sysindexkeys SIK
on SIK.id = SI.id
and SIK.indid = SI.indid
inner join syscolumns SC
on SC.colid = SIK.colid
and SC.id = SI.id
where 1=1
and object_name(SI.id) = 'SalesHeader'
and SI.status in ( 2066 , 2048 )

sysindexes (T-SQL)

Contains one row for each index and table in the database. This table is stored in each database. This same table can be used to identify clustered indexes and non-clustered indexes the clustered index always has the indid = 1 and a non-clustered index will have an indid > 1

Column name	Data type	Description
id	int	ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.
status	int	Internal system-status information:
		1 = Cancel command if attempt to insert
		duplicate key.
		2 = Unique index.
		4 = Cancel command if attempt to insert
		duplicate row.
		16 = Clustered index.
		64 = Index allows duplicate rows.
		2048 = Index used to enforce PRIMARY KEY
		constraint.
		4096 = Index used to enforce UNIQUE constraint.
first	binary(6)	Pointer to the first or root page.
indid	smallint	ID of index:
		1 = Clustered index.
		>1 = Nonclustered.
		255 = Entry for tables that have text or image
		data.
root	binary(6)	For indid >= 1 and <>root is the pointer to the root page. For indid = 0 or indid = 255, root is the pointer to the last page.
minlen	smallint	Minimum size of a row.
keycnt	smallint	Number of keys.
groupid	smallint	Filegroup ID on which the object was created.
dpages	int	For indid = 0 or indid = 1, dpages is the count of data pages used. For indid=255, it is set to 0. Otherwise, it is the count of index pages used.
reserved	int	For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data. For indid = 255, reserved is a count of the pages allocated for text or image data. Otherwise, it is the count of pages allocated for the index.
used	int	For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data. For indid = 255. used is a count of the pages used for text or image data. Otherwise, it is the count of pages used for the index.
rowcnt	binary(8)	Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rowcnt is set to 0.
rowmodctr	int	Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
soid	tinyint	Sort order ID that the index was created with. 0, if there is no character data in the keys.
csid	tinyint	Character set ID that the index was created with. 0, if there is no character data in the keys.
xmaxlen	smallint	Maximum size of a row.
maxirow	smallint	Maximum size of a nonleaf index row.
OrigFillFactor	tinyint	Original fillfactor value used when the index was created. This value is not maintained; however, it can be helpful if you need to re-create an index and do not remember what fillfactor was used.
reserved1	tinyint	Reserved.
reserved2	int	Reserved.
FirstIAM	binary(6)	Reserved.
impid	smallint	Reserved. Index implementation flag.
lockflags	smallint	Used to constrain the considered lock granularities for an index. For example, a lookup table that is essentially read-only could be set up to do only table level locking to minimize locking cost.
pgmodctr	int	Used to track the number of pages that have changed in an index.
keys	varbinary(816)	List of the column IDs of the columns that make up the index key.
name	sysname	Name of table (for indid = 0 or 255). Otherwise, name of index.
statblob	image	Statistics BLOB.
maxlen	int	Reserved.
rows	int	Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rows is set to 0. Provided for backward compatibility.

syscolumns (T-SQL)

Contains one row for every column in every table and view, and a row for each parameter in a stored procedure. This table is in each database.

Column name	Data type	Description
name	sysname	Name of the column or procedure parameter.
id	int	Object ID of the table to which this column belongs, or the ID of the stored procedure with which this parameter is associated.
xtype	tinyint	Physical storage type from systypes.
typestat	tinyint	For internal use only.
xusertype	smallint	ID of extended user-defined data type.
length	smallint	Maximum physical storage length from systypes.
xprec	tinyint	For internal use only.
xscale	tinyint	For internal use only.
colid	smallint	Column or parameter ID.
xoffset	smallint	For internal use only.
bitpos	tinyint	For internal use only.
reserved	tinyint	For internal use only.
colstat	smallint	For internal use only.
cdefault	int	ID of the default for this column.
domain	int	ID of the rule or CHECK constraint for this column.
number	smallint	Subprocedure number when the procedure is grouped (0 for nonprocedure entries).
colorder	smallint	For internal use only.
autoval	varbinary(255)	For internal use only.
offset	smallint	Offset into the row in which this column appears; if negative, variable-length row.
status	tinyint	Bitmap used to describe a property of the column or the parameter:
		0x08 = Column allows null values.
		0x10 = ANSI padding was in effect when varchar or varbinary columns were added. Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns.
		0x40 = Parameter is an OUTPUT parameter.
		0x80 = Column is an identity column.
type	tinyint	Physical storage type from systypes.
usertype	smallint	ID of user-defined data type from systypes.
printfmt	varchar(255)	For internal use only.
prec	smallint	Level of precision for this column.
scale	int	Scale for this column.
iscomputed	int	Flag indicating whether the column is computed:
		0 = Noncomputed
		1 = Computed
isoutparam	int	Is whether the procedure parameter is an output parameter:
		1 = True
		0 = False
isnullable	int	Is whether the column allows null values:
		1 = True
		0 = False

sysindexkeys (T-SQL)

Contains information for the keys or columns in an index. This table is stored in each database.

Column name	Data type	Description
id	int	ID of the table
indid	smallint	ID of the index
colid	smallint	ID of the column
keyno	smallint	Position of the column in the index

Reading Varbinary Data

We are working on a application which needs to read data from a SQL table and then create an XML packet out of it did not want to use the XML capabilities of SQL as we has some specific requirements for the data. When we came across timestamp values we could not read them in the Ado.Recordset as the datatype was binary then came across this powerful function which would do the conversion for us.

master.dbo.fn_varbintohexstr( @@DBTS)

Windows Scheduled Task

We were in the process of testing a script we had written to automate sage and insert some data reading if from a text file. Later the requirement was to read this data automatically on a scheduled basis for scheduling we suggested using Windows Scheduled Tasks. We tested the script working perfectly on the local machine and then using Remote Desktop we ported the exe to server and tried to test but the script would not do anything we later found that the exe was working fine the only problem is that when a task being scheduled in windows needs to interact using GUI is always does so on the console session.

Took quite some time to figure this out this is a handy information.

NewEnum() As IUnknown

Well started this new project to use the Collections objects in VB6.0 after i created my collections objects using the VB class builder i realized that i could write a script to generate the code for the class which i did so now it was easy whenever i made a class i used my code generator application to generate the code and paste it as a text in the VB class. Then suddenly i noticed that the for...next loop had stopped working in the classes.

The problem is that the NewEnum() as IUnknown is a special function and should be marked with the ProcedureID of -4 for it to function the properties for each procedure can be invoked and changed using the Tools -> Procedure Attributes.. option.

Using PostMessage API

Struggled with this for such long. Basically i was trying to use the PostMessage API to send some text to an inactive window without the focus i was trying to test this out with Notepad but the thing would not work when ultimately i noticed that the edit part of notepad is a different window thanks to spy++ below is the code

Dim hWindow As Long, hNotepad As Long

hNotepad = FindWindow("notepad", vbNullString)
hWindow = FindWindowEx(hNotepad, 0&, "edit", vbNullString)
Call SendText(hWindow, "Hello")

Public Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Public Const WM_KEYDOWN = &H100
Public Const WM_KEYUP = &H101
Public Const WM_CHAR = &H102


Public Sub SendText(hWnd As Long, Text As String)
Dim I As Integer

If hWnd = 0 Then Exit Sub

Dim zwParam As Long
Dim zlParam As Long
Dim xwParam As Long ' used For WM_CHAR

For I = 1 To Len(Text)
' First, get the lParam for WM_KEYDOWN
zwParam = GetVKCode(Mid$(Text, I, 1))
xwParam = zwParam And &H20 ' wants Hex20 added To it so A7 goes to C7 and 15 -> 35 (hex values)
zlParam = GetScanCode(Mid$(Text, I, 1))
PostMessage hWnd, WM_KEYDOWN, zwParam, zlParam

DoEvents
Next
End Sub


Private Function GetVKCode(ByVal Char As String) As Long
On Error Resume Next
Char = UCase(Left$(Char, 1))
GetVKCode = Asc(Char)
End Function


Private Function GetScanCode(bChar As String) As Long
' To get scancodes:
' Start SPY++ on Notepad
'Type in all chars and then stop SPY++ logging. It will tell you all scancodes

' Note: Scancode 1E = &H1E0001,30 = &H30
' 0001

Select Case LCase$(Left$(bChar, 1))
Case "a"
GetScanCode = &H1E0001
Case "b"
GetScanCode = &H300001
Case "c"
GetScanCode = &H2E0001
Case "d"
GetScanCode = &H200001
Case "e"
GetScanCode = &H120001
Case "f"
GetScanCode = &H210001
Case "g"
GetScanCode = &H220001
Case "h"
GetScanCode = &H230001
Case "i"
GetScanCode = &H170001
Case "j"
GetScanCode = &H240001
Case "k"
GetScanCode = &H250001
Case "l"
GetScanCode = &H260001
Case "m"
GetScanCode = &H320001
Case "n"
GetScanCode = &H310001
Case "o"
GetScanCode = &H180001
Case "p"
GetScanCode = &H190001
Case "q"
GetScanCode = &H100001
Case "r"
GetScanCode = &H130001
Case "s"
GetScanCode = &H1F0001
Case "t"
GetScanCode = &H140001
Case "u"
GetScanCode = &H160001
Case "v"
GetScanCode = &H2F0001
Case "w"
GetScanCode = &H110001
Case "x"
GetScanCode = &H2D0001
Case "y"
GetScanCode = &H150001
Case "z"
GetScanCode = &H2C0001
Case Else
GetScanCode = 0
End Select
End Function

UserControl In VB

Well struggled for some time but then managed to sail through there are certain inbuilt datatypes and objects which can be used while creating the properties for the user controls. Well we all know that in VB when the parameter being exchanged is a Object then instead of the Let property a Set property is used.

The object used for Font is stdFont and the datatype used for color is OLE_COLOR now as color has a data type associated to it and font has an object the difference should be borne in mind while creating the property handlers for these attibutes. The advantage of using these objects is that VB displays the standard dialogues for selection of the values which is really convenient.

Date Time Setting in IIS

We had this strange problem in one of the IIS applications that instead of the regional settings of the IIS server being set to dd/MM/yyyy the IIS application continued to interpret the dates in mm/dd/yyyy format finally we realized that the IIS applications has a provision to set the application pool to be used and these regional setting are selected from what is set on the pool and not the local computer. I am not from the internet applications but yes this is something which is worth knowing.

RAID

Well talking about performance i could not skip out of understading what RAID means and how it works i always knew it expanded as Redundant Array of Inexpensive Drives. It is a technology which allows simultaneous use of one or more disk to achieve higher performance, reliability or volume size.

When several physical disks are set up to use RAID technology, they are said to be in a RAID array. This array distributes data across several disks, but the array is seen by the computer user and operating system as one single disk. Several arrangements are possible and these arrangements are called RAID configurations we assume here that all the disks involved are of the same capacity. The most popular of the RAID configurations are 0,1 and 5:
1. RAID 0 (striped disks) distributes data across several disks in a way which gives improved speed and full capacity, but all data on all disks will be lost if any one disk fails.


2. RAID 1 (mirrored disks) uses two (possibly more) disks which each store the same data, so that data is not lost so long as one disk survives. Total capacity of the array is just the capacity of a single disk. The failure of one drive, in the event of a hardware or software malfunction, does not increase the chance of a failure or decrease the reliability of the remaining drives (second, third, etc).





3. RAID 5 (striped disks with parity) combines three or more disks in a way that protects data against loss of any one disk; the storage capacity of the array is reduced by one disk. The less common RAID 6 can recover from the loss of two disks.



RAID IMPLEMENTATIONS
RAID combines two or more physical hard disks into a single logical unit by using either special hardware or software. Hardware solutions often are designed to present themselves to the attached system as a single hard drive, and the operating system is unaware of the technical workings. Software solutions are typically implemented in the operating system, and again would present the RAID drive as a single drive to applications.

RAID involves significant computation when reading and writing information. With true RAID hardware the controller does all of this computation work. In other cases the operating system or simpler and less expensive controllers require the host computer's processor to do the computing, which reduces the computer's performance on processor-intensive tasks

SQL Performance Tuning

Where should we start? You should monitor the system. Try to isolate the problems. Is it only one bad query, or is it only one peace of functionality ... or may be a general problem that is going on? Hardware issue or software issue? So, what is causing the high pressure?

What are the hardware components that are restricting performance. We all know that:

• Disk I/O
• CPU
• Memory
  
• Network

How do we measure the disk subsystem:

• Use physical disk instead of logical disk.
• The sec/transfer <>
• The transfers/sec <120>
• The current disk queue length < (2* #disks)
• Disk bytes/sec < (10 MB/sec per disk)

Then about some RAID configurations. Writing is slow in RAID5. Off course, it depends on the number of disks, but in general, it's too slow. RAID10 is best. Good speed, and very good write speed. Therefor, this is what is recommended:

	RAID 1	RAID 5	RAID 10
DB files	avoid as generally not enough drives	acceptiable if low percentage of writes (which is seldom the case in NAV, so let's try to avoid!)	best performance
Log file	General Recommendation	avoid because of high cost of write I/O	best performance and use if RAID 1 shows pressure
Tempdb	General Recommendation	avoid because of high cost of write I/O	best performance and use if RAID 1 shows pressure
Master / MSDB		General Recommendation

Now, what could be causing the I/O cost on the disks? This could be caused by memory pressure, or excessive paging (also due to too low memory) or poorly designed queries (scans on large tables, missing key indexes, ...), high write I/O's to a RAID 5 volume, high usage during peak times, ... . So it could be hardware, or software.

Next, how do we measure the CPU?

• % processor time
  
• % privileged time
  
• Processor queue length
  
• Context switches/sec
  

So, what causes CPU bottelnecks? Compiles/recompiles of execution plans, hash joins, aggregate functions, data sorting, disk I/O activity (paging), other applications/services, screen savers, ... .

What about the memory?

• Set to dynamically allocate or raise max
• Increase physical RAM
• Evaluate high read count queries
• /3GB switch in boot.ini
• /PAE switch in boot.ini + AWE enabled in SQL. If you don't put AWE in SQL, SQL won't use the extra RAM that /PAE makes available. So remember that!

Operating systems based on Microsoft Windows NT technologies have always provided applications with a flat 32-bit virtual address space that describes 4 gigabytes (GB) of virtual memory. The address space is usually split so that 2 GB of address space is directly accessible to the application and the other 2 GB is only accessible to the Windows executive software.

The virtual address space of processes and applications is still limited to 2 GB, unless the /3GB switch is used in the Boot.ini file. The following example shows how to add the /3GB parameter in the Boot.ini file to enable application memory tuning:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="????" /3GB

No APIs are required to support application memory tuning. However, it would be ineffective to automatically provide every application with a 3-GB address space.

Executables that can use the 3-GB address space are required to have the bit IMAGE_FILE_LARGE_ADDRESS_AWARE set in their image header. If you are the developer of the executable, you can specify a linker flag (/LARGEADDRESSAWARE).

To set this bit, you must use Microsoft Visual Studio Version 6.0 or later and the Editbin.exe utility, which has the ability to modify the image header (/LARGEADDRESSAWARE) flag

However, Windows 2000 Advanced Server supports 8 GB of physical RAM and Windows 2000 Datacenter Server supports 32 GB of physical RAM using the PAE feature of the IA-32 processor family, beginning with Intel Pentium Pro and later.

Physical Address Extension. PAE is an Intel-provided memory address extension that enables support of up to 64 GB of physical memory for applications running on most 32-bit (IA-32) Intel Pentium Pro and later platforms. Support for PAE is provided under Windows 2000 and 32-bit versions of Windows XP and Windows Server 2003. 64-bit versions of Windows do not support PAE.

PAE allows the most recent IA-32 processors to expand the number of bits that can be used to address physical memory from 32 bits to 36 bits through support in the host operating system for applications using the Address Windowing Extensions (AWE) application programming interface (API)

Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 3 GB of memory.

Note To allow AWE to use the memory range above 16 GB on Windows 2000 Data Center, make sure that the /3GB switch is not in the Boot.ini file. If the /3GB switch is in the Boot.ini file, Windows 2000 may not be able to address any memory above 16 GB correctly.

When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE.

Example

The following example shows how to enable AWE and configure a limit of 6 GB for the max server memory option:

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO      




SQL Server specific counters in Performance Monitor: 

• Missing indexes: full scans/sec
• Blocking:
  • Total latch wait time (ms)
  • Lock timeouts/sec
  • Lock wait time (ms) - this is the counter Chad uses the most.
  • Number of deadlocks/sec
• Miscellaneous
  • User connections
  • Batch requests/sec
  • SQL re-compilations/sec (lower is better)

These should be the top three performance issues

• High disk latency
  • Wrong RAID
  • Queries with high I/O costs
• Execution plan recompiles
  • Query design
• High blocking lengths
  • Long transactions
  • Excessive locking

SQL Versions

To sum up the scalability and performance for different SQL Versions:

Feature	Express	Workgroup	Standard	Enterprise
Number of CPU	1	2	4	No Limit
RAM	1 GB	3 GB	OS Max	O Max
64 bit support	WOW	WOW	Yes	Yes
Database Size	4 GB	No Limit	No Limit	No Limit
Data mirroring			Yes	Yes
Failover Clustering			Yes	Yes

Navision Recommended Hardware Settings

Only 10 to 20% of the problems are due to infrastructural problems.When we think of "infrastructure", these items are important (in order of importance):

• RAM
• DISK
• CPU
• NETWORK

RAM:

There are some general guidelines what you need:

DB Size/Users	0-50	51-100	101-150	151-200	>200
<=25 Gb	4	8	8	12	12
<=50 Gb	8	8	12	12	16
>50 Gb	8	8	12	12	16

DISK:

The disks are the slowest components, thus very important to choose the right DISK configuration.

First of all: Don't use RAID 5. Use as many disks as you can afford, with a minimum of 3 times RAID 1 (=6 disks). Why? To split all OS files, all Transaction Log files and All Data files. If you have more than 6 disks, scale the array of the data files up to RAID 10.

CPU:

The CPU is tupically not a bottleneck, but here are some guidelines:

DB Size/Users	0-50	51-100	101-150	151-200	>200
<=25 Gb	2	4	4	6	6
<=50 Gb	4	4	6	6	8
>50 Gb	4	4	6	6	8

Now, Hynek wasn't that a fan of duo or quad cores, because it actually just performance 60% of the performance if you compare it with full CPU's... .

NETWORK:

Some very quice recommendations:

• 100Mb minimum
• The network should be switched (as switched as possible)
• On server side, best you use a 1Gb network

Now, for planning your hardware, you shouldn't only use these guidelines. Also take for instance in account other factors like the annual business growth, seasonality, ... .

On software side, it is best to keep everything up-to-date, but be aware:

• 2000 is not 2005: it does not behave the same
• 2005 uses tempdb more ... And it's may be a good idea to put it on a seperate spindle.
• 4.00 update 6 is a good release:
  • It fixes some SIFT issues
  • It does not use OPTION (FAST xx) any more
  • Is uses index hinting by default ... Bewar of that!

There are many infrastructure software setup things to think about. Amongst them (didn't catch them all):

• Degree of Parallellism should be 1
• Split the TL, Data Files and Tempdb (if necessary)
• Maintenance:
  • Update statistics
  • Rebuild indexes

Finally, the things you can do at application level.

• SQL Profiler
  Mainly used for analyzing queries that come from the Dynamics NAV client. Also for analyzing deadlocking and timeouts
• Client Monitor
  To record the server calls from within NAV. It links certain SQL queries to pieces of code in C/SIDE.
• SQL Server Mgt Views
  Keep in mind: only SQL Server 2005 has got these. Interesting dm views are:
  • Dm_db_index_usages_stats
  • Dm_exec_query_stats
  • Dw_os_wait_stats

Remote Desktop Console Option

Remote desktop option is limited to the a default of 1 sessions on Windows server a second session which is active is the desktop session when we connect to a remote desktop application we have an option to provide the /console option which will make sure that the desktop session is used and a new session is not started at this stage is there is a user working on the server desktop then he would be logged off.

Configuring SQL Mail

I have been using SLQ mail for sending mails for quite some time and i find it the easiest way to configure notification from SQL server below are some points to be borne in mind while setting it up.
1. The Account being used to start SQL Server Service should be the same account which is used to set up the outlook profile.
2. If domain is configured then domain account should be used for the above step.
3. Log into the server with the account used to start the SQL server, thereafter install and configure Outlook mail account.
4. Assign the outlook mail profile in the SQL mail section and you are done.

The account configured in Outlook should be an exchange account if the account is an internet account then it will require the outlook application to be open on the server as in case of internet accounts there is no local mailbox which can store the mails and maintain a queue.

Table Filter Datatype

I read that the Table filter datatype are meant for internal use by navision but here is one example where i was able to you it very effectively.

This was when we were building a promotions module where in it occurred that the items and customers eligible for the promotions to work should be accepted as filters so we used table filters fields for the same.

Table filter fields cannot be directly used in navision but there are indirect ways to use it as shown below :


Variable Used
-------------------------------------------------------------------------------------------------
ItemForm = Form ( Item List )
ItemRecord = Record (Item)
TableFilter = Variant
-------------------------------------------------------------------------------------------------

Item Filter - OnAssistEdit()
-------------------------------------------------------------------------------------------------
CLEAR(ItemForm);
ApplyItemFilter(ItemRecord);
ItemForm.SETTABLEVIEW(ItemRecord);
ItemForm.LOOKUPMODE := TRUE;

IF ItemForm.RUNMODAL = ACTION::LookupOK THEN BEGIN
ItemForm.CopyFilters(ItemRecord);
IF ItemRecord.GETFILTERS() = '' THEN BEGIN
TableFilter := '';
EVALUATE("Customer Filter", TableFilter);
END ELSE BEGIN
TableFilter := ItemRecord.TABLENAME + ':' + CONVERTSTR(ItemRecord.GETFILTERS
,':'
,'=');
EVALUATE("Item Filter", FORMAT(TableFilter));
END;
END;

-------------------------------------------------------------------------------------------------