SQL Server sp_execute

We commonly come across this statement when profiling SQL Server i have wondered a lot about what it is and there was some explanation i found for it in the BOL.

The ODBC API defines prepared execution as a way to reduce the parsing and compiling overhead associated with repeatedly executing a Transact-SQL statement. The application builds a character string containing an SQL statement and then executes it in two stages. It calls SQLPrepare once to have the statement parsed and compiled into an execution plan by the database engine. It then calls SQLExecute for each execution of the prepared execution plan. This saves the parsing and compiling overhead on each execution. Prepared execution is commonly used by applications to repeatedly execute the same, parameterized SQL statement.

thus sp_execute is a system stored procedure used with "prepared" statements from a client. The number you see is an internal pointer to the execution plan on the server. The values following that number are the parameters for a particular invocation of the prepared statement


Now when we see sp_execute in the profile and we need to know the text behind it then first thing we can do it to check if there is an "sp_prepare" with the same number in the thread, if there is then you include sql_handle in the trace and you look can up the text for the cache entry using the following statement

select text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(plan_handle)
where session_id = [spid]


you can also enable Prepare SQL event in the profiler to get the handle of the execution plan

I found this problem where a prepared sql was taking much longer to execute when i executed the same sql in the management studio it was actually fast so i am wondering what could make a unprepared sql faster then a prepared sql?

I thought maybe the statistics are out so created the statistics for this table
with a FULLSCAN option and that really helped still not convinced why? maybe i will have to study the execution plan in both the cases and spot the difference i believe that different execution plans are being used in these cases.

Bring a Standby Database online

When Transaction Log Shipment is setup a copy of the transaction database in maintained in read-only and standby mode on a pre-configured server.

If for any reason the primary server fails and the standby server needs to be promoted use the following command to make the database available

RESTORE DATABASE [databasename] WITH RECOVERY

Dynamics AX Application file types

There are lots of files with different file extensions in the Application\Appl\Std folder. Got these details from Harish Mohanbabu's blog quite a handy information

Yes. There are quite a lot of files in Axapta Application\Appl\Standard folder. Please note that all these files will be updated whenever a new version is released. Most important them are -

1. .aod - Acronym for Application Object Data file. Each Application object layer is saved in a separate file called
"Ax< layer >.aod". For example, Axsys.aod for the SYS layer, Axusr.aod for the USR layer and so on.

2. label files : If we look at our label files in the AOT we see 4 different extensions for our labels

· ALI Axapta Label Index

· ALC Axapta Label Comments

· ALT Axapta Label Temp, Store

· ALD Axapta Label Dictionary



The ALD file is readable/editable with a text editor. General speaking you only need the ALD file. When the AOS is restarted the ALI and ALC will be generated on the fly. (or updated when the time stamp of the ALD file is bigger than the timestamp of the ALC or ALI file)

Next, a developer creates new labels. These labels will be stored in the ALT file. Not yet in the ALD file. When the final AOS will stop. AX will update the ALD file this way. It will copy the ALD file to an ALB file. Next the changes in the ALT file will be stored in the ALB file. Finally this ALB file is placed back in the ALD file and the ALB file will be deleted. (HINT: make the ALD file read only and you will see it your self)

When your AOS has creased the changes are not stored in the ALD file. Even when you start and stop the AOS again the file is not updated. To solve this issue start an AX client search for the label in the Label editor. Next stop your client and the AOS. Now the label file is updated.

3. .udb - Acronym for Axapta User Database. As the acronym indicates, Axapta stores its user details in this file. Also this file is responsible for allocating session ID to users. If this file is deleted, then Axapta would regenerate whenever the system is started.

Tip : Some times in Axapta 3-tier installations, when you look at online users form, it could return false information. For example, let us assume 5 users are currently online. But this form, might show 7 or 8 users online. Or some times, though you have enough licenses, you might receive "maximum users reached" error message from Axapta.

In such cases, the solution would be deleting the "axdat.udb" file. But before deleting it, make sure that all users are exited and AOS is properly shut down. Once this is done and the file is deleted, as mentioned above, Axapta would regenerate the "Axdat.udb" file. Please note that you might not even come across this problem. But to be on the safer side, particularly on 3-tier installations, it is always advisable to regularly delete "axdat.udb" file.

But in some special conditions, online user form may be empty in Axapta ver 2.5. This condition may happen if your license has an expiry date. Even though your license may not have expired, you might still come across this condition. In such cases please go to Technet website and do a search with this key words "The online user form is empty". You would come across an Export file. Import that export file in your installation. That would solve the problem.

4. .ktd - Acronym for Kernel Text Data.

Tip : Some times when you upgrade your installation (say for example you have installed a new SP), you might not be able to see the new features of the installed SP after the upgrade is complete.

The solution for this would be manually copying the .ktd files from Client\Bin\ directory to Axapta Application\Bin directory. The reason being - when Service pack is installed, for some reasons files in Axapta Application\bin directory are not updated. Only the files that are there in Client\Bin are updated.

5. .aoi - Acronym for Application Object Index. As the name indicates this is an index file for the Application objects.

Tip : Some times you may get funny error messages. Though you might have hard disk space, sometimes the following error might popup -

Error in file: ...\standard\axapd.aoi while reading in record ...
Error code: 38 = hard disk is full

For all the error messages involving axapd.aoi, the solution would be deleting the "axapd.aoi" file from Axapta Application\Appl\Standard directory. When you restart the system again, Axapta would rebuild this index file.

If the problem persists even after deleting the file, then check whether "Open application files in exclusive mode" is enabled (Axapta Configuration Utility --> Under General Tab). If enabled, then disable it. This should solve the problem.


6. What is "Master.aoc" file and how can I create it ?

aoc stands for Axapta Object Cache. This functionality was introduced in Axapta ver 2.5 mainly intended for best performance possible.

When running a 3-Tier Navision Axapta 2.5 client, we will cache different objects as we use the application. All objects are cached in the memory on the client machine. When the client is shutdown, the cache objects are written to disk, in a file with the extension .aoc (Axapta Object Cache). The next time a user starts the client, the executable ax32.exe checks for the .aoc file and if one exists it is loaded into memory. This gives us a performance gain, as we do not have to cache these objects again.

How to create a preconfigured cache file?

This is very simple. We configure a 3-tier client and go through the parts of the application all clients will use. Ex - General Ledger, Sales Order and Accounts Receivable. Doing this will create an .aoc file, with the following naming convention - "ax_AOS [Instance name]@[Hostname]_[username].aoc". In my case, I got a file like this -
ax_Annai@Himalaya_Harish.aoc. This file, if used as the preconfigured cache file, must be renamed to "master.aoc". Please note that this naming convention of the .aoc file would be different if you have configured an Object Server Cluster. (Ex - ax[cluster name][username].aoc)

When installing the Navision Axapta 2.5 client software, the setup.exe program will look for the master.aoc file in the directory where setup.exe is located. After completing the installation of the client, the setup.exe program will, if a master.aoc file exists, copy this file to the \Log directory, located in the default directory structure of the installed client.

When a user starts the client software for the first time, the executable as32.exe, will look for a cache file that belongs to the user, who is currently logged in. If one exists then this cache file is loaded into memory. If none exists, then the ax32.exe will look in the \Log directory for the master.aoc file. If one exists, the objects in this cache file would be loaded into memory.

Dynamics AX unpack type mismatch

I had this strange problem where in when a form loaded it gave an error on the unpack method stating type mismatch. The code was find and was not able to find any issues with it until i realized that i had done a xpo import recently which means the pack was done on the previous version and now the system was trying an unpack on a new version after the import.

in this new version there was a new parameter added which was not packed thus i had to clear the cached data and the issue was resolved.

The cached data could be cleared using the link
Tools

Trigger to Stop Database Drop

CREATE TRIGGER [ddl_trig_Prevent_Drop_Database]
ON ALL SERVER
FOR DROP_DATABASE
AS

