to trace some requests on SQL server

a customer wants our to check general SQL traffic(query,etc) which comes from clients and other servers to the sql server so that means that is a kind of tracing summary for one day so which solution is the best and how should we do that? sql server is running on sql server 2008R2
Teoman SahinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What really the customer want to be traced/captured? If you run a trace to catch everything it will have a very negative impact in the performance of the SQL Server.
Teoman SahinAuthor Commented:
the thing he said is to check the SQL traffic coming to sql server from clients or other may be queries or other important look at SQL traffic and check, what important things  should we look at? yes you are right if we look at everytying,it causes negative impactive on sql performance from your side, which important things?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would try to get from him a more specific answer. Otherwise you can use the Performance Monitor from Windows Server and take a report on the SQL Server counters (Logins/sec, Transactions, user Connections, ...).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Teoman SahinAuthor Commented:
especially the thing he said is to check queries comes to sql server..he does not have any idea and in some way, he says there are important parameters to check the traffic,like queries and few important paramters from sql profiler..i think it is enough to find a few important and to check you know which ones are important and enough to observer the traffic
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would never use SQL Profiler to check the traffic. For that you have counters in Performance Monitor on another 3rd party tool that can capture network traffic.
If he's worried with query performance then configure SQL Profiler to capture long running queries (you can start filtering for queries running more than 30 seconds and then eventually reduce the threshold to 10 or 5 seconds).
Deepak ChauhanSQL Server DBACommented:
You are saying trace for one day. profiler is not a good idea.

I would like to suggest you schedule a agent job which will run at 1 minunte or 5 minute interval. this job will capture the data from dmv and insert it into trace table.

1. In section one create a table in your audit database or test database.
2. create a job and paste the code of Section 2
3. schedule this job for 1min or 5 min interval this is up to you.

--Section 1
use <DatabaseName> -- where you want to store trace data

CREATE TABLE [dbo].[Server_monitor](
	[server] [nvarchar](128) NULL,
	[spid] [smallint] NOT NULL,
	[hostname] [nchar](128) NOT NULL,
	[client_net_address] [varchar](48) NULL,
	[loginame] [nchar](128) NOT NULL,
	[login_time] [datetime] NOT NULL,
	[last_batch] [datetime] NOT NULL,
	[DatabaseName] [nvarchar](128) NULL,
	[Definition] [varchar](max) NULL,
	[program_name] [nchar](128) NOT NULL


USE <DatabaseName> ---where [dbo].[Server_monitor] exist to store trace result

INSERT INTO Server_monitor

SELECT @@servername as server, s.spid,
Definition=CAST(text AS VARCHAR(MAX)),
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
JOIN sys.dm_exec_connections ec ON s.spid=ec.session_id
WHERE s.spid > 50 AND s.dbid >4

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Teoman SahinAuthor Commented:
thanks .deepakChauhan..firstly I tried to trace the sql server with Sql Profiles  by some events like

but unfortunately within only two hours, it took thousands of trace file with 5 mb and it occupied 40GB space of disk drive in dispite of filter applied to queries that takes 5 second and more you said.this is not good idea so as far as I see, your code don't measure the needed events to see the sqlTraffic, like in the SQL Profiles events so is that possible to measure the needed informations that have to be in this code to see the SQLTRaffic?
Vitor MontalvãoMSSQL Senior EngineerCommented:
As I said
If he's worried with query performance then configure SQL Profiler to capture long running queries (you can start filtering for queries running more than 30 seconds and then eventually reduce the threshold to 10 or 5 seconds).
Did you try to capture only long running queries? It should reduce drastically the number of captured processes.
Teoman SahinAuthor Commented:
I traced queries taking more than 60 seconds to work but trace file occurred 1600 files of 5 MB each one so I think it is very difficult to investigate these files so this is scom server so I don't have any idea where I start from
Deepak ChauhanSQL Server DBACommented:
gone through the 1600 file is very defficult, you can store the log into table instead of files  and it would be easy to anaylsis because you can query a table.

And the above code was for tracking the connection which are coming from client system, "Definition" column in the table can show you the query,  but this is not for tracking the long running query.
Vitor MontalvãoMSSQL Senior EngineerCommented:
1600 files? For how long you ran the Profiler?
Should be something for few minutes to capture some queries. You can't work on all at the same time.
You can even increase the filter so instead of 60 seconds you can try to catch queries that run for more than 300 seconds (5 minutes).
Teoman SahinAuthor Commented:
customer requested the sql traffic comes from clients and SCCM servers to this server for one working day, from 07:00 AM to 16:00PM so I don't have any idea the best way  to trace sql traffic is SQL Profiles? I think there is no basic solution for this

as you said, we can increase the filter from 60 seconds to 300 seconds and store them in a table
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Profiler as impact in the SQL Server performance and that's why it shouldn't run for long.
Extended Events are the feature that replaces SQL Profiler but doesn't have a GUI interface.

The idea is starting to solve the queries that takes longer first and then the other ones.
Deepak ChauhanSQL Server DBACommented:
What is your requirement actually... you need to just provide summary details of sql traffic comming from client system within a day. Or do you want to capture the long running queries and optimize them? The above code can capture the query and client system info like IP or system name with login id.

Profiler can capture everything but performance impact and require storage if you want to run profiler full day. However you can capture data in table for further query.

As suggested by Vitor you can take advantage of Extended events.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.