Processor intensive SQL Query

Hi,

I'm using 3.50 but I still get a timeout.

So should I execute [dbo].[DeleteGuests] directly from ssms with default params?

Yes. Please try.

Are you running in Azure? I've heard about slowness of this stored procedure in Azure

No. We're on a dedicated windows server.

I'll backup the db and run it tonight when traffic is light.

We've started to get a lot of this in the log

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

Could this be related?

1. Can you make sure that you executed

\Presentation\Nop.Web\App_Data\Install\SqlServer.Indexes.sql

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

Have a look at here for more
http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

Yes we ran that script when we upgraded to 3.50 a few weeks ago.

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.

We disabled the scheduled task for deleting guest customers years ago because we didn't want to loose the records where customers had orders.

We executed the deleteguest sproc directly from ssms and after 3 hours it completed successfully


The admin panel should make it clear guests with orders are not deleted.

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'

I created a new system user called GuestCustomer
then all guests are assigned to the GuestCustomer, meaning they all share the same cart

on nop core I've added

namespace Nop.Core.Domain.Customers
{
    public static partial class SystemCustomerNames
    {
        public static string GuestCustomer { get { return "GuestCustomer"; } }
    }
}

had to change CustomerService

public virtual Customer InsertGuestCustomer()
        {
            //return customer;
            return GetCustomerBySystemName(SystemCustomerNames.GuestCustomer);

        }

and CustomerRegistrationSevice
add to:

 public virtual CustomerRegistrationResult RegisterCustomer(CustomerRegistrationRequest request)

at the end instead of update the guest user I insert a new one

//_customerService.UpdateCustomer(request.Customer);
            request.Customer.CustomerGuid = Guid.NewGuid();
            request.Customer.CreatedOnUtc = DateTime.UtcNow;
            request.Customer.AdminComment = null;
            request.Customer.SystemName = null;
            request.Customer.IsSystemAccount = false;
            _customerService.InsertCustomer(request.Customer);

part for the sharing cart its working very well
on contact us view u need to clear the email for the GuestCustomer


I have tried all suggestions, but with heavy traffic delete guest SP does not work