Indexes & tuning

Question

Indexes & tuning

marklegosz

SSCrazy

Points: 2816
More actions
April 29, 2008 at 6:35 am

#154316

Hi all.
I'm supporting a SQL 2005 DB (around 18GB), where on top sits Microsoft Dynamics NAV 5.01. The application has a 3rd party component installed, which is used for syncing data out to an online store.
The best way I can describe the component is trigger type functionality at the application level. i.e. any changes go into a 'sync' table which is then read by another application.
This other application polls from what I can tell every 5 to 10 seconds. Typically data only exists in this table whilst the polling application is sleeping. So we have transactions coming in, polling process removing the lines, and generally no data being stored for any length of time (apart from application failure).
My question is what would an appropiate indexing strategy be for such a table. There were originally 5 indexes on this table. I removed 3 after running a trace and looking at query based on dm_db_index_usage_stats.
In an environment where no data is stored for any length of time, and when there is data there we are talking about a dozen rows with half a dozez columns, would I be correct in assuming the fewer indexes the better?
Here is the create statement for the table.
CREATE TABLE [dbo].[Torlys Inc_$DVPNTN Synch_ Reference](
[timestamp] [timestamp] NOT NULL,
[Transaction No_] [int] NOT NULL,
[Main Table No_] [int] NOT NULL,
[Table No_] [int] NOT NULL,
[No_] [varchar](50) NOT NULL,
[NAV Field Name] [varchar](30) NOT NULL,
[Action] [int] NOT NULL,
[Field No_] [int] NOT NULL,
[Schema Code] [varchar](30) NOT NULL,
CONSTRAINT [Torlys Inc_$DVPNTN Synch_ Reference$0] PRIMARY KEY CLUSTERED
(
[Transaction No_] ASC,
[Main Table No_] ASC,
[Table No_] ASC,
[No_] ASC,
[NAV Field Name] ASC
)
Obviously there are probably better ways to achieve this same funcationality, but for the short to medium term we are not in a position to change architecture.
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

In this case, I'd say no indexes are necessary on the source table and for speed, the fewer indexes on the target table, the better. If the target table is huge, you need to be real careful about what the clustered index is so you don't get page splits. That will slow the world down, right now!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

marklegosz SSCrazy Points: 2816 More actions · Answer 2

When you say target table, are you referring to the destination table populated by the third party application?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Yes...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)