How to determine which sql functions and procedures cause high CPU utilization on the host server

Question

I have a C # / asp.net web application (not mvc) that has several pages that access the sql database many times using stored procedures and views. The application now has 5-10 users, and the hosts tell me that it causes 95% + CPU usage on the server.

My question is, how can I determine which functions / procedures / threads lead to high CPU utilization, so I can cache or optimize them? Please note that hosts do not give me access to ANY log table, statistics or server database of the server, only to my application database, which causes a serious headache!

Answer 1

you can use SqlProfiler to track the performance and behavior of any procedure, function, etc.

You can check this: SqlProfiler as it is a very useful tool and it really helps me in improving the performance of sql stored procedures.

, sql , " " , , , - , .

.

#, , , , :

var watch = Stopwatch.StartNew();
// the code that you want to measure comes here
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;

Visual Studio 2015

Answer 2

.NET SQL . SQL.

​​ Pinal Dave query, sys.dm_exec_query_stats. , , . .

SELECT TOP 50
  st.text AS [SQL Definition]
, SUBSTRING(
    st.text,
    qs.statement_start_offset / 2 + 1,
    ( CASE
        WHEN qs.statement_end_offset = -1 
        THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 
        ELSE qs.statement_end_offset
      END - qs.statement_start_offset ) / 2
  ) AS [Hot Path]
, qs.execution_count AS [Execution Count]
, qs.total_worker_time / 1000000 AS [Total CPU (s)]
, (qs.total_worker_time / $1000000) / qs.execution_count AS [Average CPU (s)]
, qs.total_elapsed_time / 1000000 AS [Total Time (s)]
, qs.total_logical_reads / qs.execution_count AS [Average Logical Reads]
, qs.total_logical_writes / qs.execution_count AS [Average Logical Writes]
, qs.total_physical_reads / qs.execution_count AS [Average Physical Reads]
, ISNULL(qp.query_plan, '') AS [Query Plan]
  FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
 CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
 ORDER BY [Total CPU (s)] DESC

, , , , .

Answer 3

.

, , , .

Answer 4

:

( , , ). , ... , , , ( , ).

Answer 5

Sql Profiler, , . , , / SQL-, .

Sql , / CPU, .

. , .

, , , . , Customer .

, "like" nvarchar. xml, , . , Sql, , "id" XML, Sql.