C#, SQL, Episerver/Optimizely, performance, and beyond
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”
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/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”
I said this already, and I will say it again: SQL Server optimizer is smart. I can even go further and say, 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/
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.
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)
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:
CREATE NONCLUSTERED INDEX [IX_CatalogItemSeo_UniqueSegment_CatalogEntry] ON [dbo].[CatalogItemSeo]
(
[ApplicationId] ASC
[UriSegment] ASC,
[CatalogEntryId] ASC
)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”
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.
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.
A dot (.) – it is harmless. What harm can it do, it looks pretty innocent.
And yet it can break your Catalog UI.
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)”
Update: I found a faster, more reliable way to import, here
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”