DB optimizing.. need some help or explanations..

[compunerdy]

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?

[Katie]

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.

[compunerdy]

I looked into that but I am using express which does not come with it as far as I understand it.

[AbleMods]

Is this something that needs to be addresses and if so, how?

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.

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.

[compunerdy]

Joe to the rescue once again.. THANKS!!!