Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2009-12-04 : 12:20:28
|
| I'm am doing a series of like for like comparisons between inline sql queries and dynamic sql queries.I run the 2 side by side, and then look at the execution plans to see which has the best %However, we identified that the 'cost' did not necessarily reflect which was the most performant (in terms of TIME to execute)I then ran a profiler trace to capture CPU, reads, writes, duration........ and some of the queries that had better resource utilisation took longer to run, but reported a better COST than the quicker query.So why is this, how can something that takes less CPU time, less reads, etc actually run for longer? Is this because other processes occurring on the server (but this was in an isolated environment). Is profiler the best thing to use to measure performance?? And is duration the most important factor to consider (particularly in an evnvironment where we can just add more hardware)Your opinions would be greatly received.....Hearty head pats |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-12-04 : 12:43:27
|
| Hi thereYes, I am running both those statements inbetween each queryHearty head pats |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-12-04 : 12:55:43
|
Hi TaraThanks for your responseI did record the cost of plan recompilations. I shall have a look at the stats, and will look at the queries that record a higher duration, but lower cost, and see whether these are recompiling. But that would make sense if included :). Some of the tests I did was to only remove cached data, and not the cached plans as I wanted to see whether the parameterised queries did re-use the plans. So the odd results that I have seen may have been related to only those tests. I may have been a little hasty and should have thoroughly analysed the stats.......I'll get back to you on this one once I've investigated that theory (but will be after the weekend as its now home time... yay)Have a great weekend  Hearty head pats |
|
|
|
|
|
|
|
|
How to best measure query performance