Tuesday, 21 September 2010

Basic algorithm for the "Auto update statistics"

This is an excerpt from one of the support call I read recently, thought it would be beneficial to share it.


Basically the SQL database engine will not update statistics every time a record is change, added or deleted. There is a change threshold for each table, and this is what effect the statistics update. So if you have a customer with large tansaction volumes, you could find that this update process is not triggered frequently enough.

The following describes the basic algorithm for the "auto update statistics" process:
  1. If the cardinality (in most cases this means row count) for a table is less than six and the table is in the tempdb database, auto update with every six modifications to the table.
  2. If the cardinality for a table is greater than 6, but less than or equal to 500, update status every 500 modifications.
  3. If the cardinality for a table is greater than 500, update statistics when (500 + 20 percent of the table) changes have occurred.
  4. For table variables, cardinality changes does not trigger auto update statistics.

Therefore you may find that large tables are only statistically updates when the row count increases by 20%. But this does not really take into account updates, and this does not change the row count. Therefore you may find that if you make lots of updates, the data statistics on the are not in tune with this updated data. So in this case, updating the statistics manually from a scripts would help the query optimiser when the process is run.


Thursday, 16 September 2010

(Dynamics AX) Failed to establish connection at Terminal Server

The "Failed to establish connection" in Dynamics AX is a common error seen and these are the common reasons:
  1. AOS is down
  2. AX configuration is incorrect
  3. Firewall blocked the port used
  4. Connection issue between client and AOS
  5. User account is locked

But occasionally, my colleagues encountered this error at the Terminal Server (where all of us logon to), while there's no changes to anything (AX configuration, security, network, and etc), his/her AX suddenly doesn't works, can't connect to the AOS. Restart AOS or logon logoff from Terminal Server doesn't helps. Checked the 5 possible causes listed above, none of them is the cause.

I investigate further, AOS is running, port is not blocked, I logged on to the Terminal Server and start up AX client (all works fine, able to start up and forms opened OK). I asked a few other colleague to logon to the same Terminal Server and start up AX, all of them doesn't have the problem. So, what happens that only 2 of my colleagues unable to logon (Failed to establish connection)? The weird thing is the next day it's working fine for them, but on and off, it happens again (and to other users). Until today, I still doesn't know what could have cause this, but based on a random suspect, I do found the way to solve this - refresh group policy.

I was thinking, since it only happens to particular users, could it be profile or security changes? I then tried refresh the group policy, hooray, it works.

Here's what I do:
  1. Open command prompt (Start > Run > cmd)
  2. Type in "gpupdate /Force" and press "Enter"
    (it will refresh the group policy and prompt whether OK to logoff)
  3. Press "Y" and logged-off
  4. Login again and it is now working fine

If you know what could have cause this and why, do leave a message here, others could benefit from it.

Thursday, 9 September 2010

Change password from Terminal Server session

If you're looking around the way to change password from within Terminal Server session due to pressing Ctrl + Alt + Del is applied to your host machine rather than Terminal Server, you just need to change the last "Del" to "End".

Instead of pressing "Ctrl + Alt + Del", you press "Ctrl + Alt + End", you'll get the window where you see the button for Lock, Logoff, Shutdown, Change password, Task Manager and Cancel at your Terminal Server session. Now you can click on the "Change password" button to change your password.