January 30, 2020 at 8:33 am
Just to clarify - I didn't mean to point to anyone's blog in particular - KendraLittle and ScaryDBA are a couple of the best SQL server blogs out there. They do promote using XEs, followed their articles but I've always seen both come from a point of view of how to help others convert to XEs and not ones I ever meant "shaming" people about using Profiler. That's more related to some of the curt replies by others on forums and in general when someone mentions using Profiler.
My point was more that it should be more about the feature itself limiting the adoption rather than about the people not adopting it. I think it ended up differently in regards to XE. I still reach for profiler first and not because the articles I've read were poor or not encouraging. Very much the opposite. It's related to my experience when using the different tools.
the same thing happened with WMI, Silverlight, f# and a whole bunch of other technologies
MVDBA
February 4, 2020 at 3:40 pm
Hi Jeffrey,
i noticed the same with SSMS 18.4 and MSSQL 2014.
But the funny thing is, if you execute the query "WITH profiled_sessions as (SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles . . ." manualy in SSMS with "Include Life Query statistics" ON, than the query is executed in 0 seconds and not in 40 seconds...
You have any idea why the option "Include Life Query statistics" has this positive influence to the query?
Kind regards
February 4, 2020 at 5:42 pm
does the query COMPLETE in 0 seconds OR does it show you the execution plan in 0 seconds?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 4, 2020 at 8:32 pm
Hi Jeffrey,
i noticed the same with SSMS 18.4 and MSSQL 2014.
But the funny thing is, if you execute the query "WITH profiled_sessions as (SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles . . ." manualy in SSMS with "Include Life Query statistics" ON, than the query is executed in 0 seconds and not in 40 seconds...
You have any idea why the option "Include Life Query statistics" has this positive influence to the query?
Kind regards
Not sure which query you are running...the query I provided or the original query with ROW_NUMBER()?
I believe you meant 'Include Live Query Statistics'...which could have an impact on how the query is executed since SSMS is displaying each operator in the plan while the query is executing. However, I would not expect that to somehow cause a 40 second query to now complete successfully in less than a second.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 5, 2020 at 7:04 am
@ Jeffrey:
it's the origninal query that i catched with the profiler:
WITH profiled_sessions as (
SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT
[Session ID] = s.session_id,
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[Login] = s.login_name,
[Database] = case when p.dbid=0 then N'' else ISNULL(db_name(p.dbid),N'') end,
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * (8192 / 1024),
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0),
[Workload Group] = ISNULL(g.name, N''),
[Profiled Session Id] = profiled_session_id
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN profiled_sessions ON profiled_session_id = s.session_id
ORDER BY s.session_id;
@ Brian:
The query completes in 0 seconds, i see instandly the row results...
Please try it out, so i know if only in my envoirement its so, or if others sees the same behaviour...
Thanks
February 5, 2020 at 9:33 am
sorry @ all
"Live Query Statistics" has no correlation to the execution time.
The query sometimes is fast and sometimes it needs above 40 seconds to execute.
It was yesterday for chance, that everytime i used "Live Query Statistics" it was fast. Today sometimes without "Live Query statistics" it was fast and sometimes with "Live Query statistics" it was slow...
And till now i haven't seen an other correlation, so i am searching again what causes that sometimes expanding "Processes" my activity monitor goes in paused state...
sorry again...
February 5, 2020 at 9:59 am
this sounds like a buffer cache issue - sometimes fast, sometimes slow, depends on if it is in cache...if not then it could be a recompile?
How much RAM on that box? and what is your max memory setting?
MVDBA
February 5, 2020 at 2:23 pm
@ MVDBA
it has 20 GB for the OS, 18 GB for the SQL - Server.
I don't think thats a buffer cache issue, because if i execute it after the first execution, it takes the same time.
It seems to have a correlation to SOS_SCHEDULER_YIELD, everytime i execute the query this Wait appears...
if i run sp_WhoIsActive for example after 26 seconds i see:
Wait_info : NULL
CPU: 25,117
used_memory: 4
status: runnable
It seems to me that its there for a long time in the runnable state...
February 5, 2020 at 2:47 pm
just wanting to check - 20GB purely for OS and 18 purely for SQL - so a total of 38GB ?
MVDBA
February 5, 2020 at 3:07 pm
update:
the problem is not only with SQL 2014 but with SQL 2017 too
February 5, 2020 at 3:16 pm
wait, Wellen_reiter, is this a VMWARE server?
SOS_SCHEDULER_YIELD is typical of CPU pressure - particularly where a VM instance is sharing CPU with other instances (VMware tries to be clever about the way CPU is distributed)
MVDBA
February 5, 2020 at 3:16 pm
@ mike:
the exact RAM values:
the server has 20 GB RAM
SQL has maximum memory set to 17000 MB
so the OS has purely ca. 3 GB of RAM
But an other server with SQL 2017 has the same problem...
the other server has 12 GB RAM
SQL has maximum memory set to 4915 MB (don't ask me, why so few...)
so the OS has ca. 7 GB purely RAM
February 5, 2020 at 3:34 pm
yes, its a Vmware - server
but than its the first time, that i see a negative effect with Vmware...
February 5, 2020 at 3:37 pm
and in the taskmanager of the VM i don't see CPU pressure
the CPU is ca. on 10 % on both VMs...
February 5, 2020 at 3:45 pm
next update:
if i watch the CPU in taskmanager, after i openend processes in activity monitor, the vCPUs goes from 10 % to 30%. So it seems that the query don't waits but really calculates something in this time...
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply