1)SharePoint Version differs: Version may be lower in destination farm than source farm environment. It can be verified by Go to Central Administration --> System Settings --> Manage Servers in the farm and check version.

Resolution: Version in destination farm i.e. where we restore site collection must be same as source. Upgrade the version by installing necessary Service packs or Cumulative Updates.2) SQL Server Version differs: SQL Instance version may differ in destination farm where we are restoring site collection, from the source farm where we have taken site collection backup.

Resolution: Install correct version of SQL Server with service pack in destination farm.

3) Memory Insufficient: In my case, both SharePoint farm and SQL Server versions were same. So, I checked SQL Server Logs through ULS log viewer and came to know that there was insufficient memory to execute queries in content database.