April 4, 2008 at 1:57 pm
Hi All,
I've been trying to figure out why I'm seeing the following behaviour:
We have an application which calls a stored proc to retrieve a report. When the report is run from the app, Profiler shows it taking 75 seconds (which is a ridiculous amount of time for anything on SQL Server unless we're talking million row cross-joins, which is not the case).
When I copy the stored procedure call from Profiler, and paste it into QA, it runs in 9 seconds (still too slow, but there is room for improvement: the code is sloppy, and I didn't write it).
I'm logged in as myself in the application and in QA, Windows security in both cases.
Now, I run it again from the app, thinking SQL Server may have re-optimized along the way. Same 75 second performance. Back-end continues to do it in 9.
I have a trace set up which tracks recompiles. It shows none either way.
Can someone suggest where to begin looking?
Thanks,
Paul
April 4, 2008 at 2:30 pm
Compare the query plans.
[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]
April 7, 2008 at 6:41 am
How do I see the query plans?
April 7, 2008 at 6:56 am
Paul
Does your Profiler trace show the settings that are being used when your application connects to the database (for example SET ANSI_NULLS ON and so on)? If so, put them in front of your query in Query Analyzer and see if that makes it run the same as your application. It may be that a difference in something like the transaction isolation level is making a big difference to the run time.
There are buttons at the top of Query Analyzer that you can press to show the estimated or actual execution plan. As suggested, use those to view the plan for the query that you're executing manually. You can add an event to your Profiler trace to capture the execution plan for the query that's running through the application.
John
April 7, 2008 at 8:39 am
Only difference in SET options is Quoted Identifiers on for QA, off for app.
All others identical.
Now, running same reports, but for a shorter time period: 20 minutes and counting both in QA and application.
No write activity on this server, it's a reporting server.
Indexes rebuilt 2 hours ago...
P
April 7, 2008 at 8:50 am
Indexes rebuilt for the second time in 2 hours.
What took 20 minutes earlier today -- but after the same index rebuild 2 hours ago -- now returns in...29 seconds!!!!!
My head hurts.
April 7, 2008 at 9:00 am
sp_recompile for the proc? Reboot the server?
If a bad plan gets chosen on the stored proc (happens, not sure why sometimes), then you might get stuck with it.
How complicated is this? Are you running with the same parameters? What if you run the exact same code from Profiler that the client app runs, is it slow? Meaning run all the code shown in Profiler from the connection of the app to the proc.
April 7, 2008 at 9:14 am
This weekend, I came upon the definition of kludge. The following is from Wikipedia: In modern computing terminology, a kludge (or often a "Hack") is a 'solution' to a problem, doing a task, or fixing a system (whether hardware or software) that is inefficient, inelegant, or even unfathomable, but which nevertheless (more or less) works. It has been suggested, as a folk etymology, or backronym, that it means klumsy, lame, ugly, dumb, but good enough; which rather captures the point.
That's the proc in question to a 'T' -- there's nothing pretty about it, eventually someone will scream loud enough to make it's re-write a priority.
I will try to do a scientific comparison with the entire traces from both front and back when it breaks down again -- save trace to a table, etc...
Right now, it's "cookin", according to my user, so I ain't touchin' nothin'.:D
I'll update when I can.
April 8, 2008 at 10:15 am
John,
Looks like you hit it with the SET options. Closer inspection revealed that SET ANSI_WARNINGS was ON for QA, OFF from app.
Changing that made the difference.
Steve,
Running everything from the profiler beginning with the connection string made it quite easy to spot.
Thanks all!
P
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply