Optimizing Database Performance Directly From the MSSQL Console

These ten essential Microsoft SQL Server (MSSQL) console commands help developers troubleshoot performance, explore database structures, and manage connections directly from a query window. 1. Check Server Version

Find the exact build and edition of your SQL Server instance. SELECT @@VERSION; Use code with caution.

Why use it: Verifies compatibility before using specific T-SQL features. 2. View Active Sessions

List all current user connections and active sessions on the server. EXEC sp_who2; Use code with caution.

Why use it: Identifies which users or applications are consuming server resources. 3. Identify Blocked Queries

Find active queries, execution plans, and severe performance bottlenecks. EXEC sp_WhoIsActive; Use code with caution.

Why use it: Replaces sp_who2 with modern, detailed troubleshooting data.

Note: Requires a one-time installation of the popular open-source script. 4. Kill a Hung Process

Forcefully terminate a specific database connection or blocked process. KILL 54; – Replace 54 with the specific Session ID (SPID) Use code with caution.

Why use it: Instantly clears a deadlocked or frozen query blocking other users. 5. Clear Cached Memory

Empty the execution plan cache to force SQL Server to create fresh query plans. DBCC FREEPROCCACHE; Use code with caution.

Why use it: Essential for accurate performance testing and benchmark comparison. 6. Check Database Integrity

Scan the database allocation and structural integrity for physical corruption. DBCC CHECKDB (‘YourDatabaseName’); Use code with caution.

Why use it: Catches underlying disk errors or file corruption early. 7. View Table Object Details

Display columns, data types, primary keys, and properties of a specific table. EXEC sp_help ‘YourTableName’; Use code with caution.

Why use it: Quickly inspects table structures without clicking through a GUI tree. 8. Inspect Table Dependencies

List all views, stored procedures, and triggers that depend on a specific table. EXEC sp_depends ‘YourTableName’; Use code with caution.

Why use it: Prevents breaking applications before modifying or deleting a table. 9. View Table Storage Size

Display the total number of rows and disk space used by a specific table. EXEC sp_spaceused ‘YourTableName’; Use code with caution.

Why use it: Helps track down disk space hogs and unoptimized tables. 10. Change Database Context

Switch the current query window focus to a different database on the server. USE YourDatabaseName; Use code with caution.

Why use it: Ensures subsequent queries execute against the intended target database. To narrow this down, let me know if you want to explore:

The exact syntax for fixing a specific issue you are facing right now A script to automate finding missing indexes

Commands tailored for Azure SQL instead of on-premises servers