Minimizing the time it takes for an MS SQL query to run is important when creating an application or website. You can always add more resources on the SQL server, such as RAM and CPU, but that doesn’t really fix the source of the issues, especially if the there are large volumes of SQL transactions. Microsoft SQL Server has the ability to display statistics on queries, to determine the CPU time, and elapsed time of an SQL statement.
You can use these statistics as a guide to determine how long each query may take, and then try and optimize the query, and check the statistics again. Inefficient stored procedures, views with complex joins, recursive triggers, etc, can all potentially increase the query time drastically.
A sample of what the performance statistics information looks like:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 293 ms.
To turn statistics on, execute the following SQL statement in a SQL Management Studio query window.
SET STATISTICS TIME ON
Now after running each SQL query, you can check the ‘Messages’ window/tab (instead of the ‘Results’ window/tab), to see how long in milliseconds it took to run the query.
To turn statistics off, execute the following SQL statement in a SQL Management Studio query window.
SET STATISTICS TIME OFF
If you want to just enable the statistics for a single query, you can set the statistics to be on, and then off again straight after the query.
For example:
SET STATISTICS TIME ON SELECT * FROM vProducts ORDER BY ProductCode DESC SET STATISTICS TIME OFF
This will output something similar to the following in the ‘Messages’ window/tab.
(163 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 293 ms.
