Sitecore Analytics kills MS SQL Database Server

Intro:

Very recently our DB Server broke down. Checking the logs we found this:

Exception: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Source: Sitecore.Client
   at Sitecore.Shell.Applications.Analytics.TrackingField.TrackingFieldDetailsPage.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Control.LoadRecursive()

   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


We were sure that we had disabled Sitecore Analytics because we do not have an extra Database Server for the Analytics Database. So the next check was to see what causes that heavy traffic.
We run this query:


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);

The result:




Solution:

Analytics was enabled. 
Set this setting in theWebsite\App_Config\Include\Sitecore.Analytics.config
<setting name="Analytics.Enabled" value="true" />
<setting name="Analytics.DisableDatabase" value="false" />

or put the analytics DB on a extra Server.

Comments