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