Having worked with terabyte databases in SQL Server for over 5 years i thought i would share the top 9 queries i use to manage my databases / datawarehouses.
1. View longest running queries - [_DBA_Queries_LongestRunning]
Shows which queries since the last SQL restart have taken the longest time to execute. Also shows number of times the query has been run and average execution time.
2. View queries with greatest IO impact - [_DBA_Queries_MostIO]
Shows which queries since the last SQL restart have used the greatest IO. Also shows number of times the query has been run and average execution time.
3. View blocking transactions - [_DBA_ViewBlockingTransactions]
Views which queries are currently running that are blocking other queries. Shows the Query SQL and the ID of the processes so you can KILL [ID] any naughty queries that may be screwing with your DB performance.
4. View currently running queries - [_DBA_ViewCurrentlyRunningQueries]
View which queries are active in the system. Very useful if you are curious what queries are active and shows which user is running each query.
5. View Index Fragmentation [_DBA_IndexFragmentation]
Shows how fragmented current indexes are by percentage as well as how frequently they are used.
6. Rebuild Indexes - [_DBA_Indexes_RebuildFragmentedIndex]
Run a task that rebuilds/reoptimises indexes in the most effecient manner possible. Rebuilds worst performing indexes first. Takes 20-50% of the time to run as the built-in maintenance task for reindexing.
7. Show missing Indexes - [_DBA_Indexes_FindMissing]
If you have multiple users/applications you may have queries that you are unaware need to be optimised. Its also useful to see if you have accidentally defined an index incorrectly (eg missing a column that is needed)
8. View datafile usage - [_DBA_ViewDBFileUsage]
Shows which files your database is using and how much space they are using on disk as well as how much free space may be in the file. Very useful to check if you can shrink your files.
9. Shrink datafiles in chunks - [_DBA_ShrinkFilesInChunks]
If you have a large database and clean up a big block of data, you will want to shrink the files on the filesystem. The built in SQL task that does this, may takes hours or even days to run (with no visibility on progress) and will impact database performance in the meantime. This script shrinks the datafile in chunks so you can see visibility and run small shrinks over several days
What queries have you used that have been helpful? Any comments feel free to share.
Posted by Nick HaC @ 10:13 PM