Tag: tuning

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

MICROSOFT SQL SERVER PERFORMANCE TUNNING

I’m writing this article to help everyone who whats “more juice” from a MS SQL SERVER INFRASTRUCTURE . Instead of writing lots of articles i will update this post every week. I will mix lots of concepts in this post, ranging from Physical Infrastructure/ Operating System/ SQL SERVER PARAMETERS / T-SQL . I will write a

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

MICROSOFT SQL SERVER 2005 Partitioning (English Version)

SQL SERVER 2005 Partitioning <- Can read a fully formated .doc in here . Microsoft worked well in the Microsoft SQL Server 2005 (even better in MSSQL Server 2008), bringing a shine to their RDBMS engine and building it to be a real Enterprise Class Product. I’m not going to write about all the new features

Continue reading

MICROSOFT SQL SERVER 2005 Partitioning (Versão Portuguesa)

Objectivo I. Benefícios e Limitações de Partitioned Clustered INDEX em SQL SERVER 2005. II. Metodologia de Implementação. III. Gestão de Particionamento. Descrição I. Benefícios e Limitações de Partitioned Clustered INDEX em SQL SERVER 2005: + Dados podem ser acedidos através de múltiplas partições em paralelo. Diferentes partições podem ser geridas em separado. Gestão de Histórico

Continue reading