You may not have DBA in your job title but you and I both know that you're the person responsible for the database, and you might as well have that title. You might consider yourself an accidental DBA, a reluctant DBA, an incidental DBA or even a forced DBA, but somehow you managed find your way to a place where you're the person responsible for the local instance of SQL Server. You could probably use a little help now that you've arrived.
There is a lot of documentation out there describing how to set up your backups or maintain your databases, indexes, statistics, logs, and so on, and much of this work will be automated. However, what about the day-to-day T-SQL commands that you're just going to have to run manually, over and over?
I took the following question online through various forums:
What are the top 5 T-SQL commands, scripts, or fragments that you have typed so often that they are now second nature?
I received many responses, and a pretty good collection of scripts. I also requested everyone to nominate scripts that used native commands only, and not their favorite third-party script or tool. A few, very clear winners rose to the top, and here are the top 10:
sp_who2 / sys.dm_exec_requests / sp_whoisactive
STATISTICS IO/TIME
BACKUP DATABASE
sp_help
DBCC SQLPERF
sys.dm_exec_query_stats
RESTORE DATABASE
RESTORE FILELISTONLY
sp_spaceused
DBCC SHOW_STATISTICS
If you're just getting started as a DBA, accidental or otherwise, these are the ones you should start memorizing now, because you're going to need them frequently.
#1 sp_who2 / sys.dm_exec_requests / sp_whoisactive
For several reasons, I lumped these three scripts together. While I was very explicit in asking people not to nominate third party scripts, Adam Machanic's sp_whoisactive kept showing up over and over again, so I decided that I couldn't very well keep it off the list.
The sp_whoisactive script uses various DMVs to investigate current activity on the system. It will highlight, among other things, which queries are running long and where you might be experiencing blocking. Under the covers, it makes use of the sys.dm_exec_requests Dynamic Management View (DMV), so I decided to keep them together. The tool sp_who2 represents the 'old way' of looking at current activity on the system, and I'm including it here too mainly because I want people to stop using it. Instead, use sys.dm_exec_requests and the associated DMVs to find this information (or sp_whoisactive).
There are lots of ways to put together information out of these DMVs. Here's one example that shows you what's currently running on the system, the query text and the execution plan.
SELECT *
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO
Listing 1
Armed with a simple script like this, you can quickly and easily see everything that you can see through sp_who2. Using the same data (the same FROM clause), you can start to slice and dice it in interesting ways. Here's an example, which I don't expect you to memorize:
SELECT SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
( CASE der.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
- der.statement_start_offset
END ) / 2 + 1) AS querystatement ,
deqp.query_plan ,
der.session_id ,
der.start_time ,
der.status ,
DB_NAME(der.database_id) AS DBName ,
USER_NAME(der.user_id) AS UserName ,
der.blocking_session_id ,
der.wait_type ,
der.wait_time ,
der.wait_resource ,
der.last_wait_type ,
der.cpu_time ,
der.total_elapsed_time ,
der.reads ,
der.writes
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO
Listing 2
#2 SET STATISTICS IO/TIME
Frankly, I was a little surprised to see these SET STATISTIC IO / TIME commands come up so frequently. Personally, I stopped using them after I realized that they can distort the true performance of the query under analysis. Instead, I capture query metrics using extended events, because I've found it to have lower impact, and provide more accurate measures.
Nevertheless, for 'quick and dirty' testing, these commands provide valuable information, and are clearly popular.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
...
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
Listing 3
Simply replace the ellipsis with your query, in Listing 3, and you'll see a set of messages to written to Messages output within the SSMS query window, the amount of time spent on the query and the reads and writes the query causes.
(2636 row(s) affected)
Table 'SalesOrderDetail'. Scan count 449, logical reads 1834, physical reads 3, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 321 ms.
Focus are on the 'logical reads' for each of the tables referenced and on the 'elapsed time' for the query.
#3 BACKUP DATABASE
I'm not even remotely surprised that BACKUP DATABASE made the list; in fact, I expected it to be the #1 query, rather than #3. While you're going to automate your production database backups so that they run without your direct intervention, it's extremely common to need to create a few extra database backups.
For example, you might want a 'fallback' option before be deploying new objects to your database, or to create a copy of the QA database for some additional testing.
BACKUP DATABASE AdventureWorks2014
TO DISK = 'D:\bu\adw.bak'
WITH COPY_ONLY;
Listing 4
This is a straight forward database backup command. The only wrinkle I've added is to use the WITH COPY_ONLY clause. Since this is an ad hoc backup, I don't want to interfere with any DIFFERENTIAL backups that might run subsequently.
Only a very few people listed BACKUP LOG as a commonly run command. This is probably because it's just not something that a lot of people do manually, so it's not one that comes to mind when talking about common scripts.
#4 sp_help
Based on how often sp_help came up in the survey, a lot of people spend a lot of time in databases that they probably didn't design or build themselves. sp_help and its series of associated commands, such as sp_helpdb, sp_helpindex, and sp_helpfile, allow us to gather information about the target object. Running sp_help on different objects will result in different result sets, showing varying amounts of details for those objects. For example, Listing 5 will run sp_help on a table.
Full article...
