1 / 13

TEMPDB Capacity Planning

TEMPDB Capacity Planning. Indexing. Advantages Increases performance SQL server do not have to search all the rows. Performance, Concurrency, Required Resources Disadvantages More disk space Any time ( INSERT / UPDATE / DELETE ) happens database has to update all the indexes.

ayanna
Download Presentation

TEMPDB Capacity Planning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. TEMPDB Capacity Planning

  2. Indexing • Advantages • Increases performance • SQL server do not have to search all the rows. • Performance, Concurrency, Required Resources • Disadvantages • More disk space • Any time ( INSERT / UPDATE / DELETE ) happens database has to update all the indexes.

  3. Non Clustered Indexes • Eg : Index of a book. • A book index stores words in order with a reference to the page numbers where the word is located. • Only the index key and a reference are stored.

  4. Clustered Indexes • Eg: A phone book • Sorts entries into alphabetical order. • Once we find a name in a phone book, we have immediate access to the rest of the data for the name, such as the phone number and address. • Disadvantages • If we update or delete database has to move the entire row into a new position to keep the rows in sorted order. • Advantages • Performance increases

  5. Phases of Index building • DB engine scans the data pages of the table and builds an index leaf row for each data row. • When the internal sort buffers have been filled with leaf index entries, the entries are sorted and written to disk as an intermediate sort run. • The Database Engine then resumes the data page scan. • This pattern of scanning multiple data pages followed by sorting and writing a sort run continues until all the rows of the base table have been processed. • In a clustered index, the leaf rows of the index are the data rows of the table; therefore, the intermediate sort runs contain all the data rows. • http://msdn.microsoft.com/en-us/library/ms188281.aspx

  6. ONLINE = ON OR OFF while creating or rebuilding an index • If its ON : • Can manage the performance and concurrency requirements. • Uses more resources and takes longer to complete. • Queries, inserts, updates, and deletes are allowed on the underlying table • If its OFF : • Uses less resources and takes less time. • No Concurrency.

  7. SORT_IN_TEMPDB while creating or rebuilding an index • Setting the SORT_IN_TEMPDB option to ON you can direct the SQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. • Increases temporary disk space, but can reduce the time when tempdb is on a different disks from that of the user database. • When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the same disk as that of the userdatabase.

  8. 2: DMV sys.dm_db_file_space_usage • Run the below command • SELECT SUM (user_object_reserved_page_count) *8 AS usr_obj_kb ,SUM (internal_object_reserved_page_count)*8 AS internal_obj_kb ,SUM (version_store_reserved_page_count) *8 AS version_store_kb,SUM (unallocated_extent_page_count) *8 AS freespace_kb ,SUM (mixed_extent_page_count) * 8 AS mixedextent_kbFROM sys.dm_db_file_space_usage • usr_obj_kb is taking the major percentage, then it implies that temp tables /table variablesare used heavily. This is not considered a problem as long as you have enough space on the disk. • internal_obj_kb is taking the major percentage, then it implies that large amount work tables,worker files are created because of ineffective query plans. • version_store_kb is high, then its likely due to long running transactions when there is atleast one active transaction under snapshot isolation level.

  9. Other DMVs • Additionally, to monitor the page allocation or deallocation activity in tempdbat the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views

  10. Determining the Longest Running Transaction • If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. • SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC; • Kill the ID to end the query

  11. Other Factors • DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space. • large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb • any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb

  12. Prevent TempDB growth • Separate drive for Tempdb • Set simple recovery model • Autogrowthin MB , eg: 9Gb * 10% = 900MB • Alert system

  13. Links • http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html • http://msdn.microsoft.com/en-us/library/ms175527.aspx • http://strictlysql.blogspot.com/2010/03/whats-causing-my-tempdb-to-grow-sql_17.html • Monitoring • http://msdn.microsoft.com/en-us/library/ms176029.aspx

More Related