We have a Kentico 10 based website and having performance issues.
We have large number of documents in pages application (1000+)
We have two servers: 8 GB Web server, 8 GB DB server (Both VPS)
The pages application with node having large number of documents is very slow on checkout and checkin (no events handled manually)
Website is very slow when testing with load testing for 20 users.
Our database size is around 14 GB (I don't know why, we are using file system for files)
Sum of database size from the following query is only 2717 MB
select sum(TotalSpaceMB) from(
SELECT
t.Name AS TableName,
s.Name AS SchemaName,
p.Rows AS RowCounts,
(SUM(a.total_pages) * 8/1024) AS TotalSpaceMB
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
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.Name NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.object_id > 255
GROUP BY
t.Name, s.Name, p.Rows
)a;
The following query report size as 14361 MB
SELECT sys.databases.name,
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
where sys.databases.name = 'ourdbname'
GROUP BY sys.databases.name
ORDER BY sys.databases.name
Here are the top tables in terms of size:
TableName SchemaName RowCounts TotalSpaceMB UsedSpaceMB
CMS_ObjectVersionHistory dbo 2714 709 703
OM_Activity dbo 164890 403 231
Analytics_HourHits dbo 2111833 341 314
CMS_VersionHistory dbo 23153 292 279
Analytics_DayHits dbo 1457484 228 199
CMS_Relationship dbo 1326892 223 178
Analytics_WeekHits dbo 669597 113 89
OM_Contact dbo 62728 106 57
We have read the performance documents provided from Kentico and implemented them.
Questions:
- Is the hardware we are using fine for the website?
- What is causing extra 11-12 GB of data when sum of data tables is only 2717 MB
- Do we need to clear log files?
- Is the database size causing the website to go slow?
Thanks