Root Blocker Detection Query For SQL Server

by Dev on

Hi there! In this article, we’ll explore a SQL Server Root Blocker Detection Query that helps DBAs quickly identify the source of blocking and troubleshoot performance bottlenecks.

Blocking is common in any RDBMS (Relational Database Management System). Concurrency is inevitable as multiple processes try to access same data simultaneously.

One session holds a lock and prevents other sessions from proceeding, application response times can increase dramatically. In complex blocking chains, identifying the main blocker is critical because terminating a blocked session does not resolve the underlying issue. The real solution is to locate and investigate the session at the top of the blocking chain.

What is Root Blocker?

A root blocker is the session that is not blocked by any other session but is blocking one or more sessions.

Consider the following blocking chain:

Session 55 → blocks Session 68
Session 68 → blocks Session 72
Session 72 → blocks Session 85

In this scenario:

  • Session 55 is the Root Blocker
  • Session 68, 72, and 85 are blocked sessions

Resolving the issue usually requires investigating Session 55 rather than terminating. Resolving the root blocker typically clears the entire blocking chain. Never kill any session without consent from application owner/team specifically in production environment.

There are many queries out there in the net. But the below one I found to be most affective in my repository.

SQL Server Root Blocker Detection Query

SELECT @@servername [server]
, CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(256)) AS SQLversion
, DB_NAME(s.dbid) DBName
, s.spid
, s1.max_waittime waittime
, LTRIM(RTRIM(s.loginame)) Login
, LTRIM(RTRIM(s.hostname)) Host
, LTRIM(RTRIM(s.program_name)) Programe_Name
, s.login_time
, s.last_batch
, (
    SELECT
        CASE
            WHEN encrypted = 1
                THEN 'Encrypted'
            ELSE
                ISNULL(
                    CAST([text] AS nvarchar(max)),
                    N'empty'
                )
        END
    FROM sys.dm_exec_sql_text(s.[sql_handle])
) sql_text
FROM master.sys.sysprocesses s WITH (nolock)
INNER JOIN
(
    SELECT
        blocked,
        MAX(waittime) [max_waittime]
    FROM master.sys.sysprocesses
    WITH (nolock)
    GROUP BY blocked
) s1
ON s.spid = s1.blocked
WHERE s.blocked = 0
AND s.spid != @@spid;
Example Resultset:

SQL Server Root Blocker Detection Query

Let’s understand the output:

Column Description
Server SQL Server instance name.
SQLVersion SQL Server version.
DBName Database where the blocking occurs.
SPID Session ID of the root blocker.
WaitTime Maximum wait time of the blocked sessions (in milliseconds).
Login SQL Server login executing the blocking session.
Host Client computer (host) name from which the connection originated.
Program_Name Application or program connected to SQL Server.
Login_Time Date and time when the session was established.
Last_Batch Date and time when the last SQL batch was executed.
SQL_Text SQL statement currently being executed by the blocking session.

As you can see, the output has all the required information. The Query is a valuable troubleshooting script for identifying blocking chains quickly. Hope this is helpful.

You may also want to check the following scripts:

PowerShell Script – How many Times SQL Service Restarted

Script To List SQL Agent Jobs and Schedules

Query to List SQL Database Role Owner Across All Databases

Script to get SQL Cluster Failover Time and Node Name

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

{ Add a Comment }

Understanding SQL Server Database Files and Filegroups

by Dev on

Hi there! Here we are going to build a complete Understanding of SQL Server Database Files and Filegroups. This is another basic and one of the important topics to help you in your SQL DBA carrier. This is part of SQL Server DBA Tutorial: Beginners to Pro series.

What are SQL Server Database Files:

Every SQL Server database is made up of at least two operating system files: a data file and a log file.

  • The data file stores data and database objects such as tables, indexes, stored procedures, and views.
  • The log file records the information required to recover all transactions in the database.
Demo: Create database with Default settings and check the files

Create a database with default settings with the following basic T-SQL script.

 CREATE Database Testdb

Now in SSMS Right Click on the Database > Properties > Click on Files

SQL Server Database Files and Filegroups

Types of SQL Server Database Files:

SQL Server Database have three main types of database files:

  1. Primary Data File (.mdf)
  2. Secondary Data File (.ndf)
  3. Transaction Log File (.ldf)
Primary Data File (.mdf):
  • Contains startup information for the database.
  • Stores system tables and metadata.
  • It points to the other files in the database.
  • Every database has only one primary data file.
  • Microsoft recommends the file name extension for primary data files is .mdf.
Secondary Data File (.ndf)
  • This is optional user-defined data file.
  • SQL Database can have multiple secondary files.
  • Data can spread across multiple disks by putting each file on a different disk drive. Improve performance via parallel I/O.
  • Microsoft recommends file name extension for secondary data files is .ndf.
Transaction Log File (.ldf):
  • Stores all transactions and changes.
  • Essential for recovery of the database.
  • Each database has at least one log file.
  • Recommended file name extension for transaction logs is .ldf.

Logical and Physical File Name:

There are two types of File Names if you have observed in the above screen shot. The first column says Logical Name and the last column labeled as File Name with extensions .mdf and .ldf are Physical File Name.

Logical File Name: 
  • The file name which is defined when the database is created.
  • The name used to refer to the physical file in all Transact-SQL statements (BACKUP, RESTORE, CREATE DATABASE, ALTER DATABASE, etc.)
  • The logical file complies with the rules of SQL Server identifiers.
  • Logical file name must be unique among all logical file names in the database.

e.g. In the CREATE Database T-SQL, Testdb is the logical file Name

 CREATE Database Testdb  --Testdb is the Logical File Name of the Database
Physical File Name:
  • Physical file name is the actual name of the file stored on disk.
  • Represents the real .mdf, .ndf, or .ldf file.
  • The physical file name complies with the rules of Operation System identifiers.

Example: Let’s display the logical and physical file names for the database ”Testdb” created above

 USE Testdb 
 GO
 SELECT name as logical_name, physical_name FROM sys.database_files
Result Set showing logical and physical file names:

SQL Server Files and Filegroups

What Is Filegroup:

A Filegroup is a logical container of the database files. It allows you to control how and where database objects (like tables and indexes) are physically stored on disk.

  • Helps in data allocation and administration
  • Enables performance optimization by spreading data across disks

Types of Filegroups:

  • Primary Filegroup
  • User-defined
  • Filestream
  • Memory Optimized Data
Primary Filegroup:
  • Primary filegroup contains the primary data file (.mdf).
  • It holds the system objects and tables.
  • It is the default filegroup.

Bear in mind that you can change the default filegroup using T-SQL so that new objects are created in the user defined filegroup. However system objects and tables remains with the Primary Filegroup.

Example: Let’s check the filegroup information for the database “Testdb” created above.

Right Click on the Database > Properties > Click on Files

SQL Server Files and Filegroups

As can be seen above, the default Filegroup for TestDB data file is showing as PRIMARY. Point often overlooked is the Filegroup for TestDB_log file is Not Applicable.

Hence, keep in mind that SQL Server does not allow you to place transaction log files in any filegroup

User-defined Filegroup:
  • As the name suggests, DBAs create user-defined filegroups.
  • Used to separate large tables, indexes, or archive data.
Filestream Filegroup:
  • Stores unstructured data (e.g., documents, images).
Memory Optimized Data:
  • Used for In-Memory OLTP tables.
  • To create memory-optimized tables, you must first create a memory-optimized filegroup.
Demo: The following T-SQL script shows how to create primary File on PRIMARY Filegroups and Secondary Files on User-Defined Filegroup
T-SQL Script

USE master;
GO

CREATE DATABASE FileGroupDemo
ON PRIMARY -- Primary Filegroup
(
    NAME = N'FileGroupDemo_Primary', -- Logical Filename of Primary File
    FILENAME = N'C:\SQLData\FileGroupDemo_Primary.mdf',  -- Primary Physical Filename 
    SIZE = 100MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
),
FILEGROUP FG_Sales  --User-Defined Filegroup with name FG_Sales 
(
    NAME = N'FileGroupDemo_Sales_01',  --Logical Filename of Secondary File 
    FILENAME = N'C:\SQLData\FileGroupDemo_Sales_01.ndf',  --Physical filename of Secondary File 
    SIZE = 20MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
),
FILEGROUP FG_Archive  --User-Defined Filegroup with name FG_Archive 
(
    NAME = N'FileGroupDemo_Archive_01', --Logical Filename of Secondary File 
    FILENAME = N'C:\SQLData\FileGroupDemo_Archive_01.ndf',  --Physical filename of Secondary File 
    SIZE = 20MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
)
LOG ON
(
    NAME = N'FileGroupDemo_Log', --Logical filename of Transaction Log File 
    FILENAME = N'C:\SQLData\FileGroupDemo_Log.ldf', --Physical filename of Transaction Log File 
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);

Now let’s see the database properties:

SQL Server Files and Filegroups

Hope you got a fair idea about SQL Server File and Filegroups. We’ll discuss about Filestream and Memory Optimized Filegroup in next article. Please feel free to leave a comment.

You may also like to go through:

What is SQL Server

Understanding SQL Server Versions and Editions

What Does SQL DBA Actually Do?

The Complete Guide to SQL Server System Databases

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

Tagged as:

{ Add a Comment }

PowerShell Script – How many Times SQL Service Restarted

by Dev on

Hi, being SQL DBA there may be situations when you need to find out How many times SQL Service restarted. E.g. Let’s say there is an issue going on with a SQL Server and services got restarted. Your boss has asked you “Is it the first time it has happened? Can you tell me quickly the SQL Service restart history.

This is just one example, whatever may be the scenario, it is always good to have a PowerShell script handy.

The script counts the no. of SQL Server error logs and scan them for the text “SQL Server is starting”.

The output will have the complete message in the error log containing the text along with date and time of occurrence in server local time zone.

Script To Find How Many Times SQL Service Restarted:

####################################################################
# Objective: To Check how many times SQL Services got restarted 
# Author: Tridib Dev
# Source:https://sqlgeekspro.com/
# Parameter: You need to provide SQL Instance Name
#################################################################### 

# Define Input Parameter for SQL Instance Name
param([parameter(Mandatory = $true)] $SQLInstanceName)

Write-host 'SQL Service restart Details for: ' $SQLInstanceName
Write-host '-----------------------------------------------------------'
Write-host " "

# Using SMO for Server Object creation
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstanceName
# Loading sqlserver module
Import-Module sqlserver -DisableNameCheck

# Defining the connection string which helps in establishing connection from remote server
$connection = "Data Source=$SQLInstanceName; Initial Catalog=master; Integrated Security=True; trustservercertificate=True;"

# Get error log list
$logs = Invoke-Sqlcmd -ConnectionString $connection -Query "EXEC xp_enumerrorlogs"

# Count no. of Error logs
$logCount = $logs.Count

Write-Host "Number of SQL Server error logs: $logCount"

foreach ($i in 0..$logCount) {
     
    # Call the ReadErrorLog method
    $logEntries = $srv.ReadErrorLog($i)
    
    # Filter and display results
    $logEntries | 
        Where-Object { ($_.Text -match 'SQL Server is starting') } |
        Format-Table LogDate, ProcessInfo, Text -AutoSize
}

Once you execute the script in PowerShell ISE, it will ask for the SQL Instance name for which you want to find the SQL Service restart history:

PowerShell Script How Many Times SQL Service Restarted

Example Result Set:
How Many Times SQL Service Restarted

The output shows there are 100 SQL Error log files and the script has scanned them all. The SQL Service has restarted 3 times.

Hope it is is helpful. Please feel free to customize the script as per your requirements. Also you can use the same script to scan any other messages/errors by replacing the text.

Please leave a comment about your experience. You may also like to check the following interesting scripts.

Powershell Script to Validate SQL Servers

Powershell Script to Create Folders

List all permissions for a user in all or selective databases

Powershell Script to Create Folders

 

 

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

Tagged as: ,

{ Add a Comment }

What Is SQL Server Transaction: Complete Beginner’s Guide

by Dev on

Hi There! This article is to explain What Is SQL Server Transaction and this is part of the SQL Server DBA Tutorial: Beginners To Pro category. This will help in your journey to become a better DBA.

What is SQL Server Transaction?

A SQL Server transaction is a sequence of one or more SQL statements that are executed together. The transaction ensures that either all operations succeed or none of them are applied to the database.

In other words, batch of SQL Statement(s) performing a single or multiple activities on a database. Transactions ensures data integrity by either of the below:

  • Committing all changes together
  • Rolls back changes if any operation fails.

Example 1:

What is SQL Transaction

Example 2:

What is SQL Transaction

 

Practical Example: Bank Money Transfer:

Suppose you are transferring money from Account A to Account B.
Both operations must succeed together or fail. I.e. Either money should get credited to Account B or the money should not debit from Account A.

Different Modes Of SQL Server Transactions:

  • Auto-commit transactions
  • Implicit transactions
  • Explicit transactions
  • Batch-scoped transactions

Auto-commit transactions:

Each individual statement is a transaction. The auto-commit transaction mode is the default SQL Server transaction mode. When an SQL statement executes successfully, the system commits the transaction and permanently saves the data modifications in the database. If any statement encounters an error during execution, the system rolls back the transaction and reverts all changes made during that transaction.

Example:

 INSERT INTO Products (ProductID, ProductName, Price)
 VALUES (1,'Laptop',1200)

 UPDATE Products SET Price = 1300 WHERE ProductID = 1

 DELETE FROM Products WHERE ProductID = 1

As you must have observed that there is no BEGIN TRAN statement mentioned in the above example and hence each statement (INSERT, UPDATE, DELETE) runs as its own transaction.

Imagine a scenario when INSERT and UPDATE completes successfully but the DELETE statement fails, only the DELETE is rollback, the changes for INSERT and UPDATE remains saved.

Implicit transactions

In SQL Server, an Implicit Transaction starts automatically when you execute certain SQL statements. The transaction does not commit automatically; you must explicitly end it using COMMIT or ROLLBACK.

Let’s understand Implicit transaction by coding example:

Create a Table: 
CREATE TABLE Products ( 
    ProductID INT PRIMARY KEY, 
    ProductName VARCHAR(50),
    Price DECIMAL(10,2)
)
Enable Implicit Transaction Mode:
SET IMPLICIT_TRANSACTIONS ON;
Commit an Implicit Transaction:
INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1,'Laptop',1200)
COMMIT;
Rollback an Implicit Transaction:
UPDATE Products SET Price = 1300 WHERE ProductID = 1
ROLLBACK;
Do nothing and it will keep an open transaction:
DELETE FROM Products WHERE ProductID = 1

Since you have not COMMIT or ROLLBACK this statement, it will keep this as open transaction.
Reference Screen Shot:
What is SQL Server Transaction

Explicit transactions:

In SQL Server, an Explicit Transaction is a transaction that you start manually using BEGIN TRANSACTION and end using COMMIT or ROLLBACK. This gives you full control over when the transaction starts and ends.

Explicit Transaction with COMMIT:
BEGIN TRANSACTION

INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1,'Laptop',1200)

UPDATE Products SET Price = 1300 WHERE ProductID = 1

DELETE FROM Products WHERE ProductID = 1

COMMIT;

This will save all the results of INSERT, UPDATE and DELETE. If you replace COMMIT with ROLLBACK command, it will save nothing.

Batch-scoped transactions:

Applicable only to Multiple Active Result Sets (MARS), when a Transact-SQL explicit or implicit transaction starts in a MARS session, SQL Server treats it as a batch-scoped transaction. If the transaction is not committed or rolled back before the batch finishes, SQL Server automatically rolls it back.

You may also like to go through the below articles:

What is SQL Server

Understanding SQL Server Versions and Editions

The Complete Guide to SQL Server System Databases

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

{ Add a Comment }

Why Instant File Initialization Is Not Enabled By Default: SQL DBA Interview Q&A

by Dev on

Hi There! If you are preparing for SQL DBA Interview, you are in right place. This article describes how to answer to the question Why Instant File Initialization (IFI) is not enabled by default in SQL Server.

Why Instant File Initialization Is Not Enabled By Default

I have already explained What is IFI and few possible interview questions in What is Instant File Initialization? SQL DBA Interview Q & A . Please read and come back to this for better understanding.

Now you may think that since IFI significantly improves performance, the system should enable it by default. Let’s understand why it is not so.

One Liner Answer:

By default, SQL Server does not enable IFI mainly to prevent security issues and the risk of exposing deleted/wrong data.

Next obvious question would be “Could you please explain the risk?”

Answer with Explanation:

IFI is not enabled by default because when users delete data, the Free Space Management system in the operating system does not physically delete the data. Rather it marks the space(blocks) holding the data as reusable.

Technically marking the storage blocks as “unallocated” in a file system table or bitmap. The actual data remains until overwritten, but the space is immediately available for new files, often using techniques like linked list or bitmaps.

When IFI is not enabled, the system zeros out the page before writing any data. However, when IFI is enabled, the system skips the zero-initialization step and overwrites the deleted disk content only when it writes new data to the files.

As a result, the newly allocated pages may contain previously deleted data, and a slight risk exists that a third-party tool could access the deleted content until another process writes new data to that specific area of the data file.

Additional Information:

This section is only for your knowledge and generally you don’t have to explain in Interview. But helps in case the interviewer asks further details.

While the database file remains attached to the SQL Server instance, the file’s discretionary access control list (DACL) reduces the risk of information disclosure. The DACL allows access only to the SQL Server service account, its service SID, and the local administrator. However, when someone detaches the file, a user or service without the SE_MANAGE_VOLUME_NAME privilege may still be able to access it.

Hope this helps in better understanding and you’ll be able to explain to the Interviewer Why IFI is not enabled by default. You may also like to go through the following for your SQL DBA Interview preparation.

Junior SQL Server DBA Interview Questions and Answers

Junior SQL DBA Interview Question Answers -Part2

How To Identify SQL Server Service Account: SQL DBA Interview Q&A

Junior SQL DBA Interview Questions Answers -Part3

Junior SQL DBA Interview Question Answers: Part 4

What is Instant File Initialization? SQL DBA Interview Q & A

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

{ Add a Comment }

What is Instant File Initialization? SQL DBA Interview Q & A

by Dev on

Hi There! We are going to discuss What is Instant File Initialization. The idea here is to explain you what does Instant File Initialization (IFI) mean and how it works. How far you should explain to an Interviewer and what has changed in SQL 2022.

What is Instant File Initialization

What is Instant File Initialization?

It is basically a Windows Operating System (OS) feature. Whenever SQL Server creates or expands a data or log file, OS validates the disk by writing zeros to the files. This process is known as Zeroing process.

The internal steps are as follows:
  • Allocate the space on disk
  • Writes zeros to the entire allocated portion
  • Then make it available for use

It is a single threaded process and hence for large files the delay is quite visible. Instant File Initialization allows SQL Server to allocate space for data files without zeroing out the disk space first and hence reduces the wait time. Basically it skips the zeroing process.

When you are answering in an interview this much should suffice. Continue to read for more knowledge and to be able to answer any follow up questions.

Does IFI applies to Log File?

In the past, transaction log files could not use instant file initialization. However, starting with SQL Server 2022 (16.x) — across all editions — and in Azure SQL Database and Azure SQL Managed Instance, transaction log autogrowth can now use instant file initialization for growth events up to 64 MB.

By default, new databases are configured with a 64 MB autogrowth size, which means they automatically benefit from this improvement.

If the transaction log grows by more than 64 MB at a time, instant file initialization will not apply, and the growth will take longer as the space must be zero-initialized.

What are the database Activities benefited by IFI?

  • Creating a New Database
  • Adding data or log file to an existing database
  • Increasing the existing file size manually or due to auto growth option
  • Restoring a database from backup file

How IFI works if Transparent Data Encryption (TDE) is Enabled?

If TDE is enabled for a database, IFI stops working for datafile however it is allowed for transaction log growth, because of how the transaction log file grows, and the fact that the transaction log is written into in a serial fashion.

How IFI works in SQL Azure and Managed Instance?

  • In Azure SQL Database and Azure SQL Managed Instance, transaction log autogrowth events up to 64 MB can benefit from instant file initialization.
  • Instant file initialization is automatically used in the General Purpose and Business Critical tiers of Azure SQL Database and Azure SQL Managed Instance. It helps improve the growth of transaction log files.
  • Instant file initialization isn’t configurable in Azure SQL Database and Azure SQL Managed Instance.

What kind of Environments will be benefited by IFI?

  • High-availability systems
  • Large databases
  • Systems with frequent restores
  • Systems experiencing auto-growth delays

There are very few reasons not to enable it. Ideally it should be part of SQL server build process.

You may also like to check the following:

Junior SQL Server DBA Interview Questions and Answers

SQL DBA Interview Question Answers -Part2

Junior SQL DBA Interview Questions Answers -Part3

Junior SQL DBA Interview Question Answers: Part 4

 

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

Tagged as: ,

{ Add a Comment }

Fix XTP Configuration Is Invalid Error In Always On

by Dev on

Hello, this article is going to be part2 of How To Fix: XTP Configuration Is Invalid where we have learnt about the SCOM alert, what is XTP in SQL Server and how to fix the same in stand alone SQL Instance.

Here we’ll learn about the steps to fix XTP Configuration is invalid error in Always On SQL Instance. Hence I would like you to first go through the first article.

Steps to Fix Configuration Is Invalid Error In Always On

  1. Calculate Resource Pool Memory
  2. Create a resource pool
  3. Bind the database to the pool
  4. Bring the database offline and then online
  5. Confirm the Binding

Now you may be thinking, well these are exactly the same steps for stand alone SQL Instance. Yes, you’re right, however in case of Always On you need to perform few extra steps for the point no. 4.

I have already explained how to calculate resource pool memory in the first article Link. Here I’ll start from point no.2

2. Create a resource pool

In case of Always On you have to create the resource pool in all the replicas.
Execute the below query on each replica.

 CREATE RESOURCE POOL PoolName  --Provide the pool name maintaining Organization's naming convention
  WITH
   (
     MIN_MEMORY_PERCENT = 63,  --Adjust as needed/calculated 
     MAX_MEMORY_PERCENT = 63   --Often set to the same value as MIN_MEMORY_PERCENT for Predictability
   )
 GO
 ALTER RESOURCE GOVERNOR RECONFIGURE;

e.g. XTP Configuration is Invalid

3. Bind the database to the pool:

Execute the below query on all the Replicas

EXEC  sys.sp_xtp_bind_db_resource_pool YourDatabaseName, 'PoolName';

e.g.XTP Configuration is Invalid

4. Bring the database offline and then online:

This is a bit tricky in case of Always On setup. You need to remove the db from Availability Group before you can take the db offline.

Following are the steps:

  • Remove the database from Availability Group
  • Take the database Offline and then Online on Primary Replica
  • Readd the database back to Availability Group
  • Failover the Availability Group to each of the replicas and Failback to the original Primary Replica.
5. Confirm the Binding:

This also needs to be executed on all the Replicas

SELECT d.database_id, d.name, d.resource_pool_id   
FROM sys.databases d WHERE name='SQLGeeksPro' --Change database name
GO

Example Result Set:

XTP Configuration is Invalid in Always On

As you can observe there is an associated resource_pool_id for the example database “SQLGeeksPro”.

Hope this helps. Please feel free to leave a comment.

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

Tagged as: ,

{ Add a Comment }

What Does SQL DBA Actually Do?

by Dev on

Hi There! Whether you are a student, aspiring data professional, or thinking to transform to a DBA role and wondering “What Does SQL DBA Actually Do”, you are not alone. The title Database Administrator (DBA) might sound mysterious, but it’s one of the most critical roles in IT .

What Does SQL DBA Actually Do

This guide breaks down what a SQL Server DBA really does, the tools they use and additional responsibilities as they grow in their carrier.

There are basically two type of DBA jobs in the IT industry.

Application DBA/Logical DBA — Works closely with Application Developers. Designs schema, tables, indexes, and relationships according to application needs. Require Strong SQL coding skills.

Production/Infrastructure DBA — Focuses on database operations, uptime, performance, and recovery in the production environment. Demands Strong system administration and database configuration skills.

To give you one real time scenario to understand a basic difference between the two:

When a Query is running slow, Production DBA will identify the exact slow running query and provide the query(execution) plan to App DBA. App DBA will analyze the plan and fix the query.

In some organizations the responsibilities may overlap depending upon the job requirement.

Key Responsibilities of a SQL Server DBA:

Area Application DBA Production DBA
Database Backup & Recovery Is not Responsible Ensure all database servers are backed up in alignment with Recovery Point Objective (RPO) and regularly
tests restores of the backup files to make sure Recovery Time Objective (RTO) can be met.
Database Design Designs schema, tables, indexes, and relationships according to application needs. Ensures design aligns with operational standards and performance requirements.
SQL & Performance Tunes SQL queries, stored procedures, and application-side performance. Tunes system-level performance—memory, I/O, CPU, and indexing strategies.
Development Support Works with developers during development and testing phases. Supports deployments, migrations, and production rollouts.
Maintenance Less involved in backups and system maintenance. Handles patching, upgrades, Index maintenance and monitoring.
Security & Access Defines roles and privileges for applications. Manages overall database security, auditing, and compliance.
Troubleshooting Fixes app-specific data or query issues. Diagnoses system-level issues, server crashes, corruption, and performance bottlenecks.

Essential Tools/Functionalities Every SQL Server DBA Should Know:

Tool Description
SQL Server Management Studio (SSMS) Core interface for managing SQL databases.
Azure Data Studio Modern, cross-platform alternative with notebooks.
SQL Server Extended Events For tracing and debugging query performance.
Dynamic Management Views (DMVs) Provides insight into SQL Server to assist with troubleshooting and monitoring.
SQL Agent Service Automates scheduled jobs and maintenance tasks.
PowerShell / dbatools For automating DBA workflows.
Query Store Simplifies the process of identifying and resolving Query performance issues by providing insights into query plan.
Monitoring Tool like Redgate SQL Toolbelt Professional suite for monitoring, comparison, and deployments.

Now as you grow in your DBA carrier, your role will not be limited to only technical work. I have tried to put few more critical responsibilities based on my experience.

Automation: Analyze repetitive tasks and check what and how far a task can be automated. Work with automation team, provide suggestions etc.

Incident reduction: Senior DBA/leads will work on analyzing ticket dumps and work on reducing repetitive incidents on same SQL Instance.

RCA (Root Cause Analysis) : On P1/P2 incidents, Leads/SME (Subject Matter Specialist) will work with Problem Management Team to document the RCA.

SOP (Standard Operating Procedure): You’ll be responsible for preparing SOPs for junior resources or on particular process to be followed for repetitive activities. e.g. Steps to Follow from database end during OS patching, Database Decommission, SQL Instance Provision/Decommission etc.

Customer Escalations: Handling customer escalations will be one of your prime responsibilities.

Mentoring Junior DBAs: Guiding Junior DBAs and providing necessary technical/process related trainings. Making proper plan for newly onboarded team member to bring him/her up to the speed etc.

Planning Major Activities: You will need to prepare Runbooks for major activities like DR (Disaster Recovery Drill), SQL Server upgrades etc.

Hope now you have got an overall idea about what Does a SQL DBA Actually Do. Please feel free to comment in case you want me to add any further responsivities.

You can also refer the following links:

What is SQL Server

Understanding SQL Server Versions and Editions

The Complete Guide to SQL Server System Databases

 

 

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

{ Add a Comment }

How To Fix: XTP Configuration Is Invalid

by Dev on

Hi There! In this article we are going to discuss about the SCOM Alert “MSSQL On Windows: XTP Configuration Is Invalid”. At first let’s see the alert description:

“XTP configuration for database “DatabaseName” on SQL Server instance “SQLInstance Name”, computer “ServerName” is not set according to the best practices. Database with memory-optimized tables should be bound to a dedicated resource pool.”

This alert points to issues with the configuration of In-Memory OLTP (XTP) features in SQL Server, which can impact database performance and reliability. As per the alert description, the database has memory-optimized tables and there is no dedicated resource pool. That is why you are not going to find out any error in SQL Error log related to this.

What is XTP in SQL Server?

XTP stands for Extreme Transaction Processing, an informal name to describe the In-Memory OLTP feature that powers memory-optimized tables. It is to boost transaction throughput by minimizing disk I/O and maximizing data access speed.

When you enable and create memory-optimized tables, SQL Server requires specific settings and prerequisites to ensure the XTP engine runs smoothly.

What is the Impact on SQL Server?
  • SQL Engine will allocate memory to memory-optimized tables from the default pool. This leads to uncontrolled memory consumption, which can cause overall memory pressure.
  • Without proper resource pools, SQL Server cannot isolate memory for In-Memory OLTP.
  • Result: potential server performance degradation, query slowdowns, or even crashes causing downtime.
How to Fix the XTP Configuration is Invalid Error?
  • Calculate Resource Pool Memory
  • Create a resource pool
  • Bind the database to the pool
  • Bring the database offline and then online

Well it is not that simple as it sounds. You need to calculate the memory percentage to allocate to the resource pool.

First of all you need to know the amount of memory needed for the memory optimized tables in the database. You can go through the Microsoft documentation Estimate Memory Requirements for Memory-Optimized Tables for the same.

Ideally this number should be provided by the app team to production DBA to do the rest of the configuration in SQL Server. However in some cases your app team may not be able to help with the information. Then you need to proceed as per the size in pre prod environment by monitoring the growth for couple of weeks.

Calculate Resource Pool Memory:

percentNeeded =  memoryNeeded / memoryCommitted * availablePercent

  • memoryNeeded: Amount of memory needed for memory optimized tables
  • memoryCommitted:  SQL Server Max Memory
  • availablePercent: Refer the below table:
Target Committed Memory Percent available for in-memory tables
<= 8 GB 70%
<= 16 GB 75%
<= 32 GB 80%
<= 96 GB 85%
>96 GB 90%

For example, If

  • memoryNeeded = 16 GB
  • memoryCommitted = 32 GB
  • then availablePercent (considering the table) = 80%

Hence by plugging real numbers

percentNeeded = 16 / (32 * .8) = 16/25.6 =.625 

Converting the result to percentage .625 * 100 = 62.5

percentNeeded = 62.5% rounding of to 63%

Create a resource pool:

Execute the following script to create a resource pool:

 CREATE RESOURCE POOL PoolName  --Provide the pool name maintaining Organization's naming convention
  WITH
   (
     MIN_MEMORY_PERCENT = 63,  --Adjust as needed/calculated 
     MAX_MEMORY_PERCENT = 63   --Often set to the same value as MIN_MEMORY_PERCENT for Predictability
   )
 GO
 ALTER RESOURCE GOVERNOR RECONFIGURE;

e.g. XTP Configuration is Invalid

Just to clarify, resource pool is created at SQL Instance level and not on database.

Bind the database to the pool:
EXEC  sys.sp_xtp_bind_db_resource_pool YourDatabaseName, 'PoolName';

e.g.XTP Configuration is Invalid

Next and last step is to take the database offline and than bring it online as can be seen in the messages once you bind the database to the resource pool.

Hope this helps. For Always ON SQL Instance there are few extra steps needs to be followed which we’ll discuss in the next blog Fix XTP Configuration Is Invalid Error In Always On

You may also like to go through the below:

Query to List SQL Database Role Owner Across All Databases

Query To List SQL Database Role Owner

SQL Server FORMAT Function Quick Guide

Please spread the word:
RSS
Follow by Email
Facebook
fb-share-icon
Twitter
Tweet
PINTEREST
LinkedIn
Share

Tagged as:

{ Add a Comment }