An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server. See the inner exception and http://go.microsoft.com/fwlink/?LinkId=313468 for more information
2. Find Most Expensive Queries Using DMV and use suggested indexing
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC -- logical reads -- ORDER BY qs.total_logical_writes DESC -- logical writes -- ORDER BY qs.total_worker_time DESC -- CPU time
I had the same problem with delete guests about a year ago, i have tried everything and did not get it to work. the end of the day I disabled the guest records on db, that solved the problem.
I have several website using Nop, I only had a problem with one that has around 4000 unique users a day.
OK, we've got the customer table down to under 400k and SytemName is indexed but the query is still slow.
CPU over 2000. Duration over 300
Other queries on the customer table seem to be ok. It just this query that is slow. Which is a BIG problem for SEO
exec sp_executesql N'SELECT TOP (1) [Project1].[Id] AS [Id], [Project1].[CustomerGuid] AS [CustomerGuid], [Project1].[Username] AS [Username], [Project1].[Email] AS [Email], [Project1].[Password] AS [Password], [Project1].[PasswordFormatId] AS [PasswordFormatId], [Project1].[PasswordSalt] AS [PasswordSalt], [Project1].[AdminComment] AS [AdminComment], [Project1].[IsTaxExempt] AS [IsTaxExempt], [Project1].[AffiliateId] AS [AffiliateId], [Project1].[VendorId] AS [VendorId], [Project1].[Active] AS [Active], [Project1].[Deleted] AS [Deleted], [Project1].[IsSystemAccount] AS [IsSystemAccount], [Project1].[SystemName] AS [SystemName], [Project1].[LastIpAddress] AS [LastIpAddress], [Project1].[CreatedOnUtc] AS [CreatedOnUtc], [Project1].[LastLoginDateUtc] AS [LastLoginDateUtc], [Project1].[LastActivityDateUtc] AS [LastActivityDateUtc], [Project1].[BillingAddress_Id] AS [BillingAddress_Id], [Project1].[ShippingAddress_Id] AS [ShippingAddress_Id] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[CustomerGuid] AS [CustomerGuid], [Extent1].[Username] AS [Username], [Extent1].[Email] AS [Email], [Extent1].[Password] AS [Password], [Extent1].[PasswordFormatId] AS [PasswordFormatId], [Extent1].[PasswordSalt] AS [PasswordSalt], [Extent1].[AdminComment] AS [AdminComment], [Extent1].[IsTaxExempt] AS [IsTaxExempt], [Extent1].[AffiliateId] AS [AffiliateId], [Extent1].[VendorId] AS [VendorId], [Extent1].[Active] AS [Active], [Extent1].[Deleted] AS [Deleted], [Extent1].[IsSystemAccount] AS [IsSystemAccount], [Extent1].[SystemName] AS [SystemName], [Extent1].[LastIpAddress] AS [LastIpAddress], [Extent1].[CreatedOnUtc] AS [CreatedOnUtc], [Extent1].[LastLoginDateUtc] AS [LastLoginDateUtc], [Extent1].[LastActivityDateUtc] AS [LastActivityDateUtc], [Extent1].[BillingAddress_Id] AS [BillingAddress_Id], [Extent1].[ShippingAddress_Id] AS [ShippingAddress_Id] FROM [dbo].[Customer] AS [Extent1] WHERE ([Extent1].[SystemName] = @p__linq__0) OR (([Extent1].[SystemName] IS NULL) AND (@p__linq__0 IS NULL)) ) AS [Project1] ORDER BY [Project1].[Id] ASC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'SearchEngine'