Tag Info

Hot answers tagged

20

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 ...


12

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 ...


11

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

SSMS query results cache to the C: drive by default. Go to Tool \ Options. See attached. Change this to another volume with more storage and you should be fine.


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 ...


9

If you asking if SELECT INTO can use parallelism when writing, the answer is currently "no". Parallel SELECT INTO is being added to the product in SQL Server 2014. It is functional in Community Technical Preview 1, but performance testing is not encouraged (or valid) on pre-release software. Parallel SELECT INTO does not require multiple files or file ...


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.


7

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 ...


7

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 ...


7

I asked for something built in back in 2007, but this was rejected for the 2008 release, and subsequently ignored. Feel free to vote and, more importantly, comment about your business need. In the meantime, for SQL Server 2005 and 2008, you should be able to pull this information from the default trace: DECLARE @FileName VARCHAR(MAX) SELECT @FileName = ...


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

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 ...


6

Assuming you have a maintenance window that allows for a short period of downtime I would suggest using BCP to dump the table to a file. If space is an issue, compress the target folder in advance of the export. bcp.exe "Database.dbo.OurTable" OUT "C:\Temp\bcp\OurTable.dat" -S ServerName -T -c -r "|¬|\n" -t "|¬|" /b 10000 DROP your old table, CREATE new ...


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

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 ...


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

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 ...


5

(Note: this isn't a 100% certain answer, and I don't have references, so let me know if you can prove otherwise.) The version store can only clear versions based on the oldest active transaction within the entire instance, to support the use of transaction-level snapshot isolation across multiple databases simultaneously. That simply wouldn't work if "old" ...


5

Previous to SQL 2012, all SQL Server files had to be on a shared cluster resource. It's not so much an issue of the recognition of a local path, but just that SQL Server won't allow you to use local storage in a clustered installation. This is problematic for SSDs because it is usually to expensive to attach SSDs as anything other than direct attached ...


5

Here's a query that should get you started on finding out the information you're looking for: select top 10 tsu.session_id, tsu.request_id, r.command, s.login_name, s.host_name, s.program_name, total_objects_alloc_page_count = tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count, ...


5

Even if you have plenty of DRAM, tempdb may still be used. This happens in a few situations: Snapshot isolation: Using this feature can create a lot of tempdb activity. Hash and sort Spills: When the optimiser creates a query plan, it will try to estimate the total amount of memory it needs to run the query. Before the query runs, the estimated memory is ...


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, ...


4

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 ...


4

When you encrypt the first database in the instance, tempdb will also be encrypted. As per this reference on TDE: Transparent Data Encryption and the tempdb System Database The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This will be the case when you set ENCRYPTION ON ...


4

Read committed snapshot isolation (statement-level snapshot isolation) potentially uses less storage than snapshot isolation (transaction-level snapshot isolation) because usually the row versions aren't required for as long a period of time after they've been generated. For transaction-level snapshot isolation, the versions have to stick around for the ...


4

You've got two basic options. Reduce the workload required by the tempdb database Get faster hard drives As for #1 look for indexes in other databases which are missing. Fixing missing indexes will cause less spill to the tempdb database which will reduce the workload. If the applications on the server are using temp tables a lot there's nothing you ...


4

It depends on how many disks you have available, how active your databases are going to be, and how much data work tempDB will see. In our environments we put tempDB on its own disk and size it out, with the log file lumped in with the user log files and have not had any issues. Of course, monitor for contention and keep an eye on everything. Here are some ...


4

(Consolidating the comments into an answer, so it's more easily consumable by searchers.) It appears this is related to not granting the appropriate permissions to the data container when moving the location of the physical files. Without having tried it, I believe this would also apply to user databases, not just tempdb. I have an old, but still ...


4

Is it possible that this frequency of spills could be a primary culprit in our high tempdb write latency? Yes it is possible, though typically it is the average size of the spills, and how deep they go (i.e. recursive hash spills, multi-pass sorts) that matters more than the frequency per se. SQL Server provides a wide range of metrics and DMV ...



Only top voted, non community-wiki answers of a minimum length are eligible