Poor performance Dynamics NAV 500SP1 + SQL Server 2008

jsoage

After migrating SQL Server from 2000 to 2008 I had a lot of problems with performance and locks. ](*,)

I found some information that helped me to solve this problems \:D/ and hope it can help any others.

First I needed to identify the resources consuming queries:

SELECT TOP 30 -- You can change number of lines
  st.text,
  qs.last_execution_time,
  SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset 
                                                            WHEN -1 THEN DATALENGTH(st.text)
                                                            ELSE qs.statement_end_offset END 
                                                          - qs.statement_start_offset)/2) + 1) as statement_text,
  execution_count,
  case  
    when execution_count = 0 then null
    else total_logical_reads/execution_count 
  end as avg_logical_reads, 
  last_logical_reads,
  min_logical_reads,
  max_logical_reads,
  case  
    when execution_count = 0 then null
    else total_logical_writes/execution_count 
  end as avg_logical_writes, 
  last_logical_writes,
  min_logical_writes,
  max_logical_writes,
  max_elapsed_time  
FROM sys.dm_exec_query_stats as qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE qs.last_execution_time >= dateadd(day, -1, getdate()) -- You can change the datepart and number
ORDER BY max_logical_reads DESC -- You can change to max_logical_writes

And then tell Dynamics NAV when to force SQL to use an specific index
You can find this information in Dynamics NAV documentation (w1w1adg.pdf - Performance / 30.4 Keys, Queries and Performance (Index hinting))
or in http://msdn.microsoft.com/en-us/library/dd355052.aspx

Hope it helps