Capture the deleted data without using trigger

 

We can achieve the goal to save the deleted record in other table without using delete trigger.

-- Creating two tables

CREATE TABLE MainTab (ID1 INT, Col1 VARCHAR(100))

GO

CREATE TABLE DeletedTab (ID3 INT, Col3 VARCHAR(100))

GO

-- Inserting into two tables together

INSERT INTO MainTab (ID1, Col1)

VALUES(1,'Col'), (2, 'Col2')

GO

-- Deleting from Main Table

-- Inserting in Deleted Table

DELETE FROM MainTab

OUTPUT deleted.ID1, deleted.Col1

INTO DeletedTab

WHERE ID1 = 1

GO

--Selecting from both the tables

SELECT *

FROM DeletedTab;

SELECT *

FROM MainTab;

-- Clean up

DROP TABLE MainTab;

DROP TABLE DeletedTab;

GO

In SQL Server : Remove Unused Cache MARK_IN_USE_FOR_REMOVAL

 

Remove Unused Cache

Sometimes we realized that overall system is working fine after we tuned Disk IO and CPU. 

However, there is still some issue with the SQL Server and then we have to figured out and found that it is related to the memory. 

We realize that there are lots of unused plans in the cache and by removing them we can able to make the memory available to the SQL Server.

Here is the command which we can run to release entries from all current caches once the entries become unused.

DBCC FREESYSTEMCACHE ('ALL') 

WITH MARK_IN_USE_FOR_REMOVAL;

Now, this process should be automatic and one should not be doing this manually. 

Once we clear up unused cache, and right after that, we are able to get the necessary performance.

To check the buffer pool/ buffer cache of the workload in SQL Server database.

An SQL Server buffer pool, also called an SQL Server buffer cache, is a place in system memory that is used for caching table and index data pages as they are modified or read from disk. 

The primary purpose of the SQL buffer pool is to reduce database file I/O and improve the response time for data retrieval.

To check the buffer pool / buffer cache usage by the table in SQL Server database.

SQL Server retrieves data from two areas; memory and disk. As disk operations are more expensive in terms of IO which means they are much slower SQL stores and retrieves data pages from an area known as the Buffer Pool where operations are much faster.

In order to understand how the Buffer Pool works and how it benefits our query processing we need to see it in action. Fortunately SQL Server gives us several management views and built in functionality to see exactly how the Buffer Pool is being used and how, or more importantly if, our queries are utilizing it efficiently.

Firstly, we need to ensure we have a cold cache to work with; that is a Buffer Pool that is not populated with any pages. We can do this without restarting SQL Server by issuing a DBCC, or Database Console Command entitled DROPCLEANBUFFERS. Prior to doing this we need to issue a CHECKPOINT command, this ensures that any dirty pages are wrote to disk cleaning the buffers, for reference a buffer is a 8 kilobyte page residing in memory.

CHECKPOINT -- writes dirty pages to disk, cleans the buffers

DBCC DROPCLEANBUFFERS -- removes all buffers

we can see how the Buffer Pool is allocated by using the sys.dm_os_memory_clerks Dynamic Management View:

---- // Check MEMORYCLERK_SQLBUFFERPOOL allocation 

SELECT TOP 10 [type], SUM(pages_kb) / 1024 AS SizeMb

FROM sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY SUM(pages_kb) / 1024 DESC

----  // To check the Logical read and Physical read of a query we need to SET STATISTICS IO ON

SET STATISTICS IO ON

GO

use <<DBName>>

GO

SELECT COUNT(*) FROM <<TableName>>

GO

----  // To check the buffer pool of all database...

SELECT * FROM sys.dm_os_buffer_descriptors

GO

---- // To check the buffer pool usage by the whole database.

SELECT COUNT(*) * 8192 / 1024 / 1024 AS 'MB'FROM sys.dm_os_buffer_descriptors 

GO

----  //  To get all Database with thier buffer pool usage.

SELECT DB_NAME(database_id) AS 'DatabaseName'

,COUNT(*) * 8192 / 1024 / 1024 AS 'MB'

FROM sys.dm_os_buffer_descriptors 

WHERE database_id <> 32767

GROUP BY database_id

ORDER BY COUNT(*) DESC

GO

----  // To get buffer pool for a particular database.

SELECT * FROM sys.dm_os_buffer_descriptors 

WHERE database_id = DB_ID('PRSDBAK') 

GO

----  //  To get the Table name consumes larger buffer pool 

SELECT OBJECT_NAME(p.object_id) AS 'TableName'

,COUNT(*) * 8192 / 1024 AS 'KB' 

FROM sys.dm_os_buffer_descriptors bd

INNER JOIN sys.allocation_units au ON au.allocation_unit_id = bd.allocation_unit_id

INNER JOIN sys.partitions p ON p.partition_id = au.container_id

INNER JOIN sys.tables t ON t.object_id = p.object_id

WHERE database_id = DB_ID('PRSDBAK') 

AND t.is_ms_shipped = 0 -- exclude system tables

GROUP BY p.object_id

ORDER BY COUNT(*) DESC

GO

Retrieve data after execute TRUNCATE command by the help of Transaction

 

We do use truncate table, but we don't get data after execute this command, 

But how we can  retrieve data after execute TRUNCATE command, so follow the below steps.

-- Create Test Table with "TruncateDemoTable" name

CREATE TABLE TruncateDemoTable (ID INT)

INSERT INTO TruncateDemoTable (ID)

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

GO

-- Check the data before truncate

SELECT * FROM TruncateDemoTable

GO

-- Begin Transaction

BEGIN TRAN

-- Truncate Table

TRUNCATE TABLE TruncateDemoTable

GO

-- Check the data after truncate

SELECT * FROM TruncateDemoTable

GO

-- Rollback Transaction

ROLLBACK TRAN

GO

-- Check the data after Rollback

SELECT * FROM TruncateDemoTable

GO

-- Clean up

DROP TABLE TruncateDemoTable

GO

 

Fastest way to get ROWCOUNT of any table..

During performance health check, we generally use below query to get total number of rows in any table.

SET STATISTICS IO ON 

SELECT COUNT(*) FROM CustomerTbl WITH(NOLOCK)

But this can be take lot of time to scan whole table if table is having TB's of data.

So, below is the alternative query to get row count of all the tables of your database in one go, and if you want, you can search for a particular table as well.

SELECT  SCHEMA_NAME(t.schema_id) SchemaName,

        t.[name] TableName, 

        SUM(p.row_count) TotalRows

FROM sys.tables t

INNER JOIN sys.dm_db_partition_stats p

ON t.object_id = p.object_id AND t.type_desc = 'USER_TABLE'  AND p.index_id IN (0,1)

-- WHERE t.[name] = 'CustomerTbl'

GROUP BY t.schema_id, t.[name]

ORDER BY TotalRows DESC

Use of recursive CTE to get user tree hierarchy (Top to Bottom)

Below script can be use with recursive CTE to get user hierarchy  in SQL Server..

DECLARE @userType INT = 3

DECLARE @emp AS TABLE (Id INT, FName VARCHAR(100),LName VARCHAR(100), ReportTo INT)

INSERT INTO @emp (Id,  FName, LName, ReportTo) VALUES

(1, 'Tom','Joe', 5)

,(2, 'Peter','Pep', 4)

,(3, 'Sem','Leo', 4)

,(4, 'Neeraj','Gusain', 0)

,(5, 'Mark','Pettit', 3)

,(6, 'Sara','Thomas', 2)

,(7, 'Jobb','Mular', 2)

,(8, 'Mark','Rekhei', 5)

,(9, 'Sara','Leon', 3)

,(10, 'Peter','James', 8)

;WITH CTE 

AS

(

SELECT id, FName, LName, ReportTo, 0 as [level]

FROM @emp

WHERE (ReportTo = 0 AND  @userType = 0) OR id = @userType

    UNION ALL

SELECT e.id, e.FName, e.LName, e.ReportTo, (ct.[level] + 1) AS [level]

FROM @emp e

JOIN CTE ct ON ct.id = e.ReportTo

WHERE e.ReportTo <> 0 

)

SELECT * FROM CTE 

ORDER BY id

Processes in SQL Server and script to Kill All Inactive Sessions – Kill Sleeping Sessions from sp_who2

We have different kind of Process status in SQL Server, which we can find by using below query

  SELECT * FROM master.dbo.sysprocesses

  OR 

  SP_WHO2

As we know every process need Disk, Thread and CPU to run. so below is the explanation to understand each process.

Process Status	THREAD	CPU	DISK	Description
Pending	Not available	Not available	Available	The Process has no thread, no CPU, but waiting for them to available.
Runnable	Available	Not available	Available	The Process has thread but no CPU, but waiting for CPU to available.
Running	Available	Available	Available	The Process has all three and is in running state
Suspended	Available	Available	Not available	The Process is waiting for some event to get completed (May be lock or IO is not available)
Sleeping	Not available	Not available	Not available	The Process is doing nothing and waiting for further commands
Dormant				The SQL Server is reseting this process
Background				These background process run by SQL Server for any job
SpinLock				This Process is busy waiting in CPU for it's own turn.

The below script to kill all inactive sessions. To kill sleeping sessions from sp_who2 can also use this script.

DECLARE @user_spid INT

DECLARE CurSPID CURSOR FAST_FORWARD

FOR

       SELECT SPID

       FROM master.dbo.sysprocesses (NOLOCK)

       WHERE spid>50 -- avoid system threads

       AND status='sleeping' -- only sleeping threads

       AND DATEDIFF(HOUR,last_batch,GETDATE())>=24 -- thread sleeping for 24 hours

       AND spid<>@@spid -- ignore current spid

       OPEN CurSPID

 

       FETCH NEXT FROM CurSPID INTO @user_spid

       WHILE (@@FETCH_STATUS=0)

       BEGIN

              PRINT 'Killing '+CONVERT(VARCHAR,@user_spid)

              EXEC('KILL '+@user_spid)

              FETCH NEXT FROM CurSPID INTO @user_spid

       END

       CLOSE CurSPID

       DEALLOCATE CurSPID

GO