Hot answers tagged tempdb
10
Do writes to TempDB always result in an actual physical write to disk,
or are TempDB writes cached by SQL Server for delayed write like in
the Windows file system cache?
Do they always? Most definitely not. Do they ever? Yes but not as a result of the typical mechanism. Reference here is What does checkpoint do for tempdb?.
In a "well behaved" ...
10
There are three DMVs you can use to track tempdb usage:
sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
sys.dm_db_file_space_usage
The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects.
The following example query will give you allocations ...
9
Okay, I figured it out: Eric and I were both right!
The path in the dialog is as I said, just a default path for saving query results.
Query results are cached to disk (I was wrong), but in the local profile temp folder (C:\Users\<UserName>\AppData\Local\Temp in my case here). I checked, and there doesn't appear to be an obvious way to turn this ...
7
While the cube builds, you can run Adam Machanic's sp_WhoIsActive diagnostic tool to see which queries are allocating space in TempDB. I recorded an sp_WhoIsActive tutorial video to show how it works. Include the @get_plans = 1 parameter when you call it, and you'll also get the execution plans. That way you can see exactly what's using TempDB and why.
6
Temp table usage is going to be totally up to your applications which have their databases on the SQL Server, or that are using the SQL Server. For example the monitoring application itself could be using dozens of temp tables while it gathers up data.
As for the memory usage, SQL Server will by default use up as much memory as it needs to. Every time is ...
6
1/4 to 1/2 files to cores has long been the recommendation...
But there's now even better guidance. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL
Product Support, espoused a new formula: if you have less than 8
cores, use #files = #cores. If you have more than 8 cores, use 8 files
and if you’re seeing in-memory contention, add ...
5
Also striping tempdb on the same underlying spindle is just as likely to increase latency as it is to lower it, since this may actually make tempdb access slower - a single drive still has the same moving parts, and the more files you are trying to read/write simultaneously, the worse off you'll be.
I would start with 2 files at most and see if it improves ...
5
Like most general guidelines, it is a an oversimplification in its most positive light. At best, it is a good starting point (provided you don't aren't keeping the 1:1 core:data file ratio with a large amount of cores).
There is no replacement for proper design and proper follow-up monitoring and baselining. The reason behind having multiple data files ...
5
TempDB (as with any other files) should be sized to a stable amount appropriate to your instance so that it shouldn't have to grow. The idea is to minimize autogrowth events, because any time your files have to grow, your queries will be forced to wait until the file growth has been completed. My practice(and this is my practice, your miles may vary) for ...
5
The cost is the same (1%) for both the slow and fast cases. Does that
mean the warning can be ignored? Is there a way to show "actual" times
or costs. That would be so much better! Actual row counts are the same
for the operation with the spill.
The cost shown is always the optimizer's estimated cost of the iterator, computed according to its ...
4
To move tempdb files, you simply need to do the following:
alter database tempdb
modify file
(
name = tempdev,
filename = 'C:\YourNewTempdbDir\tempdb.mdf'
)
go
alter database tempdb
modify file
(
name = templog,
filename = 'C:\YourNewTempdbDir\templog.ldf'
)
go
If you want to add a new file to tempdb, you simply need to do the following ...
4
If your running 2005+, run the two scripts below and add the output to your original question.
SELECT
DB_NAME(fs.database_id) AS [Database Name]
, mf.physical_name
, io_stall_read_ms
, num_of_reads
, CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
, io_stall_write_ms
, num_of_writes
, ...
4
By the time that you whip out dbcc page and try to look at the page, it's possibly/probably being used for something else anyway. It is tempdb, after all.
I've been at this since SQL 6.5 and I have only ever seen these sorts of page timeouts caused by slow or misconfigured storage. Every incident I can remember offhand, it's been a SAN. If you have a SAN, ...
3
Tempdb will be recreated if the server is restarted and at that time tempdb will be reset size wise to initial size settings. In the meantime once the temporary tables are dropped space will free in the data files but size on disk will remain the same.
I wouldn't however worry about the size unless you have space issues. Next time you run queries that use ...
3
There can be various sources of a problem:
usage of table variables or temporary tables
sql server created intermediate resultsets as worktables in tempdb - usually for sorting purposes (usually is a sign of absent indexes/out-of-date statistics)
sql server decided to pre-evaluate the resultset of table valued function and in this case it stores the data ...
3
Scott,
Check this statement out:
SELECT
[Database] = DB_NAME ( qt.dbid ),
[Execution Count] = SUM ( qs.execution_count ),
[Total Execution Time] = SUM (qs.total_elapsed_time / 1000000.0 ),
[Total CPU Consumption] = SUM ( qs.total_worker_time ),
[Total Reads] = SUM ( qs.total_physical_reads ),
[Total Writes] = SUM ( ...
3
Moving tempdb:
excute
ALTER DATABASE tempdb
MODIFY FILE ( name=tempdev, filename='D:\Newpath\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE ( name=templog, filename='D:\Newpath\templog.ldf')
GO
Then restart your SQL Server Service (MSSQLServer).
Number of files in tempdb - see paul randall comments: A SQL Server DBA myth a day: (12/30) tempdb ...
3
I'll suggest that moving tempdb to SSDs (which you can do in a cluster, too, as of SQL Server 2012) will:
provide a roughly equivalent benefit (not quite the same, but leaps and bounds better than spinny disks);
be supported (I don't believe Ram Disks are officially supported, but I'll confess it's been a long time since I looked into it); and,
won't use ...
2
Allocating a RAMDisk isn't the same as allocating RAM: you're putting the data in memory, sure, but as far as SqlServer is concerned, this is still just a disk. A fast disk, but just a disk.
So, doing this doesn't actually force anything on Sql server, but it could conceivably improve disk access speeds.
That said, Sql is very well optimised, and I'd ...
2
By setting Tempdb to be 30GB, you are adding the amount of time required for SQL Server to fully restart. Every time SQL starts, tempdb is recreated. Allocating that much disk space may take longer than desired. I would go with 2GB, but let auto-grow by 64MB at a time.
I would also change the MODEL database to grow by 128MB at a time for data, 64MB for ...
2
DBCC CHECKDB uses tempdb and can stress the I/O subsystem heavily when it runs. It uses tempdb to store facts during the checks it is running. Paul explains some of the details of this in his blog post (http://sqlskills.com/BLOGS/PAUL/post/How-does-DBCC-CHECKDB-WITH-ESTIMATEONLY-work.aspx) If you are getting latch contention on the system tables in tempdb, ...
2
You could run this against each file, replacing each n with the size and growth amounts:
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev,
SIZE = nMB,
FILEGROWTH = nMB
);
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev2,
SIZE = nMB,
FILEGROWTH = nMB
);
...
Now to ensure that they all grow at the same time ...
2
If it's I/O that you're after, why not compare the actual I/O of the individual database files rather than looking at the logical I/O?
SELECT *
FROM sys.dm_io_virtual_file_stats(-1, -1)
Will give you the current I/O for each file, including latency, then you just need to write a script to calculate the delta over a given interval and summarize by DB.
2
First, run your query and make a note of your SESSION_ID.
Then, run these two queries to see how much TempDB space your original query is using. Be sure to update them with your SESSION_ID.
-- TempDB session usage
select *
from sys.dm_db_session_space_usage
where session_id = 123
go
-- TempDB task usage
select *
from sys.dm_db_task_space_usage t
where ...
2
A well known SQL Server expert, Paul Randal, recommends that the number of tempdb data files should equal the number of logical cores. So if you have 4 CPU's with hyperthreading, you'd create 8 tempdb data files.
If you have more than 8 logical cores, he recommends to start with 8, and then increase in blocks of 4 if you notice tempdb contention. We've ...
2
It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it?
You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators:
USE [your ...
2
Indexes are treated the same as any other data modification i.e. they are written to the log. The performance of your log will directly impact the performance of all writes to your database. If your LOG drive is 50 times slower that your data drive, you have serious performance limitations and should consider rectifying that as soon as possible.
1
Below is the code that will help you to see what those objects are along with the row count:
SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC
SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM ...
1
TempDB will grow as needed. For a new server I will usually pregrow to about 8GB and then watch it to see what size it settles at. Then you can set that as the initial size. You'll also want to change the default autogrowth settings. Otherwise you'll get 1mb growth increments which is far less than optimal.
Sizing tempDB really depends on your workload. ...
1
You should grow the initial size of tempdb so that those large temp tables no longer cause auto-growth, since it sounds like that is your normal load. This way you reserve the disk space that is required by your application. You do not want to run into the nasty surprise that temdb cannot grow and causes failures in your app. See Capacity Planning for tempdb ...
Only top voted, non community-wiki answers of a minimum length are eligible
