Developer forum

Hi Imar,

You can truncate StatV2Page, Statv2Object, Statv2SessionBot, Statv2Session tables only.
use SQL:

delete from StatV2Page;
delete from Statv2Object;
delete from Statv2SessionBot;
delete from Statv2Session;

Hi Alexander,

Thanks, but what do I lose by doing so? Don't these tables keep track of things used elsewhere such as viewed products and so on?

Imar

Hi Imar,

You are right, some the data will lost. To control it you can restrict delete operation by Statv2SessionTimestamp column.

I mean:

declare @boundDate as DateTime = DATEADD(year,-1,GETDATE()); /*1 year ago*/

declare @sess as table(ID int not null, SessionID nvarchar(255) not null);
insert into  @sess SELECT Statv2SessionID, Statv2SessionSessionID FROM Statv2Session WHERE Statv2Session.Statv2SessionTimestamp < @boundDate;

BEGIN TRANSACTION;
BEGIN TRY    
    DELETE FROM StatV2Page WHERE [Statv2PageObjectID] IN (SELECT [Statv2ObjectID] FROM Statv2Object WHERE Statv2ObjectSessionID IN (SELECT SessionID FROM @sess));
    DELETE FROM Statv2Object WHERE Statv2ObjectSessionID IN (SELECT SessionID FROM @sess);
    DELETE FROM Statv2SessionBot WHERE Statv2SessionSessionID IN (SELECT SessionID FROM @sess);
    DELETE FROM Statv2Session WHERE Statv2Session.Statv2SessionTimestamp < @boundDate
END TRY
BEGIN CATCH
print error_message();
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0 COMMIT TRANSACTION;

Hi Alexander,

Thanks for that. However, what I am trying to find out is *what* type of data I am losing. In other words, I am trying to get a clear impact on the functionality and behavior when I delete that data.

The site has been online for only three months so if I wanrt to, say cut the database in half, I can only keep 1.5 months worth of data. That's certainly going to have some impact on functionality, but I am not sure what yet.

Imar

Hi Imar

It is statistical and lead information hold in those tables.

Statv2Page i.e. gives you visit counts on the paragraph list, page history in the visit detail window and the visit sitemap in statistics.

Statv2object have visit count on forms, searches, products, news, etc.

Statv2SessionBot is the indexing reports in statistics - just dump that.

Statv2Session is all visitor information used in statistics, leads, personalisation records/history and OMC reporting.

One problem could be the sizes of the index on these tables. They will change with 8.7 and minimize index sizes.

Try running this script on the database:

Private Sub AlterStatV2Session()
        Dim cb As CommandBuilder = CommandBuilder.Create("SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME='Statv2Object' AND COLUMN_NAME='Statv2ObjectPageID'")
        Dim pageIdType As String = Base.ChkString(Database.ExecuteScalar(cb))
        If String.Compare(pageIdType, "nvarchar", True) = 0 Then
            Using connection As IDbConnection = Database.CreateConnection()
                cb = New CommandBuilder()
                With cb
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'STATV2Object_IDX001' )) DROP INDEX [STATV2Object_IDX001] ON [dbo].[Statv2Object];")
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'STATV2Object_IDX002' )) DROP INDEX [STATV2Object_IDX002] ON [dbo].[Statv2Object];")
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'STATV2Object_IDX003' )) DROP INDEX [STATV2Object_IDX003] ON [dbo].[Statv2Object];")
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'DW_IX_Statv2Object_Type' )) DROP INDEX [DW_IX_Statv2Object_Type] ON [dbo].[Statv2Object];")
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'DW_IX_Statv2Object_Element' )) DROP INDEX [DW_IX_Statv2Object_Element] ON [dbo].[Statv2Object];")
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'DW_IX_Statv2Object_SessionId' )) DROP INDEX [DW_IX_Statv2Object_SessionId] ON [dbo].[Statv2Object];")
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'DW_IX_Statv2Object_SessionID_Type' )) DROP INDEX [DW_IX_Statv2Object_SessionID_Type] ON [dbo].[Statv2Object];")
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'Statv2Object_SessionIDTypeWithElement' )) DROP INDEX [Statv2Object_SessionIDTypeWithElement] ON [dbo].[Statv2Object];")
                    .Add("IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'IX_StatV2Object_StatV2ObjectVisitorID' )) DROP INDEX [IX_StatV2Object_StatV2ObjectVisitorID] ON [dbo].[Statv2Object];")

                    .Add("ALTER TABLE [Statv2Object] ALTER COLUMN [Statv2ObjectSessionID] [nvarchar](24) NULL;")
                    .Add("ALTER TABLE [Statv2Object] ALTER COLUMN [Statv2ObjectType] [nvarchar](50) NULL;")
                    .Add("ALTER TABLE [Statv2Object] ALTER COLUMN [StatV2ObjectVisitorId] [nvarchar](24) NULL;")
                    .Add("ALTER TABLE [Statv2Object] ALTER COLUMN [Statv2ObjectPageId] int NULL;")

                    .Add("CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_Type] ON [Statv2Object] ([Statv2ObjectType] Asc) INCLUDE ([Statv2ObjectSessionID],[Statv2ObjectElement]);")
                    .Add("CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_Element] ON [Statv2Object] ([Statv2ObjectElement] Asc);")
                    .Add("CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_SessionId] ON [dbo].[Statv2Object]([Statv2ObjectSessionId] ASC);")
                    .Add("CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_SessionID_Type] ON [dbo].[Statv2Object]([Statv2ObjectSessionId] ASC, [Statv2ObjectType] ASC) INCLUDE([Statv2ObjectElement]);")
                    .Add("CREATE NONCLUSTERED INDEX [IX_StatV2Object_StatV2ObjectVisitorID] ON [dbo].[Statv2Object] ([Statv2SessionVisitorID] Asc);")
                End With

                Try
                    Database.ExecuteNonQuery(cb, True)
                Catch
                End Try
            End Using
        End If
    End Sub

And I guess the database is using simple logging?

Is there a pure SQL version of this script as well or do I need to compile this to something?

I believe we're running in Full mode, but it's the MDF that is so huge not the log file.

Imar

You should change to Simple mode and compact the DB - that will take away 10 gb or more!

Here is the script:

IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'STATV2Object_IDX001' )) DROP INDEX [STATV2Object_IDX001] ON [dbo].[Statv2Object];
IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'STATV2Object_IDX002' )) DROP INDEX [STATV2Object_IDX002] ON [dbo].[Statv2Object];
IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'STATV2Object_IDX003' )) DROP INDEX [STATV2Object_IDX003] ON [dbo].[Statv2Object];
IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'DW_IX_Statv2Object_Type' )) DROP INDEX [DW_IX_Statv2Object_Type] ON [dbo].[Statv2Object];
IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'DW_IX_Statv2Object_Element' )) DROP INDEX [DW_IX_Statv2Object_Element] ON [dbo].[Statv2Object];
IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'DW_IX_Statv2Object_SessionId' )) DROP INDEX [DW_IX_Statv2Object_SessionId] ON [dbo].[Statv2Object];
IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'DW_IX_Statv2Object_SessionID_Type' )) DROP INDEX [DW_IX_Statv2Object_SessionID_Type] ON [dbo].[Statv2Object];
IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'Statv2Object_SessionIDTypeWithElement' )) DROP INDEX [Statv2Object_SessionIDTypeWithElement] ON [dbo].[Statv2Object];
IF EXISTS (SELECT * FROM sys.indexes WHERE ( name = N'IX_StatV2Object_StatV2ObjectVisitorID' )) DROP INDEX [IX_StatV2Object_StatV2ObjectVisitorID] ON [dbo].[Statv2Object];

ALTER TABLE [Statv2Object] ALTER COLUMN [Statv2ObjectSessionID] [nvarchar](24) NULL;
ALTER TABLE [Statv2Object] ALTER COLUMN [Statv2ObjectType] [nvarchar](50) NULL;
ALTER TABLE [Statv2Object] ALTER COLUMN [StatV2ObjectVisitorId] [nvarchar](24) NULL;
ALTER TABLE [Statv2Object] ALTER COLUMN [Statv2ObjectPageId] int NULL;

CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_Type] ON [Statv2Object] ([Statv2ObjectType] Asc) INCLUDE ([Statv2ObjectSessionID],[Statv2ObjectElement]);
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_Element] ON [Statv2Object] ([Statv2ObjectElement] Asc);
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_SessionId] ON [dbo].[Statv2Object]([Statv2ObjectSessionId] ASC);
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_SessionID_Type] ON [dbo].[Statv2Object]([Statv2ObjectSessionId] ASC, [Statv2ObjectType] ASC) INCLUDE([Statv2ObjectElement]);
CREATE NONCLUSTERED INDEX [DW_IX_StatV2Object_StatV2ObjectVisitorID] ON [dbo].[Statv2Session] ([Statv2SessionVisitorID] Asc);

Hi Nicolai,

Thanks; I ran the script and the database is still 10GB. it's already running in Simple mode, so I guess I'll have to delete data i order to decrease its size. Unless you have other ideas?

Imar

Hi Imar

Do you know what is taking up the space? Try running this one and see if you can see anything:

SELECT
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages,
 SUM(a.used_pages) AS UsedPages,
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY
 t.NAME, i.object_id, i.index_id, i.name
ORDER BY
 OBJECT_NAME(i.object_id)

Also, it maybe just takes up a lot of space.... If they have a lot of visitors, DW logs a lot of data.

Here's my top 10. It seems it's just a lot of data.

Well, just a lot of visitors... Can't do anything about it except purging data, disable statistics or shut down the site :-).

Hi Imar,

You can also work with analytics, it doens't give so much detail in the statistics, but you can build goals and with the right implementation you can grab many information.

Just my 2 cents,

Best regards,

António Ramos

Hi António and Nicolai,

Yeah I don't mind purging statistics and use an external system. In fact, that's exactly what I am trying to accomplish. However, I don't want to lose information like "other people bought", abandoned carts, etc. I am still a little unsure what I lose when purging it all and what the impact is but I guess I have to try it out.

Thanks,

Imar

Other people bought and abandoned carts are data from the Ecommerce - that is not found in Statv2 tables. Other people saw, is found in Statv2Object table, but oonly looks at the last 30 days.

Great, thank you, that helps.

Imar

Hi Nicolai,

We have the same problem with some databases and we see a lot of space being used by the Statistics Tables Indexes.

I noticed some tables Statv2Session have more than 30 different indexes. Most of them have the following numeric name "STATV2Session_IDX001". Is this normal behaviour?

Is there any recomended/special configurations to apply to the Dynamicweb Databases?

BR Diogo Brito

Hi Diogo

There should be 9 indexes on Statv2Session.

You can delete all the indexes (Except the clustered one on Statv2sessionID) and then run this script to create the once required:

GO
PRINT N'Creating [dbo].[Statv2Session].[DW_IX_Statv2Session_VisitorId]...';

GO
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Session_VisitorId]
    ON [dbo].[Statv2Session]([Statv2SessionVisitorId] ASC);

GO
PRINT N'Creating [dbo].[Statv2Session].[DW_IX_Statv2Session_Timestamp]...';

GO
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Session_Timestamp]
    ON [dbo].[Statv2Session]([Statv2SessionTimestamp] ASC);

GO
PRINT N'Creating [dbo].[Statv2Session].[DW_IX_Statv2Session_AreaId]...';

GO
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Session_AreaId]
    ON [dbo].[Statv2Session]([Statv2SessionAreaId] ASC);

GO
PRINT N'Creating [dbo].[Statv2Session].[DW_IX_Statv2Session_ExtranetUserId]...';

GO
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Session_ExtranetUserId]
    ON [dbo].[Statv2Session]([Statv2SessionExtranetUserId] ASC);

GO
PRINT N'Creating [dbo].[Statv2Session].[DW_IX_Statv2Session_SessionId]...';

GO
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Session_SessionId]
    ON [dbo].[Statv2Session]([Statv2SessionSessionId] ASC);

GO
PRINT N'Creating [dbo].[Statv2Session].[DW_IX_Statv2Session_UserAgentId]...';

GO
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Session_UserAgentId]
    ON [dbo].[Statv2Session]([Statv2SessionUserAgentId] ASC);

GO
PRINT N'Creating [dbo].[Statv2Session].[DW_IX_Statv2Session_AreaID_Timestamp]...';

GO
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Session_FirstPage_Timestamp]
    ON [dbo].[Statv2Session] ([Statv2SessionFirstpage] ASC, [Statv2SessionTimestamp] ASC)
    INCLUDE ([Statv2SessionRefererUrl],[Statv2SessionRefererSearchWord]);

GO
PRINT N'Creating [dbo].[Statv2Session].[DW_IX_Statv2Session_AreaID_Timestamp]...';

GO
CREATE NONCLUSTERED INDEX [DW_IX_Statv2Session_AreaID_Timestamp]
    ON [dbo].[Statv2Session] ([Statv2SessionAreaID] ASC, [Statv2SessionTimestamp] ASC)
    INCLUDE ([Statv2SessionRefererUrl],[Statv2SessionRefererSearchWord]);

Hello Nicolai,

Thank you for the input.

It seems to me that there is a lot of unecessary space in the Statsv2 index Tables wich causes very large databases and possible performance issues. Is there any chance to provide us with a document for managing these indexes? For what i can tell at least 4 tables have a lot of them:
Statv2Object
Statv2SessionBot
Statv2Page
Statv2Session

BR Diogo Brito

Hi Diogo

Here are the indexes you need. Make sure you clean up and rebuild using these. Apart from that, I cannot give much more information.

Statv2SessionBot

CREATE NONCLUSTERED INDEX [DW_IX_Statv2SessionBot_AreaId]
    ON [dbo].[Statv2SessionBot]([Statv2SessionAreaId] ASC);
GO

CREATE NONCLUSTERED INDEX [DW_IX_Statv2SessionBot_SessionId]
    ON [dbo].[Statv2SessionBot]([Statv2SessionSessionId] ASC);
GO

CREATE NONCLUSTERED INDEX [DW_IX_Statv2SessionBot_UserAgentId]
    ON [dbo].[Statv2SessionBot]([Statv2SessionUserAgentId] ASC);
GO

CREATE NONCLUSTERED INDEX [DW_IX_Statv2SessionBot_Timestamp]
    ON [dbo].[Statv2SessionBot]([Statv2SessionTimestamp] ASC);
GO

Statv2Page

CREATE NONCLUSTERED INDEX [DW_IX_Statv2Page_ObjectId]
    ON [dbo].[Statv2Page]([Statv2PageObjectId] ASC);
GO

CREATE NONCLUSTERED INDEX [DW_IX_Statv2Page_SessionId]
    ON [dbo].[Statv2Page]([Statv2PageSessionId] ASC);
GO

CREATE NONCLUSTERED INDEX [DW_IX_Statv2Page_PageId]
    ON [dbo].[Statv2Page]([Statv2PagePageId] ASC);
GO

Statv2Object

CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_Type]
    ON [dbo].[Statv2Object]([Statv2ObjectType] ASC);
GO

CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_Element]
    ON [dbo].[Statv2Object]([Statv2ObjectElement] ASC);
GO

CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_SessionId]
    ON [dbo].[Statv2Object]([Statv2ObjectSessionId] ASC);
GO

CREATE NONCLUSTERED INDEX [DW_IX_Statv2Object_SessionID_Type]
    ON [dbo].[Statv2Object]([Statv2ObjectSessionId] ASC, [Statv2ObjectType] ASC)
    INCLUDE([Statv2ObjectElement]);

Hi Imar and Diogo

The problem TFS#17359 "Statistics Session purge sql text"  has now been fixed in version 8.7.1.4

You are able to find this build in the download section:

http://developer.dynamicweb-cms.com/downloads/dynamicweb-8.aspx

Please contact Dynamicweb Support if you need any additional help regarding this.

Kind Regards
Dynamicweb Support
Kristian Kirkholt