May 19, 2017 at 3:30 am
Hi ,
Instead of running SQL proflier , is there a T-SQL script that can be run manually to get the exact same results that are produced from the proflier?
I've come up with the following and but yet my script doesnt seem to match results in SQL profiler
SELECT TOP 1000
SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1
) AS SQLText
, qs.execution_count
, qs.total_logical_reads
, qs.last_logical_reads
, qs.min_logical_reads
, qs.max_logical_reads
, qs.total_elapsed_time
, qs.last_elapsed_time
, qs.min_elapsed_time
, qs.max_elapsed_time
, qs.last_execution_time
, qp.query_plan
FROM
sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
qt.encrypted=0
ORDER BY
qs.last_logical_reads DESC
May 19, 2017 at 6:35 am
Trace (Profiler is just the GUI for consuming Trace events) is going to capture all executions of queries (depending on how you set it up of course) while querying sys.dm_exec_query_stats is only capturing what is currently in cache. If something ages out of cache, it won't be there when you query it. If a query has a recompile hint, it won't even go to cache, so it won't be there when you query it. If you need 100% of every query run on the system, you need to use Extended Events (Trace is so old school, it works, but it puts more overhead on the system, does very bad filtering, doesn't cover all the new functionality in 2014/2016/vNext, time to give up on Trace).
Now, SQL Server 2016 has a new functionality called Query Store. That will capture all the queries. It won't show individual calls such as Extended Events (Trace) will. It stores them in an aggregated fashion. However, it will capture every query, whether it goes in the cache or not (depending on how you set up Query Store, there is a way to avoid capturing all queries if you choose to).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 19, 2017 at 7:05 am
Everything any GUI that interacts with SQL Server just sends in TSQL. Using Profiler to capture what tools do is a GREAT way to learn how things work both with the tool and within SQL Server. SSMS will show you some REALLY bad coding by Microsoft for example. 🙂
I have attached a script I use all the time, and have clients run to throw over the wall to me for analytics when they are having a problem they can't solve. It is VERY tight and efficient, and I have run it to local disk at a number of clients with thousands of transactions per second with very minimal overhead.
Note that despite what Grant says (which is all true and valid) I can PROMISE you that I will still be using Profiler and Tracing FIFTEEN years from now. I know this because I still have clients with SQL 2000, and that was 17 years ago. Profiler still exists in SQL 2016, thus at least 15 years from now I will have clients still on that version.
As a number of people I know say: "they will have to pry Profiler from my cold, dead hands". :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply