How to trace the exact query fired in sql server I am facing a problem with performance tuning. We are using an application called ARAS (one of the leading PLM software) which is using Sql server as the back end. 

Every time we find some data using Aras interface it interacts with SQL server and give us Response.

But I am unable to find which query is being sent to SQL Server. I tried to find it using this query 

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC.


This was also not helping me properly. 

We are using SQL Server 2008 Express and ARAS 9.3
Though SQL Profiler is not available in Express Edition, still you can leverage trace functionality with any of the below mentioned approaches :


ExpressProfiler available at CodePlex
Or You can install developer Edition and then use profiler to trace the sql statements.
Use Trace Flag 4032 that will log all the sql from all clients to the errorlog.


Note : This will log all the sql from all the clients to the error log, so better turn it off once you are done with tracing. The downside of this trace flag is that it has to be applied at the startup using ;-T4032 and sql server has to be restarted.

    DBCC TRACEON(4032,3605,-1)


4. You can initiate server side trace  as shown below :

/***********************************************/ 
/* Server Side Trace                           */ 
/***********************************************/ 
-- Declare variables 
DECLARE @rc INT 
DECLARE @TraceID INT 
DECLARE @maxFileSize bigint 
DECLARE @fileName NVARCHAR(128) 
DECLARE @on bit 

-- Set values 
SET @maxFileSize = 5 
SET @fileName = N'C:\SQLTrace' 
SET @on = 1 

-- Create trace 
EXEC @rc = sp_trace_create @TraceID output, 0, @fileName, @maxFileSize, NULL  

-- If error end process 
IF (@rc != 0) GOTO error 

-- Set the events and data to collect 
EXEC sp_trace_setevent @TraceID, 41,  1, @on 
EXEC sp_trace_setevent @TraceID, 41, 12, @on 
EXEC sp_trace_setevent @TraceID, 41, 13, @on 
EXEC sp_trace_setevent @TraceID, 41, 14, @on 
EXEC sp_trace_setevent @TraceID, 41, 15, @on 
EXEC sp_trace_setevent @TraceID, 41, 16, @on 
EXEC sp_trace_setevent @TraceID, 41, 17, @on 

-- Set Filters 
-- filter1 include databaseId = 6 .. **Change your database ID *** HERE !!
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6 
-- filter2 exclude application SQL Profiler 
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' 

-- Start the trace 
EXEC sp_trace_setstatus @TraceID, 1 

-- display trace id for future references  
SELECT TraceID=@TraceID  
GOTO finish  

-- error trap 
error:  
SELECT ErrorCode=@rc  

-- exit 
finish:  
GO


-- to find trace ID

SELECT * FROM :: fn_trace_getinfo(default)


--- to stop the trace 

sp_trace_setstatus traceid, 0 --- here 0 is the trace ID which we got from above query


--Load the trace file into a new table in sql server

SELECT * INTO sqlTableToLoad 
FROM ::fn_trace_gettable('C:\SQLTrace\trace.trc', DEFAULT)