Learning: SQL Server Management Studio (SSMS) in detail - connect, Object Explorer, the query editor, execution plans, backups, security, monitoring and the shortcuts that save you hours

#About this session

Warning

Do this lab on your own instance, not on a shared or production server. Several steps create and drop databases and logins. The lab names everything SsmsLab* so it is easy to clean up in Module 12.

#Module 1: the ideas you need first

Two habits will make you good at SSMS quickly:

1. Check which server and database a query window is connected to before you press Execute. It is shown in the status bar at the bottom of every query tab and in the database drop-down on the toolbar.
2. Script instead of clicking OK. Almost every dialog has a Script button that produces the T-SQL it would run. You learn the language, you can review it, and you can save it.

#Module 2: install and update SSMS

SSMS 22 is installed through the Visual Studio Installer, the same way Visual Studio is.

1. Download the SSMS installer from the Microsoft Learn page Install SQL Server Management Studio.
2. Run it. In the installer, the core SSMS workload is selected. Optional workloads (for example SSIS integration, or Database DevOps / Schema Compare in recent builds) can be added now or later.
3. Click Install. When it finishes, start SQL Server Management Studio 22 from the Start menu.

Updates come through the same installer: open Visual Studio Installer and click Update on the SSMS entry, or use Help > Check for Updates inside SSMS.

Tip

SSMS 22 can be installed side by side with older versions such as SSMS 19 or 20. Microsoft supports only the current major version, so move your daily work to SSMS 22 and keep an old one only if you have a specific reason.

You also need a server to connect to. For this lab, install SQL Server 2022 or 2025 Developer edition with the default instance and Windows authentication, and make your Windows account a sysadmin during setup.

#Module 3: connect the right way

Open File > Connect Object Explorer (or the Connect button in Object Explorer). The fields that matter:

The most common first-day error is a certificate error after an upgrade from an old SSMS. Recent SSMS versions use the Microsoft.Data.SqlClient driver and encrypt by default, so a server with the default self-signed certificate fails validation.

Once connected, open a new query window (Ctrl+N or the New Query button) and confirm where you are:

SELECT @@SERVERNAME            AS server_name,
       SERVERPROPERTY('Edition')        AS edition,
       SERVERPROPERTY('ProductVersion') AS version,
       SUSER_SNAME()           AS my_login,
       DB_NAME()               AS current_database;

Tip

In the Options tab of the connect dialog you can set a custom color for the connection. Pick red for production. The status bar of every query window to that server turns that color, which is the cheapest safety feature in SSMS.

#Module 4: find your way around

The windows you will use every day:

Things to try in Object Explorer:

• Right-click a server > Reports > Standard Reports > Server Dashboard for a quick health view.
• Right-click a database > Reports > Standard Reports > Disk Usage to see file sizes and free space.
• Use the filter icon on the Tables node of a database with many tables to show only names containing a word.
• Drag a table name, or the Columns folder, from Object Explorer into a query window. SSMS types the name, or the comma-separated column list, for you.

#Module 5: build the lab database with the query editor

Open a new query window connected to your lab instance and run this script with F5 (or Ctrl+E).

CREATE DATABASE SsmsLab;
GO
ALTER DATABASE SsmsLab SET RECOVERY FULL;
GO
USE SsmsLab;
GO
CREATE TABLE dbo.Customer (
    CustomerId   int IDENTITY(1,1) PRIMARY KEY,
    Name         nvarchar(100) NOT NULL,
    Country      char(2)       NOT NULL,
    CreatedAt    datetime2(0)  NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE TABLE dbo.SalesOrder (
    OrderId      int IDENTITY(1,1) PRIMARY KEY,
    CustomerId   int NOT NULL REFERENCES dbo.Customer(CustomerId),
    OrderDate    date NOT NULL,
    Amount       decimal(12,2) NOT NULL
);
GO
-- 1,000 customers and 200,000 orders of test data
INSERT dbo.Customer (Name, Country)
SELECT TOP (1000) CONCAT(N'Customer ', ROW_NUMBER() OVER (ORDER BY (SELECT NULL))),
       CASE ABS(CHECKSUM(NEWID())) % 4 WHEN 0 THEN 'IN' WHEN 1 THEN 'US' WHEN 2 THEN 'GB' ELSE 'DE' END
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

INSERT dbo.SalesOrder (CustomerId, OrderDate, Amount)
SELECT TOP (200000) ABS(CHECKSUM(NEWID())) % 1000 + 1,
       DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 730), CAST(GETDATE() AS date)),
       CAST(ABS(CHECKSUM(NEWID())) % 100000 / 100.0 AS decimal(12,2))
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
GO
CREATE PROCEDURE dbo.GetCustomerOrders @CustomerId int
AS
SELECT o.OrderId, o.OrderDate, o.Amount
FROM dbo.SalesOrder o
WHERE o.CustomerId = @CustomerId
ORDER BY o.OrderDate DESC;
GO

GO is not T-SQL. It is a batch separator that SSMS understands: the text between two GO lines is sent to the server as one batch. GO 5 runs the batch above it five times.

Press F5 in Object Explorer on the Databases node to refresh it and see SsmsLab. New objects also don't show in IntelliSense until you refresh its cache with Ctrl+Shift+R.

#Editor features worth learning on day one

The Messages tab is as important as the Results tab. It shows row counts, errors, PRINT output, and the statistics from SET STATISTICS IO, TIME ON. Double-click an error in Messages to jump to the line that caused it.

#Module 6: let SSMS write the T-SQL for you

Every change you make in a dialog can be turned into a script. Try it:

1. In Object Explorer, expand SsmsLab > Tables, right-click dbo.SalesOrder > Indexes > New Index > Non-Clustered Index.
2. Add column CustomerId, then on the Included columns tab add OrderDate and Amount.
3. Don't click OK. Click the Script drop-down at the top and choose Script Action to New Query Window.
4. Read the generated CREATE NONCLUSTERED INDEX statement, give the index a clear name, and run it.

Other places to script:

• Right-click any object > Script Table as > CREATE To > New Query Editor Window to get its definition.
• Right-click a database > Tasks > Generate Scripts to script many objects, optionally with data (Advanced > Types of data to script). Good for moving a small schema between environments.
• In the table designer, Table Designer > Generate Change Script shows what a designer change will really do.

Caution

The table designer can silently rebuild a whole table (create new, copy data, drop old) for some changes, which locks the table and can fill the log on a big table. SSMS blocks those saves by default with Tools > Options > Designers > Prevent saving changes that require table re-creation. Leave that option on and make schema changes with reviewed scripts.

#Module 7: read execution plans

Execution plans show how SQL Server runs a query. Copy this into a query window in SsmsLab:

SET STATISTICS IO, TIME ON;

SELECT c.Country, COUNT(*) AS orders, SUM(o.Amount) AS total
FROM dbo.SalesOrder o
JOIN dbo.Customer c ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= DATEADD(MONTH, -3, GETDATE())
GROUP BY c.Country;

How to read it:

• Read right to left: data flows from the operators on the right to the SELECT on the left. Arrow thickness shows row count.
• Hover over an operator for a tooltip with estimated vs actual rows. A big gap between them is the classic sign of stale statistics or a hard-to-estimate predicate.
• Look for a green Missing Index hint at the top. Treat it as a suggestion to investigate, not an order: it ignores the indexes you already have and the cost of maintaining a new one.
• Right-click the plan > Show Execution Plan XML or Save Execution Plan As (.sqlplan) to share it with a colleague.
• Right-click > Compare Showplan to put a saved plan next to the current one.

In the Messages tab, logical reads from STATISTICS IO is the number to compare when you test an index: fewer reads is usually better.

