[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Ad-Hoc Query vastly outperforming Stored Procedure

Posted on 2005-04-21
13
Medium Priority
?
271 Views
Last Modified: 2010-05-18
I have a stored procedure that is not performing well anymore.  The server is busier than it used to be, but CPU never exceeds 50% or so, and this problem replicates when other applications are done and CPU is hovering near 0%.  Before this started, the stored procedure used to return results for small results sets (a few thousand rows) in a couple minutes.  Now it fails to return even after a few hours.

However, when I take the input parameters of the stored procedure, copy them into query analyzer and declare them and set their values, then copy the body of the procedure and run it, it performs beautifully, running in a couple of minutes.  I.e.

create procedure usp_blah
  @pkval int,
  @date1 datetime = null,
  @date2 datetime = null
as
if date1 is null
  set date1 = dateadd(dd, -30, getdate())
if date2 is null
  set date1 = dateadd(dd, -1, getdate())
select manyrows from giantable where somedate between @date1 and @date2
go

exec usp_blah 1
go

It never returns any results, it just stalls forever, even if you let it run for hours (whether or not the server is busy), but if you do this in query analyzer...

declare   @pkval int,
  @date1 datetimel,
  @date2 datetime

set @pkval = 1
if date1 is null
  set date1 = dateadd(dd, -30, getdate())
if date2 is null
  set date1 = dateadd(dd, -1, getdate())
select manyrows from giantable where somedate between @date1 and @date2
go

Then it runs in about two minutes.  The tables are well indexed, the table is not really that giant, the results set just a few thousand rows, the server is blisteringly fast, SQL 2000, dual processor, and enormous disk drives.  The database is a few gigs in size.  CPU, sql recompilations, cache hit ratio all look healthy in process monitor.  Procedure pool?  Memory management?  Buffer cache?  Lost.

0
Comment
Question by:josebrwn
  • 5
  • 3
  • 3
  • +1
12 Comments
 

Author Comment

by:josebrwn
ID: 13839988
One thing I forgot, this is inserting into a temp table.  So the SQL is more like

insert into #temptable
  (manyrows)
  select manyrows from giantable where somedate between @date1 and @date2

select somerows from #temptable
group by somerows

go
0
 
LVL 6

Expert Comment

by:graf0
ID: 13840944
OK, here are just some ideas, nothing known for sure:
1. Bad exec plan. It is possible that the execution plan for the proc is optimised assuming only so many rows in the temp table. Then in reality there are many more rows in temp table and the proc is not handling them optimally. That would not, however, explain why the proc fails to complete no matter how much time you give it. It would be slow but would certainly complete the execution at one moment (few hours possible).
2. Deadlock or blocking lock. This seems more likely. SQL may not detect many kinds of deadlocks and it will not warn you about the lock that takes like forever to obtain. You have two options to see if that is the case:
A. SET LOCK_TIMEOUT 10000; EXEC proc - this will set the lock timeout to 10 seconds, so if your proc can not obtain the lock within 10 second it will fail and return an error message. Then you know that you have a locking problem and can go to:
B. Run the proc and examine the output of sp_who2 and sp_lock to see what is the resource the proc is wating for (may be the lock from A or something else). Once you know it, you can try to resolve the problem. If it is a locking problem you can use locking hints in your procedure code to find your way around it (like NOLOCK or READPAST) ore go from the other end and fix the transaction that is holding the locks for the extended period of time.

If possible, try to compare the excaution plans for the two situations - query-direct in QA and via procedure. Let me know if there are any differences.
HTH
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13841058
Is this a mistype ... line 9?

declare   @pkval int,
  @date1 datetimel,
  @date2 datetime

set @pkval = 1
if date1 is null
  set date1 = dateadd(dd, -30, getdate())
if date2 is null
  set date1 = dateadd(dd, -1, getdate())      <<<<<<<<<<<<<<<<<<<<<< set date2 = dateadd(dd, -1, getdate())
select manyrows from giantable where somedate between @date1 and @date2
go
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:josebrwn
ID: 13841235
oops, yes, mistype, line 9, sorry.  should be
if date2 is null
  set date2 = dateadd(dd, -1, getdate())

I oversimplified the procedure, it actually has many steps after the insert, an update on the temp table, then several deletes and a final select.  But I have this ...

select getdate(), count(*) from #temptable

 ...after every step, and every step is absurdly slow, particularly the first insert.  When run from the command line on a very small client, which only produces 6k rows, the procedure stalls indefinitely but the ad-hoc query returns in just a few seconds.  I end up cancelling the procedure after 1-2 hrs, because it's obviously not going into production like that, so I've never timed it to see how long "never" really is, I should have said "indefinite".  I might try a DBCC FREEPROCCACHE for solution 1 above?  Re. solution 2 I'm the only user in that db, and have been watching sp_lock; it's empty.  I've watched DBCC PROCCACHE too, but nothing leaps out at me.

0
 
LVL 6

Expert Comment

by:graf0
ID: 13841244
Did you try to run the proc with SET LOCK_TIMEOUT 10000? What happens then?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 120 total points
ID: 13842852
Also, consider replacing your temporary table with a variable of type table, as in:
Replace:
insert into #temptable

With:
insert into @temptable
0
 

Author Comment

by:josebrwn
ID: 13861516
Ok, locks aren't an issue, there are none even with threshhold set to 5 sec., and using a table variable instead of a temp table doesn't change anything.  I haven't looked at the query plans yet, will do that later today, but I have found something interesting.

When you run the code as a stored procedure, Latch Wait Time goes to 1000 ms and holds, with about 400 latches queued for the duration of the insert, which takes about 40 minutes.  Sysprocesses shows latch types of PAGEIOLATCH_SH.

When you run the exact same code as ad-hoc sql, Latch Wait time and queued latches remain at 0, but CPU intensifies.  Total run time for the insert drops from 40 minutes, to 2 mnutes.

0
 
LVL 6

Accepted Solution

by:
graf0 earned 140 total points
ID: 13862798
Take a look at this:
"IO latch: The IO latches are a subset of BUF latches that are used when the buffer and associated data page or the index page is in the middle of an IO operation. PAGEIOLATCH waittypes are used for disk-to-memory transfers and a significant waittime for these waittypes suggests disk I/O subsystem issues."
(taken form: http://www.sqldev.net/articles/locklatch.htm )

You may also want to consider a related hotfix:
http://support.microsoft.com/kb/328551/

Let me know if any of those helps - I am very curious about the solution.
0
 

Author Comment

by:josebrwn
ID: 14033199
Ok well learned one knows best after all..  points split.  Converting from temp table to table variable did help, with a lot of wrangling.  Didn't install the patch because not quite apropos but got me to thinking about server configuration and health of the temp db.  Perhaps ours is the only server in the universe that's ever experienced this problem.  Thanks for the memories...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14035999
I realize you are new here, but do yourself a favor and re-read the EE Guidelines regarding grading at:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

Sepecifically this section:
<quote>
C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.

Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.

...
Similarly, the C grade is the lowest that can be given by a member, a fact which should be kept in mind when grading as well.

The use of a C in a vindictive manner is likely to be changed by a Moderator. You may not like the answer you get, and in some cases, and you may not like the way it is delivered, but if it is deemed to be accurate, no less than a B is an acceptable grade.

</quote>

Also this:
Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18

Thanks.
0
 

Author Comment

by:josebrwn
ID: 14037597
Temp table vs. table variable had nothing to do with the question at hand, namely why should the same code ran ad hoc perform better than ran in procedure.  The table variable helped both the ad-hoc and stored procedure perform better but did not address the question at all, why one should out-perform the other.  But since it was a good all around suggestion, albeit unrelated to the question, I gave the answer nearly half the available points.  I thought I was being nice.

I gave a grade of C because this question has not been answered, the problem remains, and the thread was being closed down as abandoned.  So I awarded split points and awarded the lowest grade possible.  If the moderator thinks a better grade is deserved, then by all means they should alter it.


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14038868
>>I thought I was being nice.<<
You are missing the point, it is not a question of being nice.  It is on the other hand a matter of keeping to the EE Guidelines you agree to every time you sign on to this site.  But forget about all that community "stuff", but instead think of yourself and take a look at your grading history.  Everyone else can see it.  Many experts here may think twice before they help you in future.

>>I gave a grade of C because this question has not been answered<<
Again, that is not the spirit of the grading standards.  There are other alternatives.  For example, you can request Community Support reduce the points and still award and A or B.

Good luck.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question