Stored procedure exec taking extremely long

Question

Stored procedure exec taking extremely long

Viewing 9 posts - 16 through 23 (of 23 total)

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

OomBoom Hall of Fame Points: 3481 More actions · Answer 1

CirquedeSQLeil (2/4/2010)
What does the udf do that could not be done in a stored proc?

it split a comma delimited string into fields. +- 6 fields.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

tvantonder-992012 (2/3/2010)
Thanks for all the help so far. When I try to get an estimate execution plan on just the code it gives me this error:
Msg 208, Level 16, State 0, Line 121
Invalid object name '#Temp'.
Does this mean anything? Can this cause QO to get stuck when I try the SP?

Ah, you're stuck then. You can't get an estimated execution plan because it has to recompile the procedure after creating the temp table.

Can you successfully run the query, even if it's long? If so, using the DMVs to capture the execution plan... Actually, thinking about it, you can try using the DMVs without waiting for it to complete.

SELECT deqp.Query_Plan

FROM sys.dm_exec_requests AS der

CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) as deqp

WHERE session_id = --the session that is running your long running query

This will show you the execution plan for the bad query, while it's running. However, if it's a multi-statement query, you might need to fool around a bit to identify all the plans in cache for the various statements within the query.

"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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

You have a lot of stuff in the query that's going to lead to table scans. For example:

AND PAYCODENAME LIKE '%' + @PAYCODE + '%'

Will only allow for table scans. It can't possibly make use of an index, no matter how selective. If you can't actually pass in a meaningful value to PAYCODENAME, then you might want to reconsider the design of the database.

"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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

tvantonder-992012 (2/4/2010)
CirquedeSQLeil (2/4/2010)
What does the udf do that could not be done in a stored proc?
it split a comma delimited string into fields. +- 6 fields.

I would have the proc do the comma split. There are a few threads at SSC on how to do that very thing.

I would recommend that the database be redesigned though. In addition to what Grant has pointed out, the comma delimited field is generally better as a normalized data structure.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

OomBoom Hall of Fame Points: 3481 More actions · Answer 5

thanks for the replies. I did discuss these points with the developer of the report and they are unable to change the way it works. This is a KRONOS database and any changes can cause problems with the actual program.

The suggestions still do not answer my actual question. Why would ad-hoc compile and SP compile with the same code differ this much that SP times out? As far as I can see the problem is with creating the execution plan from the SP side. After running 10 min the execution plan has not been created as yet.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

tvantonder-992012 (2/5/2010)
thanks for the replies. I did discuss these points with the developer of the report and they are unable to change the way it works. This is a KRONOS database and any changes can cause problems with the actual program.
The suggestions still do not answer my actual question. Why would ad-hoc compile and SP compile with the same code differ this much that SP times out? As far as I can see the problem is with creating the execution plan from the SP side. After running 10 min the execution plan has not been created as yet.

As i said

This is because when using adhoc , SQL Server KNOWS the SARG values and will generate an execution plan for those values. In a SP , parameter sniffing aside, SQL server with produce a plan based upon average statistics.

Try this link http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html%5B/quote%5D

Consider these 3 Queries

CREATE NONCLUSTERED INDEX [idxLastName] ON [Person].[Contact]

(

[LastName] ASC

)

INCLUDE ( [FirstName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

select LastName from Person.Contact where LastName = 'Smith'

go

Declare @LastName nvarchar(50)

Select @LAstNAme = 'Smith'

select LastName from Person.Contact where LastName = @LastName

go

Declare @LastName nvarchar(50)

Select @LAstNAme = 'Carl'

select LastName from Person.Contact where LastName = @LastName

go

There is a massive difference between the version with variables and without.

Look at the execution plan for the estimated number of rows.

Clear Sky SQL
My Blog[/url]

OomBoom Hall of Fame Points: 3481 More actions · Answer 7

I understand that it will take longer to create the exec plan but should it take this long? I have been running the execute SP for 55 min now and if I look at

SELECT deqp.Query_Plan

FROM sys.dm_exec_requests AS der

CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) as deqp

WHERE session_id = 113 -- from activity monitor

it returns null.

Is this correct

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Have you tried to alter the proc to adjust for parameter sniffing?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

OomBoom Hall of Fame Points: 3481 More actions · Answer 9

Yes I fixed the SP and it is working fine now. Are you saying that SQL gets stuck on par sniffing for 3 hours? Thats how long I ran the SP(old one) on Friday and it did not give an execution plan.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>