.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

diagnosis techniques for long execution times

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

Hello all,

Was wondering if anyone could share some tips or techniques, documented or not, to help explain what is happening under the hood on a more detailed level.

After running DBCC DROPCLEANBUFFERS and FREEPROCCACHE, I am seeing query execution time well over 1 minute. Subsequent runs are fast, so I suspect physical IO is most likely responsible. Even so, 1 min seems very excessive.

Below are the IO stats and execution plan.


Table 'UserReport'. Scan count 12747, logical reads 38286, physical reads 2254, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Report'. Scan count 1, logical reads 51048, physical reads 7568, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Rows         Executes       StmtText                                                                                                                                       
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
12747        1          select r.ID, r.ordereddate, r.updatedda

View Complete Post

More Related Resource Links

Long execution times for Search pages even after 4th or 5th user has accessed the same page.

Hi Guys We have a Sharepoint 2010 farm with two NLB web fronts ends, a Index server and a two box SQL cluster. The same problem also occurs on a dev box which has Sharepoint 2010 and SQl server running on the same single box. When a user access the search or advanced search page we get long execution times of between 15 and 30 seconds.  The strange thing is that the user will have a slow response the first time, then they are fine for a period of an hour or two and then they hit the slow response again.  Below i have copied info from the developer dashboard.  Does anyone have a suggestion of where to start tackiling this issue. Thanks 1st Run.  (Note this site was accessed by 3 other users first) BeginRequestHandler (0.05 ms) PostAuthenticateRequestHandler (0.06 ms) PostResolveRequestCacheHandler (18.48 ms) GetWebPartPageContent (16.87 ms) GetFileAndMetaInfo (15.57 ms) GetWebPartPageContent (35.25 ms) GetFileAndMetaInfo (34.99 ms) GetWebPartPageContent#1 (89.27 ms) GetFileAndMetaInfo (89.06 ms) Add WebParts (3975.17 ms) Search Box (3974.54 ms) SearchBoxEx.OnLoad (15426.56 ms) SearchBoxEx.HandleContextualScoping (0.01 ms) UserPreference.GetUserPreference (15418.52 ms) UserPreference.GetFromCache (0.04 ms) SearchServiceApplicationProxy.GetUse

Job Execution times / statistics report ...

We have several sites (countries if you may call them) and each site has its own set of ERP databases (Progress database). We have designed ETL layers that are open and which can be invoked for each of these sites. On top of the ETL layers, we scheduled the SSIS packages via SQL jobs for each site and scheduled them to run at various times of the day based on the site operating hours. I want to now present the amount of time it takes to copy data from the ERP system into our SQL 2008 Staging and finally into our DataWarehouse. I can manually right click on the SQL jobs and get the timings, however, I want to automate this report so that we can look at it initially weekly and maybe monthly when things start to run smoothly. Any pointers or ideas? I know I can run SQL Profiler and capture the SQL behind this report, would it actually work? ThanksJagannathan Santhanam

Dealing with timeout on long SP execution


I am executing a stored procedure that takes about 75 seconds to execute. My page times out while it tries to wait for the resultset. I tried adding Connect Timeout=200; pooling='true'; Max Pool Size=200" but it still times out.

I guess I do not necessarily need to wait for the result set. Is it possible to just send the SQL command with something like (making it up:) command.ExecuteOnServerAndDoNotWait  ?


Here is my code:


Using connection As New SqlConnection("Data Source=Rosebud;Initial Catalog=lovebug;Persist Security Info=True;User ID=notME;Password=L0v3ly;Connect Timeout=200; pooling='true'; Max Pool Size=200")
            Dim command As New SqlCommand("exec CalculateDataUsage @Customer,@CNumber,@Month,@Year,@TypeOfEntry,@Usage", connection)
            'CalculateDataUsage] @Customer varchar(50),@CNumber varchar(20), @Month varchar(5), @Year varchar(5), @TypeOfEntry varchar(20), @Usage numeric OUTPUT
            command.Parameters.Add("@Customer", Data.SqlDbType.VarChar)
            command.Parameters.Add("@CNumber", Data.SqlDbType.VarChar)
            command.Parameters.Add("@Month", Data.SqlDbType.VarChar)
            command.Parameters.Add("@Year", Data.SqlDbType.VarChar)

Nested Views execution time, how long does it take to "unpack" the SQL?


We have the case where in a reporting solution for an application it was decided to build a series of reporting views, fair enough. These views are layered, nested several levels deep at times and, not unexpectedly, as our customer's data grows so these views slow...

To complicate matters further, the report SQL may then join several of these top level reporting views to produce the data it needs.

In a recent support request for a slow performing report we ripped the sql out of the report, pasted it into SSMS and while investigating found a join error in the reports SQL that took execution time from ~ 2 minutes down to about 6 seconds...  Regardless of what we did after that we were unable to reduce the time below 6 seconds using our reporting views. Rewriting the SQL against our base tables takes a fraction of a second to return the needed information.

What I need to do is demonstrate where the extra time in getting the results from the views is spent.  My guess is that it is in untangling all the views and then ending up with a less than optimal query plan. Is there anyway to measure how long it takes the server to "unpack" the nested views into a single SQL statement before it can be executed? Knowing how much of the 6 seconds this is accounting for would be helpful to us in our decision making process.



Best Coding Techniques To Improve Performance for SharePoint Applications

As more developers write custom code by using the SharePoint Object Model, they encounter common issues that can affect application performance.

The following areas reflect the main issues encountered by developers as they write custom code by using the SharePoint object model:

§ Disposing of SharePoint objects

§ Caching data and objects

§ Writing code that is scalable

Code Cleanup: Using Agile Techniques to Pay Back Technical Debt


Every codebase contains some measure of technical debt - code that is maddeningly hard to test and maintain. We outline some ideas for identifying and dealing with the high-interest debt in your code.

David Laribee

MSDN Magazine December 2009

Extreme ASP.NET: The Life And Times of an ASP.NET MVC Controller


Here the author dissects the ASP.NET MVC framework and looks at how controllers work. He then explains how the framework interacts with your controllers and how you can influence those interactions.

Scott Allen

MSDN Magazine May 2009

.NET Matters: Ordered Execution With ThreadPool


This month we demonstrate how you can use the ThreadPool to support ordered execution without having to build custom thread pools yourself.

Stephen Toub

MSDN Magazine February 2009

ASP.NET Workflow: Web Apps That Support Long-Running Operations


Integrating workflow into ASP.NET applications means communicating with activities via a workflow queue and hosting the runtime in the global application class. We'll show you how.

Michael Kennedy

MSDN Magazine January 2009

Editor's Note: Hanging Onto The Long Tail


Technology changes at a lightning-fast pace. This month Howard Dierking considers how the rapid changes affect developer priorities and magazine focus.

Howard Dierking

MSDN Magazine September 2008

Concurrency: Tools And Techniques to Identify Concurrency Issues


Efficient parallel applications aren't born by merely running an old app on a parallel processor machine. Tuning needs to be done if you're to gain maximum benefit.

Rahul V. Patil and Boby George

MSDN Magazine June 2008

F# Primer: Use Functional Programming Techniques in the .NET Framework


Here we introduce you to some of the concepts behind the new F# language, which combines elements of functional and object-oriented .NET languages. We then help you get started writing some simple programs.

Ted Neward

MSDN Magazine Launch 2008

WCF Essentials: Discover Mighty Instance Management Techniques For Developing WCF Apps


Instance management refers to a set of techniques used by Windows Communication Foundation to bind a set of messages to a service instance. This article introduces the concept and shows you why you need instance management.

Juval Lowy

MSDN Magazine June 2006

No More Hangs: Advanced Techniques To Avoid And Detect Deadlocks In .NET Apps


You can combat deadlock using a combination of disciplined locking practices which Joe Duffy aptly explains in this article.

Joe Duffy

MSDN Magazine April 2006

Do You Trust It?: Discover Techniques for Safely Hosting Untrusted Add-Ins with the .NET Framework 2


When you allow your application to run arbitrary code through an add-in, you may expose users to unknown code, running the risk that malicious code will use your application as an entry point into the user's data. There are several techniques you can use to reduce the attack surface of your application, which Shawn Farkas discusses here.

Shawn Farkas

MSDN Magazine November 2005

Memory Models: Understand the Impact of Low-Lock Techniques in Multithreaded Apps


Because the use of low-lock techniques in your application significantly increases the likelihood of introducing hard-to-find bugs, it is best to use them only when absolutely necessary. Here Vance Morrison demonstrates the limitations and subtleties low-lock techniques so that if you are forced to use them you have a better chance of using them correctly.

Vance Morrison

MSDN Magazine October 2005

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