Geek Answers Handbook

Understanding SQL Profiler Tracing

I am currently having some problems on my DotNetNuke SQL Server 2005 Express site on Win2k8. It runs smoothly for most of the time. However, sometimes (on the order of one or two hours per hour) it works very slowly - from the point of view of the user, in the same way as there, a dead end of some description arises.

To try to find out what the problem is, I ran SQL Profiler in the SQL Express database.

If you look at the results, some specific questions I have are:

  • The SQL trace shows the audit logon and verifications for each RPC: completed - does this mean that the connection pool is not working?

  • When I look in the Performance Monitor in the ".NET CLR Data", none of the "SQL client" counters have any instances - is it just a problem of the lack of SQL Express functionality or does it offer me something incorrectly configured?

  • Queries performed when slowness appears until they seem unusual - they work fast at other times. What other perfmon counters or other trace / log files can you offer as useful tools for my further study.

+4
source share
4 answers

Jumping straight to Profiler is probably the wrong first step. First try checking the Perfmon statistics on the server. I have a tutorial online here:

http://www.brentozar.com/perfmon

Start taking these figures, and then, after he has experienced one of these slowdowns, stop collecting. Look at the performance indicators at this time, and a bottleneck will appear. If you want to send me csv output from Perfmon to the address brento@brentozar.com , I can give you some idea of ​​what is going on.

You may still need to run Profiler, but I would have ruled out the OS and hardware first. Also, just a thought - did you check the system and server application event logs to make sure that nothing is happening at this time? I have seen cases where, say, an anti-virus client downloads new patches too often and performs an easy scan after each update.

+2
source

My common sense tells me that you might have problems locking SQL Server. Read this article to help you monitor the lock on your server to see if this is the cause.

If you think that the problems may be related to performance and you want to know what the bottleneck in your hardware is, then you should collect statistics of the processor, disk and memory using perfmon, and then link them to your profiler trace to find out whether the slow response is related.

+1
source
  • no
  • nothing wrong with that ... it shows that you are not using the built-in .NET function in SQL Server.
  • You can check out http://www.xsqlsoftware.com/Product/xSQL_Profiler.aspx for a more detailed analysis of the profiler trace. It has reports showing the top queries by time or processor (not one query, but the sum of the total execution of one query).
0
source

Some other things to check:

  • Make sure data files or log files are not automatically distributed.

  • Make sure your antivirus is set to ignore your sql data and log files.

  • When viewing the profiler’s output, be sure to check requests that were completed immediately before your goals, they could block.

  • Make sure you turn off AutoClose in the database; reopening after closing takes some time.

0
source

More articles:


All Articles