SQL Server Admin Essentials: Monitoring, Backup, Performance Tuning & Users

Basic Commands

Retrieve fundamental information about the SQL Server instance.

SELECT @VERSION; -- version
SELECT SERVERPROPERTY('edition') AS Edition; -- edition (Enterprise, Developer, etc.)
SELECT SERVERPROPERTY('collation') AS Collation; -- character set
SELECT SERVERPROPERTY('servername') AS ServerName; -- instance name
SELECT SERVERPROPERTY('language') AS Language; -- language
SELECT GETDATE() AS CurrentDateTime; -- current date and time

Server and Database Properties

Inspect server configuration, startup parameters, and user logins.

sp_configure; -- view server configuration
EXEC sp_helplogins; -- list all logins
EXEC sp_helprotect; -- list permissions
SELECT * FROM sys.databases; -- list databases
SELECT DB_NAME(database_id) AS DatabaseName, SUM(size/128.0) AS SizeInMB FROM sys.master_files GROUP BY database_id ORDER BY SizeInMB DESC; -- database sizes

Connection and Session Information

Monitor active connections, sessions, and resource usage.

SELECT COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections; -- total connections
SELECT spid, COUNT(*) AS [ConnectionCount] FROM sys.dm_exec_connections GROUP BY spid; -- per‑session connections
SELECT CURRENT_USER AS CurrentUser, SESSION_USER AS SessionUser; -- current user context
SELECT * FROM sys.dm_exec_requests; -- active requests
SELECT login_name, COUNT(*) AS Num FROM sys.sysprocesses GROUP BY login_name ORDER BY Num DESC; -- connections per login

Disk and Memory Usage

Check disk space, memory consumption, and buffer pool status.

EXEC xp_fixeddrives; -- free space per drive
EXEC sp_spaceused; -- database space usage
SELECT * FROM sys.dm_os_process_memory; -- memory usage of the SQL Server process
SELECT * FROM sys.dm_os_sys_memory; -- OS memory information
DBCC FREESYSTEMCACHE('ALL'); -- clear all system caches

Backup and Restore

Perform full, differential, and transaction‑log backups, and restore databases.

BACKUP DATABASE test TO DISK = 'C:\backup\MyDatabase.bak';
RESTORE DATABASE test FROM DISK = 'C:\backup\MyDatabase.bak' WITH REPLACE, STATS = 5;
BACKUP LOG test TO DISK = 'C:\Backup\MyDatabase_log.trn' WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', STATS = 10;
BACKUP DATABASE test TO DISK = 'C:\Backup\MyDatabase.bak' WITH FORMAT, MEDIANAME = 'MyDatabase_Full', NAME = 'Full Backup';
BACKUP DATABASE test TO DISK = 'C:\Backup\MyDatabase_diff.bak' WITH DIFFERENTIAL, FORMAT, MEDIANAME = 'MyDatabase_Diff', NAME = 'Differential Backup';

User and Role Management

Create logins, users, assign roles, and grant permissions on specific objects.

CREATE LOGIN test WITH PASSWORD = '123123';
CREATE USER test FOR LOGIN test;
ALTER ROLE db_datareader ADD MEMBER test; -- read permission
ALTER ROLE db_datawriter ADD MEMBER test; -- write permission
-- Grant permissions on selected tables
DECLARE @Sql NVARCHAR(MAX) = '';
SELECT @Sql = @Sql + 'GRANT SELECT, INSERT, UPDATE, DELETE ON [' + name + '] TO [test];' FROM sys.tables WHERE name IN ('TableA','TableB');
PRINT @Sql;
EXEC(@Sql);
-- Disable/enable a login
ALTER LOGIN test DISABLE;
ALTER LOGIN test ENABLE;
-- Change login password
ALTER LOGIN test WITH PASSWORD = 'newPass!';
-- Drop user and login
DROP USER test;
DROP LOGIN test;

Performance Diagnostics

Identify slow or resource‑intensive queries using DMVs.

-- Top 5 CPU‑bound queries
SELECT TOP 5 st.text, qp.query_plan, qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC;
-- Top 5 I/O‑bound queries
SELECT TOP 5 st.text, qp.query_plan, qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC;
-- Query execution statistics (compile time, last execution, etc.)
SELECT creation_time AS [CompileTime], last_execution_time AS [LastExec], total_physical_reads, total_logical_reads / execution_count AS [AvgLogicalReads], total_logical_writes, execution_count, total_worker_time/1000 AS [CPUms], total_elapsed_time/1000 AS [TotalMs], (total_elapsed_time/execution_count)/1000 AS [AvgMs], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS [Statement]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st;

Job Execution Duration

Retrieve the runtime of SQL Server Agent jobs.

SELECT j.job_id, j.name AS JobName, h.run_status, h.run_date, h.run_time,
       dbo.agent_datetime(h.run_date, h.run_time) AS RunDateTime,
       h.run_duration,
       DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, h.run_duration, '1900-01-01')) AS RunDurationSeconds
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE j.enabled = 1 AND h.step_id = 0 AND h.run_duration > 0
ORDER BY j.name ASC, h.run_date ASC;

Cache and Memory Management

Clear procedure cache, buffer pool, and adjust server memory settings.

DBCC DROPCLEANBUFFERS; -- clear procedure cache
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 25600; RECONFIGURE; -- temporarily lower max memory
WAITFOR DELAY '00:00:01';
EXEC sp_configure 'max server memory (MB)', 40960; RECONFIGURE; -- restore original value
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

Deadlock Detection and Resolution

Find lock information and terminate blocking sessions.

SELECT OBJECT_NAME(resource_associated_entity_id) AS TableName, request_session_id AS PID FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT';
DBCC OPENTRAN; -- view open transactions
EXEC sp_who2 53; -- view details of session 53
DBCC INPUTBUFFER(53); -- show last command of session 53
KILL 53; -- terminate session 53

Database Shrink Operations

Reduce the size of data and log files.

DBCC SHRINKFILE('DatabaseName', targetsize); -- shrink data file
DBCC SHRINKFILE('DatabaseName_log', targetsize); -- shrink log file
DBCC SHRINKDATABASE(DatabaseName, 0); -- shrink database to minimal free space

Table and Column Metadata Extraction

Generate a spreadsheet‑ready list of table columns with attributes such as data type, length, nullability, defaults, primary key, and identity.

SELECT
    CASE WHEN A.colorder = 1 THEN D.name ELSE '' END AS TableName,
    CASE WHEN A.colorder = 1 THEN ISNULL(F.value, '') ELSE '' END AS TableDescription,
    A.colorder AS ColumnOrder,
    A.name AS ColumnName,
    CASE WHEN COLUMNPROPERTY(A.id, A.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS Identity,
    CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype='PK' AND parent_obj=A.id AND name IN (SELECT name FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id=A.id AND colid=A.colid))) THEN '√' ELSE '' END AS PrimaryKey,
    B.name AS DataType,
    A.length AS Length,
    COLUMNPROPERTY(A.id, A.name, 'PRECISION') AS Precision,
    ISNULL(COLUMNPROPERTY(A.id, A.name, 'Scale'),0) AS Scale,
    CASE WHEN A.isnullable = 1 THEN '√' ELSE '' END AS Nullable,
    ISNULL(E.Text,'') AS DefaultValue
FROM syscolumns A
LEFT JOIN systypes B ON A.xusertype = B.xusertype
LEFT JOIN sysobjects D ON A.id = D.id AND D.xtype = 'U' AND D.name <> 'dtproperties'
LEFT JOIN syscomments E ON A.cdefault = E.id
LEFT JOIN sys.extended_properties G ON A.id = G.major_id AND A.colid = G.minor_id
WHERE D.name = 'YourTableName';

Additional Utilities

List all tables and views:

SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE IN ('BASE TABLE','VIEW');

Show current active processes: SELECT * FROM sys.dm_exec_requests; Release all unused cache entries: DBCC FREESYSTEMCACHE('ALL'); This collection of commands serves as a quick‑reference cheat sheet for SQL Server administrators to perform routine monitoring, maintenance, troubleshooting, and optimization tasks.