130 likes | 271 Views
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.
E N D
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.
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.
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
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
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.
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.
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.
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
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
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
Prevent TempDB growth • Separate drive for Tempdb • Set simple recovery model • Autogrowthin MB , eg: 9Gb * 10% = 900MB • Alert system
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