SQL Server 2008 auto audit facility

Sql Server 2008 introduce automatic auditing facility. You can read detail from following link or follow these steps
1. In Security tab go to --> Audit --> New Audit.
2. In Audit Database go to -- > Audit Specificatoin.

and enjoy

for more detail please read
http://msdn.microsoft.com/en-us/library/dd392015.aspx

Finding Slow Runnig Quries

SELECT top 10
ProcedureName = o.name,
[Procedure] = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
LastLogicalReads = s.last_logical_reads,
MaxLogicalWrites = s.max_logical_writes,
LastLogicalWrites = s.last_logical_writes,
CreationDateTime = s.creation_time,
MaxPhysicalReads = s.max_Physical_reads,
LastPhysicalReads = s.last_Physical_reads,
CallsPerSecond = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
INNER JOIN SYS.objects o on t.objectid = o.object_id
ORDER BY
AvgExecutionTime DESC



-- select * from sys.databases

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,
st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Returning Comma seperated Values in SQL Server 2005

Declare @vComma Varchar(max);

Select @vComma = COALESCE(@vComma+';','') + column1 from table1

select @vComma

I have 5 values in Unique table against Val column and this command will return all values as ; seperated