As we aware, Tempdb is very important system database which is used to store temporary data. Various database operations require temporary space during sorting, grouping, and joining.
Few tips are given below to minimise TempDB utilisation:
1. Optimise queries before releasing on server: The size of temporary data in TempDB will increase if the query is written poorly. So optimise your query properly before releasing on the server.
2. Avoid Using Cursors: Cursor works on row by row and one row at a time. Avoid using cursors in your code block or SP to avoid this issue.
3. Data type also plays important role. Use the data type of column properly.
4. Store Data and Log Files on different drives to get better Read-Write performance.
5. Size of tempdb: Keep close eye on TempDB size & add more space if needed.
6. Add multiple data files for TempDB: It'll help distribute the load between multiple files available on different drives. This will really enhance the performance too.
7. Avoid triggers during bulk operations.
8. Default database Setting: Never set TempDB as default database to any user(s).
9. Try to store your TempDB files on SSD storage, which will also enhance the response time and performance.
10. If possible, try to avoid table variables because the table variables are stored in the TempDB database which will increase TempDB usage. Try to use temporary tables instead of table variables.
Hence, try to minimize TempDB utilization which will provide better performance. Use the above tips which will help to reduce TempDB contention, enhance the performance of the instance, and also it'll help us to avoid many issues related to TempDB.
No comments:
Post a Comment