Tag / SQL2014

by Arvind Shyamsundar

The mysterious ‘MD’ lock type, and why you should stop using sp_lock

Today during some discussions with customers, there was a question about some locks being held by a session. Here is an example reproduced below:

begin tran
select * from Person.Person
where LastName = ‘Singh’

exec sp_lock @@spid

Here is the output:

spid    dbid    ObjId    IndId    Type    Resource    Mode    Status
52    11    0    0    DB                                        S    GRANT
52    11    0    0    MD    14(10000:0:0)                       Sch-S    GRANT
52    11    0    0    MD    14(10001:0:0)                       Sch-S    GRANT
52    1    1467152272    0    TAB                                        IS    GRANT
52    32767    -571204656    0    TAB                                        Sch-S    GRANT

The two rows highlighted in bold in the output were the point of discussion. It was not very apparent as to what those locks were attributed to. So, here is where the power of the newer DMV: sys.dm_os_tran_locks becomes apparent:

select resource_type, resource_subtype, resource_description, request_mode from sys.dm_tran_locks
where request_session_id = @@spid

Here is the output:

resource_type resource_subtype resource_description request_mode
METADATA XML_COLLECTION xml_collection_id = 65536    Sch-S
METADATA XML_COLLECTION xml_collection_id = 65537    Sch-S

Aha! So this made much more sense. So these are metadata locks on XML schema collections. When you look at the Person.Person table, indeed there are two XML columns to which XML schema collections are bound to:

