Click here to Skip to main content
13,895,719 members
Click here to Skip to main content
Add your own
alternative version


2 bookmarked
Posted 7 Jan 2013
Licenced CPOL

SQL Server: A Query Slow in SSMS, Fast in Application, WHY?

, 9 Jan 2013
Rate this:
Please Sign up or sign in to vote.
SQL Server: A Query Slow in SSMS, Fast in Application, WHY?

Today, a colleague asked me, why his simple select query was taking around 3000ms (3 seconds) to execute while, the same query was quite fast when executed from application.

The answer is simple: SQL Server Management Studio use RBAR - Row By Agonizing Row method to fetch rows and inform row by row to SQL Server that row is received while on other hand, application which doesn’t use RBAR method, informs once after whole batch is received and reluctantly is fast as compared to SSMS or those applications which use RBAR method.

To confirm that query is running slow just because of RBAR factor, I have used extended events for single session waits analysis, a well defined method by Paul Randal. Output was as follows:

NETWORK_IO is basically ASYNC_NETWORK_IO, when working with extended events. According to BOL "Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server."

But you can find a more proper definition for this type of wait on Karthick PK’s Blog. He states that "When a query is fired, SQL Server produces the results, places it in output buffer and sends it to client/Application. Client/Application then fetches the result from the Output buffer, processes data and sends an acknowledgement to SQL Server. If client/Application takes a long time to send acknowledgement, then SQL Server waits on ASYNC_NETWORK_IO (SQL 2005/2008) or Network_IO (SQL 2000) before it produces additional results."

Hence it has been proved that our query delay was just because of NETWORK_IO wait (2870ms out of total 3000ms) and we were on the same machine where SQL Server was installed so there are no chances of any network problem and it's only RBAR method of SQL Server Management Studio which was causing this delay.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD ( based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
arid0813-Jan-13 23:03
grouparid0813-Jan-13 23:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190306.1 | Last Updated 9 Jan 2013
Article Copyright 2013 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid