Sitecore performance: CPU usage of database

Performance and Sitecore is a huge and wide topic. Check Sitecore Performance guideline for more and very detailed information.
I just want to list a few out of my head:
  • check and improve SQL server performance / caching
  • check you architecture
  • may increase number of CDs (Content Delivery Servers)
  • Throw more CPU and RAM on it (only if fully frustrated, better search for the bottleneck)
  • Run Analyzer e.g. google sitespeed, yslow, ….
  • Reduce http request, pack and minify JS and CSS, optimize your frontend
  • Increase Sitecore cache parameters in site config
-       

Today I want to share 2 SQL statements how find your bottlenecks on SQL side:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO



This is an interesting query showing CPU usage by database

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
  SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName, [CPU_Time_Ms],
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms])
       OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

Need more of this. Check here:

Comments