I found this page which shows SQL queries for checking DB performance http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
I ran the missing index one and it said I had 71 missing indexes. When I ran the one to list the most costly this is what it showed..
Total Cost avg_user_impact TableName EqualityUsage InequalityUsage Include Cloumns
337617732 99.93 [DBTCSS].[dbo].[ac_Users] [LoweredUserName] NULL NULL
256329613 98.81 [DBTCSS].[dbo].[ac_BasketItems] [BasketId] NULL NULL
245632630 99.94 [DBTCSS].[dbo].[ac_Addresses] [UserId] NULL NULL
52547250 95.72 [DBTCSS].[dbo].[ac_BasketItems] [BasketId], [OrderItemTypeId] NULL NULL
46592356 70.33 [DBTCSS].[dbo].[ac_OrderItems] [WishlistItemId] NULL [OrderItemId]
43791136 65.04 [DBTCSS].[dbo].[ac_OrderNotes] [UserId] NULL [OrderNoteId]
25762276 27.31 [DBTCSS].[dbo].[ac_Baskets] [UserId] NULL NULL
14647476 22.11 [DBTCSS].[dbo].[ac_BasketItems] [WishlistItemId] NULL [BasketItemId]
13803200 50.99 [DBTCSS].[dbo].[ac_BasketItems] [BasketId] NULL [BasketItemId]
11353917 92.04 [DBTCSS].[dbo].[ac_OrderItems] [Sku] NULL [OrderId], [Quantity]
Is this something that needs to be addresses and if so, how?
DB optimizing.. need some help or explanations..
- compunerdy
- Admiral (ADM)
- Posts: 1283
- Joined: Sun Nov 18, 2007 3:55 pm
Re: DB optimizing.. need some help or explanations..
We have been recommending that customers follow these instructions to improve performance -
viewtopic.php?f=42&t=17625&p=75890&hili ... ard#p75890
The results can vary per installation because each store is different in terms of what queries are used most/least. It should be safe to run the performance wizard and do what it recommends, but it would be best to make a database backup first.
viewtopic.php?f=42&t=17625&p=75890&hili ... ard#p75890
The results can vary per installation because each store is different in terms of what queries are used most/least. It should be safe to run the performance wizard and do what it recommends, but it would be best to make a database backup first.
Thank you for choosing AbleCommerce!
http://help.ablecommerce.com - product support
http://wiki.ablecommerce.com - developer support
http://help.ablecommerce.com - product support
http://wiki.ablecommerce.com - developer support
- compunerdy
- Admiral (ADM)
- Posts: 1283
- Joined: Sun Nov 18, 2007 3:55 pm
Re: DB optimizing.. need some help or explanations..
I looked into that but I am using express which does not come with it as far as I understand it.
Re: DB optimizing.. need some help or explanations..
The first one listed for ac_Users LoweredUserName is one I have done several times. It makes a real difference. If the list is in order of most costly first, I would manually build the first 3-5 indexes only. See how that does for you before you go hog-wild. There is a performance penalty for making SQL maintain too many indexes. You don't want to wind up on the other side of the performance curve.compunerdy wrote:Is this something that needs to be addresses and if so, how?
To add an index, first open the database using Management Studio. Now expand your store database name, expand Tables, scroll down to the ac_Users table and expand it.
Then right-click on Indexes and choose New...Non-Clustered Index from the popup menu. Add the LoweredUserName field to the index and save it. The Name of the index isn't all that relevant. Rinse and repeat for the other tables and fields from your list.
If you don't have access to your database from Management Studio, you'll have to get your ISP to do it. Or get a programmer to build a small page that can be uploaded and executed on your site.
Joe Payne
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com
AbleCommerce Custom Programming and Modules http://www.AbleMods.com/
AbleCommerce Hosting http://www.AbleModsHosting.com/
Precise Fishing and Hunting Time Tables http://www.Solunar.com
- compunerdy
- Admiral (ADM)
- Posts: 1283
- Joined: Sun Nov 18, 2007 3:55 pm
Re: DB optimizing.. need some help or explanations..
Joe to the rescue once again.. THANKS!!!