Re: Dead Zune Club

On 12/10/2011 3:52 PM, Shawn Wildermuth wrote:
> SQL Profiler is where I'd start.

Yeah, that's kinda what I figured too. Thanks for confirming.

The trouble with the profiler is that I'm going to get back "the 
phonebook" when I run it. I did actually run the profiler for an hour or 
so a few months back just to see what I could see and there was *heaps* 
of database activity and I'm not exactly sure what I should be looking 
for. (Note: I don't believe that what I call "heaps" of database 
activity translated into high CPU/Memory utilisation, I think the load 
in those terms was low (but I wasn't being very scientific in my 
observations). It's just "heaps" in the sense that it takes a really 
long time to wade through it all.)

One thing I'd really like to be able to detect is when clients are not 
using a connection pool. One thing I've found in some of the source code 
is that the previous developers somehow got it in mind that this was a 
good idea:

       sqlConn.Close();
       sqlConn.Dispose();
       SqlConnection.ClearPool( sqlConn );

I really don't know why they are blowing away the connection in the 
connection pool when they close their connections, but they seem to do 
this just about everywhere. I'm hoping that establishing all these "new" 
connections is what's causing SQL Server to respond slowly and timeout, 
because fixing that is something that's tractable. I'll just need to 
review all the code I can get my hands on and fix it so that it uses the 
connection pool.

Is there an easy way to determine how many connections are being 
established with the SQL Server? I'm not even sure what I would be 
looking for in the profiler logs to discover this manually.