What activity that cause tempdb grow

Posted on 2007-04-04
Medium Priority
Last Modified: 2008-01-09
What is the activity causing the tempdb grow?? as I know creating index is one of them
Question by:motioneye
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 75

Accepted Solution

Aneesh Retnakaran earned 600 total points
ID: 18849870
-temp tables , table variables
-GROUP BY / ORDER BY operations
- Index creations
- DBCC commands
LVL 28

Assisted Solution

imran_fast earned 600 total points
ID: 18849877
Creating index.
Bulk Insert
Update statement updating huge number of records.
Creation of temparary # tables.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 800 total points
ID: 18849986
<<-temp tables , table variables
-GROUP BY / ORDER BY operations
- Index creations
- DBCC commands >>

You may add to the above
--> Row Versionning
--> Complex calculations

Hope this helps...

Author Comment

ID: 18855555
what is that mean by Row Versionning?
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 800 total points
ID: 18856665
<<what is that mean by Row Versionning?>>In SQL 2005, MS has implemented ORACLE's read consistency that allows to make sure that at any time the user gets access the last commited version of the data.  That prevents from having users waiting for an update transaction to be committed before they can actually make a select.  To implement that functionnality, SQL Server now perpetually keeps updated the last commited version of data in tempdb.  As a result, tempdb gets a big hit in terms of IO's because it supports all select's instead of the original tables.  It is one of the factors that may strongly influence the growth of temdb.

Hope this helps...

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question