Navigating the Linux File System Hierarchy: A Beginner's Guide 🌐

 For IT professionals and Linux enthusiasts, mastering the Linux file system hierarchy is key to efficient system management. Unlike the physical layout on disk, the Linux file system is organized logically, starting from the root directory /.

Here’s a snapshot of the core directories and their functions:

  1. /bin 🛠️: Contains essential system binaries like bash, ls, and grep.
  2. /boot 🚀: Houses boot essentials such as the kernel image and bootloader.
  3. /dev 🔌: A hub for device files representing connected hardware.
  4. /etc 📜: The directory for system configuration files.
  5. /home 🏠: Stores user home directories.
  6. /lib 📚: Contains shared libraries used by various programs.
  7. /media 💿: Mount points for removable media like CDs and USB drives.
  8. /mnt 🧲: Used for temporary filesystem mounts.
  9. /opt 📦: Houses optional software packages.
  10. /proc 📊: Information central for system processes and memory usage.
  11. /root 👑: The home directory for the root user.
  12. /sbin 🔧: Contains system administration binaries like init and fdisk.
  13. /srv 🌐: Data storage for services like web servers.
  14. /tmp 🌡️: A space for temporary files.
  15. /usr 🖥️: Most user-installed software is found here.
  16. /var 🔄: Holds variable data such as logs and temporary files.

Useful Commands

  • ls — List directory contents.
  • cd — Change directory.
  • pwd — Print working directory.
  • mkdir — Make a new directory.
  • rm — Remove files or directories.
  • cp — Copy files or directories.
  • mv — Move or rename files or directories.
  • cat — Concatenate and display files.
  • chmod — Change file or directory permissions.
  • chown — Change file or directory ownership.
  • grep — Search for patterns in files.
  • top — Display system processes.
  • ps — Display running processes.
  • kill — Terminate processes.
  • sudo — Execute a command as a superuser.
  • du — Estimate file space usage.
  • tar — Create or extract archive files.
  • ping — Test network connectivity.
  • vi — Edit files using a text editor.
  • ssh — Connect to remote servers securely.

Tips for Navigating the Linux File System

  • cd 🚶: Navigate directories.
  • ls 📋: List directory contents.
  • mkdir 📁: Create a directory.
  • rmdir 🗑️: Remove a directory.
  • cp 📤: Copy files/directories.
  • mv 🚚: Move files/directories.
  • rm: Delete files/directories.

⚠️ Caution: Some directories, like /bin, are read-only. Avoid altering their contents to prevent system issues!

Understanding this hierarchy is crucial for efficient Linux system management. Dive into the Linux file system to enhance your skills and streamline your workflows! 🌟




Essential SQL Queries for Managing Employee Data: A Comprehensive Guide

In the realm of SQL, efficiently querying and managing employee data can significantly streamline your tasks. Below are some essential SQL queries you might find useful for various scenarios:

  1. Display Employees Whose Name Starts with 'M'

    sql
    SELECT * FROM emp WHERE ename LIKE 'M%';
  2. Display Employees Whose Second Letter of Name Is 'L'

    sql
    SELECT * FROM emp WHERE ename LIKE '_L%';
  3. Display the First Date of the Current Month Using predefined date functions:

    sql
    SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 FROM dual;
  4. Display Employees Who Joined in December

    sql
    SELECT * FROM emp WHERE TO_CHAR(HIREDATE, 'MON') = 'DEC';
  5. Skip the First Five Rows and Display Remaining Rows

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE r > 5;
  6. Display the Junior Most Employee

    sql
    SELECT * FROM emp WHERE hiredate = (SELECT MAX(hiredate) FROM emp);
  7. Display Employees with the Minimum Salary in Each Department

    sql
    SELECT * FROM emp WHERE (deptno, sal) IN (SELECT deptno, MIN(sal) FROM emp GROUP BY deptno);
  8. Display the Last Four Rows

    sql
    SELECT * FROM emp MINUS SELECT * FROM emp WHERE ROWNUM <= (SELECT COUNT(*) - 4 FROM emp);
  9. Display the First and Last Row

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE r = 1 OR r = (SELECT COUNT(*) FROM emp);
  10. Display Records with Odd Row Numbers

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE MOD(r, 2) = 1;
  11. Display Records with Even Row Numbers

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE MOD(r, 2) = 0;
  12. Display the 5th Highest Salary

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM (SELECT * FROM emp ORDER BY sal DESC)) WHERE r = 5;
  13. Display the nth Highest Salary Using Correlated Subquery

    sql
    SELECT * FROM emp e1 WHERE &n = (SELECT COUNT(DISTINCT(sal)) FROM emp e2 WHERE e2.sal >= e1.sal);
  14. Display the First Ten Rows

    sql
    SELECT * FROM emp WHERE ROWNUM <= 10;
  15. Display the Top Five Highest Salary Employees

    sql
    SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE ROWNUM <= 5;
  16. Display Rows with Specific Numbers (2nd, 3rd, 4th, 5th, 7th, 9th)

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE r IN (2, 3, 4, 5, 7, 9);
  17. Display the Second Row

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE r = 2;
  18. Display Salaries from Highest to Lowest in Each Department Using ROW_NUMBER()

    sql
    SELECT deptno, ename, sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) r FROM emp;

Mastering these queries will empower you to efficiently extract and analyze data from your employee tables, aiding in insightful decision-making and management.

How To Send SQL Server CPU Utilization Alerts Using SQL Server Agent

 This article explains how to send an email alert using SQL Server alerts when the SQL Server CPU utilization reaches a specific threshold.

In Microsoft SQL Server, you can use T-SQL and SQL Server Agent to generate an alert when CPU usage exceeds a threshold, such as 80%. Here's an example of how you can achieve this:

Create a SQL Server Agent alert: Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Navigate to the SQL Server Agent node in the Object Explorer, right-click on the "Alerts" folder, and select "New Alert."

Configure the alert properties: In the "New Alert" dialog, configure the following properties:

Name: Provide a name for the alert.

Type: Select the type of alert as "SQL Server performance condition alert."

Object: Choose the "Resource Pool Stats."

Counter: Choose the "CPU Usage target %" counter.

 Instance: Select "default" to monitor the overall CPU usage of the entire system.

Alert if counter: Choose "Rises above" to trigger the alert when CPU usage exceeds the threshold.

Value: Enter "0.8" to set the threshold for CPU usage to 80%.

 Enable this alert: Check this option to enable the alert.

Response: Choose the appropriate response action, such as "Notify operators" or "Execute job" to determine what action should be taken when the alert is triggered.

Additional actions: As needed, you can configure additional actions, such as sending an email or running a script.

Click on "OK" to create the alert.

Once the alert is created, SQL Server Agent will automatically monitor the CPU usage based on the specified threshold. When the CPU usage exceeds 80%, the alert will be triggered, and the configured response action will be executed, such as sending notifications or running a script.

T-SQL Stored Procedure To Send CPU-Intensive Queries

Let us understand this with a simple demonstration. Suppose I want to create an alert when the CPU utilization reaches 80%; the SQL Server automatically sends the alert along with the list of the top 10 CPU-intensive queries. We will email the queries in an HTML table.

We can use the following query to populate the top 10 CPU-intensive queries list.


SELECT TOP 10 session.session_id, 

           req.cpu_time, 

           req.logical_reads, 

           req.reads, 

           req.writes, 

           SUBSTRING(sqltext.TEXT, (req.statement_start_offset / 2) + 1, 

           ((CASE req.statement_end_offset 

                WHEN -1 THEN DATALENGTH(sqltext.TEXT) 

                ELSE req.statement_end_offset 

            END - req.statement_start_offset) / 2) + 1) AS statement_text, 

           COALESCE(QUOTENAME(DB_NAME(sqltext.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(sqltext.objectid, sqltext.dbid))  

           + N'.' + QUOTENAME(OBJECT_NAME(sqltext.objectid, sqltext.dbid)), '') AS command_text, 

       req.command, 

           session.login_name, 

           session.host_name, 

           session.program_name, 

           session.last_request_end_time,

           session.login_time   

FROM sys.dm_exec_sessions AS session 

JOIN sys.dm_exec_requests AS req ON req.session_id = session.session_id CROSS APPLY sys.Dm_exec_sql_text(req.sql_handle) AS sqltext 

WHERE req.session_id != @@SPID 

ORDER BY req.cpu_time DESC 

Query Output:



The entire stored procedure is following:

USE ajay --- change Database

 go

 Create PROCEDURE Sp_send_highcpu_alert

 AS

   BEGIN

       CREATE TABLE #tbl_highcpu_query_output

         (

            sessionid             INT,

            cpu_time              BIGINT,

            logicalreads          BIGINT,

            reads                 BIGINT,

            writes                BIGINT,

            querystatement        VARCHAR(max),

            command_text          VARCHAR(max),

            command               VARCHAR(1500),

            login_name            VARCHAR(1000),

            hostname              VARCHAR(50),

            program_name          VARCHAR(500),

            last_request_end_time DATETIME,

            login_time            DATETIME

         )

       INSERT INTO #tbl_highcpu_query_output

       SELECT TOP 10 session.session_id,

                     req.cpu_time,

                     req.logical_reads,

                     req.reads,

                     req.writes,

                     Substring(sqltext.text, ( req.statement_start_offset / 2 ) +

                                             1

                     , (

                     (

                     CASE req.statement_end_offset

                       WHEN -1 THEN Datalength(sqltext.text)

                       ELSE req.statement_end_offset

                     END - req.statement_start_offset ) / 2 ) + 1)

                     AS statement_text,

                     COALESCE(Quotename(Db_name(sqltext.dbid)) + N'.'

                              + Quotename(Object_schema_name(sqltext.objectid,

                              sqltext.dbid))

                              + N'.'

                              + Quotename(Object_name(sqltext.objectid,

                              sqltext.dbid)),

                     '') AS

                     command_text,

                     req.command,

                     session.login_name,

                     session.host_name,

                     session.program_name,

                     session.last_request_end_time,

                     session.login_time

       FROM   sys.dm_exec_sessions AS session

              JOIN sys.dm_exec_requests AS req

                ON req.session_id = session.session_id

              CROSS apply sys.Dm_exec_sql_text(req.sql_handle) AS sqltext

       WHERE  req.session_id != @@SPID

       ORDER  BY req.cpu_time DESC


       DECLARE @HTMLString NVARCHAR(max)

       DECLARE @SUBJECT VARCHAR(max) = 'High CPU Alert on: ' + @@servername + '.'


       SET @HTMLString= '   <H2>Top 10 CPU Intesnive Queries</H2>   <table id="AutoNumber1" borderColor="#111111" border="1">      <tr>     <td bgcolor="#99CC33">session ID</td>     <td bgColor="#99CC33">CPU Time</td>     <td bgcolor="#99CC33">Logical Reads<</b></td>     <td bgcolor="#99CC33">Reads</td>     <td bgcolor="#99CC33">Writes</td>     <td bgcolor="#99CC33">Query Statement</td>     <td bgcolor="#99CC33">Stored Procedure</td>     <td bgcolor="#99CC33">Command</td>     <td bgcolor="#99CC33">Login name</td>     <td bgcolor="#99CC33">hostname</td>     <td bgColor="#99CC33">program_name</td>     <td bgcolor="#99CC33">last_request_end_time</b></td>     <td bgcolor="#99CC33">login_time</td>      </tr>'

                        + Cast((SELECT DISTINCT td = sessionid, ' ', td= cpu_time

                        ,

                        ' ',

                        td = logicalreads, ' ', td = reads, ' ', td = writes, ' '

                        ,

                        td =

                        querystatement, ' ', td = command_text, ' ', td = command

                        ,

                        ' ',

                        td = login_name, ' ', td = hostname, ' ', td=

                        program_name,

                        ' '

                        , td = last_request_end_time, ' ', td = login_time, ' '

                        FROM

                        #tbl_highcpu_query_output WHERE program_name NOT LIKE

                        '%Mail%'

                        FOR xml path('tr'), type) AS NVARCHAR(max))

                        + '</table>'


       SELECT @HTMLString


       -- Send email using sp_send_dbmail  

       EXEC msdb.dbo.Sp_send_dbmail

         @profile_name = 'SQLmail',

         -- Replace with the name of your DbMail profile  

         @recipients = 'sqldbanow@gmail.com',

         @subject = @subject,

         @body = @HTMLString,

         @body_format = 'HTML';

   END 


Configure Alert

Create a database alert, as I explained at the article's beginning. The alert parameters should be as shown in the screenshot below.



When the alert is raised, we want to send the list of CPU-intensive queries. To do that, we must create an SQL Job. I have created a SQL Server Agent job named Populate_High_CPU_Queries. The SQL Job executes the sp_send_HIGHCPU_Alert stored procedure.

In our case, the job step should look like the following image:



Once the SQL job named Populate_High_CPU_Queries is created, you can specify the job name in the response option of the alert. Below is the image of the Response tab for reference.


Sample Email report



Monitoring SQL Server Worker Threads: A Comprehensive Query

Effective performance monitoring in SQL Server includes keeping an eye on worker threads and their utilization. Below is a detailed SQL query that helps you understand the current state of worker threads on your SQL Server instance. This query leverages the sys.dm_os_schedulers dynamic management view to provide valuable insights.

Query to Monitor Worker Threads

sql
-- Declare a variable to hold the maximum worker threads count DECLARE @max INT; -- Get the maximum number of worker threads SELECT @max = max_workers_count FROM sys.dm_os_sys_info; -- Retrieve current thread utilization statistics SELECT GETDATE() AS 'CurrentDate', @max AS 'TotalThreads', SUM(active_workers_count) AS 'CurrentThreads', @max - SUM(active_workers_count) AS 'AvailableThreads', SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu', SUM(work_queue_count) AS 'RequestsWaitingForThreads', SUM(current_workers_count) AS 'AssociatedWorkers' FROM sys.dm_os_schedulers WHERE STATUS = 'VISIBLE ONLINE';

Explanation

  1. @max: This variable holds the maximum number of worker threads available in SQL Server, obtained from the sys.dm_os_sys_info view.

  2. TotalThreads: Displays the total number of worker threads.

  3. CurrentThreads: Shows the current number of active worker threads.

  4. AvailableThreads: Calculates the number of threads available by subtracting the number of active threads from the total number of threads.

  5. WorkersWaitingForCpu: Indicates the number of worker threads that are waiting for CPU time.

  6. RequestsWaitingForThreads: Shows the count of requests waiting for available threads.

  7. AssociatedWorkers: Displays the number of workers currently associated with schedulers.

Why This Matters

Monitoring worker threads is crucial for diagnosing performance issues related to thread contention and scheduling. By keeping an eye on these metrics, you can ensure that your SQL Server instance is handling workload efficiently and avoid potential bottlenecks.

SQL Server Database Capacity Planning

 SQL Server database capacity planning involves estimating the resources and storage requirements necessary to support the expected growth of your database over time. Here are some key considerations and steps to help you with SQL Server database capacity planning:

Data Analysis and Profiling:

Analyze the existing data and understand its characteristics, including the size, growth rate, and usage patterns.

Identify the tables, indexes, and other database objects that contribute to the overall database size.

Profile the workload by capturing and analyzing relevant queries and transactions to determine resource usage patterns.
 
Storage Requirements:

Estimate the size of the database files (data files, transaction log files) based on the current data size and expected growth rate.

Consider factors such as data compression, partitioning, and archiving strategies to optimize storage utilization.

Choose appropriate storage technology (e.g., direct-attached storage, SAN, or cloud storage) based on performance, scalability, and cost considerations.
 
Memory Considerations:

Determine the memory requirements for SQL Server based on the database size, workload, and query complexity.

Allocate sufficient memory for SQL Server's buffer cache, query execution plans, and other caching mechanisms to optimize performance.

Configure the appropriate maximum memory setting to ensure SQL Server does not consume excessive resources on the server.
 
CPU and Processing Power:
Analyze the workload and identify the CPU-intensive queries or processes.
Determine the CPU requirements based on the workload's complexity, concurrency, and expected growth.
Consider factors such as parallelism settings, query optimization, and server hardware capabilities to ensure adequate CPU resources.
 
Network Considerations:

Assess the network bandwidth requirements based on data transfer rates between clients and the SQL Server.

Consider factors such as client connections, data replication, and backup/restore operations that may utilize network resources.
 
Optimize the network configuration and infrastructure to minimize latency and maximize performance.

Scalability and Growth:

Consider the expected growth rate of the database and plan for future scalability.
Implement strategies such as database partitioning, table partitioning, or sharding to distribute data across multiple servers if required.

Regularly monitor database growth and performance to ensure that the capacity planning remains aligned with the actual usage.
 
Monitoring and Maintenance:

Implement a robust monitoring and alerting system to track resource utilization, performance metrics, and storage growth.

Regularly review and analyze monitoring data to identify trends, bottlenecks, and areas for optimization.
Perform routine database maintenance tasks such as index maintenance, statistics updates, and backup integrity checks to optimize performance and storage utilization.