About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

Demystifying AI Agents: From Basics to Real-World Applications

Demystifying AI Agents: From Basics to Real-World Applications

Table of Contents

  1. Introduction to AI Agents

  2. AI Agents vs. Agentic AI vs. Generative AI

  3. Understanding Multi-Agent Systems (MAS)

  4. Frameworks for Building AI Agents

  5. Real-World Applications of AI Agents

  6. Next Steps for Exploration


Introduction to AI Agents

An AI Agent is an autonomous system designed to perceive its environment, process information, and take actions to achieve specific goals.

Core Functions of AI Agents:

  1. Sensing: Collecting data through various inputs like sensors, APIs, or user interactions.

  2. Reasoning: Making decisions based on algorithms, which can be rule-based, machine learning models, or large language models (LLMs).

  3. Acting: Executing tasks such as moving a robotic arm, generating responses, or triggering APIs.

  4. Learning (optional): Improving performance over time through methods like reinforcement learning.

Example: A self-driving car perceives road conditions, decides when to brake, and acts by applying the brakes.


AI Agents vs. Agentic AI vs. Generative AI

Understanding the distinctions between these concepts is crucial:

ConceptDefinitionExample
AI AgentAn autonomous system that perceives and acts within an environment.Chatbots, robotic vacuums.
Agentic AIAI that autonomously decomposes complex tasks into sub-goals and executes them.An AI planning a trip by researching flights, hotels, and weather.
Generative AIAI that creates new content such as text, images, or music without autonomy.ChatGPT, DALL·E.

Key Insight:

  • Generative AI serves as a tool for content creation.ResearchGate

  • AI Agents utilize Generative AI as a component within a larger system.

  • Agentic AI acts as a meta-agent, orchestrating multi-step workflows autonomously.


Understanding Multi-Agent Systems (MAS)

Multi-Agent Systems (MAS) involve multiple AI agents interacting within an environment, either collaborating or competing to achieve individual or shared goals.

Examples of MAS:

  • Swarm Robotics: Drones coordinating for search and rescue missions.

  • Blockchain Networks: Nodes validating transactions collectively.

  • Simulated Economies: AI agents trading goods in a virtual marketplace.

Frameworks Supporting MAS:

  • Ray/RLlib: For developing reinforcement learning agents.

  • AutoGen: Microsoft’s framework for conversational agents.

  • LangGraph: Designed for LLM-based agent workflows.​


Frameworks for Building AI Agents

Several frameworks facilitate the development of AI agents:

FrameworkUse CaseKey Feature
LangChainLLM-powered agents.Integrates tools like Google Search and APIs.
AutoGPTAutonomous goal-driven AI.Self-prompting for complex tasks.
Hugging Face AgentsNLP-centric agents.Access to pre-trained models and tools.
Microsoft AutoGenMulti-agent conversations.Customizable agent teams for complex interactions.

Example Workflow with LangChain:

  1. An agent uses an LLM to interpret a user query.​

  2. It delegates subtasks, such as fetching data via APIs.​

  3. It synthesizes and delivers a comprehensive response.​


Real-World Applications of AI Agents

AI agents are transforming various industries:

  • Healthcare: Diagnostic agents analyzing patient data for better outcomes.​

  • Finance: Autonomous trading bots making real-time investment decisions.​

  • Customer Service: Multi-agent systems handling queries, inventory checks, and processing refunds.​


Next Steps for Exploration

To delve deeper into AI agents:

  1. Hands-on Practice:

    • Build a simple agent using LangChain, such as a research assistant that scrapes the web.​

    • Experiment with AutoGen for creating multi-agent conversational systems.​

  2. Advanced Learning:

    • Study Reinforcement Learning to understand how agents learn and adapt over time.​

    • Explore Agentic AI frameworks like BabyAGI for more complex autonomous systems.​



 Below code is an example of Agent IA

from dotenv import load_dotenv

import yfinance as yf

from langchain_groq import ChatGroq

from langchain.agents import AgentExecutor, create_tool_calling_agent

from langchain_core.prompts import ChatPromptTemplate

from langchain_core.tools import tool

import pandas as pd

import logging


# Configure logging

logging.basicConfig(level=logging.INFO)

logger = logging.getLogger(__name__)


# Load environment variables

load_dotenv()


def format_table(df: pd.DataFrame) -> str:

    """Convert DataFrame to markdown table with safe formatting"""

    try:

        return df.fillna('N/A').to_markdown(floatfmt=".2f", index=False)

    except Exception as e:

        logger.error(f"Table formatting error: {str(e)}")

        return str(df)


def validate_stock(symbol: str) -> bool:

    """Enhanced stock validation with multiple checks"""

    try:

        stock = yf.Ticker(symbol)

        info = stock.info

        return all([

            not stock.history(period="1d").empty,

            info.get('currency') is not None,

            info.get('currentPrice') is not None

        ])

    except Exception as e:

        logger.error(f"Validation failed for {symbol}: {str(e)}")

        return False


@tool

def get_live_stock_data(symbol: str) -> str:

    """Get real-time stock data with enhanced reliability"""

    if not validate_stock(symbol):

        return f"Invalid symbol or data unavailable: {symbol}"

    

    try:

        stock = yf.Ticker(symbol)

        info = stock.info

        data = stock.history(period="1d")

        

        # Safely handle missing data

        current_price = data.iloc[-1].Close if not data.empty else info.get('currentPrice', 'N/A')

        market_cap = info.get('marketCap')

        

        stats = {

            "Currency": info.get('currency', 'USD'),

            "Current Price": current_price,

            "Open": info.get('open', 'N/A'),

            "Volume": f"{info.get('volume', 'N/A'):,}",

            "52W High": info.get('fiftyTwoWeekHigh', 'N/A'),

            "52W Low": info.get('fiftyTwoWeekLow', 'N/A'),

            "PE Ratio": info.get('trailingPE', 'N/A'),

            "Market Cap": (

                f"${market_cap/1e9:.2f}B" if market_cap and info.get('currency') == 'USD' else

                f"HK${market_cap/1e9:.2f}B" if market_cap and info.get('currency') == 'HKD' else

                f"₹{market_cap/1e7:.2f}Cr" if market_cap else 'N/A'

            )

        }

        

        return format_table(pd.DataFrame([stats]))

        

    except Exception as e:

        logger.error(f"Live data error: {str(e)}")

        return f"Failed to fetch live data: {str(e)}"


@tool

def get_historical_data(symbol: str, period: str = "1y") -> str:

    """Get historical data with fault-tolerant calculations"""

    if not validate_stock(symbol):

        return f"Invalid symbol: {symbol}"

    

    try:

        stock = yf.Ticker(symbol)

        hist = stock.history(period=period)

        

        if hist.empty:

            return "No historical data available"

        

        # Calculate with error handling

        hist['50MA'] = hist['Close'].rolling(window=50, min_periods=1).mean()

        hist['200MA'] = hist['Close'].rolling(window=200, min_periods=1).mean()

        

        # Safe RSI calculation

        delta = hist['Close'].diff(1)

        gain = delta.where(delta > 0, 0)

        loss = -delta.where(delta < 0, 0)

        avg_gain = gain.rolling(14).mean()

        avg_loss = loss.rolling(14).mean()

        rs = avg_gain / avg_loss

        hist['RSI'] = 100 - (100 / (1 + rs))

        

        return format_table(hist.tail(5)[['Close', '50MA', '200MA', 'RSI']])

        

    except Exception as e:

        logger.error(f"Historical data error: {str(e)}")

        return f"Failed to fetch history: {str(e)}"


@tool

def get_fundamental_analysis(symbol: str) -> str:

    """Robust fundamental analysis with type safety"""

    try:

        stock = yf.Ticker(symbol)

        info = stock.info

        

        metrics = {

            "ROE (%)": try_convert(info.get('returnOnEquity'), float, 0) * 100,

            "Debt/Equity": try_convert(info.get('debtToEquity'), float),

            "EPS": try_convert(info.get('trailingEps'), float),

            "Dividend Yield (%)": try_convert(info.get('dividendYield'), float, 0) * 100,

            "Beta": try_convert(info.get('beta'), float),

            "Institutional Ownership (%)": try_convert(info.get('heldPercentInstitutions'), float, 0) * 100

        }

        

        return format_table(pd.DataFrame([metrics]))

        

    except Exception as e:

        logger.error(f"Fundamental analysis error: {str(e)}")

        return f"Failed fundamental analysis: {str(e)}"


def try_convert(value, target_type, default=None):

    """Safe type conversion helper"""

    try:

        return target_type(value) if value not in [None, 'N/A'] else default

    except (ValueError, TypeError):

        return default


# Initialize Groq with error handling

try:

    groq_chat = ChatGroq(

        temperature=0.3,

        model_name="llama3-70b-8192",

        max_tokens=2000

    )

except Exception as e:

    logger.error(f"Groq initialization failed: {str(e)}")

    raise


# Create agent with comprehensive error handling

try:

    prompt = ChatPromptTemplate.from_messages([

        ("system", """You are Senior Financial Analyst. Use these guidelines:

         1. Always reference tool data

         2. Highlight data limitations

         3. Compare to sector averages

         4. Format with markdown tables"""),

        ("user", "{input}"),

        ("placeholder", "{agent_scratchpad}"),

    ])

    

    tools = [get_live_stock_data, get_historical_data, get_fundamental_analysis]

    agent = create_tool_calling_agent(groq_chat, tools, prompt)

    agent_executor = AgentExecutor(agent=agent, tools=tools, verbose=False, handle_parsing_errors=True)

    

except Exception as e:

    logger.error(f"Agent setup failed: {str(e)}")

    raise


# Run analysis with full error protection

def analyze_stock(symbol: str):

    try:

        response = agent_executor.invoke({

            "input": f"""Analyze {symbol} stock with:

             - Current market data

             - Technical indicators

             - Financial health

             - Institutional activity

             Format with markdown tables"""

        })

        return response["output"]

    except Exception as e:

        return f"Analysis failed: {str(e)}"


# Example usage for Tencent (0700.HK)

if __name__ == "__main__":

    import sys

    symbol = sys.argv[1] if len(sys.argv) > 1 else "NVDA"

    print(f"\nAnalyzing {symbol}...\n")

    print(analyze_stock(symbol))


-------------------------------------------------------------

why it is an examaple of Agent AI


 This code is indeed a good example of agentic AI implementation, demonstrating several key characteristics of agentic systems. Let's break down why:

Key Agentic AI Features Present:

  1. Autonomous Tool Usage

    python
    Copy
    tools = [get_live_stock_data, get_historical_data, get_fundamental_analysis]
    agent = create_tool_calling_agent(groq_chat, tools, prompt)
    • The agent automatically decides which tools to use based on the query

    • Orchestrates multiple data sources (Yahoo Finance API calls) without human intervention

  2. Decision-Making Capability

    python
    Copy
    def validate_stock(symbol: str) -> bool:
        # Complex validation logic checking multiple data points
        return all([...])
    • Makes decisions about data validity

    • Chooses appropriate error handling strategies

  3. Self-Monitoring & Error Recovery

    python
    Copy
    except Exception as e:
        logger.error(f"Historical data error: {str(e)}")
        return f"Failed to fetch history: {str(e)}"
    • Comprehensive error handling throughout

    • Automatic fallback mechanisms when data is unavailable

  4. Goal-Oriented Behavior

    python
    Copy
    prompt = ChatPromptTemplate.from_messages([
        ("system", """1. Always reference tool data
        2. Highlight data limitations
        3. Compare to sector averages
        4. Format with markdown tables""")
    ])
    • Maintains focus on financial analysis objectives

    • Enforces structured output requirements

  5. Adaptive Reasoning

    python
    Copy
    def try_convert(value, target_type, default=None):
        # Context-aware type conversion
        return target_type(value) if value not in [None, 'N/A'] else default
    • Adjusts data processing based on input types

    • Handles international stock formats and currencies

Agentic Architecture Breakdown:

ComponentAgentic FeatureImplementation Example
PerceptionMarket data ingestionyfinance API calls
PlanningAnalysis strategyPrompt template guidelines
ActionTool execution@tool decorated functions
MemoryContext preservationAgentExecutor's state management
Learning(Limited) Error adaptationError feedback in logging

Why This is Agentic vs Regular AI:

  1. Proactive Data Gathering

    • Not just responding to inputs, but actively:

    python
    Copy
    hist['50MA'] = hist['Close'].rolling(window=50, min_periods=1).mean()
    hist['RSI'] = 100 - (100 / (1 + rs))  # Automatic technical analysis

What it does:

  • Calculates a rolling average of closing prices over 50 trading days

  • Creates a new column '50MA' in the DataFrame

  • window=50: Looks at the last 50 days' closing prices

  • min_periods=1: Starts calculating even if there's only 1 data point

  • .mean(): Averages the prices in the window

Example:

DateClose50MA
2023-11-01150148.2
2023-11-02152148.5
.........
2024-01-15160155.8


What it does:

  • Calculates the Relative Strength Index (RSI), a momentum indicator

  • Typically uses 14-period lookback (default in most implementations)

  • Measures speed/magnitude of recent price changes

  • Values range 0-100 (30=oversold, 70=overbought)

Key Components:

  1. delta = hist['Close'].diff(1) - Daily price changes

  2. gain = delta.where(delta > 0, 0) - Positive changes

  3. loss = -delta.where(delta < 0, 0) - Absolute negative changes

  4. avg_gain = gain.rolling(14).mean() - 14-day average gain

  5. avg_loss = loss.rolling(14).mean() - 14-day average loss

  6. rs = avg_gain / avg_loss - Relative Strength

  7. RSI = 100 - (100 / (1 + rs)) - Final formula

Example Output:

DateCloseRSI
2023-11-0115058.7
2023-11-0215262.1
2023-11-0314954.3

Why This Matters for Agentic AI:

  1. Automated Feature Engineering: The agent creates technical indicators without human intervention

  2. Time-Series Analysis: Enables pattern recognition in price data

  3. Decision Support: Provides quantitative metrics for buy/sell recommendations

  4. Adaptability: Adjusts calculations as new data arrives

Common Use Cases:

  • Identifying trend directions (using MA crossover strategies)

  • Spotting overbought/oversold conditions (RSI >70 or <30)

  • Generating trading signals

  • Risk management

Important Note: The min_periods=1 parameter allows the calculation to start immediately with available data, but meaningful analysis typically requires:

  • At least 50 days of data for reliable 50MA

  • At least 14 days of data for reliable RSI

You can verify these calculations against platforms like TradingView or Yahoo Finance to ensure accuracy. 

  1. Multi-Step Reasoning

    python
    Copy
    # Agent workflow:
    1. Validate symbol → 2. Fetch live data → 
    3. Get historical trends → 4. Analyze fundamentals → 
    5. Synthesize report
  2. Context-Aware Decisions

    python
    Copy
    "Market Cap": (
        f"${market_cap/1e9:.2f}B" if USD else 
        f"HK${market_cap/1e9:.2f}B" if HKD else 
        f"₹{market_cap/1e7:.2f}Cr"
    )

Limitations/Improvement Opportunities:

  1. True Agency Would Require

    • Market impact prediction models

    • Portfolio optimization suggestions

    • News sentiment integration

    • Adaptive learning from past analyses

  2. Current Constraints

    • Limited to predefined tools

    • No long-term memory between sessions

    • Reactive rather than proactive alerts

This implementation represents a strong foundation for agentic AI in financial analysis, particularly demonstrating tool orchestration and autonomous data processing capabilities. To make it fully agentic, you'd want to add features like goal-setting autonomy and continuous learning mechanisms.

Azure Service Bus vs. Event Grid: Which One for Async Communication? (Sales App Example!)

To enable asynchronous communication between the Azure cloud services in your sales application, you should recommend using **Azure Service Bus** or **Azure Event Grid** as the messaging solution. Both services are designed to facilitate reliable, scalable, and asynchronous communication between distributed components. Here's how they can be used:

---


### **1. Azure Service Bus**

Azure Service Bus is a fully managed enterprise message broker that supports **queues** and **topics/subscriptions**. It is ideal for scenarios where you need reliable, ordered, and asynchronous communication between services.


#### Key Features:

- **Queues**: Use queues for point-to-point communication. For example, the "Customer Orders" service can send a message to the "Billing" service via a queue.

- **Topics/Subscriptions**: Use topics for publish/subscribe patterns. For example, the "Payment" service can publish a message to a topic, and multiple subscribers (e.g., "Inventory" and "Shipping") can process the message independently.

- **Message Durability**: Messages are stored until they are processed, ensuring no data loss.

- **Scalability**: Handles high throughput and scales automatically.

- **Sessions and Ordering**: Ensures messages are processed in the correct order.


#### Example Workflow:

1. The "Customer Orders" service sends an order message to a Service Bus queue.

2. The "Billing" service picks up the message, processes it, and sends a billing confirmation message to another queue or topic.

3. The "Payment" service processes the billing confirmation and publishes a payment success message to a topic.

4. The "Inventory" and "Shipping" services subscribe to the topic and process the payment success message asynchronously.


---


### **2. Azure Event Grid**

Azure Event Grid is an event routing service that uses a publish/subscribe model. It is ideal for event-driven architectures where services need to react to events in real time.


#### Key Features:

- **Event-Driven**: Services publish events, and subscribers react to those events.

- **Real-Time**: Events are delivered in near real-time.

- **Scalability**: Automatically scales to handle high event volumes.

- **Integration**: Integrates with many Azure services and supports custom events.


#### Example Workflow:

1. The "Customer Orders" service publishes an "Order Placed" event to Event Grid.

2. Event Grid routes the event to subscribers like the "Billing" service.

3. The "Billing" service processes the event and publishes a "Billing Completed" event.

4. The "Payment" service subscribes to the "Billing Completed" event, processes the payment, and publishes a "Payment Successful" event.

5. The "Inventory" and "Shipping" services subscribe to the "Payment Successful" event and update inventory and initiate shipping, respectively.


---


### **Recommendation**

- Use **Azure Service Bus** if you need:

  - Guaranteed message delivery and ordering.

  - Complex routing with queues and topics.

  - Long-running processes or delayed message processing.


- Use **Azure Event Grid** if you need:

  - Real-time event-driven communication.

  - A lightweight, serverless solution.

  - Integration with other Azure services or custom event sources.


For your sales application, **Azure Service Bus** is likely the better choice because it provides more control over message processing and ensures reliable communication between the transaction components. However, if your application is heavily event-driven and requires real-time reactions, consider combining both services or using **Event Grid** for specific scenarios.


---


### **Additional Considerations**

- **REST API**: Both services support REST APIs, enabling communication via REST messages.

- **Security**: Use Azure Active Directory (AAD) and Shared Access Signatures (SAS) for secure access.

- **Monitoring**: Use Azure Monitor and Application Insights to track message flow and diagnose issues.

- **Cost**: Evaluate the cost based on message volume and throughput requirements.


Recommendation: Use Azure Service Bus to handle asynchronous REST-based communication between the various cloud services. Each service can send and receive messages (e.g., orders, billing requests, shipping notifications) via queues or topics in Service Bus, ensuring reliable and decoupled communication.

Why Azure Service Bus?

  1. Asynchronous Messaging: It supports queues (point-to-point) and topics (publish/subscribe), allowing each service to process messages at its own pace without blocking.
  2. Reliable Delivery: Built-in retry and dead-lettering mechanisms ensure messages are not lost if a receiving service is temporarily offline.
  3. REST API Support: Azure Service Bus has a REST-based API, which makes it straightforward to send and receive messages from different services using HTTP/HTTPS.
  4. Decoupling: Services remain independent. Billing, payment, inventory, and shipping can each be maintained, scaled, or updated without impacting the others.

Additional References

By using Azure Service Bus, you ensure each component of the sales application can exchange transaction information reliably and asynchronously, which is essential for a microservices-based architecture handling multiple steps in a transaction.

Transactions in SQL Server 


Imagine you’re playing a video game where you have to complete a series of missions. A transaction in SQL Server is like a mission that either finishes completely or doesn’t happen at all. It’s a promise that everything will work out correctly, or nothing will change.

This promise is kept through something called the ACID properties:

  1. Atomicity (A):
    Think of this as an "all-or-nothing" rule.

    • Example: If you have a mission with three tasks (like picking up a key, opening a door, and grabbing a treasure), atomicity means you either finish all three tasks, or if you fail one, you don’t get the treasure at all. In SQL Server, if one part of a transaction fails, the entire transaction is undone.
  2. Consistency (C):
    This makes sure the rules of the game (or the database) are always followed.

    • Example: Imagine a rule in your game that says you can only have a maximum of 100 coins. Consistency ensures that after each mission, your coins never exceed 100. Similarly, in a database, consistency means that after a transaction, all data still obeys the defined rules (like no negative balances).
  3. Isolation (I):
    Isolation means that transactions don’t interfere with each other.

    • Example: Suppose two players are playing the game at the same time. Even if they are doing similar missions, one player’s progress doesn’t mess up the other’s game. In SQL Server, isolation ensures that if multiple transactions are happening at the same time, they don’t affect each other’s results.
  4. Durability (D):
    Durability guarantees that once a mission is completed, its results are saved, even if something goes wrong later (like a power outage).

    • Example: After you finish your mission and get the treasure, even if the game crashes, you still have that treasure when you restart the game. In SQL Server, durability means that once a transaction is committed (finished successfully), the changes are permanent and will survive any system failures.

Summary:

  • Transaction: A set of operations that work together as one complete unit.
  • ACID: Ensures that transactions are reliable by being All-or-Nothing (Atomicity), keeping things in order (Consistency), running independently (Isolation), and being permanent (Durability).

This way, SQL Server helps maintain your data just as rules in a game help keep everything fair and predictable.

Understanding and Managing Blocking in SQL Server: A Step-by-Step Guide for New DBAs

Blocking is a common phenomenon in SQL Server that occurs when one session holds a lock on a resource (such as a row, page, or table) and another session is forced to wait until that lock is released. In this guide, we’ll explore what blocking is, how to detect it using built-in tools like sp_who2, sp_whoisActive (a community tool), and dynamic management views (DMVs), and demonstrate a blocking scenario using the AdventureWorks database.


Table of Contents

  1. Introduction to Blocking
  2. Key Tools to Monitor Blocking
  3. Demonstrating a Blocking Scenario in AdventureWorks
    • Step 1: Open Multiple Sessions in SSMS
    • Step 2: Start a Transaction to Hold a Lock (Session 1)
    • Step 3: Attempt to Access the Locked Resource (Session 2)
    • Step 4: Monitor Blocking with DMVs and sp_who2
    • Step 5: Resolve the Blocking
  4. Key Takeaways
  5. Conclusion

Introduction to Blocking

In SQL Server, blocking happens when one session (or query) holds a lock on a resource, preventing other sessions from modifying or reading the same data until the lock is released. This is a natural part of the transaction isolation mechanism but, if not managed well, can lead to performance issues or even deadlocks.


Key Tools to Monitor Blocking

Before diving into the demonstration, here are some essential tools and queries used by DBAs to monitor and diagnose blocking issues:

1. sp_who2

  • Usage:
    Run EXEC sp_who2; to display all active SQL Server sessions.
  • What to Look For:
    The BlkBy column shows which sessions are blocking others. If a session has a non-zero value in this column, it is being blocked by the session with that ID.

2. sp_whoisActive

  • Usage:
    sp_whoisActive is a popular community tool that provides detailed insights into active sessions, including blocking and long-running queries.
  • Download:
    You can download it from its GitHub repository.

3. Dynamic Management Views (DMVs)

  • Query to Identify Blocking Sessions:

    SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource 
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0;
    

    This query displays details about sessions that are currently blocked and the resources they are waiting on.

  • Query to Check Specific Locks:

    SELECT request_session_id, resource_type, resource_description, request_mode
    FROM sys.dm_tran_locks
    WHERE request_session_id = 60; -- Replace with your session ID
    

    This query helps you inspect the locks held by a specific session (replace "60" with the appropriate session ID).


Demonstrating a Blocking Scenario in AdventureWorks

Below is a practical demonstration of blocking using the AdventureWorks database. Follow these steps in SQL Server Management Studio (SSMS):

Step 1: Open Multiple Sessions in SSMS

  • Action:
    Open at least two query windows. We will refer to these as Session 1 and Session 2.

Step 2: Start a Transaction to Hold a Lock (Session 1)

In Session 1, execute the following commands:

USE AdventureWorks;
GO

BEGIN TRANSACTION;
UPDATE Sales.SalesOrderDetail
SET UnitPrice = UnitPrice + 1
WHERE SalesOrderID = 43659;
-- Notice: We are NOT committing or rolling back yet!

Explanation:

  • This query updates the SalesOrderDetail row for a specific order (SalesOrderID = 43659).
  • The transaction remains open, and the updated row is locked. As a result, any other session trying to access this row will have to wait.

Step 3: Attempt to Access the Locked Resource (Session 2)

Switch to Session 2 and run:

USE AdventureWorks;
GO

SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659;

Observation:

  • This query will hang (not return results immediately) because it is waiting for the lock held by Session 1 to be released.

Step 4: Monitor Blocking with DMVs and sp_who2

While Session 2 is waiting, open a third session (or use an existing one) and run the following commands to monitor the blocking:

  • Using sp_who2:

    EXEC sp_who2;
    
    • Check: Look at the BlkBy column to see which session is causing the block.
  • Using DMVs:

    SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource 
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0;
    
    • Check: This query displays detailed information about the blocked sessions and what they’re waiting for.

Step 5: Resolve the Blocking

Return to Session 1 and resolve the blocking by either committing or rolling back the transaction:

COMMIT TRANSACTION;
-- OR
-- ROLLBACK TRANSACTION;

Result:

  • Once the transaction is committed (or rolled back), the lock on the row is released.
  • Session 2 will now complete its query and display the results.

Key Takeaways

  • Understanding Blocking:
    Blocking occurs when a session holds a lock that prevents other sessions from accessing the same resource.

  • Monitoring Tools:
    Use tools like sp_who2, sp_whoisActive, and DMVs (such as sys.dm_exec_requests and sys.dm_tran_locks) to detect and diagnose blocking.

  • Best Practices:

    • Always commit or roll back transactions promptly to minimize blocking.
    • Monitor long-running transactions that might lead to blocking.
    • Use diagnostic queries to understand what resources are being locked and by whom.

Conclusion

Blocking is a critical concept in SQL Server that, if not managed properly, can impact performance and user experience. This guide has walked you through a real-world demonstration using the AdventureWorks database, showing you how to create a blocking scenario, monitor it using different tools, and resolve it effectively.

As you gain more experience, you'll learn to balance transaction isolation and concurrency to maintain an optimal and responsive SQL Server environment. If you have any questions or need further assistance with diagnosing blocking issues, feel free to leave a comment below.

Happy database managing!


Would you like additional details on how to detect and even kill a blocking session? Let me know, and we can dive deeper into advanced blocking management techniques!