1. Home
  2. Support
  3. SQL queries

SQL queries

In case of performance issues, unknown application restarts or SQL timeouts/deadlocks etc.
Please run the SQL script below on the live database and send us the returned values incl. column headers (a clear screenshot is sufficient).

/*** Statistics from uMarketingSuiteAnalyticsRawPageView table */
select count(1) AS RawPageView_nr_of_records from [uMarketingSuiteAnalyticsRawPageView] WITH (NOLOCK)
go
select count(1) AS PageView_nr_of_records from [uMarketingSuiteAnalyticsPageView] WITH (NOLOCK)
go
/* if the queries above are too slow, please use:
SELECT RawPageView_nr_of_records= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'uMarketingSuiteAnalyticsRawPageView' AND (index_id < 2)
go
SELECT PageView_nr_of_records= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'uMarketingSuiteAnalyticsPageView' AND (index_id < 2)
go
***/
SELECT COUNT(id) AS RawPageView_nr_of_unprocessed_records FROM uMarketingSuiteAnalyticsRawPageview WITH (NOLOCK,INDEX(IX_NonClustered_RawPageview_ProcessingStartedAndFinished)) WHERE processingStarted IS NULL AND processingFinished IS NULL
go
select max(processingStarted) AS RawPageView_processingStarted_most_recent from [uMarketingSuiteAnalyticsRawPageView] WITH (NOLOCK)
go
select max(processingFinished) AS RawPageView_processingFinished_most_recent from [uMarketingSuiteAnalyticsRawPageView] WITH (NOLOCK)
go
select count(1) AS RawPageView_processingStarted_NOT_NULL_processingFinished_NULL from [uMarketingSuiteAnalyticsRawPageView] WITH (NOLOCK) where [processingStarted] IS NOT NULL AND [processingFinished] IS NULL
go
select count(1) AS RawPageView_processingStarted_NULL from [uMarketingSuiteAnalyticsRawPageView] WITH (NOLOCK) where [processingStarted] IS NULL
go
/*** Too slow if table is large. > 500.000 records
select count(*) AS RawPageView_processingFailed from [uMarketingSuiteAnalyticsRawPageView] WITH (NOLOCK) where [processingFailed] = 1
go
select max(timestamp) AS RawPageView_processingFailed_most_recent from [uMarketingSuiteAnalyticsRawPageView] WITH (NOLOCK) where [processingFailed] = 1
go***/
/*** Statistics from uMarketingSuiteAnalyticsRawClientSideData table */
select count(1) AS RawClientSideData_nr_of_records from [uMarketingSuiteAnalyticsRawClientSideData] WITH (NOLOCK)
go
select max(processingStarted) AS RawClientSideData_processingStarted_most_recent from [uMarketingSuiteAnalyticsRawClientSideData] WITH (NOLOCK)
go
select max(processingFinished) AS RawClientSideData_processingFinished_most_recent from [uMarketingSuiteAnalyticsRawClientSideData] WITH (NOLOCK)
go
select count(1) AS RawClientSideData_processingStarted_NOT_NULL_processingFinished_NULL from [uMarketingSuiteAnalyticsRawClientSideData] WITH (NOLOCK) where [processingStarted] IS NOT NULL AND [processingFinished] IS NULL
go
select count(1) AS RawClientSideData_processingStarted_NULL from [uMarketingSuiteAnalyticsRawClientSideData] WITH (NOLOCK) where [processingStarted] IS NULL
go
select count(1) AS RawClientSideData_processingFailed from [uMarketingSuiteAnalyticsRawClientSideData] WITH (NOLOCK) where [processingFailed] = 1
go
select max(timestamp) AS RawClientSideData_processingFailed_most_recent from [uMarketingSuiteAnalyticsRawClientSideData] WITH (NOLOCK) where [processingFailed] = 1
go /*** A bit of migration info ***/
SELECT * FROM [umbracoKeyValue] where [key] = 'Umbraco.Core.Upgrader.State+uMarketingSuite' /*** Too slow if table is large. > 500.000 records */
/* Processing statistics last 7 days of uMarketingSuiteAnalyticsRawPageView
SELECT top(7) AVG(isnull(datediff(ms, processingStarted, processingFinished),0)) AS processing_time_in_ms, CAST(processingStarted as DATE) as date
FROM [uMarketingSuiteAnalyticsRawPageView] WITH (NOLOCK)
WHERE processingstarted IS NOT NULL AND processingFinished IS NOT NULL AND processingFailed = 0
GROUP BY CAST(processingStarted as DATE)
ORDER BY CAST(processingStarted as DATE) DESC
go
***/