𝗟𝗶𝗻𝘂𝘅 𝗙𝗶𝗹𝗲 𝗦𝘆𝘀𝘁𝗲𝗺

 If you're an IT professional, navigating the Linux file system is a skill that can take you far.

The Linux file system hierarchy is a tree-like structure that organizes all of the files and directories on a Linux system.

It is a logical organization of the file system, and it is not necessarily the same as the physical organization of the files on the disk.

The root directory of the Linux file system is /. All other directories and files are contained

Let's dive into the core directories that make up the Linux file system:

 1. /𝗯𝗶𝗻 🛠️: Houses essential system binaries like the 𝚋𝚊𝚜𝚑 shell, 𝚕𝚜, and 𝚐𝚛𝚎𝚙.
 2. /𝗯𝗼𝗼𝘁 🚀: Contains boot essentials like the kernel image and bootloader.
 3. /𝗱𝗲𝘃 🔌: A hub for device files representing connected hardware.
 4. /𝗲𝘁𝗰 📜: The home for Linux system configuration files.
 5. /𝗵𝗼𝗺𝗲 🏠: User home directories reside here.
 6. /𝗹𝗶𝗯 📚: Contains shared libraries used across various programs.
 7. /𝗺𝗲𝗱𝗶𝗮 💿: Mount points for removable media like CDs and USB drives.
 8. /𝗺𝗻𝘁 🧲: For temporary filesystem mounts.
 9. /𝗼𝗽𝘁 📦: Houses optional software packages.
10. /𝗽𝗿𝗼𝗰 📊: Information central for the running system, processes, and memory usage.
11. /𝗿𝗼𝗼𝘁 👑: The home directory for the root user.
12. /𝘀𝗯𝗶𝗻 🔧: Contains system admin binaries like 𝚒𝚗𝚒𝚝 and 𝚏𝚍𝚒𝚜𝚔.
13. /𝘀𝗿𝘃 🌐: Data storage for services like web servers.
14. /𝘁𝗺𝗽 🌡️: A space for temporary files.
15. /𝘂𝘀𝗿 🖥️: Most user-installed software finds its place here.
16. /𝘃𝗮𝗿 🔄: Holds variable data, including logs and temporary files.

📂 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.

The Linux hierarchy is a masterpiece of organization, ensuring efficient file storage. Knowing it inside out aids in effective system management.

🌟 𝗟𝗶𝗻𝘂𝘅 𝗙𝗶𝗹𝗲 𝗦𝘆𝘀𝘁𝗲𝗺 𝗧𝗶𝗽𝘀:
- 𝚌𝚍 🚶: Navigate directories.
- 𝚕𝚜 📋: List directory contents.
- 𝚖𝚔𝚍𝚒𝚛 📁: Create a directory.
- 𝚛𝚖𝚍𝚒𝚛 🗑️: Remove a directory.
- 𝚌𝚙 📤: Copy files/directories.
- 𝚖𝚟 🚚: Move files/directories.
- 𝚛𝚖 ❌: Delete files/directories.

⚠️ 𝗖𝗮𝘂𝘁𝗶𝗼𝗻: Some directories, like /𝚋𝚒𝚗, are read-only. Avoid altering their contents!

For Linux newcomers, investing time in understanding this hierarchy is a step closer to mastering Linux! 🌐

SQL Queries for Practice

 1) Query to display the employees whose ename start with M from emp table?

sql> select * from emp where ename like 'M%';

2) Query to display the employees where ename second letter will be L from emp table?

sql> select * from emp where ename like '_L%';

3) Query to display first date of the current month using predefined date functions sysdate, add_months , last_date functions?

sql> select last_day ( add_months ( sysdate, -1)) +1 from dual;

4) Query to display the employees who are joining in the month of December from emp table?

sql> select * from emp where to_char (HIREDATE,'MON') = 'DEC';

5) Query to skip first five rows and then display remaining all rows from emp table?

sql> select * from ( select rownum r,ename, sal from emp ) where r>5;

6) Query to display junior most employee details from emp table?

sql> select * from emp where hiredate= ( select max(hiredate) from emp);

7) Query to display employee details who are getting min salary in each department from emp table?

sql> select * from emp where (deptno, sal) in ( select deptno, min(sal) from emp group by deptno);

8) Query to display last four rows from emp table?

sql> select * from emp minus select * from emp where rownum<= (select count(*) - 4 from emp);

9) Query to display first row and last row from emp table?

sql> select * from ( select rownum r, ename, sal from emp) where r=1 or r= ( select count(*) from emp);

10) Query to display odd number of records from emp table?

sql> select * from ( select rownum r, ename, sal from emp) where mod(r,2)=1;

11) Query to display even number of records from emp table?

sql> select * from ( select rownum r, ename, sal from emp) where mod(r,2)=0;

12) Query to display 5th highest salary employee from emp table?

sql> select * from ( select rownum r, ename, sal from ( select * from emp order by sal desc)) where r=5;

13) Query to display nth highest salary employee from emp table using corelated subquery?

sql> select * from emp e1 where &n= ( select count(distinct(sal)) from emp e2 where e2.sal>=e1.sal);

14) Query to display first ten rows from emp table?

sql> select * from emp where rownum <=10;

15) Query to display first five highest salary employees from from emp table?

sql> select * from ( select * from emp order by sal desc ) where rownum<=5;

16) Query to display 2nd, 3rd, 4th, 5th, 7th, 9th row from emp table?

sql> select * from ( select rownum r, ename, sal from emp)

where r in (2,3,4,5,7,9);

17) Query to display second row from emp tablle?

sql> select * from ( select rownum r,ename,sal from emp) where r=2;

18) Query to display highest salaries to lowest salaries in each department from emp table using row_number analytical function?

sql> select deptno, ename, sal, row_number() over ( partition by deptno order by sal desc ) r from emp;

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, 





           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, 







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


 Create PROCEDURE Sp_send_highcpu_alert



       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,





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


                     , (


                     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,


                              + N'.'

                              + Quotename(Object_name(sqltext.objectid,


                     '') AS








       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=


                        ' '

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


                        #tbl_highcpu_query_output WHERE program_name NOT LIKE


                        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';


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

Using T-SQL Query to fetch data for the SQL Server worker threads

--We can use the sys.dm_os_schedulers dynamic management view to check the current value for the maximum worker threads. 


SELECT @max = max_workers_count

FROM sys.dm_os_sys_info;

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 'RequestWaitingForThreads', 

       SUM(current_workers_count) AS 'AssociatedWorkers'

FROM sys.dm_os_Schedulers


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.