any log about db queries executed in my site?

Question

Hi,

I own this site: www.gramma.ro. (asp.net/c#)... I've been working 2 days on doing improvements on UI using YSlow and PageSpeed.

Well...at this moment the site seems to load ok...i mean from the UI point of view. (Page Speed improvments from 51 to 75; YSlow from F to D(C) ).

BUT there is still "waiting" time between pressing a link and waiting for the redirect. So, i assume there is some sql/linq improvment on the queries i must do.

Do you know how can i see which part of my site is time consuming? For instance when i load the index page to see where it spends most of the time? (fetching db results? prepare the ui?) Is there any tracking tool to help me?

I'm using Sql Server 2008 Express Edition.

Thanks.

UPDATE: Found an interesting SQL Profiler for SQL Express editions.

http://sites.google.com/site/sqlprofiler/

Answer 1

Actually, the easiest tracking tool is right at your fingertips: TRACE. You can turn this on per page or for the whole site. Then, underneath each page, you'll see all the events that passed by and how much time was spent in each event.

From an SQL point of view, you can turn tracing on in your SQL Server. Most databases that I know of support this, but it depends on the type of database you have.

There are many professional tools around that can profile your application. Google for Profile .NET web application, or Profile C# and you should get rather good hits. Many offer a free trial with full functionality, which should get you a feel of the tools.

PS: I find the loading time of your site rather excellent, it is faster than the (currently rather slow) StackOverflow ;).

Answer 2

There are many profiling tools ou there. We have tried a few and the one we like the most is dotTRACE.

If you just want to profile the SQL part of it, you can just run SQL Profiler, it comes with SQL Server, it's in the Tools menu of SQL Management Studio.

Answer 3

SQL Server itself keeps track of a good deal of the statistics you need. The best place to start from is sys.dm_exec_query_stats. This DMV tracks the execution time and IO of every query (actually of every execution plan, but these are details) still in the Query Plan cache. If you start with the top queryies in the plan cache by total_elapsed_time, you have a good chance to finding the problem spots:

select q.text, *
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text (s.sql_handle) q
order by s.total_elapsed_time desc;

All SQL editions retain these statistics, including SQL Express. You can interrogate these DMVs from any query tool, like the SQL Server Management Studio Express.