Post reply

Query Performance Degrades

  • June 7, 2007 at 8:57 am

    #372117

    I have a .NET web application that does a lot of queries for some data transfer.  More specifically, I'll use a datareader to loop through a table and instantiate some of our business objects.  Each object may have to do some reading/updating on the database.  The object basically "saves" itself back to the database and then it continues through the main datareader loop.  Now, this process creates a lot of connections that I can see "sleeping" on the server.  I realize this is due to the connection pooling and shouldn't cause a problem as long as the processes aren't locked and I close all of my connections.   Generally I'm running a month's worth of data and when that month is finished I go the next month.  The problem is that after running a few months the performance really starts to drop, to the point where it's basically taking 10 times longer to transfer one month's worth of data.  I'd appreciate if someone could point me in the right direction. 

    Thanks,

    Jim

  • June 7, 2007 at 12:26 pm

    #711493

    Are you sure the connections are due to pooling? 

     

  • June 7, 2007 at 1:10 pm

    #711504

    I saw a post on a microsoft blog that asked about the "sleeping" connections that are "awaiting command."  The microsoft guy said that these are due to connection pooling.  So I'm assuming mine are too.  Do you know of any way to tell for sure? I'm using a "using" statement in all of my .NET code so they should be closing.

  • June 8, 2007 at 9:11 am

    #711723

    As long as the connection string is identical, connection pooling will be applied.  How many connections do you see on the DB?  Is the application closing it's connections correctly?  If you are able, step through the app and watch the connection count after each DB request.

     

  • June 11, 2007 at 6:53 am

    #711975

    If you are unable to trace back your code and fix the problem.  Try with different connection string for each month and also add "Application Name" in the connection string and give value for "Application Name" combination of month and year.  So that you can findout number of open connection string for each month.

  • June 11, 2007 at 7:02 am

    #711980

    You can use query to know number of open connection for the month Jan 2007 for your typical case.

    SELECT hostname, uid=rtrim(loginame), Program_name=rtrim(Program_name), dbname=db_name(dbid), status=rtrim(status)

    FROM master.dbo.sysprocesses

    where Program_name = 'Jan2007'

     

  • June 11, 2007 at 11:01 am

    #712045

    Thanks for the suggestion about tracking a specific month, I'll definitely try that.  After checking some of the Wait statistics, using the very cool performance report, it seems I'm getting a fair amount of waiting due to Buffer IO.  The specific wait is for PageIOLatch_SH.  I've been trying to look up reasons, but it's taking me a while to really understand the issue.  If someone could maybe explain it to me and make some suggestions I would appreciate it.  Thanks for the help.

  • June 11, 2007 at 12:37 pm

    #712066

    I'm also getting a lot of Parallelism waits.  We only have 1 processor in the server, so I'm not sure I can do anything about this.

  • June 11, 2007 at 1:45 pm

    #712087

    It seems like following the performance report's index suggestions fixed the problem.  My avg disk queue dropped dramatically and things seem fine now.  My only question now is, why would that get worse over time.  I would think that it would be consistently slow.

  • June 12, 2007 at 12:05 am

    #712176

    It looks like some of your queries are scanning complete table than using indexes, validate your code Vs existing indexes.

     

    Get worse over time, because of resources (like disk space, memory etc).

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply