May 7, 2010 at 12:04 am
I have a query, such as:
Declare @From datetime, @To datetime
Set @From = '2010-01-01'
Set @To = '2010-01-31'
Select *
From dbo.Deals_vw
Where EnteredDate between @From and @To
Go
That runs fine and returns in a few seconds.
When I place the same query inside a SP:
Create procedure dbo.DealsByEnteredDate
@From datetime,
@To datetime
As
Select *
From dbo.Deals_vw
Where EnteredDate between @From and @To
Go
and call it:
Exec dbo.DealsByEnteredDate @From = '2010-01-01', @To = '2010-01-31'
it takes minutes to return.
The same thing happens when dynamic SQL is used:
exec sp_executesql N'Select *
From dbo.Deals
Where EnteredDate between @From and @To',N'@To datetime,@From datetime',@To='2010-01-31',@From='2010-01-01'
I've tried to Google an answer and this article at StackOverflow is the best I've found. It discribes the same thing I'm getting but the solution does not work for me.
I'm using MS-SQL 2005 Standard Edition, SP2 (build 3790).
- EBH
If brute force is not working you're not using enough.
May 7, 2010 at 1:21 am
It does sound like a parameter sniffing or statistics issue, if you have followed the advice in the SO question it should of sorted it.
Can you post the execution plans of both queries ?
May 7, 2010 at 6:59 am
I agree this sounds like parameter sniffing.
As an experiment, you can create local variables within your procedure and then use them in the WHERE clause. You should see a difference in performance.
But as Dave says, get the execution plans (actual, not estimated) and check those out.
"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
May 7, 2010 at 4:12 pm
Please post the actual execution plans of both the fast and slow run.
May 9, 2010 at 12:17 am
try to use WITH RECOMPLIE option with SP.might be OFF exec paln be reason of slowness
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 10, 2010 at 12:55 am
[font="Courier New"]I think U R using a view to query ur results.
while creating a view use nolock in ur create view statement, and then try it. for ex:
CREATE VIEW ABC_VW AS SELECT * FROM ABC(NOLOCK)
Just try this .....
[/font]
May 10, 2010 at 12:59 am
sk.sarim (5/10/2010)
[font="Courier New"]I think U R using a view to query ur results.while creating a view use nolock in ur create view statement, and then try it. for ex:
CREATE VIEW ABC_VW AS SELECT * FROM ABC(NOLOCK)
Just try this .....
[/font]
Why do you think replacing SP with view will help here ?
Additionally sql optimizer treat view as a query while executing it.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 10, 2010 at 1:12 am
sk.sarim (5/10/2010)
[font="Courier New"]I think U R using a view to query ur results.while creating a view use nolock in ur create view statement, and then try it. for ex:
CREATE VIEW ABC_VW AS SELECT * FROM ABC(NOLOCK)
Just try this .....
[/font]
Nothing that has been posted so far would imply a (b)locking issue.
Besides, how do you know that the OP is ok with dirty reads ?
May 10, 2010 at 1:19 am
sk.sarim (5/10/2010)
[font="Courier New"]I think U R using a view to query ur results.while creating a view use nolock in ur create view statement, and then try it. for ex:
CREATE VIEW ABC_VW AS SELECT * FROM ABC(NOLOCK)
Just try this .....
[/font]
So you want to exchange slow performance for potentially still slow performance with a possibility of incorrect data? Nice.
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2010 at 12:48 pm
use
option keepplan
or somthing like that
i think the problem is that sql server tries to generate an execution plan, aby trying to estimate row size and row count
Use perfmon to find out disk usage during execution
Also try to rebuild your indexes on the base table(s) used by the view
May 10, 2010 at 12:54 pm
kastros.george (5/10/2010)
useoption keepplan
or somthing like that
i think the problem is that sql server tries to generate an execution plan, aby trying to estimate row size and row count
Use perfmon to find out disk usage during execution
Also try to rebuild your indexes on the base table(s) used by the view
But, if you use keepfixedplan and it generates a bad plan first, you're in serious trouble.
"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
May 10, 2010 at 11:58 pm
Grant Fritchey (5/10/2010)
kastros.george (5/10/2010)
useoption keepplan
or somthing like that
i think the problem is that sql server tries to generate an execution plan, aby trying to estimate row size and row count
Use perfmon to find out disk usage during execution
Also try to rebuild your indexes on the base table(s) used by the view
But, if you use keepfixedplan and it generates a bad plan first, you're in serious trouble.
You are absolutely right...
But in practise, this is something you can control....
Furthermore for a simple view (joining 2-3 tables) the execution plan will 99% of time be the same.....
Anyway the root cause of the problem seems to be Data Page fragmentation...
You can use this tool http://ssmsallocation.codeplex.com/ ** to see if your table is fragmented at data page level....
If it is you can "defrag" either by rebuilding the indexes on him or by creating a clustered index....
After that row count and row size will be much faster -> the time to create an execution plan will significally decrease....
**the tool seems to crash (net framework crash) when you try to execute it more than one time. It also uses a lot of server resources so don't use it on your production server when users are working..
May 11, 2010 at 1:12 am
kastros.george (5/10/2010)
Furthermore for a simple view (joining 2-3 tables) the execution plan will 99% of time be the same.....
Over-generalisation. Completely depends on the view.
Anyway the root cause of the problem seems to be Data Page fragmentation...
Why do you say that? There's no evidence that there's a fragmentation problem. The reported symptoms (fast as a query, slow as a procedure) is indicative of parameter sniffing.
You can use this tool http://ssmsallocation.codeplex.com/ ** to see if your table is fragmented at data page level....
Why do you want a tool to see if a table/index is fragmented? Select * from sys.dm_db_index_physical_stats works just fine and doesn't sometimes crash.
After that row count and row size will be much faster -> the time to create an execution plan will significally decrease....
Index rebuild won't do anything to change the time the optimiser needs to generate a plan. It may make the query faster, depending whether the query was retrieving data from disk or memory, depending whether the storage engine was issuing scans or seeks and depending how much data is in the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2010 at 1:25 am
Select * from sys.dm_db_index_physical_stats
will show you if the index is fragmented Not the Data Pages (with the exception of course of a clustered index), isn't that right ???
Index rebuild seems to Defrag the table at page level....
We had the same issue with a parametrized delete stored procedure...
when the parameter Changed (once every month) SQL tried to create a new exec plan. The base table is 100GB, (100.000.000 rows)...The stored procedure usually runs daily for 5 minutes , every month's 1st it took 1+ hours, and on that time disk idle time is almost 0%.
After using option keeplan the problem was fixed, (temporary fix)
After we defraged the table by rebuilding the indexes we were able to remove option keepplan without experiencing the problem again
...
May 11, 2010 at 1:30 am
kastros.george (5/11/2010)
Select * from sys.dm_db_index_physical_statswill show you if the index is fragmented Not the Data Pages (with the exception of course of a clustered index), isn't that right ???
Clustered index or heap. (index id 0 or 1), which makes sense, seeing as they are the only things that have data pages. (nonclustered indexes have leaf pages)
We had the same issue with a parametrized delete stored procedure...
when the parameter Changed (once every month) SQL tried to create a new exec plan. The base table is 100GB, (100.000.000 rows)...The stored procedure usually runs daily for 5 minutes , every month's 1st it took 1+ hours, and on that time disk idle time is almost 0%.
After using option keeplan the problem was fixed, (temporary fix)
After we defraged the table by rebuilding the indexes we were able to remove option keepplan without experiencing the problem again
I have a feeling your problem was stale statistics and a resulting non-optimal plan. The rebuild would have updated stats, hence fixing the problem. An update stats would have worked too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply