March 17, 2011 at 2:44 pm
I I am doing a page compression on table and indexes which means doing an "ALTER INDEX" , does this process use more tempdb space?
March 17, 2011 at 5:29 pm
a user has created so many temp tables in 22 sessions which causing disk outage on the drive where tempdb is located, how do i find size of each temp tables with the sessions, is it possible?
March 18, 2011 at 5:50 pm
Tara-1044200 (3/17/2011)
a user has created so many temp tables in 22 sessions which causing disk outage on the drive where tempdb is located, how do i find size of each temp tables with the sessions, is it possible?
I don`t think it is possible to get the individual table sizes. However, you can get information on the SQL currently running that is causing tempdb to bloat.
SELECT
R1.session_id
, R1.user_objects_alloc_page_count
, R1.user_objects_dealloc_page_count
, R1.internal_objects_alloc_page_count
, R1.internal_objects_dealloc_page_count
, R3.text
, S.[program_name]
,S.login_name
,S.status
,S.cpu_time
,S.memory_usage
,S.total_scheduled_time
,S.total_elapsed_time
,S.last_request_start_time
,S.last_request_end_time
,S.reads
,S.writes
,S.logical_reads
FROM
sys.dm_db_task_space_usage AS R1
INNER JOIN
sys.dm_exec_sessions AS S
ON
R1.session_id = S.session_id
LEFT OUTER JOIN sys.dm_exec_requests AS R2
ON R1.session_id = R2.session_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
WHERE
R1.session_id > 50
AND
(
R1.user_objects_alloc_page_count > 0
OR R1.user_objects_dealloc_page_count > 0
OR R1.internal_objects_alloc_page_count > 0
OR R1.internal_objects_dealloc_page_count > 0
OR R3.text IS NOT NULL
);
See:
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx#EX1AE
http://msdn.microsoft.com/en-us/library/ms190288.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 18, 2011 at 9:59 pm
If you go into SSMS and follow these steps you can see all the temp tables along with the number of rows and the size:
* Expand Databases
* Expand System Databases
* Right-click on tempdb
* Click on Reports
* Click on Standard Reports
* Click on Disk Usage by Table (or Disk Usage by Top Tables)
March 19, 2011 at 4:12 pm
UMG Developer (3/18/2011)
If you go into SSMS and follow these steps you can see all the temp tables along with the number of rows and the size:* Expand Databases
* Expand System Databases
* Right-click on tempdb
* Click on Reports
* Click on Standard Reports
* Click on Disk Usage by Table (or Disk Usage by Top Tables)
Thank you, I tried that.
In the Disk Usage by Top Tables report I see a few tables named with application-specific names, such as "dbo.#CustomerData", whereas for the majority of the tables I see names like "dbo.153E76A7". I guess these are worktables used internally by SQL Server for calculations such as sort etc.
The problem is that these reports, useful as they are, don't link any of these tables with the individual sessions.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2011 at 4:29 pm
There's no way in SQL to link a temp table back to its session.
The tables with hex names are table variables, table parameters or cached temp table shells.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2011 at 7:09 pm
GilaMonster (3/19/2011)
There's no way in SQL to link a temp table back to its session.The tables with hex names are table variables, table parameters or cached temp table shells.
Can you elaborate on the latter two? "table parameters or cached temp table shells".
Are these also tables used internally to store intermediate results etc.?
Thank you,
Marios
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 20, 2011 at 3:34 am
Marios Philippopoulos (3/19/2011)
Can you elaborate on the latter two? "table parameters or cached temp table shells".
For table-type parameters see Books Online.
Since 2005 SQL can cache temp tables that are frequently created then dropped. Instead of dropping, it sorta just truncates them (leaves a couple pages allocated) and changes the name. Faster next time the temp table is created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2011 at 5:39 am
GilaMonster (3/20/2011)
Marios Philippopoulos (3/19/2011)
Can you elaborate on the latter two? "table parameters or cached temp table shells".For table-type parameters see Books Online.
Since 2005 SQL can cache temp tables that are frequently created then dropped. Instead of dropping, it sorta just truncates them (leaves a couple pages allocated) and changes the name. Faster next time the temp table is created.
I see, thank you.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 21, 2011 at 5:16 am
GilaMonster (3/20/2011)
Marios Philippopoulos (3/19/2011)
Can you elaborate on the latter two? "table parameters or cached temp table shells".For table-type parameters see Books Online.
Since 2005 SQL can cache temp tables that are frequently created then dropped. Instead of dropping, it sorta just truncates them (leaves a couple pages allocated) and changes the name. Faster next time the temp table is created.
Thats true only if the schema of the temp table is not updated after its creation.
A table created like this
Create table #Test(id int)
Go
Alter Table #Test
Add constraint UQ_Id unique (Id)
wont be cached but a table created this way will be cached
Create table #Test(id int unique)
Go
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 21, 2011 at 9:35 am
Marios Philippopoulos (3/19/2011)
Thank you, I tried that.In the Disk Usage by Top Tables report I see a few tables named with application-specific names, such as "dbo.#CustomerData", whereas for the majority of the tables I see names like "dbo.153E76A7". I guess these are worktables used internally by SQL Server for calculations such as sort etc.
The problem is that these reports, useful as they are, don't link any of these tables with the individual sessions.
That is true, but if you use Adam Machanic's WhoIsActive SP from here it will show how much tempdb space each session is using. (But not the table details.)
March 21, 2011 at 9:40 am
UMG Developer (3/21/2011)
Marios Philippopoulos (3/19/2011)
Thank you, I tried that.In the Disk Usage by Top Tables report I see a few tables named with application-specific names, such as "dbo.#CustomerData", whereas for the majority of the tables I see names like "dbo.153E76A7". I guess these are worktables used internally by SQL Server for calculations such as sort etc.
The problem is that these reports, useful as they are, don't link any of these tables with the individual sessions.
That is true, but if you use Adam Machanic's WhoIsActive SP from here it will show how much tempdb space each session is using. (But not the table details.)
Cool, thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply