Tag: bottleneck

Disable Lock Escalation in SQL SERVER 2008 or Higher

For who don’t know, it is know possible to remove Lock Escalation, and this is something that be “granularly” done to a table on SQL SERVER 2008 or Higher without the need to use Trace Flags. Sample sintax ALTER TABLE table_name_goes_gere SET (LOCK_ESCALATION=DISABLE) . — PT — Para quem não sabe, já é possível remover

Continue reading

Analyzing SQL SERVER 2005 LONG RUNNING QUERIES

I’m leaving this tip for you to find what exactly is running on sql server. SELECT r.session_id, s.HOST_NAME, s.PROGRAM_NAME, s.host_process_id, r.status, r.wait_time, wait_type, r.wait_resource, SUBSTRING(qt.text,(r.statement_start_offset/2) +1, (CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 ELSE r.statement_end_offset END -r.statement_start_offset)/2) AS stmt_executing,r.blocking_session_id, r.cpu_time,r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions s

Continue reading