[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
[Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,

When you further reconcile the xml_collection_id from the tran_locks DMV, this is sealed:

select xml_collection_id, name from  sys.xml_schema_collections
where xml_collection_id in (65536, 65537)

Here is the output:

xml_collection_id    name
65536    AdditionalContactInfoSchemaCollection
65537    IndividualSurveySchemaCollection

So, what other types of resources can we expect in the sys.dm_os_tran_locks DMV? If you do some poking around in my favorite catalog view sys.dm_xe_map_values, you will find the answer:

select map_value from sys.dm_xe_map_values
where name = ‘lock_resource_type’
order by map_key

Here is the output:

UNKNOWN_LOCK_RESOURCE
NULL_RESOURCE
DATABASE
FILE
UNUSED1
OBJECT
PAGE
KEY
EXTENT
RID
APPLICATION
METADATA
HOBT
ALLOCATION_UNIT
OIB
ROWGROUP
LAST_RESOURCE

Note: the above output was produced from a SQL 2014 instance, so you may not find all the values in older versions of SQL. Most of the above are easy to understand (for example, Object, Page or Key.)

NOW – there are some others in the above list which are not that easily understood. If you want to hear more, please leave a comment and indicate what exactly you want to know more about! I’ll do my best to explain within the boundaries of what we can share publicly Smile

Advertisement
by Arvind Shyamsundar

Error message “Copying cluster-resource …fssres.dll to C:Windowssystem32fssres.dll failed…” during SQL Server 2014 setup

First and foremost, let me reproduce the complete error message I refer to above: “Copying cluster-resource C:Program FilesMicrosoft SQL ServerMSSQL12.SQL14MSSQLBinnfssres.dll to C:Windowssystem32fssres.dll failed because the destination file exists and is read-only”

Investigation

I received the above error message while setting up a SQL Server 2014 instance side by side with an existing copy of SQL Server 2012. The setup caused the “Database Engine” component to fail and later it had to be removed before re-attempting setup.

The root cause of such setup failures is often quite easy to locate when you look at the setup logs, which are conveniently placed under the “C:Program FilesMicrosoft SQL Server120Setup BootstrapLog” folder on your machine. When I looked at the Detail.txt file from such the problematic server, I could sport the following section:

(01) 2015-01-04 08:09:34 Slp: UpdateClusterResourceAction: Resource type = ‘SQL Server FILESTREAM Share’ ResourceDllName = fssres.dll Source Location = ‘C:Program FilesMicrosoft SQL ServerMSSQL12.SQL14MSSQLBinn’ Target Location = ‘C:Windowssystem32’.
(01) 2015-01-04 08:09:40 Slp: Type ‘SQL Server FILESTREAM Share’ not found. Performing copy directly …
.…
(01) 2015-01-04 08:09:40 Slp: Failed to version-copy file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.SQL14MSSQLBinnfssres.dll’ to ‘C:Windowssystem32fssres.dll’. Exception data is: System.IO.IOException: The process cannot access the file ‘C:Windowssystem32fssres.dll’ because it is being used by another process.
   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite)
   at Microsoft.SqlServer.Configuration.Cluster.UpdateClusterResourceAction.VersionCopy(String source, String target).
….
Microsoft.SqlServer.Configuration.Cluster.UpdateClusterResourceException:  Copying cluster-resource C:Program FilesMicrosoft SQL ServerMSSQL12.SQL14MSSQLBinnfssres.dll to C:Windowssystem32fssres.dll failed because the destination file exists and is read-only. —> System.IO.IOException: The process cannot access the file ‘C:Windowssystem32fssres.dll’ because it is being used by another process.
….
Microsoft.SqlServer.Configuration.Cluster.UpdateClusterResourceAction.VersionCopy(String source, String target)

From the above, it is quite clear that the FSSRES.DLL already exists previously. Now, when I checked the file version it was clear that the existing version is of version SQL Server 2012:

image

On executing a TASKLIST /M fssres.dll command it quickly became clear that the process which has this DLL already loaded is the RHS.exe for the existing instance of SQL 2012. That instance had an Availability Group already configured hence the RHS.exe was loading the DLL.

Given that the DLL was already loaded by the RHS.exe, there was no way for SQL setup to update it. That is why the SQL 2014 instance setup failed.

Workaround

The workaround I had to use in the above case was to firstly remove the SQL 2014 Database Engine instance; shutdown the cluster service on the failed node and then re-attempt the SQL 2014 setup. Less than ideal, but till the above issue is handled in the setup, we have very little options. I have logged also a Connect item requesting the Product team to investigate this issue. If you think this issue should be fixed, please vote it up on Connect!

by Arvind Shyamsundar

Error message “Invalid column name ‘uses_native_compilation’” when scripting objects in SQL 2014 CTP1

Due to the In-Memory OLTP enhancements in SQL 2014, there is metadata support added in the form of a new column: uses_native_compilation in the sys.all_sql_objects catalog view. When you use SMO to retrieve a database object (typically when you script it using the SSMS Generate Scripts wizard, for example,) it retrieves properties from such catalog views to populate the corresponding SMO properties such as IsSystemObject and IsNativelyCompiled.

Unfortunately the code in the CTP1 (after all, it is non-production ready status for a good reason Smile) has an issue because it does not consider that IsNativelyCompiled property is absent from the catalog views for older releases of SQL Server. You may see this today as an error when scripting objects from Management Studio 2014, or if you directly use SMO to script objects as well. Note that this issue is only when connected to SQL 2012 or SQL 2008; scripting objects for SQL 2014 instances will work just fine.

FYI the relevant portion of the call stack might look like this:

Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. 
System.Data.SqlClient.SqlException: Invalid column name ‘uses_native_compilation’.

… at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveSprocDependencies(List`1 schemaboundList)

So if you are getting this issue, please vote on the Connect Bug I filed for this issue, so that the concerned team in the product group can prioritize this and fix it. Here is the link again in plain text format:

https://connect.microsoft.com/SQLServer/feedback/details/792637/system-data-sqlclient-sqlexception-invalid-column-name-uses-native-compilation-when-scripting-older

by Arvind Shyamsundar

SQL 2014 In-Memory OLTP ‘Hekaton’: training videos and white papers

Personally, the feature I am most excited about in SQL Server 2014 is the In-Memory OLTP (codenamed ‘Hekaton’) feature. Since SQL Server CTP1 was released, a lot of my interactions with colleagues and customers have revolved around this new feature and the one question everyone has is, how do I get started? Well, here’s a readiness roadmap I have put together to help you!

Videos

Sunil Agarwal, Principal Program Manager from the SQL Server product team, has been one of the key people steering the work towards its eventual public unveiling in CTP1. I’ve been fortunate enough to interact with Sunil on a few occasions and I marvel at his passion and energy. He recently presented three sessions at TechEd 2013 North America, which are now available online, complete with slides and video!

A related set of deep sessions were delivered by Jos de Bruijn (also a key member of the Hekaton PM team) at TechEd Europe 2013. Jos is a PhD and his technical depth and passion to share information is clearly evident – take a look for yourself!

 

In-Memory OLTP Code Samples

Here are some useful samples from MSDN.

 

General Reading

 

Research Papers

All the inside scoops from the team which made it happen!

If you find more resources which you would like to share, please post a comment or contact me through this blog to let me know, I will gladly link them up in this roadmap!

by Arvind Shyamsundar

Error 41342 when creating In-Memory OLTP (‘Hekaton’) filegroup

A Twitter conversation unearthed a specific requirement of using in-memory OLTP features, namely the processor instruction set requirements. The user was trying to create a ‘Hekaton’ database using the sample script from here, and was getting error message 41342:

The model of the processor on the system does not support creating filegroups with MEMORY_OPTIMIZED_DATA. This error typically occurs with older processors.

This error is documented at our (brand new) SQL 2014 Books Online page. According to the page, memory-optimized tables require a processor model that supports atomic compare-and-exchange operations on 128-bit values. In the Intel x86-64 instruction set, such 128-bit atomic operations are accomplished by the CMPXCHG16B (assembly language in case you were wondering Smile) instruction.

With this background, and knowing that the user was running VirtualBox, I thought this is a problem with the CPU emulation code in VirtualBox. Personally, I am fortunate to be running Windows 8 Hyper-V so I would never see this issue!

For VirtualBox, it turns out that we need to adjust the virtualization software settings as per this thread. More information is also available at this link. From my understanding the VirtualBox setting is only effective if the physical hardware allows it as well. In other cases, if you get this error on physical hardware, check if it is a processor which supports the CMPXCHG16B instruction (older AMD processors might not support it, for example.)

Hope this helps!