April 29, 2008 at 6:37 am
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.
April 29, 2008 at 7:44 am
I assume the sync table is used by just an insert, a single query, and a delete (basically a queue).
It should be pretty easy to tune this because the activity is pretty well fixed.
Trace the database if you have to and get the actual statements that go against this table and spend some time making sure their execution plans are ok.
I would not mess too much with the schema that a third party utility uses though. Perhaps the vendor can tell you how the table should be indexed - I assume they have other customers and have had to performance tune this before.
April 29, 2008 at 9:28 am
I agree. You should minimize what is maintained there to only what is needed to run the processes that move the data in & out. Assuming it's not also used for reporting. That would be the only need for additional indexes.
Also, since it only has a very few rows, most indexes, except for a cluster against the key or maybe a compound cluster against the most likely search criteria, are likely to be less than useful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2008 at 11:40 am
Thanks all for your help - I appreciate it.
April 29, 2008 at 11:59 am
If a table has less than 200 rows, SQL Server won't even use non-clustered indexes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply