SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 sys.dm_exec_query_stats info
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

buzzi
Starting Member

48 Posts

Posted - 10/21/2008 :  11:10:58  Show Profile  Reply with Quote
Hi all,
I need experts help in explaining the below query that i got when going through the documentation of tempdb,the below query will return the top 10 I/O intensive queries, that are causing my tempdb to bloat like crazy.

SELECT top 10 (total_logical_reads/execution_count),
(total_logical_writes/execution_count),
(total_physical_reads/execution_count),
Execution_count, sql_handle, plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) Desc

When i ran this on my production server, I have the first record coming as

logicalreads logicalwrites physicalreads Execution_count
3646472 85 25990 140

Q1. I got the sp that is running, and turned on the profiler to see the reads column, I am confused to see this very low, some times few 100's and other times few 1000's, which is in contrary of the logical reads(3646472), that i see in the above record.

I am not able to make sense of this, can somebody please explain whats going on?

Q2.does not droping a temp table explicitly creates any problem, i know the temp tables gets dropped when the user session is closed,but is there any downside of keeping this till the user section ends?

Thanks for all your help




buzzi
Starting Member

48 Posts

Posted - 10/21/2008 :  15:00:56  Show Profile  Reply with Quote
Hi all,
any insight into this will be of great help

Thank you all
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 10/21/2008 :  15:10:15  Show Profile  Reply with Quote
quote:
Originally posted by buzzi

Hi all,
I need experts help in explaining the below query that i got when going through the documentation of tempdb,the below query will return the top 10 I/O intensive queries, that are causing my tempdb to bloat like crazy.

SELECT top 10 (total_logical_reads/execution_count),
(total_logical_writes/execution_count),
(total_physical_reads/execution_count),
Execution_count, sql_handle, plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) Desc

When i ran this on my production server, I have the first record coming as

logicalreads logicalwrites physicalreads Execution_count
3646472 85 25990 140

Q1. I got the sp that is running, and turned on the profiler to see the reads column, I am confused to see this very low, some times few 100's and other times few 1000's, which is in contrary of the logical reads(3646472), that i see in the above record.

I am not able to make sense of this, can somebody please explain whats going on?

I guess you wanna look physical read.

Q2.does not droping a temp table explicitly creates any problem, i know the temp tables gets dropped when the user session is closed,but is there any downside of keeping this till the user section ends?

Depends on what temp table or table variable is doing.If you inserting millions of records to it,then TempDB will increase.It is good idea to drop temp table .

Thanks for all your help






Go to Top of Page

buzzi
Starting Member

48 Posts

Posted - 10/21/2008 :  23:25:17  Show Profile  Reply with Quote
Thank You sodeep, ok i took your suggestion for the 2nd point and got this right, but on the 1st one can you please eloborate
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000