Archive

Archive for May, 2016

Microsoft PSS – SQL 2016 Series: It just runs faster | May updates

May 31, 2016 1 comment

Microsoft PSS Engineers have built a series on “It Just Runs Faster”!

“In the Sep 2014 the SQL Server CSS and Development teams performed a deep dive focused on scalability and performance when running on current and new hardware configurations. The SQL Server Development team tasked several individuals with scalability improvements and real world testing patterns. You can take advantage of this effort packaged in SQL Server 2016. – https://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/”

SQL 2016 will run faster in many ways, without changing code.
 

The series kickoff is here:
https://blogs.msdn.microsoft.com/psssql/2016/02/23/sql-2016-it-just-runs-faster-announcement/
 

Previous updates:

https://sqlwithmanoj.com/2016/03/28/sql-2016-series-it-just-runs-faster-feb-march-updates/

https://sqlwithmanoj.com/2016/04/30/microsoft-pss-sql-2016-series-it-just-runs-faster-april-updates/
 

The announcements so far in May are:

SQL 2016 – It Just Runs Faster: AlwaysOn Parallel Compression / Improved Algorithms
https://blogs.msdn.microsoft.com/psssql/2016/05/03/sql-2016-it-just-runs-faster-alwayson-parallel-compression-improved-algorithms/

SQL 2016 – It Just Runs Faster – AlwaysOn AES-NI Encryption
https://blogs.msdn.microsoft.com/psssql/2016/05/05/sql-2016-it-just-runs-faster-alwayson-aes-ni-encryption/

SQL 2016 – It Just Runs Faster: In-Memory Optimized Database Worker Pool
https://blogs.msdn.microsoft.com/psssql/2016/05/10/sql-2016-it-just-runs-faster-in-memory-optimized-database-worker-pool/

SQL 2016 – Leverages On Demand MSDTC Startup
https://blogs.msdn.microsoft.com/psssql/2016/05/12/sql-2016-leverages-on-demand-msdtc-startup/

SQL 2016 – It Just Runs Faster: XEvent Linq Reader
https://blogs.msdn.microsoft.com/psssql/2016/05/18/sql-2016-it-just-runs-faster-xevent-linq-reader/
 

Check my blog posts on most of the new features released in SQL Server 2016 here
https://sqlwithmanoj.com/sql-server-2016-articles/


Categories: SQL Server 2016 Tags:

SQL Server CROSS APPLY and OUTER APPLY usage – MSDN TSQL forum

May 29, 2016 Leave a comment

–> Question:

I need to see two small scenario when people should use CROSS APPLY and OUTER APPLY.

Please discuss the scenario with code and example.

Thanks !
 

–> My Answer:

CROSS APPLY acts like an INNER JOIN, and OUTER APPLY acts like a LEFT OUTER JOIN.

–> The APPLY clause (irrespective of CROSS/OUTER option) gives you flexibility to pass table’s columns as parameters to UDFs/functions while Joining while that table. It was not possible with JOINS. The function will execute for each row value passed to the UDF as parameter. But the JOIN works as a whole set.

Check the blog post on CROSS APPLY vs OUTER APPLY operators, https://sqlwithmanoj.com/2010/12/11/cross-apply-outer-apply/

–> Apart from this you can also use APPLY clause with Tables/SubQueries, like if you want to get top 5 products sold by sales persons, or get top 10 populated Cities from all States.

Check here: Using CROSS APPLY & OUTER APPLY operators with UDFs, Derived-Tables/Sub-Queries & XML data, https://sqlwithmanoj.com/2012/01/03/using-cross-apply-outer-apply-operators-with-udfs-derived-tables-xml-data/
 

–> Answer from Russ Loski:

First let’s start with the use for APPLY.

You would use APPLY if you need to use a column from a table as an argument in a derived table or function. For example, this query from http://blog.sqlauthority.com/2009/08/21/sql-server-get-query-plan-along-with-query-text-and-execution-count/:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'

I need to use columns from dm_exec_cahced_plans to pass to two functions to get rows from those functions. I have to use the APPLY keyword (rather than Join) to be able to do that.

I can do the same with a derived table:

SELECT * 
FROM TableA 
outer apply (
SELECT * from TableB
where TableA.id = TableB.id
) tb2

That is a horrible example (you can do the same using standard join syntax). But there are very rare circumstances where I need to use a column in a where clause in a derived table, but I can’t use a join.

The difference between CROSS APPLY and OUTER APPLY is the difference between INNER JOIN and OUTER JOIN. CROSS APPLY will only return rows where there is a row in both the first table and the second table/function, while OUTER APPLY returns a row if there is a row in the first Table even if the second table/function returns no rows.
 

Ref link.


SQL Server 2012: Error handling for multiple errors – MSDN TSQL forum

May 28, 2016 Leave a comment

–> Question:

I have a stored procedure which steps through a list of transaction log backups and applies them to a DR database with NORECOVERY. As part of our DR test process, it is sometimes possible that the DR database gets ahead of the transaction log list. In this circumstance I get the error:

Msg 4326, Level 16, State 1, Line 8
The log in this backup set terminates at LSN 74000000023300001, which is too early to apply to the database. A more recent log backup that includes LSN 74000000025200001 can be restored.

Msg 3013, Level 16, State 1, Line 8
RESTORE LOG is terminating abnormally.

This is fine and I’m happy to ignore this log and move on to the next one. However there appears to be no way to catch the 4326 error. Wrapping the restore in a TRY … CATCH only identifies the 3013 error number and that could be caused by many issues that I don’t want to ignore. If my TRY … CATCH uses the THROW command, both the 4326 and 3013 errors are displayed so my session clearly has a handle to them both.

My questions are:

1. Can I catch the first error thrown?
2. Can I review all the errors thrown?
3. Can the output from THROW be captured in a variable so I can parse it?
 

–> Answer:

While using RAISERROR it will only catch the last error thrown, it won’t catch and return all the errors.

The new THROW keyword introduced in SQL Server 2012 returns all the errors, check this link: https://sqlwithmanoj.com/2015/02/04/capture-multiple-errors-in-try-catch-by-using-throw-statement/

But I don’t think if there is any mechanism to store both the errors returned by any script as after THROW the execution ends and control is transferred to the client.

Other than using DBCC OUTBUFFER(@@spid), you will need to parse the multiple error details spread through several rows. 

Ref link.


How resolve error OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error – MSDN TSQL forum

May 14, 2016 Leave a comment

–> Question:

Im getting following error:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

I am using the following query:

select * 
from openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=\\abc\k\excel.xlsx',[sheet1$])

driver Microsoft.ACE.OLEDB.12.0 installed and configure properly.

Any help ?
 

–> Answer:

Check this link: https://sqlwithmanoj.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/

You might need to register the ACE OLEDB provider, go thru all the steps I’ve mentioned in my blog, may help.
 

Ref link.


What is external tables for in SQL Server 2016 ? – MSDN TSQL forum

May 1, 2016 1 comment

–> Question:

Running 2016 CTP3.

I would appreciate your personal comments/experience more than links.
 

–> Answer:

External Tables in SQL Server 2016 are to setup the new Polybase feature with SQL Server.

With this new feature (Polybase) you can connect to Azure blog storage or Hadoop to query non-relational data from SSMS and integrate with SQL Server relational tables.

You can check some details here, where I’ve : https://sqlwithmanoj.com/2015/05/29/installing-sql-server-2016-ctp2-and-new-features-in-ssms/

Check here how to setup Polybase: https://msdn.microsoft.com/en-IN/library/mt163689.aspx

 

Ref link.