Query Performance Degrades

Question

Query Performance Degrades

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

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

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 1

Are you sure the connections are due to pooling?

James Appenzeller SSC Enthusiast Points: 149 More actions · Answer 2

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.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 3

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.

busettymayura SSC Enthusiast Points: 133 More actions · Answer 4

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.

busettymayura SSC Enthusiast Points: 133 More actions · Answer 5

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'

James Appenzeller SSC Enthusiast Points: 149 More actions · Answer 6

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.

James Appenzeller SSC Enthusiast Points: 149 More actions · Answer 7

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.

James Appenzeller SSC Enthusiast Points: 149 More actions · Answer 8

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.

busettymayura SSC Enthusiast Points: 133 More actions · Answer 9

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).