If you have a single query that is performing poorly the best way to identity why is to run it in query analyzer while displaying the graphical execution plan. You will then be able to see where the time/resource is going.
If you are trying to identify what is running slowly then you should monitor your query activity with SQL Profiler, capturing queries that are consuming excessive resource.
I notice you mention you have previous problems with locking, if you have upgraded your code you may have locking hints within your queries to deal with the issues you experience in SQL Server 6.5. I would recommend that you remove all locking hints and see how the queries perform. Locking hints are usually not needed in SQL Server 7.0 and above and often cause negative effects.
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Microsoft SQL Server Installation
Related Q&A from Tony Bain
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.