Understanding SQL Profiler trace

Question

I'm currently experiencing some problems on my DotNetNuke SQL Server 2005 Express site on Win2k8 Server. It runs smoothly for most of the time. However, occasionally (order once or twice an hour) it runs very slowly indeed - from a user perspective it's almost like there's a deadlock of some description when this occurs.

To try to work out what the problem is I've run SQL Profiler against the SQL Express database.

Looking at the results, some specific questions I have are:

1. The SQL trace shows an Audit Logon and Audit Logoff for every RPC:Completed - does this mean Connection Pooling isn't working?

2. When I look in Performance Monitor at ".NET CLR Data", then none of the "SQL client" counters have any instances - is this just a SQL Express lack-of-functionality problem or does it suggest I have something misconfigured?

3. The queries running when the slowness occur don't yet seem unusual - they run fast at other times. What other perfmon counters or other trace/log files can you suggest as useful tools for my further investigation.

Thanks

Stuart

Answer 1

1. no
2. nothing wrong with that...it shows that you're not using the .NET functionality embed in SQL Server.
3. You can check http://www.xsqlsoftware.com/Product/xSQL_Profiler.aspx for more detailed analysis of profiler trace. It has reports that show top queries by time or CPU (Not one single query, but sum of all execution of a single query).

Answer 2

My spidey sense tells me that you may have SQL Server blocking issues. Read this article to help you monitor blocking on your server to check if its the cause.

If you think the issues may be performance related and want to see what your hardware bottleneck is, then you should gather some cpu, disk and memory stats using perfmon and then co-relate them with your profiler trace to see if the slow response is related.

Answer 3

Jumping straight to Profiler is probably the wrong first step. First, try checking the Perfmon stats on the server. I've got a tutorial online here:

http://www.brentozar.com/perfmon

Start capturing those metrics, and then after it's experienced one of those slowdowns, stop the collection. Look at the performance metrics around that time, and the bottleneck will show up. If you want to send me the csv output from Perfmon at [email protected] I can give you some insight as to what's going on.

You might still need to run Profiler afterwards, but I'd rule out the OS and hardware first. Also, just a thought - have you checked the server's System and Application event logs to make sure nothing's happening during those times? I've seen instances where, say, the antivirus client downloads new patches too often, and does a light scan after each update.

Answer 4

Some other things to check:

• Make sure your datafiles or log files are not auto-extending.

• Make sure your anti-virus is set to ignore your sql data and log files.

• When looking at the profiler output, be sure the check the queries that finished just prior to your targets, they could've been blocking.

• Make sure you've turned off Auto-close on the database; re-opening after closing takes some
  time.