Hot answers tagged

50

From http://www.sqlservercentral.com/scripts/tempdb/72007/ ;WITH task_space_usage AS ( -- SUM alloc/delloc pages SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS alloc_pages, SUM(internal_objects_dealloc_page_count) AS dealloc_pages FROM sys.dm_db_task_space_usage WITH (NOLOCK) WHERE ...


25

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


18

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


15

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


13

When you restart the service or reboot, the buffer pool and plan cache get wiped out as a result, and so when the server starts back up again, they don't get loaded until users start executing queries. What you can do to mitigate this is have a stored procedure aimed at simply running some queries that will load up your most frequently-used data into ...


11

I've seen this very issue and the hotfix that was ultimately released to fix it was actually a direct result of my case with Microsoft CSS. There is no public KB article for the fix. Please make sure you've applied Service Pack 4 and the most recent cumulative update to SQL Server (at the time of writing, that's Cumulative Update #3 (9.00.5259)). Until the ...


11

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


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.


10

Start SQL Server as an application, not a service, in minimal config mode, and only recovering master, from a command prompt: [path to this instance]\BINN\sqlservr.exe -c -f -T3608 (Add -s InstanceName if this is a named instance.) Now, in a different command prompt, connect using SQLCMD: sqlcmd -S InstanceName -E And issue a correction (triple-check ...


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

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


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


9

Both references are valid and will resolve correctly, but the #temp table is created under the dbo schema. Same answer (on your system, some number I couldn't possibly guess): SELECT OBJECT_ID('dbo.#MyTempTable'); SELECT OBJECT_ID('SomeSchema.#MyTempTable'); Same answer (both 1, which is dbo): SELECT schema_id FROM sys.tables WHERE [object_id] = ...


9

For SQL Server 2000 → 2014 I generally agree with Paul Randal, that this is something you could always turn on, but I do have a little bit of hesitation to say it should always be on and you should leave it on regardless of what you observe (with or without adequate testing). People can have poorly set up tempdb configurations, for example not enough ...


8

So first, why is your data file growth set to 1MB? If you need to accommodate 20MB worth of data in tempdb the file will have to grow 20 individual times! Imagine if you have a query that requires a 200MB or 2GB spill to disk? Yikes. Growth events are expensive, especially on older SAS/SATA storage and especially if you don't have instant file ...


8

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


8

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


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

Moving tempdb: execute 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 ...


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

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


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


6

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


6

I think you've overfragmented your tempdb and there is a mismatch between the server CPU and disk setup, but let's collect some more information: Questions / Further information required Please confirm processor name and type (I'm basically trying to establish if it's 2 x hex-core with HT ). Use System information (eg Control Panel > System and Security >...


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

You probably need trace flag 1118 See Paul Randal's myths about tempdb first, and his TF 1118 article too The TF is described here in KB 328551 I have no direct experience of this but it sounds like what I've read


5

I presume that you've already split out your TempDB data-files to try to alleviate contention (via pre-production first obviously). If you're braver, consider the trace flag that Paul Randal authoratively refers to: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx In ...


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

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



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