Question: I have a table with about 20+ million records.

Structure is like:

```
EventId UNIQUEIDENTIFIER
SourceUserId UNIQUEIDENTIFIER
DestinationUserId UNIQUEIDENTIFIER
CreatedAt DATETIME
TypeId INT
MetaId INT

```

Table is receiving about 100k+ records each day.

I have indexes on each column except MetaId, as it is not used in 'where' clauses

The problem is when i want to pick up eg. latest 100 records for desired SourceUserId

Query sometimes takes up to 4 minutes to execute, which is not acceptable.

Eg.

```
SELECT TOP 100 * FROM Events WITH (NOLOCK)
WHERE SourceUserId = '15b534b17-5a5a-415a-9fc0-7565199c3461'
AND 
(
 TypeId IN (2, 3, 4)
    OR 
 (TypeId = 60 AND SrcMemberId != DstMemberId)
)
ORDER BY CreatedAt DESC

```

I can't do partitioning etc as I am using Standard version of SQL Server and Enterprise is too expensive.

I also think that the table is quite small to be that slow. 

I think the problem is with ORDER BY clause as db must go through much bigger set of data.

Any ideas how to make it quicker ?

Perhaps relational database is not a good idea for that kind of data.

Data is always being picked up ordered by CreatedAt DESC

Thank you for reading.

PabloX

Answer: 
The fact that the table has indexes built on GUID values, indicates a possible series of problems that would affect performance:

* **High index fragmentation:** since new GUIDs are generated randomly, the index cannot organize them in a sequential order and the nodes are spread unevenly.
* **High number of page splits:** the size of a GUID (16 bytes) causes many page splits in the index, since there's a greater chance than a new value wont't fit in the remaining space available in a page.
* **Slow value comparison:** comparing two GUIDs is a relatively slow operation because all 33 characters must be matched.

Here a couple of resources on how to investigate and resolve these problems:

* [How to Detect Index Fragmentation in SQL Server 2000 and 2005](http://www.sql-server-performance.com/articles/per/detect_fragmentation_sql2000_sql2005_p1.aspx)
* [Reorganizing and Rebuilding Indexes](http://msdn.microsoft.com/en-us/library/ms189858.aspx)
* [How Using GUIDs in SQL Server Affect Index Performance](http://www.sql-server-performance.com/articles/per/guid_performance_p1.aspx)
I would make sure CreatedAt is indexed properly