If you are anything like me then you will have already downloaded and installed the CTP1 release of SQL Server 2014. There are a lot of things of interest here, probably top of most peoples list is Hekaton, the new in memory OLTP transactionally compliant database.
There are some bold claims with hekaton but to add a touch of realism to the mix the MSDN page Support SQL Server Features [for Hekaton] http://msdn.microsoft.com/en-us/library/dn133181(v=sql.120).aspx is an invaluable resource for the ins and outs of the tripwires in the way to a hekaton deployment.
So you downloaded and installed SQL Server 2014 CTP and have run the demo script supplied at http://msdn.microsoft.com/en-us/library/dn133079(v=sql.120).aspx what else do you need to know about hekaton ? Firstly you would be foolish to overlook Kalen Delanys whitepaper and excellent resource http://download.microsoft.com/download/F/5/0/F5096A71-3C31-4E9F-864E-A6D097A64805/SQL_Server_Hekaton_CTP1_White_Paper.pdf. Perhaps less accessible is this http://research.microsoft.com/pubs/193594/Hekaton%20-%20Sigmod2013%20final.pdf paper from Microsoft Research. Im sure that there will lots of caveats and techniques popping up on the web from here on going forwards too.
SQL Server 2014, also features a slew of new extended events, one that caught my eye is “hekaton_slow_parameter_passing” in the Analytics channel. So as the name would suggest there is a ‘fast’ and ‘slow’ way to pass parameters into a natively compile stored procedure.
So lets modify the example provided in the link above to have a parameter:
1 2 3 4 5 6 7
CREATE PROCEDURE ParamTest @col3 integer WITH NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') SELECT col1, col2, col3 FROM dbo.Destination where col3 = @Col3; END GO
And run an XE Session with this event and ‘natively_compiled_proc_execution_started’ ( which as the name implies is raised when a compiled procedure is executed’ in SSMS.
If you call the procedure with :
In the live data window you should now see the call to the stored procedure registered.
So what is involved in a ‘slow’ parameter call ?
ParamTest @Col3 = 1
You should now see:
Yup, naming the parameter has sent the code down a ‘slow’ route. Another reason is ‘parameter_conversion’, so if the data type has had to be converted.
This is simple to see
Is there any real significant performance difference between the two ? Good question, I would say yes as why the ‘fast’ code route. Once I get a good test-bed of data it is something I will certainly try.
It is interesting to think that now this goes against accepted best practice, there is a valid reason for not naming procedure parameters on the call. Also think of the RPC calls, they all specify a parameter name right ?