• Commerce,  Episerver,  Performance,  Uncategorized

    Fixing ASP.NET Membership performance – part 1

    Even though it is not the best identity management system in the .NET world, ASP.NET Membership provider is still fairly widely used, especially for systems that have been running for quite long time with a significant amount of users: migrating to a better system like AspNetIdentity does not comes cheap. However, built from early days of ASP.NET mean Membership provider has numerous significant limitations: beside the “architecture” problems, it also has limited performance. Depends on who you ask, the ultimate “maximum” number of customers that ASP.NET membership provider can handle ranges from 30.000 to 750.000. That does not sound great. Today if you start a new project, you should be…

  • Debugging,  Performance,  SQL Server,  Uncategorized

    Useful T-SQL snippets for development and troubleshooting

    This post is more of a note-to-self. These are the useful T-SQL statements which can be incredibly useful in development and troubleshooting [crayon-5c06d8dc7daa7609529703/] Turn on the IO statistics for statements run after that until set to OFF explicitly. We then switch to Messages tab to see how many IO operations were done on each table. [crayon-5c06d8dc7daaf427444612/] Find out about the statements were executed: which statements, its texts, how many reads (logical), how many time was spent on CPU and how many time was spent total

  • Catalog,  Commerce,  Episerver,  Performance,  SQL Server

    A curious case of SQL Server function

    This time, we will talk about ecfVersion_ListFiltered, again. This stored procedure was previously the subject of several blog posts regarding SQL Server performance optimizations. When I thought it is perfect (in term of performance), I learned something more. Recently we received a performance report from a customer asking about an issue after upgrading from Commerce 10.4.2 to Commerce 10.8 (the last version before Commerce 11). The job “Publish Delayed Content Versions” starts to throw timeout exceptions. This scheduled job calls to a ecfVersion_ListFiltered to load the content versions which are in status DelayedPublish, it looks like this when it reaches SQL Server: [crayon-5c06d8dc7de1d766588435/]This query is known to be slow. The…

  • Commerce,  Episerver,  Order,  Performance,  SQL Server

    Optimizing T-SQL COUNT

    This is a continuation of my previous post about paging in SQL Server. When it comes to paging, you would naturally want to know the total number of rows satisfying, so you can display some nice, useful information to your end-users. You would think, well, it’s just a count, and a simple query like this would be enough: [crayon-5c06d8dc7e654393299499/] There should be nothing to worry about, right? Actually, there is. Let’s get back to the example in previous post – we have to count the total number of orders in that big table. [crayon-5c06d8dc7e65c844589395/] Because ObjectId is the clustered index of OrderGroup_PurchaseOrder, I did expect it to be use that index…

  • Commerce,  Episerver,  Order,  Performance,  SQL Server,  Uncategorized

    The art of paging

    No this is not really “art” – I’m just trying to have a more clickbait title. It’s more about understanding what you have at your disposal and use them for your benefits – in this case – how new SQL statement can drastically improve your performance. In this blogpost we will look into paging feature of SQL Server. in Commerce we usually work with large set of data – millions of rows are fairly common, and it’s natural to load data by page. There is no point loading thousands, or even millions of rows in one go. First it’s not practical to display all of them. Second you’ll likely end…

  • Catalog,  Commerce,  Episerver,  Performance,  SQL Server

    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.

  • Catalog,  Commerce,  Episerver,  Performance,  SQL Server

    A curious case of SQL execution plan

    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/

  • SQL Server,  Tips,  Uncategorized

    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),…

  • Commerce,  Episerver,  Performance,  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…