10/2/2023 0 Comments Query with sqlpro![]() Although the query may return multiple result sets, OPENQUERY returns only the first one. This is subject to the capabilities of the OLE DB provider. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. What is OPENQUERY? According to Microsoft, OPENQUERY executes the specified pass-through query on the specified linked server. One of the easiest ways to fix performance problems with a Linked Server is to run it via OPENQUERY. Needless to say, it will very hard to improve the query with that execution plan. Fig #5 Fig #6Īs you might notice, 93% of the query execution cost goes to a “mysterious” remote query. Now if were to review Statistics IO (see Fig #5) and execution plan details (see Fig #6), this is what we are going to see. Meaning, your local SQL Server is clueless on remote table indexes and statistics, so it might use incorrect joining mechanism and might be grossly inefficient.įor example, if were tasked to join all January 2013 orders between OLTP and OLAP tables and compare revenue per product while showing top 5 contributors, we might build the following query (see Fig #4) to achieve that goal. ![]() When SQL Server runs query with Linked Server, it will use the least optimal execution plan due to lack of knowledge of those remote tables. The main problem occurs when you are running queries against a remote server, which is not healthy. While joining few small tables might not add any noticeable pressure to the server, joining 3-5 fairly large remote tables might introduce locking and blocking and increase the run-time into minutes. While Linker Server feature is making it easy to join tables between two or more different servers, it’s not free and it comes with a price tag – performance overhead. A small price to pay for lots of convenience. You just to setup a Linked Server (see Fig #2) and add a Linked Server reference in front of the 3 part-naming convention (see Fig #3) as well security context with other minor things. Linked server is effectively removing the need for a solution that will move and synchronize the data. If were trying to join a local table in WideWorldImporters transactional database Figure #1 With very little to nothing code changes, you can suddenly join multiple tables between local and remote/cloud servers. Linked Server is a pure convenience feature. This blog post is going to discuss the pros and cons of linked servers as well as how to reduce the cons using an OPENQUERY.Īccording to Microsoft, Linked servers enable the SQL Server Database Engine and Azure SQL Database Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL Server. This is where Linked Server comes handy, especially at the data discovery phase where building a prototype needs to happen quick and there no time to adhere to the best practices. Most likely this is going to change in a future (Azure VM and AWS EC2 have full support) and you will be tasked to join data between multiple database servers or even between different RDBMS systems, like for example: all the transactional sales data is stored in SQL Server, but all the analytical sales data is stored on another SQL Server server (this could be even MySQL). You are fortunate to source all your data needs from one single database server and that is fine or maybe you are consuming your SQL Server needs from Azure Single Database or AWS RDS (both public cloud solutions don’t support linked server out of the box). ![]() If you are not familiar with a Linked Server concept in SQL Server, don’t worry, you probably didn’t have a need for that yet.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |