March 2023
M	T	W	T	F	S	S
	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Archive for the ‘Internals’ Category

SQL Internals : Useful Parameters for xp_readerrorlog

Posted by blakhani on December 10, 2014

I know that most of you have been using sp_readerrorlog for long time. While working with SSMS, I noticed that we have options to put filters on various parameter. I became curious to know what is done in the background.

If we look at code of sp_readerrorlog using sp_helptext system procedure, this is the output.

create proc sys.sp_readerrorlog(
    @p1        int = 0,
    @p2        int = NULL,
    @p3        nvarchar(4000) = NULL,
    @p4        nvarchar(4000) = NULL)
as
begin

    if (not is_srvrolemember(N'securityadmin') = 1)
    begin
       raiserror(15003,-1,-1, N'securityadmin')
       return (1)
    end
    
    if (@p2 is NULL)
        exec sys.xp_readerrorlog @p1
    else
        exec sys.xp_readerrorlog @p1,@p2,@p3,@p4
end

Which means that under the cover, xp_readerrorlog is being used by sp_readerrorlog. Coming back to SSMS, here is the snip of information taken from profiler while launching Log File Viewer. I have done some formatting to make it look proper.

-- Below is the way SSMS Reads the ErrorLog. Can be found via profiler

Declare @InstanceName nvarchar(4000),
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,
        @ArchiveID=0,
        @Filter1Text='recovery',
        @Filter2Text='mydb',
        @FirstEntry='2014-11-22 07:14:46.930',
        @LastEntry='2014-11-22 07:14:50.020'

EXEC master.dbo.xp_readerrorlog @ArchiveID, 1, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName

That gives us a lot of hint about the parameters of xp_readerrorlog. Here is the explanation.

Parameter Name	Usage
@ArchiveID	Extension of the file which we would like to read. 0 = ERRORLOG/SQLAgent.out 1 = ERRORLOG.1/SQLAgent.1 and so on
@LogType	1 for SQL Server ERRORLOG (ERRORLOG.) 2 for SQL Agent Logs (SQLAgent.)
@FilterText1	First Text filter on data
@FilterText2	Another Text filter on data. Output would be after applying both filters, if specified
@FirstEntry	Start Date Filter on Date time in the log
@LastEntry	End Date Filter on Date time in the log
@SortOrder	‘asc’ or ‘desc’ for sorting the data based on time in log.

Sample with explanation

I have customized the script so that you can copy paste in your environment and put the values.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,          -- Don't know 🙂
        @LogType = 2,                -- File Type (1 = ERRORLOG OR 2 = SQLAgent)
        @ArchiveID=3,                -- File Extension (0 = Current i.e. ERRORLOG or SQLAgent.out, 1 = ERRORLOG.1 or SQLAgent.1 and so on)
        @Filter1Text='Waiting',      -- First Text Filter
        @Filter2Text=NULL,           -- Second Text Filter
        @FirstEntry=NULL,            -- Start Date
        @LastEntry=NULL              -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName

Here are few samples

Example 1

Read ERRORLOG and apply filter for ‘login failed’ and ‘sa’

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,          -- Don't know 🙂
        @LogType = 1,                -- 1 = ERRORLOG
        @ArchiveID=0,                -- File Extension 0 
        @Filter1Text='Login failed', -- First Text Filter
        @Filter2Text='sa',           -- Second Text Filter
        @FirstEntry=NULL,            -- Start Date
        @LastEntry=NULL              -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName

Here is the output

Example 2

In above example, let’s apply date time filer as well. We will filter records only for 05-Dec-2014.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,                  -- Don't know 🙂
        @LogType = 1,                        -- 1 = ERRORLOG
        @ArchiveID=0,                        -- File Extension 0 
        @Filter1Text='Login failed',         -- First Text Filter
        @Filter2Text='sa',                   -- Second Text Filter
        @FirstEntry='2014-12-05 00:00:00',   -- Start Date
        @LastEntry='2014-12-05 23:59:59'     -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'asc', @InstanceName

As expected, the output is similar to Example 1 but with date filter applied.

Example 3

In this example, we are trying to read Read SQLAgent.3 (i.e. LogType = 2 and ArchiveID = 3) and output should be descending date time.

Declare @InstanceName nvarchar(4000),
        @LogType int,
        @ArchiveID int,
        @Filter1Text nvarchar(4000),
        @Filter2Text nvarchar(4000),
        @FirstEntry datetime,
        @LastEntry datetime

Select  @InstanceName=NULL,    -- Don't know 🙂
        @LogType = 2,          -- 2 = SQLAgent
        @ArchiveID=3,          -- File Extension 3 
        @Filter1Text=NULL,     -- First Text Filter
        @Filter2Text=NULL,     -- Second Text Filter
        @FirstEntry=NULL,      -- Start Date
        @LastEntry=NULL        -- End Date

EXEC master.dbo.xp_readerrorlog @ArchiveID, @LogType, @Filter1Text, @Filter2Text, @FirstEntry, @LastEntry, N'DESC', @InstanceName

Here is the output.

Hope this gives you an understanding of neat way of reading SQL Server ERRORLOG and SQLAgent logs.

Cheers,

Balmukund Lakhani

Twitter

Posted in Internals, SQL Server | Tagged: parameters, sp_readerrorlog, undocumented, xp_readerrorlog | 4 Comments »

SSMS Internals : What is the meaning of Wait Category in Activity Monitor Under Resource Waits

Posted by blakhani on November 27, 2014

Recently I was working with a friend on performance issue and she asked an simple question.

I use activity monitor tool which is provided with management studio. When I look at the Resource Waits group, there is a column called Wait Category. What does that show and how data is picked? Here is the screenshot from my machine.

At almost same time, same question popped in Facebook group (SQLBangalore) as well. So I decided to dig into more details. Captured the profiler and found that Activity Monitor uses two DMVs to show the values in that section (Resource Waits).

sys.dm_os_wait_stats is used to get information for completed waits
sys.dm_os_waiting_tasks for threads that are in an in-progress wait.

Here are the various waits we could potentially see.

Wait Category	Count(Type)
Backup	7
Buffer I/O	12
Buffer Latch	6
Compilation	3
Full Text Search	3
Idle	26
Latch	9
Lock	21
Logging	5
Memory	6
Network I/O	6
Other	120
Parallelism	3
SQLCLR	17
Transaction	18
User Waits	1

How did I find this? By running profiler! As per profiler, the waits related information in Activity Monitor (AM) is populated by procedure #am_generate_waitstats. This is created when AM is launched. The temporary table which is populated in #am_wait_types which has mapping of “display” values and DMV values.

category_name	wait_type	ignore
Backup	BACKUP	0
Backup	BACKUP_CLIENTLOCK	0
Backup	BACKUP_OPERATOR	0
Backup	BACKUPBUFFER	0
Backup	BACKUPIO	0
Backup	BACKUPTHREAD	0
Backup	DISKIO_SUSPEND	0
Buffer I/O	ASYNC_DISKPOOL_LOCK	0
Buffer I/O	ASYNC_IO_COMPLETION	0
Buffer I/O	FCB_REPLICA_READ	0
Buffer I/O	FCB_REPLICA_WRITE	0
Buffer I/O	IO_COMPLETION	0
Buffer I/O	PAGEIOLATCH_DT	0
Buffer I/O	PAGEIOLATCH_EX	0
Buffer I/O	PAGEIOLATCH_KP	0
Buffer I/O	PAGEIOLATCH_NL	0
Buffer I/O	PAGEIOLATCH_SH	0
Buffer I/O	PAGEIOLATCH_UP	0
Buffer I/O	REPLICA_WRITES	0
Buffer Latch	PAGELATCH_DT	0
Buffer Latch	PAGELATCH_EX	0
Buffer Latch	PAGELATCH_KP	0
Buffer Latch	PAGELATCH_NL	0
Buffer Latch	PAGELATCH_SH	0
Buffer Latch	PAGELATCH_UP	0
Compilation	RESOURCE_SEMAPHORE_MUTEX	0
Compilation	RESOURCE_SEMAPHORE_QUERY_COMPILE	0
Compilation	RESOURCE_SEMAPHORE_SMALL_QUERY	0
Full Text Search	SOAP_READ	0
Full Text Search	SOAP_WRITE	0
Full Text Search	MSSEARCH	0
Idle	ONDEMAND_TASK_QUEUE	1
Idle	REQUEST_FOR_DEADLOCK_SEARCH	1
Idle	RESOURCE_QUEUE	1
Idle	LOGMGR_QUEUE	1
Idle	KSOURCE_WAKEUP	1
Idle	LAZYWRITER_SLEEP	1
Idle	BROKER_RECEIVE_WAITFOR	1
Idle	CHECKPOINT_QUEUE	1
Idle	CHKPT	1
Idle	BROKER_EVENTHANDLER	1
Idle	BROKER_TRANSMITTER	1
Idle	SERVER_IDLE_CHECK	1
Idle	SNI_HTTP_ACCEPT	1
Idle	SLEEP_BPOOL_FLUSH	1
Idle	SLEEP_DBSTARTUP	1
Idle	SLEEP_DCOMSTARTUP	1
Idle	SLEEP_MSDBSTARTUP	1
Idle	SLEEP_SYSTEMTASK	1
Idle	SLEEP_TASK	1
Idle	SLEEP_TEMPDBSTARTUP	1
Idle	SQLTRACE_BUFFER_FLUSH	1
Idle	TRACEWRITE	1
Idle	WAITFOR_TASKSHUTDOWN	1
Idle	WAIT_FOR_RESULTS	1
Idle	XE_DISPATCHER_WAIT	1
Idle	XE_TIMER_EVENT	1
Latch	VIEW_DEFINITION_MUTEX	0
Latch	DEADLOCK_ENUM_MUTEX	0
Latch	LATCH_DT	0
Latch	LATCH_EX	0
Latch	LATCH_KP	0
Latch	LATCH_NL	0
Latch	LATCH_SH	0
Latch	LATCH_UP	0
Latch	INDEX_USAGE_STATS_MUTEX	0
Lock	LCK_M_BU	0
Lock	LCK_M_IS	0
Lock	LCK_M_IU	0
Lock	LCK_M_IX	0
Lock	LCK_M_RIn_NL	0
Lock	LCK_M_RIn_S	0
Lock	LCK_M_RIn_U	0
Lock	LCK_M_RIn_X	0
Lock	LCK_M_RS_S	0
Lock	LCK_M_RS_U	0
Lock	LCK_M_RX_S	0
Lock	LCK_M_RX_U	0
Lock	LCK_M_RX_X	0
Lock	LCK_M_S	0
Lock	LCK_M_SCH_M	0
Lock	LCK_M_SCH_S	0
Lock	LCK_M_SIU	0
Lock	LCK_M_SIX	0
Lock	LCK_M_U	0
Lock	LCK_M_UIX	0
Lock	LCK_M_X	0
Logging	LOGBUFFER	0
Logging	LOGMGR	0
Logging	LOGMGR_FLUSH	0
Logging	LOGMGR_RESERVE_APPEND	0
Logging	WRITELOG	0
Memory	UTIL_PAGE_ALLOC	0
Memory	SOS_RESERVEDMEMBLOCKLIST	0
Memory	SOS_VIRTUALMEMORY_LOW	0
Memory	LOWFAIL_MEMMGR_QUEUE	0
Memory	RESOURCE_SEMAPHORE	0
Memory	CMEMTHREAD	0
Network I/O	ASYNC_NETWORK_IO	0
Network I/O	DBMIRROR_SEND	0
Network I/O	NET_WAITFOR_PACKET	0
Network I/O	OLEDB	0
Network I/O	MSQL_DQ	0
Network I/O	DTC_STATE	0
Other	EXECUTION_PIPE_EVENT_INTERNAL	0
Other	FAILPOINT	0
Other	INTERNAL_TESTING	0
Other	IO_AUDIT_MUTEX	0
Other	KTM_ENLISTMENT	0
Other	KTM_RECOVERY_MANAGER	0
Other	KTM_RECOVERY_RESOLUTION	0
Other	MSQL_SYNC_PIPE	0
Other	MIRROR_SEND_MESSAGE	0
Other	MISCELLANEOUS	0
Other	MSQL_XP	0
Other	REQUEST_DISPENSER_PAUSE	0
Other	PARALLEL_BACKUP_QUEUE	0
Other	PRINT_ROLLBACK_PROGRESS	0
Other	QNMANAGER_ACQUIRE	0
Other	QPJOB_KILL	0
Other	QPJOB_WAITFOR_ABORT	0
Other	QRY_MEM_GRANT_INFO_MUTEX	0
Other	QUERY_ERRHDL_SERVICE_DONE	0
Other	QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN	0
Other	QUERY_NOTIFICATION_MGR_MUTEX	0
Other	QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX	0
Other	QUERY_NOTIFICATION_TABLE_MGR_MUTEX	0
Other	QUERY_NOTIFICATION_UNITTEST_MUTEX	0
Other	QUERY_OPTIMIZER_PRINT_MUTEX	0
Other	QUERY_REMOTE_BRICKS_DONE	0
Other	QUERY_TRACEOUT	0
Other	RECOVER_CHANGEDB	0
Other	REPL_CACHE_ACCESS	0
Other	REPL_SCHEMA_ACCESS	0
Other	DBMIRROR_WORKER_QUEUE	0
Other	DBMIRRORING_CMD	0
Other	DBTABLE	0
Other	DAC_INIT	0
Other	DBCC_COLUMN_TRANSLATION_CACHE	0
Other	DBMIRROR_DBM_EVENT	0
Other	DBMIRROR_DBM_MUTEX	0
Other	DBMIRROR_EVENTS_QUEUE	0
Other	DEADLOCK_TASK_SEARCH	0
Other	DEBUG	0
Other	DISABLE_VERSIONING	0
Other	DLL_LOADING_MUTEX	0
Other	DROPTEMP	0
Other	DUMP_LOG_COORDINATOR	0
Other	DUMP_LOG_COORDINATOR_QUEUE	0
Other	DUMPTRIGGER	0
Other	EC	0
Other	EE_PMOLOCK	0
Other	EE_SPECPROC_MAP_INIT	0
Other	ENABLE_VERSIONING	0
Other	ERROR_REPORTING_MANAGER	0
Other	FSAGENT	1
Other	FT_RESTART_CRAWL	0
Other	FT_RESUME_CRAWL	0
Other	FULLTEXT GATHERER	0
Other	GUARDIAN	0
Other	HTTP_ENDPOINT_COLLCREATE	0
Other	HTTP_ENUMERATION	0
Other	HTTP_START	0
Other	IMP_IMPORT_MUTEX	0
Other	IMPPROV_IOWAIT	0
Other	ABR	0
Other	BROKER_REGISTERALLENDPOINTS	0
Other	BROKER_SHUTDOWN	0
Other	BROKER_TASK_STOP	1
Other	BAD_PAGE_PROCESS	0
Other	BROKER_CONNECTION_RECEIVE_TASK	0
Other	BROKER_ENDPOINT_STATE_MUTEX	0
Other	CURSOR	0
Other	CURSOR_ASYNC	0
Other	BUILTIN_HASHKEY_MUTEX	0
Other	CHECK_PRINT_RECORD	0
Other	BROKER_INIT	0
Other	BROKER_MASTERSTART	0
Other	SOSHOST_EVENT	0
Other	SOSHOST_INTERNAL	0
Other	SOSHOST_MUTEX	0
Other	SOSHOST_RWLOCK	0
Other	SOSHOST_SEMAPHORE	0
Other	SOSHOST_SLEEP	0
Other	SOSHOST_TRACELOCK	0
Other	SOSHOST_WAITFORDONE	0
Other	SOS_STACKSTORE_INIT_MUTEX	0
Other	SOS_SYNC_TASK_ENQUEUE_EVENT	0
Other	SHUTDOWN	0
Other	SOS_CALLBACK_REMOVAL	0
Other	SOS_DISPATCHER_MUTEX	0
Other	SOS_LOCALALLOCATORLIST	0
Other	SOS_OBJECT_STORE_DESTROY_MUTEX	0
Other	SOS_PROCESS_AFFINITY_MUTEX	0
Other	SNI_CRITICAL_SECTION	0
Other	SNI_HTTP_WAITFOR_0_DISCON	0
Other	SNI_LISTENER_ACCESS	0
Other	SNI_TASK_COMPLETION	0
Other	SEC_DROP_TEMP_KEY	0
Other	SEQUENTIAL_GUID	0
Other	VIA_ACCEPT	0
Other	SQLSORT_NORMMUTEX	0
Other	SQLSORT_SORTMUTEX	0
Other	WAITSTAT_MUTEX	0
Other	WCC	0
Other	WORKTBL_DROP	0
Other	XE_BUFFERMGR_ALLPROCECESSED_EVENT	0
Other	XE_BUFFERMGR_FREEBUF_EVENT	0
Other	XE_DISPATCHER_JOIN	0
Other	SQLTRACE_LOCK	0
Other	SQLTRACE_SHUTDOWN	0
Other	SQLTRACE_WAIT_ENTRIES	0
Other	SRVPROC_SHUTDOWN	0
Other	TEMPOBJ	0
Other	THREADPOOL	1
Other	TIMEPRIV_TIMEPERIOD	0
Other	XE_TIMER_MUTEX	0
Other	XE_TIMER_TASK_DONE	0
Other	XE_MODULEMGR_SYNC	0
Other	XE_OLS_LOCK	0
Other	XE_SERVICES_MUTEX	0
Other	XE_SESSION_CREATE_SYNC	0
Other	XE_SESSION_SYNC	0
Other	XE_STM_CREATE	0
Parallelism	CXPACKET	1
Parallelism	EXCHANGE	1
Parallelism	EXECSYNC	1
SQLCLR	CLR_AUTO_EVENT	1
SQLCLR	CLR_CRST	0
SQLCLR	CLR_JOIN	0
SQLCLR	CLR_MANUAL_EVENT	1
SQLCLR	CLR_MEMORY_SPY	0
SQLCLR	CLR_MONITOR	0
SQLCLR	CLR_RWLOCK_READER	0
SQLCLR	CLR_RWLOCK_WRITER	0
SQLCLR	CLR_SEMAPHORE	0
SQLCLR	CLR_TASK_START	0
SQLCLR	CLRHOST_STATE_ACCESS	0
SQLCLR	ASSEMBLY_LOAD	0
SQLCLR	FS_GARBAGE_COLLECTOR_SHUTDOWN	0
SQLCLR	SQLCLR_APPDOMAIN	0
SQLCLR	SQLCLR_ASSEMBLY	0
SQLCLR	SQLCLR_DEADLOCK_DETECTION	0
SQLCLR	SQLCLR_QUANTUM_PUNISHMENT	0
Transaction	TRAN_MARKLATCH_DT	0
Transaction	TRAN_MARKLATCH_EX	0
Transaction	TRAN_MARKLATCH_KP	0
Transaction	TRAN_MARKLATCH_NL	0
Transaction	TRAN_MARKLATCH_SH	0
Transaction	TRAN_MARKLATCH_UP	0
Transaction	TRANSACTION_MUTEX	0
Transaction	XACT_OWN_TRANSACTION	0
Transaction	XACT_RECLAIM_SESSION	0
Transaction	XACTLOCKINFO	0
Transaction	XACTWORKSPACE_MUTEX	0
Transaction	DTC_TMDOWN_REQUEST	0
Transaction	DTC_WAITFOR_OUTCOME	0
Transaction	MSQL_XACT_MGR_MUTEX	0
Transaction	MSQL_XACT_MUTEX	0
Transaction	DTC	0
Transaction	DTC_ABORT_REQUEST	0
Transaction	DTC_RESOLVE	0
User Waits	WAITFOR	1

Hope this helps in understanding Activity monitor wait category little better.

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Internals, SQL Server Management Studio, SSMS | Tagged: Activity Monitor, Internals, Resource Waits, SQL Server Management Studio, ssms, ssms tips, ssms tips and tricks, Wait Category | 3 Comments »

A-Z of In-Memory OLTP : Behind the scenes

Posted by blakhani on December 12, 2013

If you have a kid in your life, you probably get inundated every day with questions starting with word “why” and “what”. Why do kids ask why? One of the answers is, that children are naturally curious about the world around them. After all, there’s much to learn, and much to know. We all have a kid inside us who is very curious, that’s why I thought of writing this blog post.

In A-Z Series, we have discussed about T-SQL constructs and SSMS enhancements to create In-Memory objects. In this part, we would discuss “behind the scene” of those commands.

CREATE DATABASE

Once database is created and one (and only one) filegroup with CONTAINS MEMORY_OPTIMIZED_DATA clause, SQL Server would create a folder which is specified in filename parameter for that filegroup. In our sample database SQLSeverHelp_IMO we have added filename = “C:\IMO_Database\SQLSeverHelp_IMO_dir” which would create a folder behind the scene as shown below.

If we look inside the folders, the would be empty because there is no in-memory table created so far. We also have MDF and LDF file which are same as earlier version of SQL, nothing new there. We can use catalog view to get details about Memory optimized filegroup

Use SQLSeverHelp_IMO
GO
Select * from sys.data_spaces

Here is the query in SSMS and the output:

 

CREATE TABLE

Next logical step is to create a In-Memory table in the database. In our last post, we have created table called MyFirstMemporyOptimizedTable. As soon as table is created, SQL Server is going to create few files into the folder which were created during previous step of database creation.

Above files are created as soon as I created one table. Don’t worry by seeing so many files as we would discuss about these file later part of the series. All you need to remember that per file is 131072 KB = 128 MB by default. There are files having zero KB size as we have not inserted/updated/deleted any data in the table. In contrast to disk-based tables having 8K size pages in buffer pool, the In-memory tables don’t have pages in buffer pool. Think of this as rows in the memory linked to each other because we have index on table. In-memory data is not stored on data files at all. Instead, its stored as blob data using filestream files, which are shown in picture above. At this point just remember that they are called CheckPoint files which combination is DATA files and DELTA files. We would go deeper into those files once we go into blog about checkpoint for in-memory tables.

Another thing which has happened behind the scene is to generate the DLL for the table definition and load into SQL Server Memory. Why? Well, In-Memory engine doesn’t have any knowledge about format of data record. It doesn’t know how to access the data which is part of in-memory table. Whenever we create table, a C file is generated which would have all callback functions used by In-Memory Engine. The C file, it’s compiled by C complier and DLL is generated. If you are a developer in C/C++, you can open the file and check the content to get a feeling.

Here is the query

use SQLSeverHelp_IMO
go
SELECT name, 
       description 
FROM   sys.dm_os_loaded_modules 
WHERE  description = 'XTP Native DLL' 
       AND name LIKE '%xtp_t_' + Cast(Db_id() AS VARCHAR(10)) + '_' 
       + Cast(Object_id('dbo.MyFirstMemporyOptimizedTable') AS VARCHAR(10)) + '.dll'

Here is the query in SSMS and the output:

Let’s look at windows explorer and see what we have in that folder. If we look at xtp folder, we would see numbered folders. These are databases IDs which have in-memory objects. Default directory of XTP folder is DATA directory (C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\). Do you remember what XTP stands for? Go back and read first post.

For database SQLSeverHelp_IMO the database_id is 8 (you may have different ID) so let’s go inside the folder 8 and check what we have got there.

If you go back and look at the query, which we executed earlier, its easy to understand that DLL (and other files) have name as xtp_t_<DB_ID>_<Object_ID> where t stands for table and rest is self explanatory. Each in-memory table will have one corresponding DLL generated which would be loaded in the memory at all times.The end goal of converting table definition to machine level instruction (using DLL) is to avoid compilation during execution phase. There is no need for a DBA to worry about backup of those files. They would be re-generated as soon as database is opened and recovered. Once SQL Server restarts, it performs recovery of database and table would be loaded into memory again.

CREATE PROCEDURE

Before moving any forward, remember that here we are talking about procedure which are natively compiled. In earlier post, we created stored procedure [InsertName]. During creation time of stored procedure, compiler will convert interpreted T-SQL, query plans and expressions into native code.

Here is the query

use SQLSeverHelp_IMO
go
SELECT name, 
       description 
FROM   sys.dm_os_loaded_modules 
WHERE  name LIKE '%xtp_p_' + Cast(Db_id() AS VARCHAR(10)) + '_'        
       + Cast(Object_id('dbo.InsertName') AS VARCHAR(10)) + '.dll'

Here is the query in SSMS and the output:

Now, lets open the same XTP folder and look at content.

As we can see above, now we have new files having “p” in it and they are for stored procedure. The naming standard of those files is is xtp_p_<DB_ID>_<Object_ID>. Native Compilation of stored procedure takes an abstract tree representation of a stored procedure including queries, table and index metadata, and compiles them into native code designed to execute against tables and indexes managed by In-memory engine.

What are the other files (.c, .mat, .obj, .pdb) ? Well, they are intermediate files created by complier. They are used for troubleshooting compilation issues. Here is the quick summary table

File Extension	Usage
.c	C source file generated by In-Memory engine
.dll	Natively compiled DLL to be loaded into SQL Server process
.mat.xml	MAT export file
.obj	Object file generated by C compiler
.out	Compiler output file
.pdb	Symbol file for the dll. Used for debugging code issues.

If you are C++ developer, you can understand those files easily. Since they are not cause of worry for DBAs and Developers, I would not go deeper into explanation.

With this, I would conclude this blog post and hopefully this has given you some insights about inner working of In-Memory object.

Stay tuned for more..

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, Internals, SQL Server 2014, SSMS | Tagged: A-Z-Series, CTP, Hekaton, Hekaton Series, In-Memory OLTP, Internals, SQL Server 2014 | 7 Comments »

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Blogroll

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta