Discussion in 'Performance Tuning for DBAs' started by ks9072, Dec 20, 2002.
How to fine BAD Querys from SQL 2000
Use profiler. You can then run a trace and filter by duration to highlight queries which are taking long time to run.
You should then examine them in more detail, decide if they need tuning and are worth tuning in terms of how frequently they are used/how long it would take to tune. No point spending two hours tuning a query if you will save 30 seconds, but its only run once a month. Its up to you to decide if theyre 'bad' or not.
Also run a trace which isnt filtered by duration. I've seen badly written software which makes repeated and unnecessary queries that only take a few milliseconds each (and so didnt appear in my initial trace), but were so frequently issued they impacting performance.
Also from Query Analyzer using Execution Plan which shows minimal information, but PROFILER gives you more in detailed.
See this article for more useful information:http://www.sql-server-performance.com/jc_sql_server_quantative_analysis1.asp
Brad M. McGehee
Separate names with a comma.