A Tempdb is a system database that is available to all users who are connected on an instance of SQL Server. Tempdb holds temporary user objects like global/local temporary tables, temporary stored procedures, table variables, cursors. Also, it holds some internal objects that are created by the SQL Server, for example, work tables (created by SQL server when order by, group by statements are used), online index operations etc. Tempdb is re-created every time when SQL Server starts so that the system always starts with a fresh copy of the database (Fresh copy means that temp objects are dropped, users are disconnected) . This means, when SQL server is restarted, data/log files will be reused if they were there before restarting. And data/log files will be sized (reset) to the value specified in the database properties as initial size and will start growing as and when required. Here, as a best practice for Tempdb – a DBA must keep tempdb on a separate drive. Now, you can correlate that if tempdb is recreated and grows again and again, this may cause physical fragmentation on drive which may lead to performance issues.
If your tempdb is set with autogrow option, using fixed-growth (like 100 MBs) is a better approach as compared to % value as it makes autogrow events more predictable. For example, autogrow set to 10% of 20 GB transactions, will take a long time and can affect the availability of the tempdb.
If you have configured multiple data files and you have set autogrowth on, you can consider enabling trace flag 1117, which will force all data files to grow uniformly so you don’t break the load balancing between files. Be careful, if you are planning to implement trace flag 1117 – this is an instance level setting. This will impact all databases on your instance.
Now, it’s worth discussing that Microsoft suggests some restrictions on tempdb.
- You cannot add filegroups. You can add files though.
- You cannot back up or restore tempdb. Is this required?
- No changes to collation are allowed. Tempdb collation = Server Collation.
- Tempdb is owned by SA – this cannot be changed. In fact, owner can’t be changed for any system database.
- You cannot drop tempdb.
- You cannot run DBCC CHECKALLOC.
- You cannot run CHECKCATALOG.
- You cannot take tempdb offline.
- You cannot use tempdb for HA features. Like Mirroring or Log shipping on Tempdb.
….there are few more…
Capacity planning for tempdb is always a typical question for every DBA. Determining the right size for tempdb in a new production environment or existing environment depends on many factors. The problem, there is no thumb rule. Based on requirements, server usages, user database size, table size, existing workload, SQL Server features etc. you should plan capacity. You can also analyze the existing workload by performing these tasks in a SQL Server test environment
- First, set autogrow on for tempdb. This will help you to determine the size of tempdb at the end.
- Create SQL server job to monitor tempdb size at peak and not peak time
- Execute queries or your workload one by one.
- Execute index maintenance operations, such as rebuilding indexes on large tables.
- In fact, you must monitor tempdb size regularly to forecast the trend and predict requirements proactively. Creating a report showing trending will be a good idea for this.
Once you have some numbers in your hand from the previous steps, try to predict your total usage and then set the size of tempdb accordingly.
I hope this helps you.