What is the lowest impact method for profiling SQL Server for a specific client?

Question

My local friendly DBA does not like the idea of allowing profiling on the SQL servers (even non-production ones) due to the impact on performance they have.

Ironically, I'm trying to reduce the need for a whole bunch of SQL queries, and would like to be able to check in a fairly non-invasive manner what queries are being sent over the wire, without impacting on the server performance. To this end is there a tool similar in approach to how useful Fiddler is for debugging HTTP connections, but allows client side profiling of SQL Server (at least for .net applications)? Or is it the case that running a profile for a single client will not impact on server performance?

Answer 1

Microsoft's Network Monitor is a tool I've considered for such a purpose in the past - got as far as installing it and having a play but then had to do some real work so gave it up. It certainly looked promising - and has parsers for SQL-related protocols including TDS.

Answer 2

The latest version of ANTS Performance Profiler includes SQL and I/O profiling, so you can get those details client side, without putting any load on the server.

Answer 3

You've tagged this ADO.NET. ADO.NET has inbuilt tracing facilities have you investigated those?

Are you on SQL Server 2008? If so how does your DBA feel about extended events? These can be configured to drop surplus events rather than cause server degradation as SQL Trace/ SQL Profiler does.

A light weight way of getting some idea of the queries that are being run and how often is

select * from sys.dm_exec_cached_plans
outer apply sys.dm_exec_sql_text(plan_handle)
order by usecounts desc

but that wouldn't meet the specific client criteria though and is subject to the vagaries of plan caching.

Answer 4

The best you can do in this situation is to use the Default Trace in SQL Server, assuming it's enabled, but you may find that to be of limited utility.

Ok, I take that back. The best you can do is to talk some sense into your DBA and get him or her to at least allow traces on development servers. You're in a ridiculous situation right now.

You could try to use a tool like Wireshark and watch the network traffic, then pull out queries of interest and run them in SSMS individually with SET STATISTICS IO set to ON and glean some information from that. Hopefully he gives you enough access to get the query plans too. But that's a horrible solution. In fact, you should downvote me for even suggesting it.

This is like trying to swim with both hands tied behind your back. I will drink a cold one for you tonight.

Answer 5

My local friendly DBA does not like the idea of allowing profiling on the SQL servers (even non-production ones) due to the impact on performance they have.

Well he or she may have a point and may well have burned by this in the past.

However, you have a need, so throw it over to the dba to suggest a method to fulfill your need if you can't use profiler. I'm a firm beliver in the "you tell me no, then I expect you to provide me with alternative way to do my job" philosophy. When it becomes painful to say no, people are more inclined to say yes.

Profiler can be configured to profile only very limited events which has less impact on performance. The dba could even be the one you suggest set up the profiler conditions and run it saving the results to a table for you while you run your processes that you want to profile. That way, he has control over what is profiled and makes sure it is stopped when not needed any more and makes sure that it will have as little impact as possible and you get what you want. It is after all the dba's job to do those sorts of things. Perhaps she needs a reminder.