I think it's well and truly nailed now. For some event types the SELECT where clause looked like this:

WHERE table1.col8 IN (NULL, N'value')

The code was recently fixed to remove...

Interesting observation, Colin and thanks for sharing.

When my test started I noted much improved performance. For the first 329mins I was getting really good throughput, comparable with Oracle and...

updating the column definition for col8 from varchar(255) to nvarchar(255) has resulted in a dramatic difference. I'll leave the test running to see if anything else happens but so...

the number of entries in the IN clause can be any number, maybe up to 100-200. It's purely driven by how many items are included with the event. ...

When I run the slow query in SQL Studio I get fairly consistent times. I setup a test to run the same statement 10 times in a row and...

I captured the execution plan from SQL Profiler and it looks like the INDEX is being used. The duration reported is 176ms which is way longer than anything I've...

Here's some more information that may be useful.

1. I let my data load run for a couple of days and notes the continued degradation of the SELECT statement. When...

That select will table scan if the number of records returned exceeds more than about 1% of the table. I can't tell whether the in is a single value (and...

I have manually checked the execution plans of the queries by running them in the Management Studio and according to the output I get, the SELECT should be using the...

When I run this query:

I see this:

Full Scans/sec 597397

as I run this the number increases. I have only one database instance in use so I know that all the...

I think I've nailed the problem (although I haven't fixed it).

In short, we have SELECTs that are taking longer and longer to run. It's like index isn't being used...

I'll see what I can dig up. Thanks.

I have reverted the index situation back to how it was originally. The things I tried were:

1. PK nonclustered

2. no indexes except for PKs

3. various fillfactor sizes

None of these...

Thanks, Colin.

Just to re-iterate, even though the test I am using is styled like a data load, it's not really a data load at all. It's just a high...

Just to correct some earlier statements.

I have been told the server is a dual CPU with hyperthreading technology. Also, when I reported the high CPU for the SQL Server...