tempdb file writes much larger than read, memory soaring problem

A recent encounter with a database server disk IO has risen more than once, and memory has grown rapidly. The CPU is normal, the network traffic is normal, obviously not someone is pulling the data. Just want to analyze and see. Finally found the reason. Record the analysis process.

1. Use DBCC SHOWFILESTATS to view file space allocation, and sp_lock and sp_who to see if there is a deadlock condition.

2. Use SELECT * from tempdb. sysobjects where xtype= ' U ' to see what objects are in tempdb. Tables with 1 # are unable to view table data with # #的是全局临时表是可以查看表数据的.

3. Run the following script to get more logical writes than the SQL statement.

SELECT TOP 1000
St.text as ' executed SQL statement ',
Qs.execution_count as ' execution times ',
Qs.total_elapsed_time as ' time consuming ',
Qs.total_logical_reads as ' logical read Count ',
Qs.total_logical_writes as ' logical write times ',
Qs.total_physical_reads as ' physical read Count ',
Qs.creation_time as ' execution time ',
qs.*
From Sys.dm_exec_query_stats QS
Cross APPLY
Sys.dm_exec_sql_text (Qs.sql_handle) ST
WHERE qs.creation_time between ' 2017-01-19 00:00:00 ' and ' 2017-01-19 14:00:00 '
ORDER by
Qs.total_logical_writes DESC

4. There is also a Microsoft official resolution document on this issue:

https://blogs.msdn.microsoft.com/apgcdsd/2011/01/24/tempdb/
https://blogs.msdn.microsoft.com/apgcdsd/2011/02/10/sql-servertempdb/from Microsoft Official get a Run script:

Use tempdb

--— run every 1 seconds until the user manually terminates the script run

While 1=1

Begin

Select GETDATE ()

--— tempdb usage at file level

DBCC SHOWFILESTATS

--— Query 1

--— returns the session information for all space applications

Select ' Tempdb ' as DB, GETDATE () as
Time,

SUM (User_object_reserved_page_count) *8 as user_objects_kb,

SUM (Internal_object_reserved_page_count) *8 as internal_objects_kb,

SUM (Version_store_reserved_page_count) *8 as version_store_kb,

SUM (Unallocated_extent_page_count) *8 as freespace_kb

From Sys.dm_db_file_space_usage

Where database_id = 2

--— Query 2

--— This management view can reflect the overall allocation of tempdb space at that time

SELECT t1.session_id,

T1.internal_objects_alloc_page_count, T1.user_objects_alloc_page_count,

T1.internal_objects_dealloc_page_count,
T1.user_objects_dealloc_page_count,

t3.*

From Sys.dm_db_session_space_usage T1,

--— reflect the cumulative space request for each session

Sys.dm_exec_sessions as T3

--— information for each session

where

t1.session_id = t3.session_id

and
(t1.internal_objects_alloc_page_count>0

or T1.user_objects_alloc_page_count
>0

Or
T1.internal_objects_dealloc_page_count>0

Or
T1.USER_OBJECTS_DEALLOC_PAGE_COUNT>0)

--— Query 3

--— returns the running statement of a session that is running and has made a space request

SELECT t1.session_id,

St.text

From Sys.dm_db_session_space_usage as
T1,

Sys.dm_exec_requests as T4

Cross APPLY
Sys.dm_exec_sql_text (T4.sql_handle) as St

where t1.session_id = t4.session_id

and
t1.session_id =244

and
(t1.internal_objects_alloc_page_count>0

or T1.user_objects_alloc_page_count
>0

Or
T1.internal_objects_dealloc_page_count>0

Or
T1.USER_OBJECTS_DEALLOC_PAGE_COUNT>0)

WAITFOR DELAY ' 0:1:0 '

End

5. Use the SQL statement below to find out which database is using larger memory.

Select COUNT (*) *8/1024 as ' CacheSize (MB) ',
Db_name (database_id) ' database '
From Sys.dm_os_buffer_descriptors
Group by Db_name (database_id), database_id
ORDER BY ' CacheSize (MB) ' desc

6, through the operation of the above statement and careful analysis, finally found that there is a function need to randomly display 10 data, the result is written every time from the database through the N-table join to remove 1000 data and each column through case when converted back, The program selects 10 data to display in the 1000 returned data. As long as the user refreshes a few times, the efficiency of this SQL is measured.

7, first encounter this kind of problem, record down, for reference. Many times seem to be anything (delegate, multi-threading, generics), but ignore these basic performance, SQL casually write, eventually will only lead to write out the function can not withstand the test of time and site traffic, function melted down transformation is the only way out.

tempdb file writes much larger than read, memory soaring problem