Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”

by

The profiler trace puts it into perspective.

  • Query A: 1.3 secs CPU, 1.4 secs duration
  • Query B: 2.3 secs CPU, 1.2 secs duration

Query B is using parallelism: CPU > duration
eg the query uses 2 CPUs, average 1.15 secs each

Query A is probably not: CPU < duration

This explains cost relative to batch: 17% of the for the simpler, non-parallel query plan.

The optimiser works out that query B is more expensive and will benefit from parallelism, even though it takes extra effort to do so.

Remember though, that query B uses 100% of 2 CPUS (so 50% for 4 CPUs) for one second or so. Query A uses 100% of a single CPU for 1.5 seconds.

The peak for query A is lower, at the expense of increased duration.
With one user, who cares? With 100, perhaps it makes a difference…

More Related Contents:

  1. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  2. Add a column with a default value to an existing table in SQL Server
  3. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  4. How can I do an UPDATE statement with JOIN in SQL Server?
  5. The multi-part identifier could not be bound
  6. SQL Server indexes – ascending or descending, what difference does it make?
  7. Fastest way to update 120 Million records
  8. Is it possible to select sql server data using column ordinal position
  9. How to find current transaction level?
  10. How to insert multiple records and get the identity value?
  11. How to Replace Multiple Characters in SQL?
  12. How can a LEFT OUTER JOIN return more records than exist in the left table?
  13. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  14. Dynamic SQL to generate column names?
  15. SQL comma-separated row with Group By clause
  16. How to copy a huge table data into another table in SQL Server
  17. Column name or number of supplied values does not match table definition
  18. SQL Server PIVOT perhaps?
  19. How to find consecutive rows based on the value of a column?
  20. Should I use SQL_Variant data type?
  21. SQL server select distinct rows using most recent value only
  22. Script to save varbinary data to disk
  23. SQL poor stored procedure execution plan performance – parameter sniffing
  24. How to select date without time in SQL
  25. What is the internal representation of datetime in sql server?
  26. Eliminate and reduce overlapping date ranges
  27. Add a row number to result set of a SQL query
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Is there a quick way to check if ANY column is NULL?
  30. SQL query for parent-child chain

Leave a Comment