Useful SQL

generate customer email list for mass email.sql

SELECT
    c.CompanyIdentifier AS CID,
    c.TradingName,
    c.AllocatedTo AS Rep,
    l.Region AS Branch,
    p.Position,
    p.FirstName,
    p.Family AS LastName,
    p.AccountsPerson,
    p.EmailList,
    p.Email
FROM
    tblCompanyDetails c INNER JOIN tblPerson p
    ON c.CompanyIdentifier=p.CompanyID
    INNER JOIN TblLogon l ON l.LogonName=c.AllocatedTo
WHERE c.Supplier_customer='C' AND c.Status IN ('CR','CA','SC') AND p.EmailList=1

performance analysis queries.sql

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 (
            'BROKER_EVENTHANDLER',
            'BROKER_RECEIVE_WAITFOR',
            'BROKER_TASK_STOP',
            'BROKER_TO_FLUSH',
            'BROKER_TRANSMITTER',
            'CHECKPOINT_QUEUE',
            'CHKPT',
            'CLR_AUTO_EVENT',
            'CLR_MANUAL_EVENT',
            'CLR_SEMAPHORE',
            'CXCONSUMER',
            'DBMIRROR_DBM_EVENT',
            'DBMIRROR_EVENTS_QUEUE',
            'DBMIRROR_WORKER_QUEUE',
            'DBMIRRORING_CMD',
            'DIRTY_PAGE_POLL',
            'DISPATCHER_QUEUE_SEMAPHORE',
            'EXECSYNC',
            'FSAGENT',
            'FT_IFTS_SCHEDULER_IDLE_WAIT',
            'FT_IFTSHC_MUTEX',
            'HADR_CLUSAPI_CALL',
            'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            'HADR_LOGCAPTURE_WAIT',
            'HADR_NOTIFICATION_DEQUEUE',
            'HADR_TIMER_TASK',
            'HADR_WORK_QUEUE',
            'KSOURCE_WAKEUP',
            'LAZYWRITER_SLEEP',
            'LOGMGR_QUEUE',
            'MEMORY_ALLOCATION_EXT',
            'ONDEMAND_TASK_QUEUE',
            'PARALLEL_REDO_DRAIN_WORKER',
            'PARALLEL_REDO_LOG_CACHE',
            'PARALLEL_REDO_TRAN_LIST',
            'PARALLEL_REDO_WORKER_SYNC',
            'PARALLEL_REDO_WORKER_WAIT_WORK',
            'PREEMPTIVE_OS_FLUSHFILEBUFFERS',
            'PREEMPTIVE_XE_GETTARGETSTATE',
            'PWAIT_ALL_COMPONENTS_INITIALIZED',
            'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
            'PWAIT_EXTENSIBILITY_CLEANUP_TASK',
            'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
            'QDS_ASYNC_QUEUE',
            'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            'QDS_SHUTDOWN_QUEUE',
            'REDO_THREAD_PENDING_WORK',
            'REQUEST_FOR_DEADLOCK_SEARCH',
            'RESOURCE_QUEUE',
            'SERVER_IDLE_CHECK',
            'SLEEP_BPOOL_FLUSH',
            'SLEEP_DBSTARTUP',
            'SLEEP_DCOMSTARTUP',
            'SLEEP_MASTERDBREADY',
            'SLEEP_MASTERMDREADY',
            'SLEEP_MASTERUPGRADED',
            'SLEEP_MSDBSTARTUP',
            'SLEEP_SYSTEMTASK',
            'SLEEP_TASK',
            'SLEEP_TEMPDBSTARTUP',
            'SNI_HTTP_ACCEPT',
            'SOS_WORK_DISPATCHER',
            'SP_SERVER_DIAGNOSTICS_SLEEP',
            'SQLTRACE_BUFFER_FLUSH',
            'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'SQLTRACE_WAIT_ENTRIES',
            'VDI_CLIENT_OTHER',
            'WAIT_FOR_RESULTS',
            'WAITFOR',
            'WAITFOR_TASKSHUTDOWN',
            'WAIT_XTP_RECOVERY',
            'WAIT_XTP_HOST_WAIT',
            'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
            'WAIT_XTP_CKPT_CLOSE',
            'XE_DISPATCHER_JOIN',
            'XE_DISPATCHER_WAIT',
            '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]
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;

select total_physical_memory_kb / 1000 as [total_physical_memory_mb],
    available_physical_memory_kb / 1000 as [available_physical_memory_mb],
    total_page_file_kb / 1000 as [total_page_file_mb],
    available_page_file_kb / 1000 as [available_page_file_mb],
    system_cache_kb / 1000 as [system_cache_mb],
    kernel_paged_pool_kb / 1000 as [kernel_paged_pool_mb],
    kernel_nonpaged_pool_kb / 1000 as [kernel_nonpaged_pool_mb],
    system_high_memory_signal_state,
    system_low_memory_signal_state,
    system_memory_state_desc
from sys.dm_os_sys_memory;

select *
from sys.dm_os_process_memory;

select memory_utilization_percentage,
    physical_memory_in_use_kb / 1000.0 / 1000.0 as [physical_memory_in_use_gb],
    total_virtual_address_space_kb / 1000.0 / 1000.0 as [total_virtual_address_space_gb],
    virtual_address_space_reserved_kb / 1000.0 / 1000.0 as [virtual_address_space_reserved_gb],
    virtual_address_space_committed_kb / 1000.0 / 1000.0 as [virtual_address_space_committed_gb],
    virtual_address_space_available_kb / 1000.0 / 1000.0 as [virtual_address_space_available_gb],
    available_commit_limit_kb / 1000.0 / 1000.0 as [available_commit_limit_gb]
from sys.dm_os_process_memory;

select * from sys.dm_os_sys_info;
left(object_name,50) as [object_name],
left(counter_name,50) as [counter_name],
left(instance_name,50) as [instance_name],
cntr_value,
cntr_type;

-- database size (disk space)
SELECT
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
GROUP BY database_id;