I am having trouble figuring out why my simple SELECT query is performing poorly on a relatively small table. The execution time on the full push INSERT query on the target table is under 10 seconds, while SELECT * FROM target_table took 7:48 when I ran it this morning.
Every database on my server has “Allow Snapshot Isolation” and “Is Read Committed Snapshot On” set to “True” in Database Properties > Options. My tables are all using ROWSTORE indexes–1 clustered for primary key (defined PK) and 6 non-clustered for foreign key columns (none are defined as an FK).
Table physical stats:
| calculation | value |
|---|---|
| Row count | 303,562 |
| Page count | 19,577 |
| avg record size (bytes) | 504.01 |
| PK index avg fragmentation size in pages | 264.24675 |
| PK index avg fragmentation in percent | 0.01 |
| total columns | 33 |
The slow SELECT queries are random; meaning they do not happen all the time and the problem isn’t limited to this table. The problem seems to be limited to SSMS querying and not from Power BI querying the data. The problem doesn’t perfectly correlate to when we enabled SNAPSHOT isolation, as the problem was present a short time before enabling it too.
What I need are some good tools for figuring out why this could possibly be happening on my end. Helpful sys schema queries or SSMS built-in tools or other suggestions are welcome.