Search This Blog & Web

Friday, June 26, 2009

Difference between Editions

1. SQL Server 2008 Features Comparison
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
http://msdn.microsoft.com/en-us/library/cc645993.aspx

2. SQL Server 2005 Features Comparison
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

3. SQL Server 2000 Features Supported
http://msdn.microsoft.com/en-us/library/aa175266(SQL.80).aspx

Monday, June 1, 2009

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;