Hi,

I am working on a live gps tracking project. The project is using SQL Server 2008 web edition. There are thousands of devices which are sending data packets in every 10 seconds or less depending on various settings to the server. On server there is a tcp server listener program which reads and parse data and insert it into a "Table-A". Table-A has a trigger which updates Table-B on each insertion (Table-B is used to show live updated status on website). Now Table-A has millions of records per day and high insertion rate per second.

We also need to show the history reports and other reports using "Table-A". The problem is query on "Table-A" is very slow and affacts the insertion on "Table-A". Please help to overcome this problem.

Since there are frequent inserts ,I believe you are selecting records using NOLOCK hint to avoid shared locks.

Also I would suggest to check your query whether it is being blocked when data is retieved.

Kindly check the following on this table -'Table-A'.

1. Fragmentation details on index

2. Table statistics

Action: - 1. If Fragmentation is high kindly perform regular index rebuild.

             2. Perform regular statistics update may be at every 8 hours.

 Above step will improve the query performance.

And compare the performance.

• Edited by Niting123 58 minutes ago