Hi all,
Seenas this is my first blog post on Sage City, let me first say, hi! I hope to be blogging with some type of regular basis. One thing I'd like to mention right off the bat is that the topics we'll cover in all my blogs won't always be topics that X3 Customer Support covers in their supported topics. Suffice it to say, there are a lot of really interesting things to talk about and not all of those things I'll talk about are something that fall nice and neat into the X3 product scope. Ok, we got the caveat out of the way, let's get on with it!
What causes your tempdb to grow? It could be a wide variety of things, and many or most times, not even X3! This will be a three part post, mostly because it is pretty dang long and I wouldn't subject anyone to reading that much at one time. So, having said that, let's get to the main point.
What is stored inside the tempdb?
First, what gets stored inside the tempdb? Well, if you are going to my presentation at Sage Summit (Friday at 8:30 AM!) this year check slide number 14, it's quite a few types of things.
That is to say, anything that is a direct temp table (tables a SQL developer builds on his own start with one or two pound signs like #tmp), and more. Anyways, if you are going to Sage Summit, leave a comment in the blog so we can meet up!
Now, onto the goodies. What I'd like to do in this blog is provide you with a technique to gather the objects that might be causing your tempdb to grow, so you can tell whomever that might be responsible that the tempdb is growing too fast or too large, or whatever.
- We will gather the following attributes:
- Session ID (the SPID)
- Correlating database name
- The host name of the computer originating the SPID
- The program name of the computer originating the SPID
- The login name of the user
- The host process ID
- The client interface name (ODBC, SQL Native Drivers, etc…)
- The status of the SPID
- CPU Time
- Total scheduled time
- Total elapsed time
- Memory usage expressed in kilobytes
- User objects allocated expressed in kilobytes
- User objects deallocated expressed in kilobytes
- Internal SQL objects allocated expressed in kilobytes
- Internal SQL objects deallocated expressed in kilobytes
- Whether or not this is a user session or a system session
- The row counts
- The SQL query responsible for the payload distributed into the tempdb
The Assumption
We will assume that the steps to duplicate tempdb growth are unknown, meaning we
cannot readily duplicate when the tempdb will grow. So we will need to find a way to log when it
happens and what was happening at that time, giving us the ability to check the cause at a later date
and time.
The Approach
To accomplish the aforementioned goals we will perform the following:
- Create a table inside the X3 database to store diagnostic data
- Develop an insert query to stuff the aforementioned table with necessary data
- Develop a SQL Agent job to run the code on a regular basis (This will be in part 2)
To determine whether or not the tempdb is actually growing, we will leverage a report that ships with SQL Server called the disk usage report. To get to the disk usage report you right click on the Databases |
System Databases | tempdb node inside the object explorer, then click Reports | Standard Reports | Disk Usage, as shown in the screenshot below:
When you do this, you will see a report like the following:
As you can see in this report, the tempdb is quite small. The Total space of the database is 8.5 MB where the data file (MDF) is using 8 MB and the log file is at 0.5 MB. In a production scenario the tempdb
should never be set to this small, neither for the data file or the log file. You should always pre-size each file in your tempdb, as well as isolate it from the physical storage where you other mdf and ldf files are
used, especially if you suspect that the tempdb is heavily used. For X3, the tempdb is not used as a part of the X3 4GL framework.
Create the LogOfTempDBActivity Table
To begin this task you should already have SQL Server Management Studio (SSMS) open and pointed to your X3 database. In our examples, we will use a database name of x3v6 and we will use the dbo schema
which is an administrator level activity, and not one that will be used within the X3 application. Create the table with the following code:
|
USE [x3v6]
IF OBJECT_ID('dbo.LogOfTempDBActivity', 'U') IS NULL BEGIN PRINT 'Table does not exist, creating LogOfTempDBActivity now'
CREATE TABLE [dbo].[LogOfTempDBActivity]( [Snap_Shot_Time_Stamp] [datetime] NOT NULL, [Snap_Shot_GUID] UNIQUEIDENTIFIER NOT NULL, [SESSION_ID] [smallint] NOT NULL, [DATABASE_Name] [nvarchar](128) NULL, [System_Name] [nvarchar](128) NULL, [Program_Name] [nvarchar](128) NULL, [USER_Name] [nvarchar](128) NOT NULL, [Host_Process_ID] INT NULL, [Client_Interface_Name] [nvarchar] (32) NULL, [status] [nvarchar](30) NOT NULL, [CPU_TIME_milisec] [int] NOT NULL, [Total_Scheduled_TIME_milisec] [int] NOT NULL, [Elapsed_TIME_milisec] [int] NOT NULL, [Memory_USAGE_KB)] [int] NULL, [SPACE_Allocated_FOR_USER_Objects_KB] [bigint] NULL, [SPACE_Deallocated_FOR_USER_Objects_KB] [bigint] NULL, [SPACE_Allocated_FOR_Internal_Objects_KB] [bigint] NULL, [SPACE_Deallocated_FOR_Internal_Objects_KB] [bigint] NULL, [SESSION_Type] [varchar](14) NULL, [ROW_COUNT] [bigint] NOT NULL, [SQLText] [varchar](4000) NULL, [RowID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL ) END ELSE BEGIN PRINT 'This table already exists. No action performed' END |
Create the Query to Insert Into the Log Table
To store data into the above table use the following query:
|
DECLARE @SnapshotID UNIQUEIDENTIFIER SET @SnapshotID = NEWID()
INSERT INTO dbo.LogOfTempDBActivity ( [Snap_Shot_Time_Stamp], [Snap_Shot_GUID], [SESSION_ID], [DATABASE_Name], [System_Name], [Program_Name], [USER_Name], [Host_Process_ID], [Client_Interface_Name], [status], [CPU_TIME_milisec], [Total_Scheduled_TIME_milisec], [Elapsed_TIME_milisec], [Memory_USAGE_KB)], [SPACE_Allocated_FOR_USER_Objects_KB], [SPACE_Deallocated_FOR_USER_Objects_KB], [SPACE_Allocated_FOR_Internal_Objects_KB], [SPACE_Deallocated_FOR_Internal_Objects_KB], [SESSION_Type], [ROW_COUNT], [SQLText] ) SELECT GETDATE() -- SnapShotTimeStamp ,@SnapshotID -- Snap_Shot_GUID ,dmes.session_id -- AS [SESSION_ID] ,DB_NAME(dsess.database_id) -- AS [DATABASE_Name] ,dmes.host_name -- AS [System_Name] ,dmes.program_name -- AS [Program_Name] ,dmes.login_name -- AS [USER_Name] ,dmes.host_process_id ,dmes.client_interface_name ,dmes.status ,dmes.cpu_time -- AS [CPU_TIME_milisec] ,dmes.total_scheduled_time -- AS [Total_Scheduled_TIME_milisec] ,dmes.total_elapsed_time -- AS [Elapsed_TIME_milisec] ,(dmes.memory_usage * 8) -- AS [Memory_USAGE_KB)] ,(dsess.user_objects_alloc_page_count * 8) -- AS [SPACE_Allocated_FOR USER_Objects_KB] ,(dsess.user_objects_dealloc_page_count * 8) -- AS [SPACE_Deallocated_FOR_USER_Objects_KB] ,(dsess.internal_objects_alloc_page_count * 8) -- AS [SPACE_Allocated_FOR_Internal_Objects_KB] ,(dsess.internal_objects_dealloc_page_count * 8) -- AS [SPACE_Deallocated_FOR_Internal_Objects_KB] ,CASE dmes.is_user_process WHEN 1 THEN 'user session' WHEN 0 THEN 'system session' END AS [SESSION Type], row_count -- AS [ROW COUNT] ,CAST(sqlt.text AS VARCHAR(4000)) FROM sys.dm_db_session_space_usage dsess INNER join sys.dm_exec_sessions dmes ON dsess.session_id = dmes.session_id LEFT JOIN sys.sysprocesses spn ON dmes.session_id = spn.spid OUTER APPLY sys.dm_exec_sql_text(spn.sql_handle) sqlt |
Quick Summary So Far...
So, now you have:
- The query to create the log table
- The query to populate the log table
What you need next is to put this query into a schedule and looking to see how to interpret the results. I'll show you how to put the query into a SQL agent job next post, and interpret the data the post after that. Until we meet again!