Sunday, August 31, 2008

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

Friday, August 29, 2008

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.

Tuesday, August 12, 2008

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

Monday, August 04, 2008

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