1

I want to run sql profiler to see the performance of my database Sql Server 2008, but I'm afraid that running the profiler in the same machine it will affect the performance of the server, and I don't want to slow down ther server.

A long time ago I heard from a DBA than he run the profiler from his laptop connected to the sql server, in a way that it does not affect the performance of the server.

Si bassically my question is How to run Sql profiler from an external computer without causing slow performance of the sql server?

Artemination
  • 703
  • 2
  • 10
  • 30
  • 1
    I believe it is nothing to do with from where you start profiler but the actual tracking happens from server. – techspider Sep 06 '16 at 18:20
  • 5
    `I heard from a DBA than he run the profiler from his laptop connected to the sql server, in a way that it does not affect the performance of the server.` They weren't a particularly knowledgeable DBA, were they? – TZHX Sep 06 '16 at 18:20

5 Answers5

5

Any database being profiled has to do work in order for profiling to be possible - there is no way around it. Generally speaking, observation of a system always induces a load to that system. However, SQL Server Profiler and other similar tools also do ADDITIONAL work outside of the target db, and this additional work can be offloaded to another computer.

To offload what you can, you just run SQL Server Profile from ANY machine that is not the database server. When you start a New Trace, you tell it to connect to the database on whatever server the database is running on. That's all there is to it. Your target db will incur some additional load (unavoidable), but you will be offloading as much work as you can to whatever machine you run Profiler on.

SlimsGhost
  • 2,849
  • 1
  • 10
  • 16
3

If you are able to connect to the computer from external computer,then there would be no issues running profiler remotely as well..

So basically my question is How to run Sql profiler from an external computer without causing slow performance of the sql server?

When you run profiler for long periods of time ,it affects performance,since it has to keep track of all events in memory and log it before discarding ..So running profiler for long periods of time is not recommended..

You also can use extended events starting from SQL2008(very light weight relative to profiler) to track events similar to Profiler ..

http://www.sqlteam.com/article/introduction-to-sql-server-2008-extended-events

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
2

I am a DBA and I am not aware of any performance issues by running SQL Server Profiler on the server itself. That said when you run SQL Server Profiler It loads just like SSMS where you can select which server to use.

If you have a query that is running so long that its killing SQL resources yes running it at all will still use up resources but regardless of where the source of the profiler is.

See screenshot of SS Profiler enter image description here

If you are concerned about performance on the SQL Server instance don't run Profiler in production during peak hours.

logixologist
  • 3,694
  • 4
  • 28
  • 46
  • 1
    I think the image is blocked from my work. Let me know if you cant see it I can always upload a screenshot tonight. – logixologist Sep 06 '16 at 18:29
  • 1
    Profiler can be a resource hog and I've seen it take down several produciton instances becasue it wasn't limited. – Steve Mangiameli Sep 06 '16 at 18:30
  • 1
    I've never been in a shop that willingly ran profiler in a production instance for this exact reason. – Steve Mangiameli Sep 06 '16 at 18:46
  • Its very possible if there is a limited license issue. For example a company only has 1 SQL Server license so they cant use SSMS on a external instance. Older legacy versions of SQL Server Express used to not offer SSMS. As a programmer one time I had to do it that way. Not proud of it but that's all we could go with at the time. – logixologist Sep 06 '16 at 18:55
2

Profiler can be initiated from any computer with appropriate permissions and access, but it ALWAYS runs on the actual SQL Server instance. There is no way around this. You can minimize the operations that are logged and filter by a specific user to mitigate performance issues, but that's about it.

The DBA in question may have run a server side trace, which can be less impactful, but it's still inititated ON the appicable instance.

Steve Mangiameli
  • 688
  • 7
  • 15
1

If you want to minimize the impact of SQL Trace then it is the best to use the server-side tracing: https://msdn.microsoft.com/en-us/library/cc293613.aspx

Like that you can record the SQL commands into a trace file and SQL Profiler is closed. When you are done with the SQL command collection, you can copy the trace file and open it using SQL Profiler in some other computer. It is much better than runing SQL Trace directly through SQL Profiler (which is called the client-side tracing).

Radim Bača
  • 10,646
  • 1
  • 19
  • 33