Q:

SQL Server - Performance of Querying Large Tables With GROUP BY

I have table "TRANSACTION" in Sql Server 2008. Approximately, 6 records in 1 second are inserted into this table. (Since it is financial transactions table) So, in 1 day, 500.000 records are inserted. 
Table is partitioned weekly.
This table is heavily used for many kind of select  (with NOLOCK, of course), insert, update operations. 
Do you think the query below may slow down other critical select, insert, update operations on the same table? I think, even if the query below lasts too long, other select queries are not going to slow down since this query does not lock the table. But I cannot be sure, and ask to you.
Note that, the columns in the select list are NOT indexed on table.
SET @END_DATE = GETDATE()

SET @START_DATE = DATEADD(HOUR, -24, @END_DATE) 

SELECT Column1, Column2, Column3, Column4, COUNT(*) FROM [TRANSACTION] WITH(NOLOCK)
WHERE TRANSACTION_DATE BETWEEN @START_DATE AND @END_DATE
GROUP BY Column1, Column2, Column3, Column4

A:

Running any query on the server will use CPU / Memory / IO, so in essence anything you run can have an impact on other queries being executed. 
You are definately going to read in ~500k rows from your own figures, the row size you could calculate and you could even get a rough idea of how many pages this data would therefore be stored on. You would have to cross check against the query plan to make sure it was at least not doing a full partition scan, otherwise it would be 3.5 million rows scanned into memory.
Will that put you outside of your SLAs? we have no way of telling that, only you can determine that through suitable load testing.

