SQL Query, how to know if is resource intensive? I/O

I am not a SQL guru, but I am troubleshooting an issue with our marketing platform as its become slugish and slow, I’ve requested the top 50 heavy/resource intensive queries executed on our sql server to correlate with our marketing workflows.

Here is an example or some of the top queries, based on the average I/O would you say these queries are consuming too much resources? whats a normal acceptable IO for a query?

If I take the first query execution plan, it will be as following.

Plan Link https://www.brentozar.com/PasteThePlan/?id=HJtGXHxUc

There is not a value that is good, or bad, for a query. It completely depends on what the query is doing. SELECT * from a table without a WHERE clause from a table with billions of rows will have a very high level of I/O. Whereas, SELECT ID from a table with a WHERE clause on the ID column and a clustered index on the ID column will have about as low a level of I/O as it’s possible to get. Neither is right or wrong and there’s not a >42 is bad kind of measure here.

Instead, you have to look at the query and what it’s doing. Then, look at the execution plan to see how the query is being resolved.

In this case, you have a table scan, meaning it’s a heap with no clustered index, even though there are clear WHERE clause values that could use an index to find data. So either the heap doesn’t haven any nonclustered indexes, or, the nonclustered indexes it has, don’t support the query. Does this mean there is excessive I/O going on? Yeah, probably.

Additionally, you have a nonclustered index in use, skypipeline_eventid, but it’s not a covering index because then you have an RID lookup (another heap table). Is this excessive I/O? Yeah, probably.

In general, the vast majority of your tables should have clustered indexes. Indications are, this database has none, or few (sample size of 2 ain’t exactly dispositive). Without clustered indexes on the column(s) that define the most commonly used path to the data, you’re doing nothing but table scans all over the place. So, yeah, you’re probably experiencing excessive I/O.