.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

Query regarding SQL Server Performance with ADO

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

MDX query using linked server - performance question

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  

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

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,


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

Customizing the Content Query Web Part in SharePoint Server 2007

Walk through how to customize the Content Query Web Part in SharePoint Server 2007 to query content across multiple sites in a site collection and display the results in any way that XSL can support. Learn how to get similar results when customizing the CQWP does not meet your needs.

Designing and Developing High-Performance SharePoint Server 2007 Publishing Sites

Learn about the Web Content Management features in SharePoint Server 2007 that enable organizations to deploy Internet-facing publishing sites, and considerations for developers and designers when designing and building high-performance publishing sites.

Query Performance & Overall Design - SSAS MDX WCF

Hi All, We have a Cube which is to be queried by the Online system , using WCF service. Peroformance of the queries running on the Cube is not really very good , as we have to calculate various percentile (which are all calculated members) on the cube, this howver works well within limits of 5-6 secs for small sample size. But this goes beyond the threshold of 30 secs when the record counts increases. What we have is our SSAS Cubes , and we have WCF Service querying the Cube using ADOMD.NET. This may not be one of the best way to achieve this requirement , but we are kind of completed with development and it may not be feasible to work on another approach, what we are looking for is optimize this design and make it work with in expected time limits of 5-8 secs. Kindly let me know, if i am not clear or if you need any more info to suggest something ! Thanks a lot for your help !   Kindly

SQL Server 2005 Performance Dashboard Reports user context

HI, We are planning on installing the Dashboard reports. But we'd like to know if the Dashboard is run under admin user or the login user who runs the Dashboard reports? Thanks! Kong

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

Query on XML datatype in SQL Server 2008

Hi All, In table I have column with XML datatype. I can able to read the below XML tag. SET   @Title = CAST(@XMLContent.query('data(root/chemicalName)') AS VARCHAR(50)) <root><chemicalName>Sulphuric acid</chemicalName></root> but how can I read (in SQL Server 2008) with multiple rows in it, and how to know the list of child nodes and data of it dynamically? <Root> <Row> < CurrencyCode>GBP</CurrencyCode> < CurrencyName>POUND (STERLING)</CurrencyName> </ Row> <Row> < CurrencyCode>INR</CurrencyCode> < CurrencyName>Rupee</CurrencyName> </ Row></

Full text performance of a certain query

I am doing a full text query on a very simple table and when I include the search term "y5v" in the conditions it slows the query down like 8 to 1. The table is defined as: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblTextSrchData]( [intItemId] [int] NOT NULL, [srchTxt] [varchar](max) NOT NULL, CONSTRAINT [PK_tblTextSrchData] PRIMARY KEY CLUSTERED ( [intItemId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Here is the query: SELECT II.intItemID, KEY_TBL.[RANK] as Score FROM tblItems II INNER JOIN CONTAINSTABLE(dbo.tblTextSrchData,srchTxt, '("capacitor" OR FORMSOF(INFLECTIONAL, "capacitor") OR "capacitor*") AND ("active" OR FORMSOF(INFLECTIONAL, "active") OR "active*") AND ("y5v" OR FORMSOF(INFLECTIONAL, "y5v") OR "y5v*")') KEY_TBL ON KEY_TBL.[KEY] = II.intItemID If I take any of the additional search terms out it improves performance.  If I remove the yv5 search term the results are returned in less than a second. What seems to be the problem is for some reason when I add yv5 it starts doing these sorts after the full text matching.  These sorts take most of the time.  I'm not sure
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