December 3, 2008 at 11:51 am
Since upgrading to SP2, I've had a really strange problem with 1 application which connects via ODBC using MFC C++ (I don't have the source code to this). This is a Windows Service.
Usually the service starts without any issues (and has for the last 4 years). Now once I close the service and restart it, it takes a long, long time and eventually times out. I litterally have to install the service on another computer to get it to start.
The queries that it runs are very short, yet take a very long time. I discovered this by using the Profiler.
I am running SQL Server 2005 (9.00.4028). After a while I will use the same machine that would start early, and it will work fine.
The queries that take forever run fine within SQL Query analyzer on the same PC that is taking forever.
I am very confused, and my management team is very frustrated.
Thanks,
Dave
December 3, 2008 at 12:10 pm
Are there any other services that it is dependent upon that need to be restarted/refreshed?
December 3, 2008 at 12:15 pm
Nothing else requires a reset. It's really strange, it gets hung up on a "select * from tableXYZ" and there are only 5000 rows of data in it. Running it crazy fast anywhere, it just seems to be this application. The developper says that it's a straight ODBC connection and that if the SQL Server is taking 30 seconds to run a query that should only take a few milliseconds that it is not his problem.
The tables aren't fancy, they don't have any triggers, views or anything else that may add complexity to running a query.
December 3, 2008 at 2:39 pm
You should check for Blocking, Recompilation and table statistics problems.
* Noel
December 3, 2008 at 3:53 pm
What kind of wait states is the SQL process getting?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 4, 2008 at 5:52 am
First, thanks for your responses. I'm going to read up on Blocking. One quick question though, can this affect performance on just 1 thread? Other connections work fine, just this one is affected?
Thanks Again.
December 4, 2008 at 6:32 am
Check the connection settings between the two, especially the ANSI settings. I wouldn't be surprised if they're different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2008 at 6:39 am
What I am wondering is if your Service process is stalling the output from its SQL Session. That could cause it to act like this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply