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
Post a Comment
a new comment