Monitoring Tempdb Database Usage

Analyzing Tempdb usability by the various SQL server resources/activities. We can analyze these kind of monitoring activities using the following various ways

1. Performance Monitor
2. System Stored Procedures
3. DMVs

In this post, We going to proceed with Performance Monitor

Creating a Table:
USE SQLServerBuddy
Go
IF OBJECT_ID('Tb_Table1','U') IS NOT NULL
DROP TABLE Tb_Table1
Go
CREATE TABLE Tb_Table1
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(100),
Column2 VARCHAR(100)
)
Go

Inserting Sample Records:
USE SQLServerBuddy
Go
INSERT Tb_Table1(Column1,Column2)
SELECT 'SQL Server Buddy','Analysing Tempdb Database File Growth'
GO 10000
 
The table Tb_Table1 has 10000 records. 
 
Creating a Stored Procedure:
USE SQLServerBuddy
Go
IF OBJECT_ID('USP_FileGrowth','P') IS NOT NULL
DROP PROC USP_FileGrowth
Go
CREATE PROC USP_FileGrowth
AS
BEGIN
SET NOCOUNT ON
DECLARE @valTable1 TABLE(Id INT, Col1 VARCHAR(100), Col2 VARCHAR(100))

INSERT @valTable1
SELECT a.Id,a.column1,a.column2 FROM Tb_Table1 a, Tb_Table1 b
END
Go

Inside the procedure, We trying to insert N x M records into a Table variable. Now, we can identify that Table variables used Tempdb database resources.

Configuring Performance Monitor:
1. Start --> Run --> Enter PerfMon
2. Clear the all existing Counters as we going to proceed with Tempdb database usage counter.
3. Click (+) on Tool bar or Press Ctrl+D as given below.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
4.Choose the Database Server name at drop down list.
5.Select Databases in Performance Object Drop down list.
6.Select Log File(s) Size(KB) in Select counters from list area.
7.Select Tempdb in Select Instance from list area.
8.Click Add button and Cancel Button.
 

 
 
 
















9. Execute the Stored Procedure which we have created now.

USE SQLServerBuddy
Go
EXEC USP_FileGrowth
Go
 
10.Check the Performance Monitor, How is behaving for the Tempdb database usage.
 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Conclusion:

We can avoid the usage of Tempdb database for these kind of activities by avoiding Table variable(s).