Now run the query with the actual plan on, add an index on SalesOrder(OrderDate) INCLUDE (CustomerId, Amount), and run it again. Compare the plan shape and the logical reads.

#Module 8: back up and restore

Take a full backup and a log backup with the GUI, scripting them first:

1. Right-click SsmsLab > Tasks > Back Up.
2. Backup type: Full. Under Destination, keep or set a path such as C:\SQLBackups\SsmsLab_full.bak.
3. On Backup Options, tick Verify backup when finished and Perform checksum before writing to media, and set compression if your edition supports it.
4. Click Script > Script Action to New Query Window, then run the script.

You get something like this, which you can save and reuse:

BACKUP DATABASE SsmsLab
TO DISK = N'C:\SQLBackups\SsmsLab_full.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10, NAME = N'SsmsLab-Full';

BACKUP LOG SsmsLab
TO DISK = N'C:\SQLBackups\SsmsLab_log1.trn'
WITH CHECKSUM, COMPRESSION, STATS = 10;

Then practise a restore as a new database, so the original is untouched:

1. Right-click Databases > Restore Database.
2. Source: Device, add both backup files. SSMS reads them and builds the restore plan (full, then log).
3. On the General page change Destination database to SsmsLab_Restored.
4. On Files, tick Relocate all files to folder so the new database doesn't try to use the original file names.
5. Script it, read the RESTORE ... WITH MOVE ... NORECOVERY / RECOVERY steps, and run it.

Warning

When you restore over an existing database, the Options page offers Overwrite the existing database (WITH REPLACE) and Take tail-log backup before restore. Read both carefully. A restore over the wrong database is one of the most common self-inflicted outages, which is another reason to color-code production connections.

For recovery models, backup types and chains, see SQL Server backup basics.

#Module 9: logins, users and permissions

Create a SQL login and a read-only user. The GUI path is Security > Logins > New Login, then User Mapping. Script it and you get the equivalent of:

USE master;
CREATE LOGIN SsmsLabReader WITH PASSWORD = N'Use-A-Long-Passphrase-1!', CHECK_POLICY = ON;
GO
USE SsmsLab;
CREATE USER SsmsLabReader FOR LOGIN SsmsLabReader;
ALTER ROLE db_datareader ADD MEMBER SsmsLabReader;
GO

Test it: open a new query window, click Change Connection on the toolbar, sign in as SsmsLabReader, and try:

SELECT TOP (5) * FROM SsmsLab.dbo.Customer;   -- works
DELETE FROM SsmsLab.dbo.Customer;             -- fails: permission denied

To see what a principal can do, right-click the user > Properties > Securables, or run:

USE SsmsLab;
EXECUTE AS USER = 'SsmsLabReader';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;

In real environments, grant to Windows or Entra groups rather than individuals, and prefer Windows or Entra authentication over SQL logins. More detail in logins vs users.

#Module 10: watch a live server

Generate some load first. In one query window run this (it loops for about a minute):

USE SsmsLab;
DECLARE @i int = 0;
WHILE @i < 300
BEGIN
    EXEC dbo.GetCustomerOrders @CustomerId = 42;
    SET @i += 1;
    WAITFOR DELAY '00:00:00.2';
END;

Then, in another window, cause some blocking:

-- Window A
BEGIN TRAN;
UPDATE SsmsLab.dbo.Customer SET Name = N'Blocked' WHERE CustomerId = 1;
-- leave it open

-- Window B (new query window)
SELECT * FROM SsmsLab.dbo.Customer WHERE CustomerId = 1;   -- waits

Now look at the server with these tools:

A quick T-SQL check that does the same as the Processes pane, and works on servers where Activity Monitor is slow:

SELECT r.session_id, r.blocking_session_id, r.status, r.wait_type,
       r.wait_time / 1000.0 AS wait_s, DB_NAME(r.database_id) AS db, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID;

Finish by running ROLLBACK; in Window A. Window B completes immediately.

Tip

Activity Monitor runs its own queries every few seconds. On a busy production server, keep it open only as long as you need it, and close XEvent Profiler sessions when you are done.

Related reading: blocking and deadlocks, Extended Events introduction, Query Store plan regression.

#Module 11: set SSMS up like an experienced DBA

#Registered servers and multi-server queries

1. Open Registered Servers (Ctrl+Alt+G). Under Database Engine > Local Server Groups, create a group such as Lab.
2. Right-click the group > New Server Registration, add your instance (add a second one if you have it), and set a color on the Connection Properties tab.
3. Right-click the group > New Query. The query runs against every server in the group, and SSMS adds a Server Name column to the results.

SELECT SERVERPROPERTY('ProductVersion') AS version,
       (SELECT COUNT(*) FROM sys.databases) AS database_count;

For many servers shared with a team, a Central Management Server stores the same kind of list on a SQL Server instance instead of on your PC.

#Keep your scripts in a project

File > New > Project > SQL Server Management Studio Project keeps connections and .sql files together in Solution Explorer. Put the folder under Git: recent SSMS versions include Git integration in the Git menu.

#Options worth changing

Open Tools > Options:

Option names and places can move slightly between SSMS releases. If you can't find one, type part of its name in the search box at the top of the Options dialog.

#AI assistance

SSMS 22 has optional GitHub Copilot integration, which needs a GitHub Copilot subscription. It can explain or write T-SQL using your connected database's schema. Treat its output like any other unreviewed script: read it, run it on a lab copy first, and never let it run changes on production without review.

#Module 12: clean up

USE master;
ALTER DATABASE SsmsLab_Restored SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SsmsLab_Restored;
ALTER DATABASE SsmsLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SsmsLab;
DROP LOGIN SsmsLabReader;

Delete the .bak and .trn files in C:\SQLBackups if you no longer need them.

#Exercises

1. Open the New Database dialog, set a name and file sizes, and script it instead of clicking OK. Change one setting in the script and run it.
2. Use Generate Scripts to script dbo.Customer with schema and data, then run the script in a new empty database.
3. Write a query that returns each customer's latest order. Look at the actual plan, add one index, and record the logical reads before and after.
4. Create a SQL Server Agent job that takes a log backup of SsmsLab every 15 minutes. Use the SQL Agent job walkthrough if you get stuck.
5. Set a red connection color for an instance, open three query windows to it, and confirm the status bar color in each.
6. Start an XEvent Profiler > TSQL session, run dbo.GetCustomerOrders a few times, and find your calls in the live view using the filter.

#Knowledge check

1. What does GO do, and is it part of T-SQL?
2. Why should you use the Script button in dialogs instead of clicking OK?
3. What is the difference between an estimated and an actual execution plan?
4. Your SSMS connection fails with a certificate trust error after upgrading SSMS. Why, and what are the lab and production fixes?
5. Why is restoring a backup to a new database name a safer way to practise than restoring over the original?
6. Which tool would you use to see who is blocking whom right now, and which to see which queries used the most CPU last week?

#Summary

• SSMS is a client: always check the server, database and connection color before you run anything.
• Object Explorer, Object Explorer Details, Registered Servers and Template Browser cover most daily navigation.
• Learn the editor shortcuts: run a selection, results to grid/text, comment, snippets, F12, Alt+F1, Ctrl+L/Ctrl+M.
• Script every dialog. It turns SSMS into a T-SQL teacher and gives you reviewable changes.
• Practise backup and restore, permissions and monitoring on a lab instance before you need them on a real one.
• Next step: SQL Server backup strategy for RPO and RTO and the Extended Events introduction.

#Further reading

• Microsoft Learn: Install SQL Server Management Studio, SSMS release notes, Keyboard shortcuts for SSMS, Display an actual execution plan, Monitor performance by using the Query Store, Use the SSMS XEvent Profiler.
• On this site: SQL Server Agent: create a job in SSMS, backup basics and blocking and deadlocks.