--log attempt to drop database
DECLARE @db VARCHAR(209)
SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +
' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))
RAISERROR(@db, 16, 1)WITH LOG

--prevent drop database
ROLLBACK
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

--turn on trigger
ENABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER


--test trigger
CREATE DATABASE test1

DROP DATABASE test1

Msg 50000, Level 16, State 1, Procedure ddl_trig_Prevent_Drop_Database, Line 11
Database Dropped Attempted by TestSQLUser executing command: DROP DATABASE test1
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.


--turn off trigger
DISABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO


/****** Object: DdlTrigger [ddl_trig_Prevent_Drop_Database] Script Date: 01/11/2010 19:22:28 ******/
IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'ddl_trig_Prevent_Drop_Database')
DROP TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO

--cleanup current errorlog
sp_cycle_errorlog

List of Actively running commands by SPID in SQL Server

This following code snippet would list all the actively running commands by SPID


select session_id, Text

from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(sql_handle) t

SQL Server Transaction Log

DBCC LOG([,{0|1|2|3|4}])

0 - Basic Log Information (default)

1 - Lengthy Info

2 - Very Length Info

3 - Detailed

4 - Full

Example:

DBCC log (MY_DB, 4)

And it displays the following transaction log information:

* Current LSN
* Operation (string starts with LOP_XXXXX)
* Context (string starts with LCX_XXXX)
* Transaction ID
* Tag Bits (Hex)
* Log Record Fixed Length (int)
* Log Record Length (int)
* Previous LSN
* Flag Bits (Hex)
* Description (string)
* Log Record (Hex)



There is another command which is used to read the transaction log ::fn_dblog. I found an interesting hack by Paul S Randal on how to find which user dropped an object using the transaction log.

SELECT [Transaction Id], [Begin Time], [UID], [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

The (NULL, NULL) is the starting LSN and ending LSN to process - NULL means process everything available.


Now, this only shows us that a table was dropped, not which table it was. There's no way to get the name of the table that was dropped, only the object ID - so you'll need to have some other way to determine what the table ID is if there are multiple table drops and only one of them is malignant.

For SQL Server 2000, the code to find which object ID we're talking about is as follows (dropping the Transacation Id into the WHERE clause):

SELECT DISTINCT [Object Name] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:000000e0'
AND [Context] = 'LCX_IAM';
GO

Object Name
--------------------
(2009058193)

The object ID in parentheses is the ID of the table that was dropped.

For SQL Server 2005 and 2008, the code is as follows (with the same Transaction Id substitution):

SELECT TOP (1) [Lock Information] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:00000587'
AND [Lock Information] LIKE '%SCH_M OBJECT%';
GO

Lock Information
--------------------------------------------
ACQUIRE_LOCK_SCH_M OBJECT: 8:2073058421:0

The 8:2073058421 is the database ID and object ID of the table that was dropped.

PS If you find the you don't get enough info from ::fn_dblog, try turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.

Install DAX without Domain Controller

The DAX installer compares current user's domain with the local machine netbios name. If they match the installer thinks that it has been run under a local user and throws an error

«You are logged on with a local computer account. You must be logged on with a domain account to run Microsoft Dynamics AX Setup».

To avoid this check change local machine netbios name in the registry (HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName) AND after that change UserDnsDomain environment variable to the same value prior to running the setup.

You can run cmd.exe, set the environment variable and run the setup from the command line so that it can "see" the changed value. After installation change ActiveComputerName in the registry back to its original value.

Transaction log not truncating

I landed into this strange problem where i was not able to truncate the log of the database i tried the backup log and then shrinkfile a no of times with no luck.

Then i remembered that the database was being replicated using transactional log shipments so maybe there was some issue there. Thus started to look for any open transactions which might be holding the transaction logs used the command
DBCC OPENTRAN(db_name)

Transaction information for database 'BATEELNAV5'.

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (28941:199:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Got a message which confirmed that there was a replication transaction which was holding the database I got rid of the same using

use
go
sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
go


I was then able to truncate the log successfully.