April 16, 2011 at 7:16 am
Yup, that's pretty much what I was thinking about.
Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)
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
April 16, 2011 at 4:34 pm
GilaMonster (4/16/2011)
Yup, that's pretty much what I was thinking about.Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)
Thank you, duly noted.
__________________________________________________________________________________
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]
April 18, 2011 at 7:48 am
GilaMonster (4/16/2011)
Yup, that's pretty much what I was thinking about.Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)
I know this is 2k8 forum, but for 2k5I had to use where S.is_user_process = 1
April 18, 2011 at 7:59 am
Ninja's_RGR'us (4/18/2011)
GilaMonster (4/16/2011)
Yup, that's pretty much what I was thinking about.Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)
I know this is 2k8 forum, but for 2k5I had to use where S.is_user_process = 1
Thanks both, here is the query again with this correction:
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
S.is_user_process = 1
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
);
__________________________________________________________________________________
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]
April 18, 2011 at 8:04 am
Ninja's_RGR'us (4/18/2011)
GilaMonster (4/16/2011)
Yup, that's pretty much what I was thinking about.Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)
I know this is 2k8 forum, but for 2k5I had to use where S.is_user_process = 1
sorry, got it the wrong way around. Posting without testing again. Bad!
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
April 18, 2011 at 8:06 am
GilaMonster (4/18/2011)
Ninja's_RGR'us (4/18/2011)
GilaMonster (4/16/2011)
Yup, that's pretty much what I was thinking about.Just one comment, if the session_id>50 is supposed to filter out system processes, remove it as it won't necessarily do what you intend. Rather use is_system_process = 0 (column from sys.dm_exec_sessions)
I know this is 2k8 forum, but for 2k5I had to use where S.is_user_process = 1
sorry, got it the wrong way around. Posting without testing again. Bad!
I'll let it slide, THIS TIME. :hehe::hehe::hehe::-P
April 18, 2011 at 10:49 am
Hi all, I even did not expect so many replyes over the weekend.
Dan: Yes, I am looking at space available, and that's easy query, I use sys.database_files along with FileProperty function.
Jack: Thanks a lot for good links, I will read them after I update with my answers.
GilaMonster: I agree this is not perfromance but a space issue. But anyway I have to resolve it , no matter how to label it. Yes, all files are pre-sized. We have 8 files * 20000 MB each = ~150 GB. For most of the cases it is good enough, our average utilization is about 12%. However sometimes they run very huge and complex reports (this is data warehouse) with apparently large temp tables. As I mentioned in my initial post, I also have to select top 5 largest tables there. But the only problem in my case is that I cannot see local temp tables, that's because of my restricted privileges.
Mario: Thanks for the query, I will use it too.
April 18, 2011 at 11:56 am
SQL Guy 1 (4/18/2011)
I also have to select top 5 largest tables there. But the only problem in my case is that I cannot see local temp tables, that's because of my restricted privileges.
The thing is, just finding the top 5 largest tables is useless, unless you just want to say 'there's a large table in TempDB'. You can't tie the table names back to the session.
Hence the use of Marios' query, not to find the largest table, but to find the session (and the query) that is using the most space. Once you have that, you can do something practical, like try to fix it.
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
April 18, 2011 at 12:04 pm
They might be better off if they used permanent tables instead of temporary tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply