Question: I’d like to understand if there is a way in SQL Server to estimate the size of a sql result set in kilobytes (KB)? Is there some sort of statistic returned which associates a size statistic to a result set?
Answer: The first thing is to consider is that size is not a property of data. This means we need to use various calls to methods which supply the data.
SQL Server Client statistics returns data which will supply you with various details. One of the details it supplies is “Bytes received from server”. To access Client Statistics , go to SQL Server Management Studio. And then navigate to : Query | Include Client Statistics. The next query you execute will include and added tab to the Query results window.
A few things to consider when using SQL Server Client Statistics .
1) The figures represent the workload measured between SSMS and the database engine. If you’re using .Net 4.5 there are function calls which supply various client statistics.
2) Copying and Pasting to Excel doesn’t include the headers
3) The Client statistics retains the last 10 executions of a query. You can use this information to track changes made and performance impact
Read More on SSMS and Query Tuning