Profiling SQL Server and/or ASP.NET

Question

How would one go about profiling a few queries that are being run from an ASP.NET application? There is some software where I work that runs extremely slow because of the database (I think). The tables have indexes but it still drags because it's working with so much data. How can I profile to see where I can make a few minor improvements that will hopefully lead to larger speed improvements?

Edit: I'd like to add that the webserver likes to timeout during these long queries.

Answer 1

Sql Server has some excellent tools to help you with this situation. These tools are built into Management Studio (which used to be called Enterprise Manager + Query Analyzer).

Use SQL Profiler to show you the actual queries coming from the web application.

Copy each of the problem queries out (the ones that eat up lots of CPU time or IO). Run the queries with "Display Actual Execution Plan". Hopefully you will see some obvious index that is missing.

You can also run the tuning wizard (the button is right next to "display actual execution plan". It will run the query and make suggestions.

Usually, if you already have indexes and queries are still running slow, you will need to re-write the queries in a different way.

Keeping all of your queries in stored procedures makes this job much easier.

Answer 2

To profile SQL Server, use the SQL Profiler.

And you can use ANTS Profiler from Red Gate to profile your code.