Problems with SQL Profiler 2005????

[Catadmin]

All,

Has anyone run into any problems with Profiler for Server 2k5 killing their applications?

We have a clustered Production environment (active/passive). The database is connected to via an MS Access ADP file which in turn the end users open via load balanced Citrix Servers (there are 3 or 4 in the farm). Things got a little slow in the past week so I went to run Profiler and PerfMon on the SQL Server.

PerfMon caught some spikes in the Disk Queue Length & Pages per Seconds, but it looked like normal intermintant activity, nothing persistant that could be causing huge problems. Profiler (I filtered it down to a specific database and looked for duration of 30 seconds or longer) wasn't finding any queries which met my criteria.

However, every time I started Profiler --whether or not PerfMon was running, whether I was logging to a trace table, trace file or not logging at all -- All the people logged on through the ADP had problems. The application froze up, screens when blank, queries and reports timed out.

We verified with our system admins that it wasn't a network issue, everything seemed to still work fine through the backend (though we weren't running complex queries), but nothing through the front end worked until Profiler was stopped.

Does anyone know of any issues that might have caused this scenario?

Thanks in advance.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

Never sean this. Especially since in SQL 2005 there is a trace always running. Check the trace procs, you'll see trace id 1 started as soon as SQL started.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

I posted this on MS's forum as well and inadvertantly started a fight between two of the respondants. @=)

One person actually told me that Profiler wasn't supposed to be used against a Production server, which seems to be a load of BS since that's one of the things it's there for.

Anyway, we figured out a few problems I wasn't aware of. Traces that worked on SQL 2000 may not work on SQL 2005 because:

1) Duration has been changed from milliseconds to microseconds. It displays as milliseconds, but when you're filtering by it, you have to add 6 zeros instead of 5 to get the proper # of seconds you want to see.

2) So many new columns have been added to the 2005 Profiler that if you don't de-Select most of them, they will kill your performance, even if only 4 people are logged in.

3) Some people are recommending creating Server-side traces with Profiler and run those as they have less overhead than Profiler. Apparently there's a way to take your Profiler Trace definition and script it out so you can place it directly on the server. I haven't figured out how to do this option, or how to locate it in BOL yet, but I'm looking.

Anyway, these are a few of the things I've been told that could be causing problems.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"