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

---