Stored procedure oddity

Question

Stored procedure oddity

kevlray

SSCommitted

Points: 1908
More actions
April 13, 2010 at 10:17 am

#221320

We recently had a database moved to SQL Server 2008 from SQL Server 2005. There is a stored procedure that gets called from a web page. For a number of years that stored procedure worked like a charm. In the past couple of weeks users noticed that the reponse time for the web page got real bad. Upon investigating, it showed that a particular stored procedure, once it ran it took all of the CPU #1 time and the task never ended (the users got there results). Now the stored procedure is pretty simple (just a select statement), the only complicated part is that is built the where clause based on whether or not parameters were passed to it (I got the idea from this site on how to do it).
Any how the DBA said that I should have used INNER JOINS instead of just JOINS. Also I was using one alias. Anyhow he made the changes and the stored procedure works fine now.
Thought I would let everyone know.

Viewing 4 posts - 1 through 3 (of 3 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

No offence you your DBA, but they don't know what they're talking about if they think replacing JOIN with INNER JOIN will affect the statement in any way.

If you post the procedure code and table definitions, people may be able to give more targeted advice.

Are you building the where clause dynamically for optional parameters then executing dynamic SQL, or are you using where column=@variable or @variable is NULL?

Have a look here for general performance problems with Catch-All Queries:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Out of date statistics or parameter sniffing are the most likely culprits...

Lowell SSC Guru Points: 323518 More actions · Answer 2

I agree with Howard;

I'll bet it's parameter sniffing with out of date statistics also, and the recompile of the procedure because you made some text changes(but no functional change: INNER JOIN = JOIN) fixed it for now, but it will happen again when statistics get out of date.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kevlray SSCommitted Points: 1908 More actions · Answer 3

I was building the clause dynamically. It was working fine for about three years, it just starting acting up a couple of weeks ago. Unfortunately I do not have access to the stored procedure anymore. The DB is static and is used by an internal web site.