We're looking at converting several normal T-SQL Stored Procedures into Natively Compiled SPs for performance.

The SPs use a ton of #tmp tables - select into, delete from, update from, and several unsupported constructs like SUM/MAX/ROW_NUMBER OVER PARTITION BY, WITH, LIKE etc.

We have figured workarounds for many of these :
Select into - Create table type ahead of time
Delete from - use while loop as cursor

I was able to work around SUM/MAX OVER PARTITION BY.. but don't know how to do it for ROW_NUMBER OVER PARTITION BY. Is there a way ?

Also, has anyone done large-scale conversion of SPs to Natively-Compiled and any roadblocks you encountered ?

  • Are your procedures sufficiently complex such that they would benefit from native compliation? And that the performance gains you might attain are worth the requisite tradeoffs with regard to TSQL surface area? – NedOtter Dec 31 '17 at 22:11
  • @NedOtter, I don't know and I'm trying to find out. We're dealing with improving performance by order of milliseconds/seconds, so trying to squeeze every bit by trying Natively Compiled. We won't know how much we'll gain until we do it. – d-_-b Dec 31 '17 at 22:19
  • @NedOtter, btw, i have been looking through your blog - do u have a list of unsupported constructs and their corresponding workarounds ? – d-_-b Dec 31 '17 at 22:27
  • I don't have a list of unsupported constructs, but Microsoft has this: docs.microsoft.com/en-us/sql/relational-databases/… As an example, if your bottleneck was tempdb, and not CPU cycles, then native compilation won't improve performance. In-Memory is a study unto itself, with the requisite gotchas. For native procs, data type conversion and the way you call the proc can have serious performance impact. – NedOtter Dec 31 '17 at 23:23
  • Is your workload primarily read or write? In-Memory doesn't do much to scale read workloads, but does a lot to scale write workloads, assuming you have the right kind of bottlenecks. Would you have cold data on-disk and hot data in-memory? If so, how will you handle the fact the FKs cannot span on-disk/in-memory? – NedOtter Dec 31 '17 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.