I have a 4000000 lines table (SQL Server 2008). This table has a private key composed of two int columns. One corresponds to a PK in another table of the same database. There is a relation between them. The other to a PK of a table in another database. (I don't know if it is important)
Sometimes, there are the same amount of queries as usualy, but the CPU usage grows to 90-100%. And some stored procedures on this table are too long. At this moment, just SELECT queries run on this table.
If I wait, the CPU usage gets normal after 1 or 2 hours. But I also can drop and recreate the PK index. And the CPU usage gets normal immediately. So I think the problem is related to this index. Am I wrong ?
But the index is not fragmented. I rebuild it every night in a maintenance plan. I even tried to reorganise it after each big insert or delete operations. I also tried to update its statistics. And I also tried to do nothing at all.
I get this problem randomly every day but at different hours, without any way to reproduce it. So I don't know how I could compare the execution plans.
EDIT : This table is populated with a SSIS package. It inserts missing lines and delete deprecated lines (no update). The insert is done by fast load within an oledb destination limited to 500 lines per batch. It does a bulk insert. I tried adding several things at the end of this package including reorganize indexes and update statistics.
The issue does not occure during the execution of this SSIS package. It can be for example 15 minutes after its end. It can occure even if there ar few lines manipulated by the package (50 lines for example).