SQL server pegged at 100% after 7.0.3 upgrade

[WylieE]

Hello everyone,

We're in a bit of a stressful situation right now. We upgraded to 7.0.3 last night. We noticed our SQL server activity was higher than normal. This was a bit of a concern but thought the upgrade might be performing some sort of re-index and let it go. Today, processor usages is pegged constantly at 100%.

Are there any significant changes under the hood? We've found page tracking requests are running slow and have disabled page tracking for now. Unfortunately, that made very little difference.

Anyone?

[jmestep]

Check for SQL Deadlocks?
Restart the App Pool?

[Logan Rhodehamel]

Are there any significant changes under the hood? We've found page tracking requests are running slow and have disabled page tracking for now. Unfortunately, that made very little difference.

What version did you upgrade from?

Is it possible to run SQL Profiler and find out what the SQL Server is trying to do when it maxes the CPU?

[WylieE]

Are there any significant changes under the hood? We've found page tracking requests are running slow and have disabled page tracking for now. Unfortunately, that made very little difference.

What version did you upgrade from?

Is it possible to run SQL Profiler and find out what the SQL Server is trying to do when it maxes the CPU?

We were on 10863. The odd thing is we previously upgraded our development box and did not experience any issues. Granted, our live site has hundreds of guests at any given time. It was my understanding performance should have been better under 7.0.3 and did not expect to see any such degredation.

I've run several different profiler tests and these are the statements that are taking the most time:
RPC: Completed: exec sp_executesql N'SELECT UserId FROM ac_Users WHERE LoweredUserName = @userName',N'@userName nvarchar(36)',@userName=N'39a09cb6-8653-41e7-8a43-df2b26ad74c0'

I'm totally open to run any other test or run profiler for any specific events.

[Logan Rhodehamel]

I've run several different profiler tests and these are the statements that are taking the most time:
RPC: Completed: exec sp_executesql N'SELECT UserId FROM ac_Users WHERE LoweredUserName = @userName',N'@userName nvarchar(36)',@userName=N'39a09cb6-8653-41e7-8a43-df2b26ad74c0'

Is it possible you are getting bombarded by a spider?

Since you have high traffic site, have you ever compiled a trace and then used the index tuning wizard tools? We produce a few indexes out of the box, but depending on the kind of traffic a site receives and the kind of store data, what indexes are most helpful can vary. If you have never adjusted the indexes, it could be a step to give you a huge boost in speed.

[WylieE]

I've run several different profiler tests and these are the statements that are taking the most time:
RPC: Completed: exec sp_executesql N'SELECT UserId FROM ac_Users WHERE LoweredUserName = @userName',N'@userName nvarchar(36)',@userName=N'39a09cb6-8653-41e7-8a43-df2b26ad74c0'

Is it possible you are getting bombarded by a spider?

Since you have high traffic site, have you ever compiled a trace and then used the index tuning wizard tools? We produce a few indexes out of the box, but depending on the kind of traffic a site receives and the kind of store data, what indexes are most helpful can vary. If you have never adjusted the indexes, it could be a step to give you a huge boost in speed.

There was no evidence of a spider.

As I have dug deeper into AC, I see fewer indexes than I would have expected. This surprises me. Adding an index of LoweredUserName in ac_Users reduced our CPU usage by over 50%. It might be good to go ahead an make this index a default for AC. Filespace took a bit of a spike, but the performance gains are well worth it, IMO. Based on our profiler testing, we may also index BasketID in ac_BasketItems. Queries referencing this field now represent the largest CPU hits.

Edit: I added an index to BasketID and saw CPU usage drop by about 10-20%. Our staff have noticed increased performance.

[AbleMods]

WOW!

Just for the sake of my own curiosity and a bit of boredom last night, I added those two indices to my Solunar.com site. I've noticed it's grown more and more sluggish over the last 6 months. I attributed it to just some bloated tables, leftover images (60,000 was my starting count) and all the customizations I'd done.

I didn't expect to see much if any difference on my puny site. But holy cow! Those two indices alone really improved the speed up my site. Even the admin side is snappy. The whole site is much snappier. SNAP SNAP SNAP!

There should be a SQL Profiler test built right into the app so you can fine-tune your install for your own specific needs.

I like it - never have dived into SQL profiling and now I think I'll need to study it a little better.

[afm]

There should be a SQL Profiler test built right into the app so you can fine-tune your install for your own specific needs.

I don't know if SQL Profiler functionality is exposed as an API or if those API's are available to normal apps (such as AC running on a shared host), but if they are, that is a fantastic idea!

[WylieE]

It would be interesting to see if any other large sites would try adding these indexes to see if performance increases on their sites. Or if larger sites would be willing to divulge their index structure.

This is only two tests here, Joe's site and ours, but this makes a strong argument for AC investigating this further, IMO. ac_Users and ac_basketitems are constantly hit and can grow rapidly as a customer base expands. Out of the box, AC doesn't seem to scale well for larger customer base/traffic sits. SQL Profiler/Database Engine Tuning Advisor are not difficult tools to use, but not everyone uses them nor knows how.

[jmestep]

We have a routine and Web2Market that index tunes the sites as part of the normal installation package.
We are going to be selling it as an add-on at some point in time.

[bkort@web2market.com]

Over the last decade I've found that good indexing of the database makes a huge difference in performance. I'd strongly recommend adding indexes to all primary and foreign keys in the AbleCommerce database. If you're not sure how to do that, we can help:
http://www.web2market.com/Database-Perf ... 25C43.aspx

[Logan Rhodehamel]

I have an idea. If you have SQL 2005 or higher, try running this:

Code: Select all

CREATE TABLE #tmpOutput (TableName VARCHAR(300), TableRowCount INT)
EXEC dbo.sp_MSForEachTable 'INSERT INTO #tmpOutput(TableName, TableRowCount) SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, TableRowCount
FROM #tmpOutput
ORDER BY 1
DROP TABLE #tmpOutput

Send me the results in a PM. It will give you a list of all tables and record counts. I might be able to use that to create a sample profiling environment and see what kind of index results I get. Maybe I can include a few more default indexes.

And yes, tuning the database can make a huge difference.

[AbleMods]

Wow 691,000 user records. No wonder the index helped me.

Wonder if my user maintenance is working....no way I've had that many user visits recently unless it's Google Feed hitting my site each day after my feed upload.

[vsammons]

Why don't the Able Commerce database already have these indexes scripted out in the install for SQL database? It should be a turn key for the non-developer customers. Are there any plans for this from Able Commerce?

[plugables]

Noticed something strange when creating index for ac_Users.LoweredUserName. It appears that there are duplicate records for the same user name. How this could have happened is a mystery.

CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.ac_Users' and index name 'IDX_LoweredUserName'. The duplicate key value is (ee88811c-70ea-46f0-b955-e1d35b30a43c).

The user seems to be an anonymous user with GUID as its generated user name. Did the system generate same GUID for more than one users?
Here are the users in the database with user name 'ee88811c-70ea-46f0-b955-e1d35b30a43c'

[AbleMods]

How that could have happened is a mystery to me - almost sounds like SQL crashed somehow and the record got duplicated.

I'd say nuke the duplicate and see if you have others...might be an indication of a larger problem.

[plugables]

Maybe when Able created the user it called User.Save() twice or something like that?

[AbleMods]

Maybe when Able created the user it called User.Save() twice or something like that?

Possible, or there was a SQL error that forced the transaction to roll back but SQL somehow couldn't roll it back.

Like I said, that's like a one-in-a-million issue. It's not "supposed" to ever happen