Hello,
One of our webfarm sites was causing excessive CPU, running steady all day every day. We were able to determine that it's because of the Statsv2 logging. The load balancer isn't skipping stats, so it was building the statsv2 session table, and also querying that table over and over again.
My recommendation is that the load balancer skips stats logging since it's too verbose and doesn't really count for session traffic. That will keep the stats table size down and enable it to run quicker.
The two queries are:
(@p0 nvarchar(24),@p1 datetime)
SELECT TOP 1 SUM( [Statv2Session].[Statv2SessionPageviews] ) AS [Pageviews], COUNT( [Statv2Session].[Statv2SessionID] ) AS [Visits], MAX( [Statv2Session].[Statv2SessionLastvisitDate] ) AS [LastVisit] FROM [Statv2Session] WHERE ( [Statv2Session].[Statv2SessionVisitorID] = @p0 ) AND ( [Statv2Session].[Statv2SessionTimestamp] >= @p1 )
(@p0 nvarchar(24),@p1 datetime,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 int,@p10 int,@p11 nvarchar(4000),@p12 nvarchar(8),@p13 bigint,@p14 bit,@p15 datetime,@p16 nvarchar(15),@p17 nvarchar(4000))
INSERT INTO [Statv2Session] ( [Statv2SessionSessionID] ,[Statv2SessionVisitorID] ,[Statv2SessionTimestamp] ,[Statv2SessionTimestampEnd] ,[Statv2SessionAreaID] ,[Statv2SessionYear] ,[Statv2SessionMonth] ,[Statv2SessionWeek] ,[Statv2SessionDay] ,[Statv2SessionHour] ,[Statv2SessionMinute] ,[Statv2SessionFirstpage] ,[Statv2SessionLastpage] ,[Statv2SessionPageviews] ,[Statv2SessionPath] ,[Statv2SessionTimePrPage] ,[Statv2SessionEngagement] ,[Statv2SessionGwIP] ,[Statv2SessionGwIPDbl] ,[Statv2SessionCampaign] ,[Statv2SessionBot] ,[Statv2SessionIsLead] ,[Statv2SessionLeadStateChanged] ,[Statv2SessionUtmSource] ,[Statv2SessionUtmMedium] ,[Statv2SessionUtmTerm] ,[Statv2SessionUtmCampaign] ,[Statv2SessionProfileEstimate] ,[Statv2SessionLastGroup] ,[Statv2SessionGroupPath] ,[Statv2SessionIsLandingGroup] ,[Statv2SessionIsExitGroup] ,[Statv2SessionProfileDynamics] ) VALUES ( @p0 ,@p0 ,@p1 ,@p1 ,@p2 ,@p3 ,@p4 ,@p5 ,@p6 ,@p7 ,@p8 ,@p9 ,@p9 ,@p10 ,@p11 ,@p10 ,@p10 ,@p12 ,@p13 ,@p11 ,@p14 ,( SELECT MAX( [Statv2Session].[Statv2SessionIsLead] ) FROM [Statv2Session] WHERE ( [Statv2Session].[Statv2SessionVisitorID] = @p0 ) ) ,@p15 ,@p11 ,@p11 ,@p11 ,@p11 ,@p16 ,@p17 ,@p17 ,@p17 ,@p17 ,@p11 )
The first one (SELECT) runs about 33,000 time per day (that's about 1 request every 3 seconds)
The second one (INSERT) runs about 18,000 time per day (that's about 1 insert every 5 seconds)
It would be helpful for the load balancer can skip both the reads and writes for stats.
Note, I haven't tested to see if the same is happening for the new statatics module. It's not causing a problem at the moment anyway.
Thanks,
Scott