Re: Dead Zune Club

On Wed, Oct 12, 2011 at 1:03 AM, John Elliot <[email protected]> wrote:

> 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.)
>
>
It is a phone book and a half, unless you are looking for something in
particular. Most times, the net's much too large, and you have far too much
crap to wade through to find anything helpful.

It might be worth looking at the Activity Monitor in SSMS, which is weirdly
hidden for no good reason. It'll give you an instantaneous view into what's
happening, and it's rather better than Process Monitor. The "Recent
Expensive Queries" section might be quite helpful.


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.
>
>
That's been moved into activity monitor, although you could use Profiler to
keep track of that too, with a bit of effort.

Herb

-- 
"Inquisition this, science dinks" - The Tick