3

I recently migrated from Delphi 7 with SQL Server 2000 to Delphi 2010 with SQL Server 2008. I am using dbExpress.

After installing the new version I have found that the on sites that have a lot of data that system has become slow and unstable.

Can any one tell me if there is an issue between dbExpress and SQL Server 2008? Please help!!!!!

RRUZ
  • 134,889
  • 20
  • 356
  • 483
user734781
  • 273
  • 1
  • 9
  • 19
  • 1
    Define **slow**, **unstable** - give us something more to go on!! This is just way too broad as it is..... – marc_s Dec 26 '11 at 16:56
  • Well, on the same process that where normal, while using Delphi 7 and SQL-Server 2008, Now with Delphi 2010 and SQL-Server 2008 are very slow, Opening tables, searching, posting etc. – user734781 Dec 26 '11 at 17:15

1 Answers1

1

By performing a profiler trace you can see if you have any bottlenecks on SQL Server. Your default profiler trace (include TextData for RPC:Completed) should be good enough to start with.

The profiler trace can be analysed to see what takes the longest time. You can easily load the trace into a table and analyse it there. Note that when loaded into a table, the duration column is in microseconds. See the function fn_trace_gettable for a quicker way of loading a trace file into a table.

A common cause for poor performance, especially after a major change, is bad indexing. Since SQL Server 2005 the optimiser stores within in-memory structures the indices it would like to have seen. These can be accessed with the dynamic management views sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_groups_stats.

Here is a simple sample SQL to create your own missing index report, including the basic code to generate the missing index.

select
d.statement
,   d.equality_columns
,   d.inequality_columns
,   d.included_columns
,   s.user_seeks    Seeks
,   s.last_user_seek
,   cast (s.avg_total_user_cost as decimal (9,2)) Cost
,   s.avg_user_impact   [%]
, 'CREATE INDEX MissingIndex_ ON ' + d.statement + '('
    + case when equality_columns IS NOT NULL then equality_columns else '' end
    + case when equality_columns IS NOT NULL AND inequality_columns IS NOT NULL then ', ' else '' end
    + case when inequality_columns IS NOT NULL then inequality_columns else '' end
    + ')'
    + case when included_columns IS NOT NULL then ' INCLUDE (' + included_columns + ')' else '' end
AS SQL
from sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
Steven Devaney
  • 31
  • 1