RANDOM (05-02-2025) query to review from notes

 SELECT protocol_desc 

FROM sys.dm_exec_connections 

WHERE session_id = @@SPID;


Purpose of the Query

SELECT protocol_desc 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

This query is used to determine the protocol used for the current SQL Server session/connection.

Breakdown of the Query

Component Meaning
sys.dm_exec_connections A Dynamic Management View (DMV) that provides details about active connections to SQL Server.
protocol_desc The network protocol used by the connection (e.g., TCP, Named Pipes, Shared Memory).
session_id A unique identifier for each active session in SQL Server.
@@SPID Returns the Session ID (SPID) of the current connection (i.e., the user running the query).

Possible Values of protocol_desc

Protocol Description
TCP Connection is using TCP/IP, the most common network protocol for remote connections.
Named Pipes Connection uses Named Pipes, mostly used for local connections or within a LAN.
Shared Memory Connection is using Shared Memory, which is the fastest protocol but works only for local connections (same machine).
VIA (deprecated) Virtual Interface Adapter (VIA), but this is rarely used and deprecated in newer SQL Server versions.

When is This Query Useful?

✅ Troubleshooting Connection Issues

  • If a remote connection is failing, check if the client is using the correct protocol (TCP is usually preferred).
  • If the result is Shared Memory, the connection is local and cannot be accessed remotely.

✅ Checking Performance Impact

  • Shared Memory is the fastest but only works locally.
  • TCP/IP is the most scalable and preferred for production environments.

✅ Confirming Configuration Changes

  • If you recently changed SQL Server network configuration, this query verifies which protocol is being used.

Example Usage

1️⃣ Check Protocol for Current Session

SELECT protocol_desc 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;
  • Output Example: TCP

2️⃣ Check Protocols for All Active Connections

SELECT session_id, protocol_desc, client_net_address 
FROM sys.dm_exec_connections;
  • Helps monitor how all users are connecting.

How to Change SQL Server Network Protocol?

  1. Open SQL Server Configuration Manager.
  2. Navigate to SQL Server Network Configuration → Protocols for [Instance Name].
  3. Enable or disable protocols like TCP/IP, Named Pipes, Shared Memory.
  4. Restart the SQL Server service for changes to take effect.

Would you like help troubleshooting a specific connection issue? 😊🚀



sp_readerrorlog 

Run this query to check your database size:

=============================================

EXEC sp_spaceused;

or

SELECT name AS DatabaseName, size * 8 / 1024 AS Size_MB

FROM sys.master_files

WHERE type = 0;


 


select @@version 

select @@spid


ys View / Table Description

sys.tables          Lists all user tables in the database.

sys.columns       Lists all columns for each table.

sys.indexes      Shows all indexes created in the database.

sys.foreign_keys Displays foreign key constraints.

sys.sysprocesses Shows currently running processes.

sys.dm_exec_sessions Lists active user sessions.

sys.configurations Lists SQL Server configuration settings.


SELECT name AS DatabaseName, size * 8 / 1024 AS Size_MB

FROM sys.master_files

WHERE type = 0;

sys.master_files is a system catalog view in SQL Server that provides details about all database files (MDF, NDF, and LDF) across all databases on the SQL Server instance.

What is sys.master_files in SQL Server?

sys.master_files is a system catalog view in SQL Server that provides details about all database files (MDF, NDF, and LDF) across all databases on the SQL Server instance.

Breakdown of the Query

SELECT name AS DatabaseName, size * 8 / 1024 AS Size_MB
FROM sys.master_files
WHERE type = 0;
Clause Purpose
sys.master_files Retrieves information about all database files stored on the SQL Server instance.
size * 8 / 1024 Converts pages (8 KB each) to MB.
WHERE type = 0 Filters only data files (MDF, NDF).

Understanding sys.master_files Columns

Column Description
database_id The ID of the database that owns the file.
file_id Unique ID of the file within the database.
name The logical name of the file.
physical_name The full file path on disk.
type File type: 0 = Data file (MDF, NDF), 1 = Log file (LDF).
size File size in 8 KB pages.
max_size The maximum size the file can grow.
growth Growth settings (e.g., in MB or percentage).
state_desc File state (e.g., ONLINE, OFFLINE, RECOVERY_PENDING).

How to Get More Details About All Database Files?

SELECT database_id, name, physical_name, type_desc, size * 8 / 1024 AS Size_MB, max_size, growth
FROM sys.master_files
ORDER BY database_id;

✅ Shows file location, size, type (MDF, NDF, LDF), growth settings, and max size.

Difference Between sys.master_files and sys.database_files

View Scope
sys.master_files Shows all database files in the SQL Server instance (including system databases).
sys.database_files Shows only the files for the current database (MDF, NDF, LDF).

🔹 If you want files for a specific database, use:

SELECT * FROM sys.database_files;

(This works only inside the database context.)

Example Queries

1️⃣ Find All Data & Log Files for All Databases

SELECT name, physical_name, type_desc, size * 8 / 1024 AS Size_MB
FROM sys.master_files;

2️⃣ Find Log File Sizes Only

SELECT name, physical_name, size * 8 / 1024 AS Size_MB
FROM sys.master_files
WHERE type = 1;  -- Log files (LDF)

3️⃣ Find File Locations for a Specific Database (e.g., MyDB)

SELECT name, physical_name, size * 8 / 1024 AS Size_MB
FROM sys.master_files
WHERE database_id = DB_ID('MyDB');

Why Use sys.master_files?

✅ Monitor database file sizes.
✅ Find file locations for backup or migration.
✅ Identify log file growth issues.
✅ Troubleshoot database storage problems.



Why Do We Use size * 8 / 1024 in SQL Server?

===============================================

In SQL Server, database file sizes are stored in pages, and each page is 8 KB in size. To convert the file size from pages to MB or GB, we use the following calculation:


Breakdown of size * 8 / 1024:

size (from sys.master_files) represents the number of 8 KB pages.

Multiply by 8 to get the size in KB.

Divide by 1024 to convert KB to MB.


===============================================================



dm_


dm_exec_sessions


SELECT session_id, login_name, host_name, program_name, status

FROM sys.dm_exec_sessions;

SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID;


Shows currently running SQL statements, their status, and execution time.

SELECT session_id, status, blocking_session_id, wait_type, start_time, command, sql_handle

FROM sys.dm_exec_requests;

--->Helps monitor active queries.

--->Identifies blocking sessions.


SELECT session_id, login_name, host_name, program_name, status, cpu_time

FROM sys.dm_exec_sessions;

 --> Lists all active user and system sessions.

 

 Provides details about active connections, including protocol and encryption.

 ===============================================================================

 SELECT session_id, local_net_address, client_net_address, protocol_type

FROM sys.dm_exec_connections;


Shows execution statistics of cached queries (CPU, I/O, execution count).

===========================================================================

SELECT TOP 10 total_worker_time AS CPU_Time, execution_count, total_elapsed_time,

       (total_elapsed_time / execution_count) AS Avg_Run_Time,

       (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS QueryText

FROM sys.dm_exec_query_stats

ORDER BY total_worker_time DESC;

No comments:

Post a Comment