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
In this post, We going to proceed with Performance Monitor
Creating a Table:
IF OBJECT_ID('Tb_Table1','U') IS NOT NULL
DROP TABLE Tb_Table1
CREATE TABLE Tb_Table1
ID INT IDENTITY(1,1),
Inserting Sample Records:
SELECT 'SQL Server Buddy','Analysing Tempdb Database File Growth'
The table Tb_Table1 has 10000 records.
Creating a Stored Procedure:
IF OBJECT_ID('USP_FileGrowth','P') IS NOT NULL
DROP PROC USP_FileGrowth
CREATE PROC USP_FileGrowth
SET NOCOUNT ON
DECLARE @valTable1 TABLE(Id INT, Col1 VARCHAR(100), Col2 VARCHAR(100))
SELECT a.Id,a.column1,a.column2 FROM Tb_Table1 a, Tb_Table1 b
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.
10.Check the Performance Monitor, How is behaving for the Tempdb database usage.
We can avoid the usage of Tempdb database for these kind of activities by avoiding Table variable(s).