Measuring Execution Times
The SQL Server Management Studio shows query measurement time in seconds. If we are concern about performance then in-depth measurement is required such as milliseconds.
Using Statistics
set statistics time on-- your queryset statistics time off
Messages Tab
SQL Server parse and compile time:
CPU time = 67 ms, elapsed time = 67ms
Setting the timing as default for every query
Query -> query options -> advanced -> Execution
check the “set statistics time” checkbox
Check “set statistics IO” checkbox
Using Client Statistics
Ways to turn on the Client Statistics
- Menu: Query -> Include client Statistics
- Toolbar: Click button Include Statistics
- Keyboard: Shift+Alt+S
Properties
| Client Execution Time | Time the trial was started. |
| Query Profile Statistics | |
| Number of INSERT, DELETE and UPDATE statements | The number of Insert, Delete or Update statements that were executed in that particular trail. |
| Rows affected by INSERT, DELETE or UPDATE statements | Number of rows that were affected by Insert, Delete or update statement part of your trial. |
| Number of SELECT statements | Number of select statement that were executed under that particular trial execution. It includes fetch statements to retrieve rows from cursors. |
| Rows returned by SELECT statements | Rows selected as part of that trail execution. |
| Number of transactions | User transactions used in a trail execution. |
| Network Statistics | How much traffic is moving from client to the server and back. |
| Number of server roundtrips | Number of times request sent to server and number of time reply received from server in a trail execution. |
| TDS packets sent from client | Number of TDS packets client has sent to the database server under a trial execution. |
| TDS packets received from server | The number of TDS packets received by client from database server under a trial execution. |
| Bytes sent from client | The number of bytes that the client has sent to database server under a trial execution. Includes spaces selected after query as well. |
| Time Statistics | How much time was spent processing on the client versus how much time was spent waiting for the server in milliseconds. |
| Client processing time | The cumulative amount of time that the client spent in executing code while the query was executed |
| Total execution time | The cumulative amount of time (in milliseconds) that the client spent processing while the query was executed, including the time that the client spent waiting for replies from the server as well as the time spent executing code. |
| Wait time on server replies | The cumulative amount of time (in milliseconds) that the client spent while it waited for the server to reply. |
By default Client Statistics shows up to 10 trails. After 10 trails, every trail position get decreased by 1 and trail 0 get removed from list.
Reset Client Statistics
Query Menu -> Reset Client Statistics
Using Execution Time as Variable
DECLARE @StartTime datetime DECLARE @EndTime datetime SELECT @StartTime=GETDATE() --Query goes here SELECT @EndTime=GETDATE() SELECT DATEDIFF(ms, @StartTime, @EndTime) AS [Duration in millisecs]