Performance bottleneck - Linq to SQL or database - how do I say?

Question

I'm currently trying to tune more performance on my reporting website, which uses linq for sql and SQL Server Express 2008. I find that when I now approach millions of rows of my uglier tables, performance becomes a real problem. moreover, one report, in particular, takes 3 minutes to create.

Essentially, I have a loop that for each user enters the database and captures a dataset on them. This data is then requested in various ways (and more lines are loaded as needed) until I have a small enough summary object that I can run into the silverlight map set. Lazy loading is used, and the report takes data from about 8 related tables.

The problem is that I don’t know where the bottleneck is now and how to improve performance. Due to certain restrictions, I was forced to use uniqueidentifiers for a number of primary keys in the tables involved - could this be a problem?

Basically, I need to take time to improve performance, but not enough to do this with both the database and linq in sql. Anyway, I see where the bottlenecks are?

How do I work express, I do not have access to the profiler. I am considering the possibility of rewriting my queries into compiled linq in sql, but I am afraid that the database might be the culprit.

I understand that this question is a little open, and it is difficult to answer, not knowing much more about my setup (database schema, etc.), but any tips on how to find out where the bottlenecks are most valued!

thank

UPDATE: Thanks for all the great guys, and some links to some great tools.

, linq. , . . , - . - 800 000 + . , , !

, 20 , 3 !

Answer 1

: Linq2Sql. , .

, SQL- LINQ , (cs aspx), , n + 1 (, ) . , .

Answer 2

, : LinqPad Visual Studio Debugger. -, LinqPad, , , SQL ... .

-, Visual Studio, , DataContext ( : , )

#if DEBUG
  private readonly Stopwatch Watch = new Stopwatch();

  private static void Connection_StateChange(object sender, StateChangeEventArgs e)
  {
    if (e.OriginalState == ConnectionState.Closed && e.CurrentState == ConnectionState.Open) 
    {
      Current.Watch.Start();
    }
    else if (e.OriginalState == ConnectionState.Open && e.CurrentState == ConnectionState.Closed)
    {
      Current.Watch.Stop();

      string msg = string.Format("SQL took {0}ms", Current.Watch.ElapsedMilliseconds);
      Trace.WriteLine(msg);
    }
  }
#endif

private static DataContext New
{
  get
  {
    var dc = new DataContext(ConnectionString);
#if DEBUG
    if (Debugger.IsAttached)
    {
      dc.Connection.StateChange += Connection_StateChange;
      dc.Log = new DebugWriter();
    }
#endif
    return dc;
  }
}

, , SQL-. DebugWriter, , (: ). , - . , DataContext:

var DB = DataContext.New;

( , SQL-, - , )

Answer 3

SQL Express, Profiler, , . SQL Express. , .

, Dynamic Management Views, , : 10 ,

SELECT TOP 10 t.text, q.*, p.query_plan
FROM sys.dm_exec_query_stats q
    CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t
    CROSS APPLY sys.dm_exec_query_plan (q.plan_handle) AS p
ORDER BY q.total_worker_time DESC