wait types

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/#wait_info_current
====================
sys.dm_os_wait_stats-->shows the time for waits that have completed. This dynamic management view does not show current waits.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);--> 
GO

SELECT *
FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0
ORDER BY wait_time_ms DESC
GO

=============================================
select session_id,
status,
command,
blocking_session_id,
wait_type,
wait_time,
last_wait_type,
wait_resource
from sys.dm_exec_requests
where r.session_id >= 50
and r.session_id <> @@spid;
==========================================================
The picture tells us what wait types are most prevalent, on aggregate, on this SQL Server instance.
This can be an important step toward identifying a bottleneck cause.

select *
from sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
order by wait_time_ms desc;
============================================================
To show sys.dm_os_waiting_tasks, which is a SQL Server DMV specifically designed to show currently waiting tasks:


select * from sys.dm_os_waiting_tasks
where r.session_id >= 50
and r.session_id <> @@spid;
================================================================

select r.session_id,
status,
command,
r.blocking_session_id,
r.wait_type as [request_wait_type],
r.wait_time as [request_wait_time],
t.wait_type as [task_wait_type],
t.wait_duration_ms as [task_wait_time],
t.blocking_session_id,
t.resource_description
from sys.dm_exec_requests r
left join sys.dm_os_waiting_tasks t
on r.session_id = t.session_id
where r.session_id >= 50
and r.session_id <> @@spid;
=================================================================================================================================
In order to get details of currently running queries using sys.dm_exec_requests, the following query can be used:

SELECT
 sprc.loginame as LoginName,
 db_name(sprc.dbid) DatabaseName,
 sprc.spid as SPID,
 sprc.sql_handle as SqlHandle,
CONVERT(smallint, sprc.waittype) WaitType,
 sprc.lastwaittype as WaitTypeName,
 sprc.ecid as Ecid,
 sprc.waittime as WaitTime,
req.statement_start_offset as StmOffsetStart,
 req.statement_end_offset as StmOffsetEnd,
 req.start_time as StartTime
FROM master..sysprocesses AS sprc WITH(NOLOCK) LEFT
 OUTER JOIN sys.dm_exec_requests req ON req.session_id = sprc.spid
WHERE
( sprc.dbid <> 0 AND
  sprc.spid >= 51 AND
  sprc.spid <> @@SPID AND
  sprc.cmd  <>'AWAITING COMMAND' AND
  sprc.cmd NOT LIKE '%BACKUP%' AND
  sprc.cmd NOT LIKE '%RESTORE%' AND
  sprc.hostprocess > ''
  )
=================================================================================================================================
Wait type: Logbuffer--> it is waiting for log cache to move data from logblocks.

Writelog-->(suspended) it is waiting for log cache.

Logwiter--> it is thread which is transfer the data from log cache to disk(2012 spid 1<=2012,spid 42014>=)

How to see number of outstadning i\o's?

sys.dm_io_pending_io_requests

sys.dm_io_virtual_stats-->it will gives the total stall amount of reads\writes i\o

====================
This will show the waits grouped together as a percentage of all waits on the system, in decreasing order.

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        -- These wait types are almost 100% never a problem and so they are
        -- filtered out to avoid them skewing the results. Click on the URL
        -- for more information.
        N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
        N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
        N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
        N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
        N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
        N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
        N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
        N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
        N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
        N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE
        N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER

        -- Maybe comment these four out if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
        N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
        N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
        N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD

        N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
        N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
        N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
        N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
        N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX

        -- Maybe comment these six out if you have AG issues
        N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
        N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
        N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
        N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
        N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
        N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE

        N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
        N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
        N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
        N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
        N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
        N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER
        N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE
        N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST
        N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC
        N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK
        N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
        N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            -- https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
        N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
        N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
        N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
        N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
        N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
        N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
        N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
        N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
        N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
        N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
        N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
        N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
        N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
        N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
        N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
        N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
        N'SOS_WORK_DISPATCHER', -- https://www.sqlskills.com/help/waits/SOS_WORK_DISPATCHER
        N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
        N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
        N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
        N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
        N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
        N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
        N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
        N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
        N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
        N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
        N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
        N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
        N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
        )
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO

======================================================================================================================

Memory queries

https://www.nobroker.in/property/rent/pune/Hinjewadi,%20Pimpri%20Chinchwad?nbPlace=ChIJ7xsESMC7wjsR5d7Dw1rrydA&rent=0,14000&lat_lng=18.6019538074161,73.7176250962409&sharedAccomodation=0&type=RK1,BHK1&leaseType=FAMILY&orderBy=nbRank,desc&radius=2&propertyType=rent&




https://blogs.technet.microsoft.com/markrussinovich/2008/07/21/pushing-the-limits-of-windows-physical-memory/=====Memory utilization

========================================================================================
should be Available physical memory is high / steady

select
      total_physical_memory_kb/1024 AS total_physical_memory_mb,
      available_physical_memory_kb/1024 AS available_physical_memory_mb,
      total_page_file_kb/1024 AS total_page_file_mb,
      available_page_file_kb/1024 AS available_page_file_mb,
      100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3)))
      AS 'Percentage_Used',
      system_memory_state_desc
from  sys.dm_os_sys_memory;
==========================================================================================
This helps you find the most expensive cached stored procedures from a memory perspective

SELECT  TOP(25)
        p.name AS [SP Name],
        qs.total_logical_reads AS [TotalLogicalReads],
        qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
        qs.execution_count AS 'execution_count',
        qs.total_elapsed_time AS 'total_elapsed_time',
        qs.total_elapsed_time/qs.execution_count AS 'avg_elapsed_time',
        qs.cached_time AS 'cached_time'
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats AS qs
                   ON p.[object_id] = qs.[object_id]
WHERE
        qs.database_id = DB_ID()
ORDER BY
        qs.total_logical_reads DESC;

===========================================================================================

Below is the query that tells the information about the SPID which has high Memory Usage in SQL Server.

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)
========================================================================================

To find currently allocated memory:
==============================
SELECT 
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB, 
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB, 
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB, 
process_physical_memory_low, 
process_virtual_memory_low 
FROM sys.dm_os_process_memory; 
===============================
To find currently allocated memory:
select
      physical_memory_in_use_kb/1048576.0 AS 'physical_memory_in_use (GB)',
      locked_page_allocations_kb/1048576.0 AS 'locked_page_allocations (GB)',
      virtual_address_space_committed_kb/1048576.0 AS 'virtual_address_space_committed (GB)',
      available_commit_limit_kb/1048576.0 AS 'available_commit_limit (GB)',
      page_fault_count as 'page_fault_count'
from  sys.dm_os_process_memory;
===============================
op 25 Costliest Stored Procedures by Logical Reads

SELECT  TOP(25)
        p.name AS [SP Name],
        qs.total_logical_reads AS [TotalLogicalReads],
        qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
        qs.execution_count AS 'execution_count',
        qs.total_elapsed_time AS 'total_elapsed_time',
        qs.total_elapsed_time/qs.execution_count AS 'avg_elapsed_time',
        qs.cached_time AS 'cached_time'
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats AS qs
                   ON p.[object_id] = qs.[object_id]
WHERE
        qs.database_id = DB_ID()
ORDER BY
        qs.total_logical_reads DESC;
===============================

You can use the sys.dm_os_memory_clerks DMV as follows to find out how much memory SQL Server has allocated through AWE mechanism.
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks
===============================
Find the memory consumetion by each database:

-- Memory used by each database
SELECT DB_NAME(database_id),
COUNT (1) * 8 / 1024 AS MBUsed
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
GO

===========================

-- Query to identify objects that are taking up most of that memory in Buffer Pool.
-- This is only for the current database context. Please prefix <USE DBNAME> as per your requirement

SELECT TOP 25
 DB_NAME(bd.database_id) as DBNAME,
 obj.[name] as [Object Name],
 sysobj.type_desc as [Object Type],
 i.[name]   as [Index Name],
 i.[type_desc] as [Index Type],
 COUNT_BIG(*) AS Buffered_Page_Count ,
 COUNT_BIG(*) * 8192 / (1024 * 1024) as Buffer_MB,
 bd.page_type as [Page Type] -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_name(object_id) AS name
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name 
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
LEFT JOIN sys.objects sysobj on i.object_id = sysobj.object_id
WHERE database_id = DB_ID()
and sysobj.type not in ('S','IT')
GROUP BY DB_NAME(bd.database_id), obj.name, obj.index_id , i.[name],i.[type_desc],bd.page_type,sysobj.type_desc
ORDER BY Buffered_Page_Count DESC

==================

Locking queries

How can I found which user has locked the tables and is it possible to release the lock by other users? Please provide me T-SQL query to get the lock details and How can I release it?



I got this query
Locking queries
1->select * from sys.sysprocesses Where SPID=65--and Open_Tran>0



2->SP_LOCK

3->SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
o1.name 'object name',
o1.type_desc 'object descr',
p1.partition_id 'partition id',
p1.rows 'partition/page rows',
a1.type_desc 'index descr',
a1.container_id 'index/page container_id'
FROM sys.dm_tran_locks as t1 INNER JOIN sys.dm_os_waiting_tasks as t2 ON t1.lock_owner_address = t2.resource_address
LEFT OUTER JOIN sys.objects o1 on o1.object_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions p1 on p1.hobt_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units a1 on a1.allocation_unit_id = t1.resource_associated_entity_id

useful sql scripts

backup history:
select  top 5 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
 ,b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where a.database_name Like 'master%'
order by a.backup_finish_date desc

==========================================

SELECT [backup_start_date], [backup_finish_date] FROM msdb.dbo.backupset WHERE [type] = 'D' AND [database_name] = 'pam' ORDER BY [backup_start_date] DESC;
=======================================
Rename database:

USE master; 
GO 
ALTER DATABASE AdventureWorks2012 
Modify Name = Northwind ; 
GO 

==========================================

alter database DBOldName set single_user with rollback immediate
alter database DBOldName modify name = DBNewName
alter database DBNewName set multi_user

=========================================
finding SQl server memory allocation:

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory
================================
SELECT command,
s.text,
start_time,
percent_complete,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
==================================================================================================
SELECT command,
start_time,
percent_complete,
estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
==================================================================================================
q sch
=========================================================
select * from sys.dm_os_memory_objects
select * from sys.dm_os_memory_pools
select * from sys.dm_os_memory_nodes
select * from sys.dm_os_memory_cache_entries
select * from sys.dm_os_memory_cache_hash_tables
============================================================
select type, name, sum(multi_pages_kb)/1024 as multi_pages_mb
from sys.dm_os_memory_clerks
where multi_pages_kb > 0
group by type, name
order by multi_pages_mb desc
============================================================

SELECT percent_complete,*
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )
=======================================================
sp_who2

DBCC INPUTBUFFER (59);

print @@version

USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
GO

dbcc sqlperf(logspace)


select * from sys.databases
select * from sys.sysprocesses 

select * from sys.dm_exec_requests where blocking_session_id<>0

=============================
To finding the blocking:
SELECT session_id, blocking_session_id, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle);
====================

/**** Query to check currently running sessions ****/
SELECT DISTINCT
        name AS database_name,
        session_id,
        host_name,
        login_time,
        login_name,
        reads,
        writes
FROM    sys.dm_exec_sessions
        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE   resource_type <> 'DATABASE'
--AND name ='specific db name'
ORDER BY name
====================
to find currently running on the system:
SELECT  *
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
        CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO
============================
To find the blocking:

SELECT  SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
                  ( CASE der.statement_end_offset
                      WHEN -1 THEN DATALENGTH(dest.text)
                      ELSE der.statement_end_offset
                           - der.statement_start_offset
                    END ) / 2 + 1) AS querystatement ,
        deqp.query_plan ,
        der.session_id ,
        der.start_time ,
        der.status ,
        DB_NAME(der.database_id) AS DBName ,
        USER_NAME(der.user_id) AS UserName ,
        der.blocking_session_id ,
        der.wait_type ,
        der.wait_time ,
        der.wait_resource ,
        der.last_wait_type ,
        der.cpu_time ,
        der.total_elapsed_time ,
        der.reads ,
        der.writes
FROM    sys.dm_exec_requests AS der
        CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
        CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO
============================================================

*********
To find the latest restore:

select restore_date, destination_database_name, user_name, restore_type,
stop_at, stop_at_mark_name, stop_before
from msdb.dbo.restorehistory
where restore_date > '20180125 08:00'
--you may use a filter on the database name as well
--AND destination_database_name like 'MyDatabase%'
order by restore_date desc;

*********
==============================================================

SQL Server Long Query Script:

SELECT DISTINCT TOP 20
est.TEXT AS sqlturkiye_queryText
   ,DB_NAME(dbid)
   ,eqs.execution_count AS sqltr_execCount
   ,eqs.max_elapsed_time AS sqltr_maxelapsedTime
   ,ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count, 0), 0) AS sqltr_avgElapsedTime
   ,eqs.creation_time AS sqltr_CreatedTime
   ,ISNULL(eqs.execution_count / NULLIF(DATEDIFF(s, eqs.creation_time, GETDATE()), 0), 0) AS sqltr_execPerSecond
   ,total_physical_reads AS sqltr_agPhyRead
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est
ORDER BY eqs.max_elapsed_time DESC
=======================================================
==============================================================================

DBCC SHOWCONTIG
================================
To find the fragmentaion level:

SELECT OBJECT_NAME(ips.OBJECT_ID)
 ,i.NAME
 ,ips.index_id
 ,index_type_desc
 ,avg_fragmentation_in_percent
 ,avg_page_space_used_in_percent
 ,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC
================================================================================
Script : Index Fragmentation Report Script

--To Find out fragmentation level of a given database
--This query will give DETAILED information
--CAUTION : It may take very long time, depending on the number of tables in the DB
USE AdventureWorks
GO
SELECT object_name(IPS.object_id) AS [TableName],
   SI.name AS [IndexName],
   IPS.Index_type_desc,
   IPS.avg_fragmentation_in_percent,
   IPS.avg_fragment_size_in_pages,
   IPS.avg_page_space_used_in_percent,
   IPS.record_count,
   IPS.ghost_record_count,
   IPS.fragment_count,
   IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO
========================================================
==========================
SELECT *
FROM master.sys.syslogins;
===========================
EXEC sp_helplogins
You can also pass an "@LoginNamePattern" parameter to get information about a specific login:

EXEC sp_helplogins @LoginNamePattern='fred'
=====================================================
SELECT name FROM master..sysxlogins WHERE sid IS NOT NULL
=======================================================
select name, sid, password_hash from sys.sql_logins
========================================================
Get the list of all Login Accounts in a SQL Server:
SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc
==================================================
Get the list of all SQL Login Accounts only:
SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
==========================
How to find out List of all logins in SQL Server those are enabled/disabled. :
SELECT name, type_desc, is_disabled
FROM sys.server_principals
==========================================
---------To find users permission on SP------------

SELECT
dp.Class,
dps1.Name As Grantee,
dps2.Name As Grantor,
so.Name,
so.Type,
dp.Permission_Name,
dp.State_Desc
FROM sys.database_permissions AS dp
JOIN Sys.Database_Principals dps1
ON dp.grantee_Principal_ID = dps1.Principal_ID
JOIN Sys.Database_Principals dps2
ON dp.grantor_Principal_ID = dps2.Principal_ID
    JOIN sys.objects AS so
    ON dp.major_id = so.object_id
    WHERE so.Name = 'UpdateStock'
============================================================

Get the list of all Login Accounts in a SQL Server

SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc
=====================
Get the list of all SQL Login Accounts only

SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
and name not like '%##%'
======================================
Get the list of all Windows Login Accounts only

SELECT name
FROM sys.server_principals
WHERE TYPE = 'U'
======================================
Get the list of all Windows Group Login Accounts only

SELECT name
FROM sys.server_principals
WHERE TYPE = 'G'
=================================================

select highest_cpu_queries.plan_handle,highest_cpu_queries.
plan_generation_num,highest_cpu_queries.max_worker_time,
highest_cpu_queries.total_physical_reads,
highest_cpu_queries.total_logical_reads,
highest_cpu_queries.total_elapsed_time,q.[text],q.dbid,q.objectid,q.number,q.encrypted,query_plan
from (select top 50 qs.plan_handle,
qs.plan_generation_num,qs.creation_time, qs.execution_count, qs.total_worker_time,
qs.max_worker_time, qs.total_elapsed_time,
qs.max_elapsed_time, qs.total_logical_reads, qs.max_logical_reads,
qs.total_physical_reads, qs.max_physical_reads from sys.dm_exec_query_stats
qs order by qs.total_worker_time DESC)
as highest_cpu_queries
cross apply sys.dm_exec_sql_text (plan_handle) as q
cross apply sys.dm_exec_query_plan (plan_handle) as qp
order by highest_cpu_queries.total_worker_time DESC
================================================================================

When was the last time a login was used?


--list of logins and last time each logged in
SELECT [Login] = login_name
,[Last Login Time] = MAX(login_time)
FROM sys.dm_exec_sessions
GROUP BY [login_name];




=======================================================

Top 3 CPU-sapping queries for which plans exist in the cache

SELECT TOP 3
total_worker_time ,
execution_count ,
total_worker_time / execution_count AS [Avg CPU Time] ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END ) - deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;
==========================
Top ten most costly queries in cache by total worker time.
SELECT TOP ( 10 )SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,( ( CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)ELSE QS.statement_end_offset
 END - QS.statement_start_offset ) / 2 ) + 1) AS statement_text , execution_count ,total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms ,total_logical_reads ,       
total_logical_reads / execution_count AS avg_logical_reads , total_elapsed_time / 1000 AS total_elapsed_time_ms ,       
 ( total_elapsed_time / 1000 ) / execution_count AS avg_elapsed_time_ms ,   qp.query_plan FROM    sys.dm_exec_query_stats
qs  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_worker_time DESC
========================================================
high CPU usage
tempdb usage
stack dump

You should analyse and determine the cause of the Stack dumps, If you have support from microsoft, you can consult them and of course you can always delete them,
they are nothing but memory dumps, may be generated because of memory issue,
access violations, DB courruption, etc. You can also check SQL Server error log for the information or errors logged at the same time when dump was generated.
Sometimes dumps are also generated because of Database corruption, So i will also suggest to run DBCC CHECKDB.

Scenario 1

I asked you about Version of SQL Server and you did not responded, the reason I asked it because if you are running RTM version of SQL Server or your SQL Server is not patched to latest Service Pack and cumulative update there is no point in opening case with Microsoft. If you do so the Microsoft Engineer or the support personal would first ask you to apply latest SP.

Other scenario is if you have not updated your SQL Server to latest SP, for instance for SQL Server 2012 you have SP3 released and you are still on SP1 and you log a case with Microsoft for this issue you would be charged and its quite possible the support guy would say this is known issues and is fixed in Sp3. So you would end up wasting money. So I strongly suggest you to check whether the SQL Server is on latest SP.

I also wanted to check whether you are actually running supported version of SQL Server or not. The way it is creating dump I have hunch that you are running SQL Server which is not patched to latest SP

Scenario 2

If the SQL Server is patched to latest SP and still it is crashing producing stack dumps I would suggest you to open case with Microsoft they are the best in terms of analyzing the stack dump file and would surely tell you the reason. Unless you are really good with analyzing dumps I would not suggest you to waste time in doing so.

I can share with you few blogs which would give you some hint on how to analyze the dumps

Looking into SQL Server Minidump File
How to analyze deadlock scheduler dumps
Analyzing non yielding resource monitor
Scenario 3

Not all crash dumps are because of bug in SQL Server many occur due to poorly configured SQL Server or some rouge queries running. But since you have not shared detailed errorlog it is difficult to say at this point. Make sure your SQL Server is configured correctly. Again if such is the case MS support will point this out.

Moral:

If SQL Server is not updated with latest SP first update it, look for the cumulative updates released after the SP(you can get that from first link I have shared) and make sure bug you are facing is not fixed in CU releases ONLY then open case with Microsoft.

can I delete them and if it is good thing to do ?

If you are planning to open case with Microsoft I suggest you move them to some other location just in case. If you have those dumps you can give more information to support personal who would be looking at your case. Also note that its quite likely that the dump produced would not capture all information related to the issue and support personal would ask you to enable trace flag and wait for the next dump to occur which will capture all the related information.

If you really like to delete it, delete the old ones and leave the new ones.


==============================================================================================

How to get a listing of all available cluster resources?

C:\Windows\System32>cluster resource
--or
C:\Windows\System32>cluster resource /status
31. How to failover a service from one node to another?

C:\Windows\System32>cluster group "groupname" /move:nodeName

==================================================================================================
open tran:


down vote
You can get all the information of active transaction by the help of below query

SELECT
trans.session_id AS [SESSION ID],
ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL
===========================================
dbcc opentran()


====================================

SELECT *
   FROM sys.dm_tran_session_transactions tst INNER JOIN sys.dm_exec_connections ec ON tst.session_id = ec.session_id
   CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)


================================


SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0

==============================================

1-SELECT * FROM SYS.SYSPROCESSES WHERE OPEN_TRAN = 1
2-SELECT * FROM SYS.DM_TRAN_SESSION_TRANSACTIONS

=====================
uncommited transactinos

SELECT
er.session_id
,er.open_transaction_count
FROM sys.dm_exec_requests er

sql performance active transaction

SELECT
    trans.session_id AS [SESSION ID],
    ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
    trans.transaction_id AS [TRANSACTION ID],
    tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION BEGIN TIME],
    tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL

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

SELECT * 
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

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

Are the statistics current?

If your database statistics are not up to date, it will cause the query optimizer to not have the right information to build the best query plan. For example, the optimizer could opt for a parallel plan when a nonparallel plan was the best option making you think that you have something wrong with the max degree of parallelism because you see lots of SOS_SCHEDULER_YIELD or CXPACKET waiting tasks. If this is the case you should update the statistics right away and then execute the DBCC FREEPROCCACHE command to clear the procedure cache so new plans can be built using the updated statistics.

EXEC sp_updatestats
GO
DBCC FREEPROCCACHE() 
GO

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

You can also use the DMVs to get information about blocking.

SELECT session_id, command, blocking_session_id, wait_type, wait_time, wait_resource, t.TEXT
FROM sys.dm_exec_requests 
CROSS apply sys.dm_exec_sql_text(sql_handle) AS t
WHERE session_id > 50 
AND blocking_session_id > 0
UNION
SELECT session_id, '', '', '', '', '', t.TEXT
FROM sys.dm_exec_connections 
CROSS apply sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE session_id IN (SELECT blocking_session_id 
                    FROM sys.dm_exec_requests 
                    WHERE blocking_session_id > 0)

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

Dynamic Management Views

There are some useful Dynamic Management Views (DMVs) to check CPU bottlenecks. The sys.dm_exec_query_stats DMV shows you the currently cached batches or procedures which are using the CPU. The following query can be used to check the CPU consumption per plan_handle.

select plan_handle,
      sum(total_worker_time) as total_worker_time, 
      sum(execution_count) as total_execution_count,
      count(*) as  number_of_statements 
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time), sum(execution_count) desc

-----------------------------------------------USE SQLSentry;
GO

;WITH src AS
(
SELECT
[Object] = o.name,
[Type] = o.type_desc,
[Index] = COALESCE(i.name, ''),
[Index_Type] = i.type_desc,
p.[object_id],
p.index_id,
au.allocation_unit_id
FROM
sys.partitions AS p
INNER JOIN
sys.allocation_units AS au
ON p.hobt_id = au.container_id
INNER JOIN
sys.objects AS o
ON p.[object_id] = o.[object_id]
INNER JOIN
sys.indexes AS i
ON o.[object_id] = i.[object_id]
AND p.index_id = i.index_id
WHERE
au.[type] IN (1,2,3)
AND o.is_ms_shipped = 0
)
SELECT
src.[Object],
src.[Type],
src.[Index],
src.Index_Type,
buffer_pages = COUNT_BIG(b.page_id),
buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
src
INNER JOIN
sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE
b.database_id = DB_ID()
GROUP BY
src.[Object],
src.[Type],
src.[Index],
src.Index_Type
ORDER BY
buffer_pages DESC;
=========================================================

-- Note: querying sys.dm_os_buffer_descriptors
-- requires the VIEW_SERVER_STATE permission.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT 
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767 
THEN 'Resource DB' 
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), 
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC; 
=============================================================
SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage, 
    process_physical_memory_low AS sql_process_physical_memory_low, 
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;
============================================================

SELECT * FROM SYS.SYSPERFINFO WHERE
OBJECT_NAME='SQLSERVER:BUFFER MANAGER' AND
(COUNTER_NAME='TARGET PAGES' OR
COUNTER_NAME='TOTAL PAGES' OR
COUNTER_NAME='DATABASE PAGES' OR
COUNTER_NAME='STOLEN PAGES' OR
COUNTER_NAME='FREE PAGES')

================================================
Use the following DMV query to determine which SQL Server components are consuming the most amount of memory, and observe how this changes over time:

SELECT TYPE, SUM(MULTI_PAGES_KB) FROM
SYS.DM_OS_MEMORY_CLERKS WHERE
MULTI_PAGES_KB != 0 GROUP BY TYPE

================================================
The sample output shows that the total memory allocated is 18 MB system-level memory consumption and 1358MB allocated to database id of 5. 
Since this database is mapped to a dedicated resource pool, this memory is accounted for in that resource pool.

Sample Output
SELECT type  
     , name  
     , memory_node_id  
     , pages_kb/1024 AS pages_MB   
   FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%' 

================================================

This query will show which SQL Server objects are consuming memory:

SELECT TYPE, PAGES_ALLOCATED_COUNT FROM
SYS.DM_OS_MEMORY_OBJECTS WHERE
PAGE_ALLOCATOR_ADDRESS IN (SELECT TOP 10
PAGE_ALLOCATOR_ADDRESS FROM
SYS.DM_OS_MEMORY_CLERKS ORDER BY
MULTI_PAGES_KB DESC) ORDER BY
PAGES_ALLOCATED_COUNT DESC
=================================================
To get an idea of which individual processes are taking up memory, use the following query:

SELECT TOP 10 SESSION_ID, LOGIN_TIME, HOST_NAME,
PROGRAM_NAME, LOGIN_NAME, NT_DOMAIN, 
NT_USER_NAME, STATUS, CPU_TIME, MEMORY_USAGE, 
TOTAL_SCHEDULED_TIME, TOTAL_ELAPSED_TIME, 
LAST_REQUEST_START_TIME,
LAST_REQUEST_END_TIME, READS, WRITES, 
LOGICAL_READS, TRANSACTION_ISOLATION_LEVEL, 
LOCK_TIMEOUT, DEADLOCK_PRIORITY, ROW_COUNT, 
PREV_ERROR FROM SYS.DM_EXEC_SESSIONS ORDER
BY MEMORY_USAGE DESC
=============================================
Disk:
Processes that are disk intensive typically do not have the appropriate indexes or have poor execution plans. Here is a DMV query that lists the top 25 tables experiencing I/O waits.

SELECT TOP 25 DB_NAME(D.DATABASE_ID) AS
DATABASE_NAME, 
QUOTENAME(OBJECT_SCHEMA_NAME(D.OBJECT_ID, 
D.DATABASE_ID)) + N'.' +
QUOTENAME(OBJECT_NAME(D.OBJECT_ID,
D.DATABASE_ID)) AS OBJECT_NAME, D.DATABASE_ID, 
D.OBJECT_ID, D.PAGE_IO_LATCH_WAIT_COUNT,
D.PAGE_IO_LATCH_WAIT_IN_MS, D.RANGE_SCANS,
D.INDEX_LOOKUPS FROM (SELECT DATABASE_ID, 
OBJECT_ID, ROW_NUMBER() OVER (PARTITION BY
DATABASE_ID ORDER BY
SUM(PAGE_IO_LATCH_WAIT_IN_MS) DESC) AS
ROW_NUMBER, SUM(PAGE_IO_LATCH_WAIT_COUNT) AS
PAGE_IO_LATCH_WAIT_COUNT, 
SUM(PAGE_IO_LATCH_WAIT_IN_MS) AS
PAGE_IO_LATCH_WAIT_IN_MS, 
SUM(RANGE_SCAN_COUNT) AS RANGE_SCANS, 
SUM(SINGLETON_LOOKUP_COUNT) AS
INDEX_LOOKUPS FROM
SYS.DM_DB_INDEX_OPERATIONAL_STATS(NULL, NULL, 
NULL, NULL) WHERE PAGE_IO_LATCH_WAIT_COUNT > 0
GROUP BY DATABASE_ID, OBJECT_ID ) AS D LEFT JOIN
(SELECT DISTINCT DATABASE_ID, OBJECT_ID FROM
SYS.DM_DB_MISSING_INDEX_DETAILS) AS MID ON
MID.DATABASE_ID = D.DATABASE_ID AND
MID.OBJECT_ID = D.OBJECT_ID WHERE
D.ROW_NUMBER>20 ORDER BY
PAGE_IO_LATCH_WAIT_COUNT DESC
=============================================
generate a list of columns that should have indexes on them:

SELECT * FROM SYS.DM_DB_MISSING_INDEX_GROUPS
G JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS GS
ON GS.GROUP_HANDLE = G.INDEX_GROUP_HANDLE
JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON
G.INDEX_HANDLE = D.INDEX_HANDLE

==============================================
CPU. One of the most frequent contributors to high CPU consumption is stored procedure recompilation. 
Here is a DMV that displays the list of the top 25 recompilations:
SELECT TOP 25 SQL_TEXT.TEXT, SQL_HANDLE, 
PLAN_GENERATION_NUM, EXECUTION_COUNT, DBID, 
OBJECTID FROM SYS.DM_EXEC_QUERY_STATS A
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) 
AS SQL_TEXT WHERE PLAN_GENERATION_NUM >1
ORDER BY PLAN_GENERATION_NUM DESC
============================================
Top CPU consumers:

SELECT TOP 50 SUM(QS.TOTAL_WORKER_TIME) AS
TOTAL_CPU_TIME, SUM(QS.EXECUTION_COUNT) AS
TOTAL_EXECUTION_COUNT, COUNT(*) AS
NUMBER_OF_STATEMENTS, SQL_TEXT.TEXT, 
QS.PLAN_HANDLE FROM SYS.DM_EXEC_QUERY_STATS
QS CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) AS SQL_TEXT
GROUP BY SQL_TEXT.TEXT,QS.PLAN_HANDLE ORDER
BY SUM(QS.TOTAL_WORKER_TIME) DESC
=============================================

look for memory bottlenecks, then disk and finally CPU

=============================================
When searching for bottlenecks, look for memory bottlenecks, then disk and finally CPU. Capture a baseline using System Monitor, 
SQL Profiler and DMVs to determine what is causing the bottleneck and if it can be solved by a hardware upgrade. Once you have a baseline,
 you are ready to start diagnosing the problem. In most cases, the solution will involve query tuning, query rewrites or re-architecting your solution. 
Many times, throwing hardware at the problem will not have the performance gains of simple index placement.
=================================================
In our database, run a query that we have created to find indexes that we have created but are not being used.

SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id 
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO


=============================================================

update statistics:
To find statistics on database level:

USE AdventureWorks2012;
GO
DBCC SHOW_STATISTICS ('Sales.Currency', AK_Currency_Name) WITH STAT_HEADER
GO

=====
to find on object level:

USE AdventureWorks2012;
GO

SELECT OBJECT_NAME(object_id) AS [Objects], 
MAX(STATS_DATE([object_id], [stats_id])) AS [StatisticsUpdatedOn] 
FROM sys.stats GROUP BY OBJECT_NAME(object_id) 
HAVING OBJECT_NAME(object_id) NOT LIKE 'sys%' 
ORDER BY [StatisticsUpdatedOn] DESC


==========
to find the app quries 

SELECT
session_id,status,
command,sql_handle,database_id
,(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS query_text 
FROM sys.dm_exec_requests r
WHERE session_id >= 51


select s.session_id, s.login_name, s.host_name, s.status,
s.program_name, s.cpu_time, s.last_request_start_time,
(SELECT text FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS query_text 
from sys.dm_exec_sessions s, sys.dm_exec_connections c
where s.session_id = c.session_id and
s.session_id > 50 
order by s.last_request_start_time desc

===========================

to find wait stats:

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold
GO

DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR)

----

This is Praveen. I have completed my graduation in the stream of Bachelor of Technology at JNTU University in 2009. I have completed my Intermediate education at Shri Prathiba Junior College.
I have worked as a MS SQL DBA in Wipro. Later, I worked as a MS SQL DBA at Ensono Technologies.
My hobbies are listening music, watching television, and playing caroms.
My strengths are self motivated and being dedicated.
I can present my skill and abilities as much as possible for the growth of the organisation.

Missing MSI & MSP

Symptoms:

SQL Server Setup has encountered the following error:

1)   When we started to do the Installation or Up gradation on the SQL Server if MSI and MSP files missing then we encountered below errors:

2)   After getting above errors, we have to check in error log using below path to identifying the exact error

“C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt”.

 Resolution

Please follow the below steps for resolution if MSI & MSP files:

1)      Before fixing MSI and MSP files, need to run the VBS script to capture all missing MSI and MSP files

2)      Firstly, Create empty folder with Name as Missing_MSI_MSP in any drive which having enough space.

3)      After creating folder, Copy the text mentioned under FindSQLInstallsOnly.vbs Script content to a Notepad and renames the file to FindSQLInstallsOnly.vbs. Copy vbscript from below link

    ( https://support.microsoft.com/en-gb/help/969052/how-to-restore-the-missing-windows-installer-cache-files-and-resolve-problems-that-occur-during-a-sql-server-update)

4)      After creating folder, Copy the text

 

  

5)      Open a Command Prompt and browse to the location where this VBS script is located.

Go to Run < CMD run as Administrator<

Run the following command:

“Cscript FindSQLInstallsOnly.vbs %Server name%_sql_install_details.txt “

6) After running the above command and review the Output which have created as text document.               Find the find the error with Missing files and getting error as highlighted below:

MSI ERROR:

================================================================================

PRODUCT NAME   : Microsoft SQL Server 2008 R2 RsFx Driver

================================================================================

  Product Code: {D8C23BDE-4748-44D9-A9DD-8AB64EB18BE3}

  Version     : 10.51.2500.0

  Most Current Install Date: 20110804

  Target Install Location:

  Registry Path:

   HKEY_CLASSES_ROOT\Installer\Products\EDB32C8D84749D449ADDA86BE41BB83E\SourceList

     Package    : rsfx.msi

  Install Source: \x64\setup\

  LastUsedSource: n; 1; o:\6c4e8f5248900e41e6b64c80d1\x64\setup\

 !!!! rsfx.msi DOES NOT exist on the path in the path o:\6c4e8f5248900e41e6b64c80d1\x64\setup\ !!!!

 Action needed, re-establish the path to o:\6c4e8f5248900e41e6b64c80d1\x64\setup\

Installer Cache File: C:\Windows\Installer\6690cd2.msi

 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 !!!! C:\Windows\Installer\6690cd2.msi DOES NOT exist in the Installer cache. !!!!

 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

     Action needed, recreate or re-establish path to the directory:

o:\6c4e8f5248900e41e6b64c80d1\x64\setup\then rerun this script to update installer cache and     results

    

The path on the line above must exist at the root location to resolve this problem with your msi/msp file not being found or corrupted, In some cases you may need to manually copy the missing file or manually

Replace the problem file overwriting it is exist:

 Copy "o:\6c4e8f5248900e41e6b64c80d1\x64\setup\rsfx.msi" C:\Windows\Installer\6690cd2.msi

 Replace the existing file if prompted to do so.

MSP ERROR:

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

Microsoft SQL Server 2008 Full text search Patches Installed

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

 Display Name:    Hotfix 2723 for SQL Server Database Services 2008 Full Text (64-bit) (KB971491)

 KB Article URL:  ;http://support.microsoft.com/?kbid=971491

 Install Date:    20100106

   Uninstall able:   1

 Patch Details:

   HKEY_CLASSES_ROOT\Installer\Patches\42BCD43AD9C6D1141A0F427744DCFCA9

   PackageName:   sql_fulltext.msp

    Patch LastUsedSource: n;1;C:\Windows\CUSTOM\InstallSource\microsoft\MSSQLServer\SQL2k8ENTx64_2723\Servers\CU\x64\setup\

   Installer Cache File Path:     C:\Windows\Installer\2b61c3.msp

Per SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\42BCD43AD9C6D1141A0F427744DCFCA9\LocalPackage

!!!! C:\Windows\Installer\2b61c3.msp package DOES NOT exist in the Installer cache. !!!!

     Action needed, recreate or re-establish path to the directory:

       C:\Windows\CUSTOM\InstallSource\microsoft\MSSQLServer\SQL2k8ENTx64_2723\Servers\CU\x64\setup\ then rerun this script to update installer cache and results

    

The path on the line above must exist at the root location to resolve this problem with your msi/msp file not being found or corrupted, In some cases you may need to manually copy missing files or manually

Replace the problem file, Copy

"C:\Windows\CUSTOM\InstallSource\microsoft\MSSQLServer\SQL2k8ENTx64_2723\Servers\CU\x64\setup\sql_fulltext.msp" C:\Windows\Installer\2b61c3.msp

 Replace the existing file if prompted to do so.

Fixing MSI Files MSP files:

Launch media go to last used path or location of the file and then copy the file to Installer cache and rename with as shown in VBS script file (Repeat this step for all missing files).

Ex: As per above details

Go to the last used path with exists (o:\6c4e8f5248900e41e6b64c80d1\x64\setup\) or If this error message was generated for rsfx.msi, sql_fulltext.msp then you need to locate this file from the setup media under the folder structure: MSI:\x64\setup\rsfx_msi\,

                                                              MSP:CU\x64\setup\sql_fulltext.msp

Rename with rsfx.msi to 6690cd2.msi, sql_fulltext.msp to 2b61c3.msp and copy in Installer cache which as shown in VBS script file (Repeat this step for all missing files).

2) Not clustered or the cluster service is up and online. Failed

a) This issue occurs because of an invalid MSCluster namespace in Windows Management Instrumentation (WMI).

Resolution:

To resolve this issue, follow these steps:

1.At an administrative command prompt, type cd %systemroot%\system32\wbem, and then press Enter.

2.Type the following command, and press Enter:

regsvr32 cluswmi.dll

3.Type the following command, and press Enter:

mofcomp.exe ClusWMI.mof

4.Rerun the Setup of the service packs or cumulative updates on the serve

In standalone we have to skip the rules using cmd:

Path of patch executable>:\Setup /SkipRules=Cluster_IsOnlineIfClustered /action=patch

b) Some cases after running mof also we will get same error in this case we have to run wmi file .it is mostly happened in 2008 .We can get wmi file in below node

3) Attributes do not match:

To work around this problem, remove the Archive attribute from the installation folder. To do this, follow these steps:

1. Open the folder that contains the folder that has the Archive attribute.

2. Right-click the folder that you want to remove the Archive attributes from, and then click Properties.

3. On the General tab, click Advanced.

4. On the Advanced Attributes tab, click to clear the Folder is ready for archiving check box.

5. Re_run the SQL install

If the installation folder does not exist, remove the Archive attribute from the closest parent folder. For example, you install SQL Server 2008 R2 into the following folder:

C:\FolderA\FolderB\FolderC

The FolderC folder does not exist. The FolderB folder has the Archive attribute set. In this situation, you must remove the Archive attribute from the FolderB folder.

4) Element not found: Patch fails with Element not found. Check the errors are listed in the event logs what are the reasons and Check the disks are presented in the failover cluster manager or not.

Please check below sample snap I found below Server disks failed state in cluster manager it’s already decommissioned so I removed failed state disks in cluster manager, then I applied patch it was successfully completed. Keep track on the whole cluster manager all services, Nodes and disks are working or not.

6) Log OR Data directory in the registry is not valid:

Resolution:

When we check on error details it’s showing the data registry is not valid. That means below registry patch is not exists on mount point

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer

Please check below snap for server all mount points.

So I have changed registry  paths from H:\MSSQL$UTR_MAIN1_UAT\MntVol-Remote-TRANLOG01\Log   to   H:\MSSQL$UTR_MAIN1_UAT\MntVol-Remote-TRANLOG02\Log

After changed patch re_run the patch