Skip to main content

Posts

Showing posts from September, 2014

What is SQL Server Profile

By
SQL Server Profiler. It is a graphical based tool provided by SQL Server. It enables you to trace a query. For example.you have a SP which you wish to trace. you can execute the SP and start a profiler trace on it. It will show you row by row execution of the SP SQL Server Profiler is used to trace and analyze the SQL Server running processes and queries in the cases of performance issue. It is taken high memory so not used in main server. It is have so many events for trace. so your select desired event and filter it. It can use to find the blocking by applying trace and catch everythng runing on the server
Post a Comment
Read more

How to Get historical data of blocked process in SQL

By
WITH [Blocking]AS (SELECT w.[session_id] ,s.[original_login_name] ,s.[login_name] ,w.[wait_duration_ms] ,r.[scheduler_id] ,w.[wait_type] ,r.[status] ,r.[wait_resource] ,w.[resource_description] ,s.[program_name] ,w.[blocking_session_id] ,s.[host_name] ,r.[command] ,r.[percent_complete] ,r.[cpu_time] ,r.[total_elapsed_time] ,r.[reads] ,r.[writes] ,r.[logical_reads] ,r.[row_count] ,q.[text] ,q.[dbid] ,p.[query_plan] ,r.[plan_handle] FROM [sys].[dm_os_waiting_tasks] w INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id] INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id] CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p WHERE w.[session_id] > 50 ) Insert into Blocking_v1 ([session_id] ,[blocking_session_id] ,[WaitingUserSessionLogin] ,[BlockingUserSessionLogin] ,[scheduler_id]...
Read more