Dynamic management views

In order to identify the  server resources used by your queries, here are the two DMV's.
sys.dm_Exec_requests  Dmv can be used to display the requests currently executing on sql server.
select cpu_time, reads,total_elapsed_time,logical_reads, row_count from Sys.dm_exec_requests
cpu_time : cpu time in milliseconds used by this request or by the requests in this sesssion.
reads : number of reads performed by this request or by the requests in this session.
logical_reads : Nuber of logical reads that have been perfomed by the requests or by the requests in this session.
row_count : Number of rows that have been returned to the client by this request.

 

select cpu_time,reads, total_elapsed_time, logical_reads,row_count from sys.dm_exec_sessions
these Queries are supposed to run after DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

 

 

 

Script to determine which database is using most of the memory in SQL Server.

SELECT

[DatabaseName] = CASE [database_id] WHEN 32767

THEN 'Resource DB'

ELSE DB_NAME([database_id]) END,

COUNT_BIG(*) [Pages in Buffer],

COUNT_BIG(*)/128 [Buffer Size in MB]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id]

ORDER BY [Pages in Buffer] DESC;

How to identify the columns that didn’t match while using the Except class in TSQl.

declare @Data1 xml, @Data2 xml

select @Data1 = 
(
    select * 
    from (select * from Test1 except select * from Test2) as a
    for xml raw('Data')
)

select @Data2 = 
(
    select * 
    from (select * from Test2 except select * from Test1) as a
    for xml raw('Data')
)

;with CTE1 as (
    select
        T.C.value('../@ID', 'bigint') as ID,
        T.C.value('local-name(.)', 'nvarchar(128)') as Name,
        T.C.value('.', 'nvarchar(max)') as Value
    from @Data1.nodes('Data/@*') as T(C)    
), CTE2 as (
    select
        T.C.value('../@ID', 'bigint') as ID,
        T.C.value('local-name(.)', 'nvarchar(128)') as Name,
        T.C.value('.', 'nvarchar(max)') as Value
    from @Data2.nodes('Data/@*') as T(C)     
)
select
    isnull(C1.ID, C2.ID) as ID, isnull(C1.Name, C2.Name) as Name, C1.Value as Value1, C2.Value as Value2
from CTE1 as C1
    full outer join CTE2 as C2 on C2.ID = C1.ID and C2.Name = C1.Name
where
not
(
    C1.Value is null and C2.Value is null or
    C1.Value is not null and C2.Value is not null and C1.Value = C2.Value
)

Script to determine the block sessions.

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id

How do, ​I rotate the data (PIVOT) in SQL Server.

You use the PIVOT operator within your query’s FROM clause to rotate and aggregate the values in a dataset. The data is pivoted based on one of the columns in the dataset. Each unique value in that column becomes its own column, which contains aggregated pivoted data.

To better understand how this all works, let’s start with the basic syntax for a query that uses the PIVOT operator:

SELECT column_list FROM table_expression   PIVOT   (     aggregate_function(aggregate_column)     FOR pivot_column     IN( pivot_column_values )   ) [AS] pivot_table_alias [ORDER BY column_list];

if you ever close a ssms window and didn’t save your script, this will be your savior

Use dbname
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

Query to List all the tables and the number of rows in each table.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Delete All the databases from the Server, that Follow certain Pattern.

CREATE TABLE #databaseNames (name varchar(100) NOT NULL, db_size varchar(50), owner varchar(50), dbid int, created date, status text, compatibility_level int);
INSERT #databaseNames
exec sp_helpdb;

select * from #databaseNames

delete from #databaseNames where name not like ‘%upload%’

select * from #databaseNames

DECLARE dropCur CURSOR FOR
SELECT name FROM #databaseNames WHERE name like ‘%upload%’;
OPEN dropCur;
DECLARE @dbName nvarchar(100);
FETCH NEXT FROM dropCur INTO @dbName;
DECLARE @statement nvarchar(200);
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = ‘DROP DATABASE ‘ + @dbName;
EXEC sp_executesql @statement;
FETCH NEXT FROM dropCur INTO @dbName;
END
CLOSE dropCur;
DEALLOCATE dropCur;
DROP TABLE #databaseNames;

System Stored Procedures

The following SQL Server System stored Procedures provide a Powerful alternative for many monitoring tasks:

1.  Sp_who : Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.

2. sp_lock: Reports snapshot information about locks , including the object ID,       indexID,type of lock, and type or resource to which the lock applies.

3)  Sp_Spaceused :  Displays an estimate of the current amount of disk space used by a table .

4) Sp_Monitor : Displays statistics, including CPU usage , I/O Usage, and the amount of time idls since sp_Monitor was last executed.

Identify Last User Access of Table using T-SQL Script

SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
AND ius.[object_id] = OBJECT_ID(‘YourTableName’)
GROUP BY ius.[database_id], ius.[object_id];