tempdb causing constant Disk IO

Question

tempdb causing constant Disk IO

kuba12345

SSC Veteran

Points: 272
More actions
August 4, 2017 at 5:52 am

#343948

Hello,
we have SQl Server 2012 SP3 Standard Edition.
The only database on that machine is Esri ArcGIS 10.4 geodatabase. The size of the database on disk is realtively small (3 GB), the size of tempdb is around 50 mb.
We have checked that the machine has enough memory (currently allocated around 9GB, there is still 1 GB left free (in total the machine has 16GB).
We have checked that all the tables are actually in RAM, queries use indexes and indexes are not fragmented, we have recreated statistics.
The problem is that whenever arcgis is sending queries to sql server, there is disk IO (reads and writes at the same level of a few MB/s) . The queries are basically sequences of Select and then cursor iterations through the results.
I am curious why there is constant io to tempdb. Do you know what can be causing that?
thank you in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

tempdb is used internally to handle things that require sorts...ORDER BY in a select that is not satisfied by an existing index, or sorting the data first, in order to do a MERGE JOIN, because the engine deemed a merge join to be the most efficient.

tempdb being used is perfectly normal; i would think especially with queries using GIS data, where you need the data in a specific order(in order to find the nearest {whatever}), it would be extremely common to see a lot of sorts, which would occur in tempdb.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kuba12345 SSC Veteran Points: 272 More actions · Answer 2

Thank you for the reply.
Yes, the usage of tempdb itself is perefctly fine and understandable. The thing I do not understand is why there is so much IO to the disk, should not tempdb reside in RAM especially if it has enough of it to be allocated ?

br

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Reside in, yes, but structures in TempDB still get written to disk.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sue_H SSC Guru Points: 90860 More actions · Answer 4

There can be a lot of tempdb activity if you are using session based log files.
That used to be the recommended log file configuration when using SQL Server.

Sue

Arsh SSCertifiable Points: 6354 More actions · Answer 5

As long as there are no performance issues , the tempdb activity here looks normal , as the temp tables are not pure memory only objects and hence the IO's are reported due to Read/Write from/to the disk . To dig deep in case of performance issues , the wait_types associated with code need to be looked into.

Arshad

Super Cat SSCertifiable Points: 7332 More actions · Answer 6

Can you post the results from the query below.

PLEASE TEST ANY QUERIES BEFORE RUNNING ON LIVE.

SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

kuba12345 SSC Veteran Points: 272 More actions · Answer 7

Hi here are the results
physical_name , name , num_of_writes , avg_write_stall_ms , num_of_reads , avg_read_stall_ms
E:\tempdb.mdf , tempdev , 3236473 , 5.71913437868939428 , 3351725 , 2.31443420925046058

Sue_H SSC Guru Points: 90860 More actions · Answer 8

Please check the log file configuration you have on the ArcGIS server.

Sue

kuba12345 SSC Veteran Points: 272 More actions · Answer 9

kuba12345

SSC Veteran

Points: 272

August 7, 2017 at 1:03 pm

#1954374

It is at "info" level

Sue_H SSC Guru Points: 90860 More actions · Answer 10

kuba12345 - Monday, August 7, 2017 1:03 PM
It is at "info" level

Try referring to these articles:

Log file table configuration options for geodatabases in SQL Server

SQL Server configuration parameters

Logfiles configuration parameters
SESSION_TEMP_TABLE (default: 1 in SQL Server, 0 in other DBMSs)â€”This DBTUNE LOGFILE_DEFAULTS parameter specifies whether the session-based log file tables should be create as a temporary table in tempdb. Only valid for geodatabases in SQL Server.

Sue

Arsh SSCertifiable Points: 6354 More actions · Answer 11

In the query above, pls include io stall type column also and let know value ... 1 indicates pending to write on the disk.. If the same file name is returned multiple times then disk configuration has to be looked at .

Thank...Arshad