May 20, 2019 at 4:07 pm
Hi all,
I can see that identical request is executing much longer on stage comparing to prod.
How do I check if it is CPU, memory or hard disk bound?
Please advise.
May 21, 2019 at 3:40 am
The single best method is to use Extended Events to capture the call. You can get a general set of data through rpc_completed or sql_batch_completed, or you can get individual statements with them using the appropriate events. Add in sqlos_waitinfo and capture the causality between events and you can easily identify everything going on. This will tell you precisely what's up with each.
You can also capture a general set of behaviors using Query Store, including the wait statistics. You can also capture an actual execution plan on each. In SQL Server 2017, you get query runtime and wait stats stored in execution plans (actual ones, which are just estimated plans plus runtime metrics). If you want to go old school, capture the wait statistics from the system view before and after you run the query and compare the two. This really a stone knives & bear skins approach, but it'll work.
The key, is simply identifying what's different. You could probably just look at the system configurations to understand that. Worth noting, if there are differences in how the systems are configured, such as cost threshold for parallelism, max degree of parallelism or even the default ANSI connections, this can lead to differences in execution plans which can lead to differences in performance. So, I'd compare the execution plans too.
"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 21, 2019 at 12:42 pm
Hi Grant Fritchey,
How do I start? I'm new to SQL Server. Please, be so kind, give me hints to begin, like path in SSMS menu. I'm using SQL Server 2017.
Thanks!
May 21, 2019 at 3:12 pm
Honestly, the best resource is the query performance tuning book in my signature. It's completely focused on what you need.
However, to get started with extended events, start with Microsoft. You can go here. I've got a video on how to use the Live Data Window. I have a bunch of entries on my blog covering various aspects of Extended Events. The best one to get started with on your problem is probably this one.
Seriously though, get a copy of the book. That has everything you can possibly need on this topic in a single resource.
"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 21, 2019 at 3:14 pm
Oh, and here's another video on waits and queries.
"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 21, 2019 at 9:05 pm
Grant's advice is great, and he really is a top expert on the subjects at hand, but for someone new to SQL Server going through all that is like drinking from a fire-hose.
While the query is running, you can use a query against the dynamic management views to see info about it:
/* running requests */
SELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status,
db_name(er.database_id) AS database_name, er.status AS request_status, er.command, er.percent_complete,
er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.last_wait_type, er.wait_resource,
s.memory_usage / 128.0 AS memory_meg, mg.used_memory_kb, mg.granted_memory_kb, mg.requested_memory_kb, mg.ideal_memory_kb,
er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
er.transaction_id, er.open_transaction_count,
er.cpu_time / 1000.0 AS cpu_sec, er.total_elapsed_time / 1000.0 AS total_elapsed_sec, er.start_time, er.reads, er.writes, er.logical_reads,
CASE er.transaction_isolation_level WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE 'Unspecified' END AS isol_level,
OBJECT_NAME(st.objectid, st.dbid) AS object_name, qp.query_plan, er.sql_handle, er.plan_handle, st.[text]
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID
looking at the following columns will help determine the bottleneck:
wait_type, wait_sec As Grant mentioned, waits in SQL Server can show you where bottlenecks are
cpu_sec, total_elapsed_sec time the query has been running, note cpu_sec may be greater than total if the query is CPU intensive and running in a parallel plan
reads, writes, logical_reads shows you the amount of I/O this query is causing. logical reads are against RAM, reads and writes are against disk
May 21, 2019 at 9:09 pm
If you want to analyze something that has already run, the quickest way to look at it is through the internally stored query stats:
SELECT TOP 25
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.total_worker_time / qs.execution_count / 1000000.0 AS avg_cpu_seconds,
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.total_logical_reads, qs.total_logical_writes,
qs.total_logical_reads / qs.execution_count AS average_logical_reads,
qs.total_logical_writes / qs.execution_count AS average_logical_writes,
qs.execution_count, qs.last_execution_time, qs.creation_time,
OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid) AS schema_name, OBJECT_NAME(qt.objectid, qt.dbid) AS object_name, o.modify_date,
qp.query_plan, qs.sql_handle, qs.plan_handle,
DB_NAME(qt.dbid) AS database_name,
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 statement_text
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
WHERE qt.dbid = DB_ID()
--AND o.name = 'stp_insSalesProductionTransaction'
--AND qs.last_execution_time >= '2019-03-15 09:50'
--AND qs.execution_count >= 2
--ORDER BY average_seconds DESC;
ORDER BY total_seconds DESC;
May 22, 2019 at 3:35 am
I could be wrong but it looks like all of that only covers SQL Server. If there's something else on the box that's putting the hammer to CPU or file I/O, you might want to start with a PerfMon session as a simple confirmation and then actually see what else is running using Task Manager Details.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2019 at 7:38 am
Thank you for all your replies.
Well, stage SQL Server is the only software that works on that machine (I installed it, so I know what is going on). Also it started to work better after installing windows update. Now I even can't see any difference between prod and stage (though RAM on prod is 3.7GB and one on stage is 2.00 GB). I can't understand it deeper for now though.
May 22, 2019 at 7:41 am
I could be wrong but it looks like all of that only covers SQL Server. If there's something else on the box that's putting the hammer to CPU or file I/O, you might want to start with a PerfMon session as a simple confirmation and then actually see what else is running using Task Manager Details.
+1
Also check the settings and size of the paging file on the server. Had experience once where this was set wrong and too small and caused IO and CPU to go through the roof.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply