Please, rebuild your database indexes, now

I will make it quick and to the point: if you are expecting a lot of customers visiting your site tomorrow (and you should) for Black Friday, you should rebuild your database indexes, now.

On average, it will help you to serve more customers and they will be happier with a more responsive, faster website. On best cases it will help prevent catastrophes.

Continue reading “Please, rebuild your database indexes, now”

A curious case of SQL execution plan, part 2

Recently I wrote about how to look into, identify and solve the problem with a SQL Server execution plan – as you can read here: http://vimvq1987.com/2017/10/curious-case-sql-execution-plan/

I have some more time to revisit the query now, and I realized I made a “small” mistake. The “optimized” query is using a Clustered Index Scan

So it’s not as fast as it should be, and it will perform quite poorly in no cache scenario (when the buffer is empty, for example) – it takes about 40s to complete. Yes it’s still better than the original one, both in non cached and cached cases. But it’s not good enough. An index scan, even cached, is not only slower, but also more prone to deadlocks. It’s also worse in best case scenario, when the original one can use the proper index seek.

Continue reading “A curious case of SQL execution plan, part 2”

A curious case of SQL execution plan

I said this, and I will repeat it here: SQL Server optimizer is smart, and I can even say that, generally, it’s smarter than you and me (I have no doubt that you are smart, even very, very smart 🙂 ). So most of the cases, you leave it to do whatever it thinks is the best. But there are cases SQL Server optimizer is fooled by the engine – it gets confused and chooses an sub-optimal plan, because it was given wrong, outdated, or incorrect information. That’s when you need to step in.

Today I face one case like that, as reported here: http://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2017/10/database-timeout-on-productvariant-update/

(As a side note, I don’t answer direct questions, nor provide any personal support service (I would charge plenty for that 😉 ). I would appreciate if you go through World forums, or contact Episerver Developer support service. There are several reasons for that, including knowledge sharing, and work item tracking. I can make exceptions when I know the problem is highly urgent and is hurting your business, by jumping into it sooner than I’m expected to/before it’s escalated through several level of supports. But all in all, it should be registered with Deverloper support. We at development team are supposed to be the final line of support, not the front line. )

Continue reading “A curious case of SQL execution plan”

Maintaining your indexes

Indexes are crucial to SQL Server performance. Having the right indexes might make the difference of day and night with your application performance – as I once talked here.

However, even having the right indexes is not everything. You have to keep them healthy. Indexes, as any other kinds of storage, is subjected to fragmentation. SQL Server works best if the index structure is compact and continuous, but with all of the inserts/updates/deletes, it’s inevitable to get fragmented. When the fragmentation grows, it starts affecting the performance of SQL Server: Instead of having to read just one page, it now have to read two, which increases both time and resource needed, and so on and so forth.

Continue reading “Maintaining your indexes”

Import a bacpac to SQL Server

This is more of a note-to-self.

I sometimes have to import a bacpac file from customer’s database (usually from Azure SQL database) to my local machine – . For most of the time it’ll be very easy when the databases are in .bak format, but for .bacpac file it can be pretty complicated.

Sqlpackage.exe is the standard tool to import the .bacpac file, and it can be found with the installation of Visual Studio (for example C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130) or SQL Server ( C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin ). Latest version should be used because they can support the older formats (.bacpac exported from older SQL Server version), but not the way around (older version might not support .bacpac files exported from newer SQL Server versions)

Continue reading “Import a bacpac to SQL Server”

Watch your indexes closely

Recently we were tasked to help a customer having a problem with a query. This specific query ate a lot of CPU resources (30-40%) and causing performance problem for other queries – as it slows the entire SQL Server instance down.

Upon investigation, we discovered that the query was accessing a table with an outdated index. The index was supposedly updated in Episerver Commerce 7.10.3, which was released almost 3 years ago.

For some reasons, the index was not updated in customer’s table. Instead of just having to do a index seek, SQL Server was forced to do a full table scan, which is much slower, causing the problem.

If you want to go into details, it’s mdpsp_getchildrenbysegment stored procedure, which looks into UriSegment column of CatalogItemSeo table, previously, the index was like this:

You can see the problem: The order of the index was bad – because ApplicationId was not distinctive (in fact, in most of the cases it’s the same for every row), and because UriSegment was not the first column in the index, this index will not be used if a query uses UriSegment only. Continue reading “Watch your indexes closely”

Tale from inside TransactionScope

One of the last things you want to get from your Commerce site is that the order data is gone. What can be more confused than if your log shows that the cart has been converted into a purchase order, you even got the PO number, but after that, the order disappears? It’s nowhere to be found, even if you look into database. It’s kind of magic, but not the kind of magic you would want to have.

But everything happens for a reason. And actually it’s with a good reason: data consistency.

Episerver Commerce has the concept of TransactionScope. Simply put, it allows two or more database operations to be done as atomic: Either all of them succeed, or all of them will revert back. If a TransactionScope contains 3 operations A, B, C, then even if A, B succeeded, but C is yet to commit, and something goes wrong, then A and B would be reverted.

Continue reading “Tale from inside TransactionScope”

Speed up your catalog entries indexing

Even with the raising popularity of FindCommerce, I suspect that many Commerce customers are still using the search provider system, as it comes with an undeniable benefit: It can be used within Commerce Manager. And while I suspect a majority of you have the eventual indexing turned on (aka the entry will be indexed as soon as it’s changed), many still index the entries on demand. Aka manually, or periodically via scheduled job.

We received a report from a customer recently as the indexing timeout, as he was indexing a large amount of entries (about 250.000 entries on 8 catalogs). When we looked into the problem, we discover a possible improvement which is almost free for you.

Continue reading “Speed up your catalog entries indexing”

The hidden danger of dot (Or why should your metafield not contain . in the name)

A dot (.) – it is harmless. What harm can it do, it looks pretty innocent.

And yet it can break your Catalog UI.

Psyduck, from Pokemon Go
A dot can look pretty harmless and innocent, just like a Psyduck. Frankly, its eyes are also two dots.

Catalog UI relies on the Shell UI from CMS to render properties and such. Shell UI, in its hands, needs to know about the metadata of the properties. When you have dot in the metafield names, the MetaDataPropertyMapper will create an Property with that name on site start up. And then when you open All properties mode, Shell UI will request your content type models, and CMS Core will happily return those properties.

Continue reading “The hidden danger of dot (Or why should your metafield not contain . in the name)”

Unable to import .bacpac files exported from Azure

Today when I tried to import a .bacpac file from a customer, I ran across this issue:

Could not load schema model from package. (Microsoft.SqlServer.Dac)

——————————
ADDITIONAL INFORMATION:

Internal Error. The internal target platform type Sql120DatabaseSchemaProvider does not support schema file version ‘2.6’. (File: D:\supportcases\something.bacpac) (Microsoft.Data.Tools.Schema.Sql)

I’m using SQL Server 2014, Developer edition. Searching for the error returns no matched result. However, as the file was exported by Azure v12, we can assume Microsoft should have done something to support the latest version (2.6) in Management Studio.

 


Continue reading “Unable to import .bacpac files exported from Azure”