Server Side Trace with Security Audit Event Class Events

DECLARE @return_code INT;

DECLARE @TraceID INT;

DECLARE @maxfilesize BIGINT;

SET @maxfilesize = 5;

--step 1: create a new empty trace definition

EXEC sp_trace_create

                @traceid OUTPUT

               , @options = 2

               , @tracefile = N'C:\TraceFiles\LoginAudit21'

               , @maxfilesize = @maxfilesize

    , @stoptime =NULL

    , @filecount = 2;

-- step 2: add the events and columns

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 10 -- Application Name

               , @on = 1;--include this column in trace

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 35 --Database Name

               , @on = 1;--include this column in trace

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 11 --Login Name

               , @on = 1;--include this column in trace  

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 6 -- NTUserName

               , @on = 1;--include this column in trace

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 12 --SPID

               , @on = 1;--include this column in trace

EXEC sp_trace_setevent

                @traceid = @TraceID

               , @eventid = 14 -- Security Audit

               , @columnid = 14 --EndTime

               , @on = 1;--include this column in trace        

-- step 3: add duration filter

--DECLARE @DurationFilter BIGINT;

--SET @DurationFilter = 10000000; --duration in microseconds

--EXEC sp_trace_setfilter

--                @traceid = @TraceID

--               , @columnid = 13

--               , @logical_operator = 0 --AND

--               , @comparison_operator = 4 -- greater than or equal to

--               , @value = @DurationFilter; --filter value

--SELECT @TraceID AS TraceID;

Simple DDL Trigger for auditing at the database level

This simple DDL trigger will use the EVENTDATA() function to capture data at the database level when DDL statements are run

--Audit Table

CREATE TABLE audit_table
(
table_name  VARCHAR (200)
, [user] VARCHAR (200)
, date_time DATETIME
, activity VARCHAR (200)
)

--DDL Trigger (To be created on the database that needs to be audited)

CREATE TRIGGER DDL_Trigger
ON DATABASE
FOR ALTER_TABLE, DROP_TABLE, ALTER_PROCEDURE, REVOKE_DATABASE
AS
INSERT INTO audit_table
SELECT
 EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')
,SUSER_SNAME()
,GETDATE()
,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

Explanation:

I have enabled this trigger to capture only a few events such as ALTER, DROP and REVOKE but you can get the entire list of actions that can be captured in this link

http://msdn.microsoft.com/en-us/library/bb522542.aspx

Memory usage by object for all databases

SELECT a.table_name, a.index_name, a.object_type, a.index_type, d.cache_size_in_MB
FROM
(
SELECT TOP 100 so.name as table_name, si.name as index_name, si.type_desc as index_type, so.type_desc as object_type, au.allocation_unit_id--, *
FROM sys.partitions sp
INNER JOIN sys.allocation_units au
ON sp.hobt_id = au.container_id
INNER JOIN sys.objects so
ON sp.object_id = so.object_id
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
AND sp.index_id = si.index_id
WHERE so.is_ms_shipped = 0
) a

INNER JOIN


(SELECT (COUNT(*)*8)/1024 AS cache_size_in_MB, db.allocation_unit_id
    ,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS database_name
FROM sys.dm_os_buffer_descriptors db
GROUP BY DB_NAME(database_id) ,database_id, db.allocation_unit_id) d
--ORDER BY cache_size_in_MB DESC
ON a.allocation_unit_id = d.allocation_unit_id

Session ID

SELECT  session_id, status, command, blocking_session_id, wait_time as wait_time_in_milli_seconds, SUBSTRING(detail.text,
                  requests.statement_start_offset / 2,
                 ABS ((requests.statement_end_offset - requests.statement_start_offset)) / 2) AS sql_text
, requests.wait_type
, requests.wait_resource
, requests.percent_complete AS percent_complete_for_maintenance_jobs
, cpu_time AS cpu_time_in_milliseconds
, total_elapsed_time AS total_elapsed_time_in_milliseconds
, logical_reads
, row_count
FROM    sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.sql_handle) detail

SQL Server Memory Grants

DMV Query to examine the cached query plans that are taking up memory space

The query below tells us which SQL is using what amount of memory currently. This space is used to for the duration of the query processing only and does not include the space occupied by compiled plans in the cache

SELECT
session_id
, granted_memory_kb
, syst.text AS sql_text
, sysp.query_plan
FROM sys.dm_exec_query_memory_grants sysm
CROSS APPLY sys.dm_exec_sql_text (sysm.sql_handle) syst
CROSS APPLY sys.dm_exec_query_plan (sysm.plan_handle) sysp
ORDER BY 1 DESC

DMV Query to see which queries are waiting for a memory grant

This query tells us which SQLs are waiting for memory space to be granted for processing their hash and sort data

SELECT
session_id
, dop as [degree_of_parallelism]
, request_time
, requested_memory_kb
, query_cost
, timeout_sec
, wait_order
, CASE is_next_candidate WHEN 1 THEN 'Next Candidate' ELSE 'Not the Next Candidate' END AS is_next_candidate
, wait_time_ms
, syst.text AS sql_text
FROM sys.dm_exec_query_memory_grants sysm
CROSS APPLY sys.dm_exec_sql_text (sysm.sql_handle) syst
WHERE wait_time_ms > 0
ORDER BY 1 DESC

Reference: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx?Redirected=true

Simple query to find the blocking and blocked SQLs

This query relies on sys.dm_exec_requests, sys.dm_exec_sql_text and sys.processes DMV, function and catalog view. I out this together quickly so had to overlook the fact that sys.processes may be deprecated in the future. I will try to re-write this with the more current DMVs and functions

SELECT
  session_id
, blocking_session_id
, st.text AS blocked_sql
, st2.text AS blocking_sql
FROM sys.dm_exec_requests dmer
CROSS APPLY sys.dm_exec_sql_text (dmer.sql_handle) st
INNER JOIN sys.sysprocesses sp
ON dmer.blocking_session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle) st2

Script to see all the processes that are waiting for a lock and their SQL statements

This script below will list out all the sessions that are waiting to acquire a lock. The 'WAIT' where clause parameter will filter out other locks such as those that are in 'GRANT' mode

SELECT
resource_type
, DB_NAME (resource_database_id) as database_name
, request_mode
, request_type
, request_status
, request_session_id
, request_owner_type
, dmet.text
FROM sys.dm_tran_locks dtl
INNER JOIN sys.dm_exec_connections dmec
ON dtl.request_session_id = dmec.session_id
CROSS APPLY sys.dm_exec_sql_text (dmec.most_recent_sql_handle) dmet
WHERE request_status = 'WAIT'