![\"\"](\"https:\/\/nenadnoveljic.com\/blog\/wp-content\/uploads\/2019\/04\/performance_single_thread.png\")
<\/a><\/p>\nNotice that the Y-axis scale is logarithmic, so the differences are much larger than they visually appear.<\/p>\n
We can see that Oracle outperforms SQL Server by an order of magnitude.<\/p>\n
Next, I’ll be focusing on how the execution time changes with a concurrent load.<\/p>\n
Scalability<\/h1>\n
Since I don’t have multi-core licences for Oracle on Windows, I measured the execution times on Solaris x64. In order to compare the databases on different platforms, I normalized the mean execution time by dividing it with the execution time of a single session test:<\/p>\n
(1) <\/span> <\/span> In other words, T(N) is a factor by which the performance degrades when N sessions are running simultaneously.<\/p>\n Unlike Oracle, SQL Server doesn’t scale at all – the execution time dramatically increases with the load. For example, the execution time is twice as long when 5 sessions are executing the load.<\/p>\n In conclusion, not only does SQL Server perform much worse than Oracle, but also it doesn’t scale.<\/p>\n As the procedure is completely CPU-bound, stack profiling will provide a clue to where the time goes.<\/p>\n I used Windows Performance Toolkit<\/a> for collecting and aggregating stacks and also Bruce Dawson’s awesome UIforETW<\/a> for facilitating the whole process.<\/p>\n As you can see, the stack part beginning with the function CMetaStmtSet::PxstmtGetEmbeddedXstmt consumed more than 30% of the total execution time. So, it makes sense to inspect the lowest function on the stack: sqllang!CStatement::AddRef.<\/p>\n By observing its input and output with WINDBG, we can see that the function increments the memory location passed through the RCX register. Actually, the exact memory location is RCX+7C0h.<\/p>\n The calls to sqllang!CStatement::AddRef are traced with WINDBG – the following commands spool the thread information and the RCX register value:<\/p>\n Here’s the excerpt from the rather long output:<\/p>\n The pattern above is repeated over and over again, because the function gets called several times during each loop iteration.<\/p>\n Furthermore, there were two threads working in parallel: 6<\/span> and 137<\/span>. Curiously, both of them were trying to increment the same memory locations: 000001fad58e93b0<\/span> and 000001fad58e89c0<\/span>.<\/p>\n These target locations are in the database cache, more precisely in its region annotated as MEMOBJ_EXECUTE.<\/p>\n Since multiple threads are incrementing the same location, they must utilize the atomic instructions of processors to atomically update a memory location. That involves specifying a lock prefix, which will assert a lock on the cache and sometimes even on the bus. Consequently, its frequent usage will impair performance.<\/p>\n This locking mechanism is nothing unusual within a database and it’s normally used to serialize changes to a shared resource. In fact, that’s exactly how latches are implemented [Lewis 2011]<\/a>: “Essentially a latch is the combination of a memory location in the SGA and an atomic CPU operation that can be used to check and change the value of that location.”<\/p>\n When executing a stored procedure, for example, a latch might be used to count the number of sessions executing the procedure, to prevent other sessions from altering it. When the execution finishes, the session counts itself off. Once the value is decremented to zero, it means that nobody’s executing the procedure anymore, so, another session can now acquire the latch and proceed with a DDL.<\/p>\n Generally speaking, acquiring a latch is a rather normal event, but what’s completely strange in this case is the huge number of these operations for a single stored procedure call. It almost looks like the individual lines are protected by the latch instead of the whole procedure.<\/p>\n That’s quite a serious disadvantage and, as it turns out, the main reason for the TSQL inefficiency.<\/p>\n Let’s see now if this excessive serialization code path can be somehow avoided.<\/p>\n There’s a possibility to compile TSQL and store it in a DLL. This feature is referred to as natively compiled stored procedure<\/a><\/p>\n This indeed boosts the throughput. In fact, the efficiency has approached the C implementation.<\/p>\n Also, the scalability has approved with the native compilation.<\/p>\n However, natively compiled procedures impose some quite serious limitations. For instance, they only work with memory-optimized tables – neither conventional nor temporary tables are supported. Neither can they reference conventional stored procedures.<\/p>\n As a consequence, you can’t just compile the procedure and, by doing so, make it run X times faster.<\/p>\n In summary, there is a serious performance and scalability issue with SQL Server TSQL, which is caused by excessive serialization of shared memory access. Not only does Oracle PL\/SQL perform an order of magnitude better, but also it scales perfectly. While natively compiled procedures perform really good in SQL Server, they also come with some serious limitations, which, in my opinion, render them unusable for most conventional use cases.<\/p>\n The scripts for creating stored procedures used in this blog post are in my Github repository<\/a>.<\/p>\n You can use Invoke-Load.ps1<\/a> for starting them concurrently.<\/p>\n Why Oracle outperforms SQL Server Continue Reading <\/p>\n<\/a><\/p>\nStack profiling<\/h1>\n
<\/a><\/p>\nSerialization<\/h1>\n
bp sqllang!CStatement::AddRef \"~.; r rcx; gc\"<\/code><\/pre>\n...\n. 6<\/span> Id: 1bac.1ff4 Suspend: 1 Teb: 000000aa`cb8f2000 Unfrozen\n Start: sqldk!SchedulerManager::ThreadEntryPoint (00007ffc`1eda7c10)\n Priority: 0 Priority class: 32 Affinity: 3e0\nrcx=000001fad58e93b0<\/span>\n.137<\/span> Id: 1bac.1b54 Suspend: 1 Teb: 000000aa`cb906000 Unfrozen\n Start: sqldk!SchedulerManager::ThreadEntryPoint (00007ffc`1eda7c10)\n Priority: 0 Priority class: 32 Affinity: 1f\nrcx=000001fad58e93b0<\/span>\n. 6<\/span> Id: 1bac.1ff4 Suspend: 1 Teb: 000000aa`cb8f2000 Unfrozen\n Start: sqldk!SchedulerManager::ThreadEntryPoint (00007ffc`1eda7c10)\n Priority: 0 Priority class: 32 Affinity: 3e0\nrcx=000001fad58e89c0<\/span>\n.137<\/span> Id: 1bac.1b54 Suspend: 1 Teb: 000000aa`cb906000 Unfrozen\n Start: sqldk!SchedulerManager::ThreadEntryPoint (00007ffc`1eda7c10)\n Priority: 0 Priority class: 32 Affinity: 1f\nrcx=000001fad58e89c0<\/span>\n. 6 Id: 1bac.1ff4 Suspend: 1 Teb: 000000aa`cb8f2000 Unfrozen\n Start: sqldk!SchedulerManager::ThreadEntryPoint (00007ffc`1eda7c10)\n Priority: 0 Priority class: 32 Affinity: 3e0\nrcx=000001fad58e93b0\n.137<\/span> Id: 1bac.1b54 Suspend: 1 Teb: 000000aa`cb906000 Unfrozen\n Start: sqldk!SchedulerManager::ThreadEntryPoint (00007ffc`1eda7c10)\n Priority: 0 Priority class: 32 Affinity: 1f\nrcx=000001fad58e93b0<\/span>\n. 6<\/span> Id: 1bac.1ff4 Suspend: 1 Teb: 000000aa`cb8f2000 Unfrozen\n Start: sqldk!SchedulerManager::ThreadEntryPoint (00007ffc`1eda7c10)\n Priority: 0 Priority class: 32 Affinity: 3e0\nrcx=000001fad58e89c0<\/span>\n.137<\/span> Id: 1bac.1b54 Suspend: 1 Teb: 000000aa`cb906000 Unfrozen\n Start: sqldk!SchedulerManager::ThreadEntryPoint (00007ffc`1eda7c10)\n Priority: 0 Priority class: 32 Affinity: 1f\n...<\/code><\/pre>\nSELECT memory_object_address \n , pages_in_bytes \n , bytes_used \n , type \n FROM sys.dm_os_memory_objects order \n by memory_object_address\n\nmemory_object_address\tpages_in_bytes\tbytes_used\ttype\n0x000001FAD5880060<\/span>\t32768\tNULL\tMEMOBJ_EXECUTE\n0x000001FAD58EC060\t16384\tNULL\tMEMOBJ_COMPILE_ADHOC<\/code><\/pre>\nNatively compiled stored procedures<\/h1>\n
<\/a><\/p>\n<\/a><\/p>\nSummary<\/h1>\n
Appendix<\/h1>\n
References<\/h1>\n
\n