.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

MDX query using linked server - performance question

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

View Complete Post

More Related Resource Links

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

insert query from ms SQL server into a mysql linked server

INSERT into openquery(dbserver1MySQL,'select * from graham.lookup_in_table') select * from NavteqAPAC.dbo.Admin_Names   is giving me the following error! Please help me   OLE DB provider "MSDASQL" for linked server "dbserver1MySQL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.49-community]Commands out of sync; you can't run this command now". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "dbserver1MySQL" could not INSERT INTO table "[MSDASQL]".

How to calculate a SQL Server performance of a query based upon table schema, table size, and availa

Hi What is the best way to calculate (without actual access to a SQL Server) the processing speed of a query (self-inner-join) based upon table schema, table size, and hardware (CPU, RAM, Drives)? ThanksThanks Jeff in Seattle

extra token error on linked server query


I am writing a query across a linked server in SQL Server 2005 and am getting the error below about an extra token.



OLE DB provider "MSDASQL" for linked server "Live_Navision" returned message "[Navision a/s][Navision Attain ODBC Driver]Unexpected extra token: .".

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "select ManitouSerialno from Navision.dbo.InterfaceSecuritySystemsLLC$CustomerTable" for execution against OLE DB provider "MSDASQL" for linked server "Live_NAVISION".

Lee Markum

Query Performance Question



I have a view , it gets the data from almost 20 joins , couple case statements and some subqueries and it is not materialized.

I am loading this view's data into a table so it takes 80 minutes to load with select into clause.

there are almost 100 milyon rows in that view.

here is the view , it will be great if I can take 80 min down to 20-30 min.

 , l =
  case when cd.a is not null then (select name from bbb where dd = caz.dd)
    when cd.a is not null then (select name from cn where d_id = caud.d_id)
    when cd.a is not null then (select name from cn where di_id = cap.d_id)
    else null
 ,m= case when cd.a is not null then (select name from bbb where dd = caz.dd)
    when cd.a is not null then (select name from cn where d_id = caud.d_id)
    when cd.a is not null then (select name from cn where di_id = cap.d_id)
    else null
  cd inner join  cpcs on
  cd.c = cpcs.c
 left outer ........
 left outer ........
 and cdid = (select id from c where class = 'bbbbe' and

vastly different performance of update query on development machine and on server



Using VisualWebDeveloper 2010 on the development machine. All nice and fast.

Using VisualWebDeveloper 2008 on the server. Server runs on Windows Server 2008 and has IIS 7.

It looks like that when 'table', a datatabe filled up in C# code behind, is getting longer (say 100 rows), then the server (whether running the website on IIS or on the local host of VIsualWebDeveloper2008!!!) is really slowing down. Checked it in debug mode and traced it down to


This line takes 5 seconds to execute, with 100 rows. On the development machine, it takes almost no time.

How can that be?

Are there any know issues? Could it be that there is incompatibility between different version of SQL server or something like that? The update works, it is just awefully slow, but only on the server.

What's going on? And how to solve it?

Thank you.

Best regards,


Query regarding SQL Server Performance with ADO


Hi All,

We have a production SQL Server 2005 64bit Standard Edition with SP3. We recently moved the database to a virtual machine, after which we are facing a lot of problems with SQL Queries timing out which has brought down the performance. The SQL Server usually runs with 100% CPU usage. While trying to debug the problem we came across a procedure 'sp_cursoropen' in the SQL Server profiler. 


declare @p1 int

set @p1=180150021

declare @p3 int

set @p3=1

declare @p4 int

set @p4=16388

declare @p5 int

set @p5=287164

exec sp_cursoropen @p1 output,N'select * from StatusMessages',@p3 output,@p4 output,@p5 output

select @p1, @p3, @p4, @p5

The procedure is executing a select query on a table 'StatusMessages'. The profiler says the query is being executed by the 'Operating System'?

When i ran the standard report 'Performance - Top queries by total CPU time & Average CPU time' the results show that the query taking up maximum cpu is the select query on the StatusMessages table 'select * from StatusMessages'. I'm not sure why this query is being executed? We use ADO to connect to the database from our applications. Most of the applications are doing a insert into 'StatusMessages' table. We rarely do a select from this table and when we do there is always a

Linked Server and the Query Builder

I have a Linked Server that I need to generate reports from two databases, is it possible to use the query builder with a linked server?  I have not found a way to do this, I would expect to select Linked Server as the data source.  Thanks.

SQL 2008 to iSeries via linked server (IBMDASQL) - TOP 10 * SQL query returns ALL records


I am querying a iSeries - AS400 database from SQL server 2008 server 64 bit,  via a linked server (using OLE DB Provider - IBMDASQL)

The SQL is select top 10 * FROM <linked Svr>.<Dest server>.<DBName>.<TableName>

Instead of returning top 10 rows, the query returns ALL the rows. I use the top 10 sql query in my "Heart beat monitor" to check the viability of the linked server after a server reboot.  If the test query work

Question about KB 2498818 (linked server issue)

So we have a linked server setup, and will run into random issues pulling data through the linked server setup, getting this error:

The OLE DB provider "SQLNCLI" for linked server "SERVER" reported a change in schema version between compile time ("A") and run time ("B") for table ""database"."schema"."tablename"".

Stumbled across KB 2498818, which fits our situation exactly (using synonyms, blah blah blah) and indicates the bug is fixed in SQL Server 2008 SP2 CU3. I applied CU3 on the server that's pulling data via the linked server connection, but the issue still persists. We can replicate it by doing an index rebuild/reorg.

Anyone else run into this bug? The article isn't quite clear, so before I spend the $$$ to call Microsoft, anyone have any thoughts on whether I would also need to patch the "source" server as well? That's unfortunately a bit more of an undertaking in terms of downtime, testing, etc. than just patching the server that's pulling the data.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

SQL Server Performance

Improving .NET Application Performance and Scalability
SQL: Scale Up vs. Scale Out
Stored Procedures
Execution Plans
Deployment Considerations

Basic Instincts: Increase LINQ Query Performance


Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008

SQL Server: Uncover Hidden Data to Optimize Application Performance


SQL Server 2005 gathers a lot of useful data that you can use to identify areas where database performance can be improved. Here's what you need to improve performance.

Ian Stirk

MSDN Magazine January 2008

Extreme ASP.NET: Tools of the Trade: SQL Server Profiler and Query Analyzer


In my last column, I discussed Microsoft® Application Center Test and how it could be used to measure the performance of your Web application (see Extreme ASP. NET: Tools of the Trade: Application Center Test).

Rob Howard

MSDN Magazine August 2005

IIS 6.0: New Features Improve Your Web Server's Performance, Reliability, and Scalability


As the Web evolves, so does the role that Internet servers play. The Internet has seen the growth of e-commerce, B2B business, collaboration, streaming and other new media, and these new applications require new features to meet increasingly complex needs. Microsoft Internet Information Services (IIS) has many of the features today's mature Web sites need. This article outlines the features in the upcoming version 6.0 and discusses how they promote better scalability, reliability, and performance. Features such as Remote administration, caching, and metabase improvements, as well as custom isolation and security enhancements, make IIS 6.0 the Web server of the future.

George Shepherd

MSDN Magazine March 2002

ATL Server and Visual Studio .NET: Developing High-Performance Web Applications Gets Easier


When developing high-performance applications for the Web, developers often must choose between performance and ease of development. With ATL Server, new with Visual Studio .NET, developers get the best of both worlds. ATL Server uses a tag replacement engine written in C++, provides a simple programming model, and promotes enhanced performance and easy debugging. This article presents an overview of the ATL Server architecture, then creates a basic ATL Server project. It then goes on to explain processing SRF files, HTTP streams, forms, cookies, and header files. Managing session state is also discussed, along with file uploads and performance monitoring.

Shaun McAravey and Ben Hickman

MSDN Magazine October 2000

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend