Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

Writing Dynamic Stored Procedure Expand / Collapse
Author
Message
Posted Tuesday, June 2, 2009 3:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
TheSQLGuru (6/2/2009)Perhaps because they found that their poorly written code (IS NULL OR constructs) required it to perform acceptably?!?

Still not sure about that being the issue though. Can you restore a backup of your database (probably pretty darn big given the table sizes you listed) and then set forced parameterization off and see if you get the same query plans?? Or maybe just change production for a few minutes off hours (if doable obviously)?


More likely just the way they call the procedures - or, it could be the fact that they have a lot of cursors in their code :)

As for restoring a copy - not going to be possible at this time. I don't have the storage to restore another copy of a 350GB database, which is also copied for the mirror and also a copy available for the test system. At least, not at the moment.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #727770
Posted Tuesday, June 2, 2009 4:43 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 40,185, Visits: 36,592
Jeffrey Williams (6/2/2009)
Gail, I really don't want to argue this point - but I am having a small problem here. I have a query where I use this construct and include the with recompile option, and the actual execution plans for different criteria all use index seeks where appropriate.


SQL 2008?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #727790
Posted Tuesday, June 2, 2009 5:35 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
Nope - SQL Server 2005 SP3 and had the same experience on 2005 SP2 CU7.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #727810
Posted Tuesday, June 2, 2009 6:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 23, 2013 5:54 PM
Points: 1, Visits: 7
What if the age is not null, is empty ? I think this is kind of problem..
Post #727819
Posted Wednesday, June 3, 2009 12:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 40,185, Visits: 36,592
Jeffrey Williams (6/2/2009)
Nope - SQL Server 2005 SP3 and had the same experience on 2005 SP2 CU7.


Odd. I've played around with RECOMPILE on these before and on 2005 I always get 'average' plans. Might be the table size. I don't test on anything under 500 000 rows



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #727909
Posted Wednesday, June 3, 2009 2:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Jeffrey ,

Can you post a full XML query plan of one of your queries that Seek ?.
If you could try to make the query as simple as possible though, just one column / variable.
We may be able to gleam a vital piece of information from that.





Clear Sky SQL
My Blog
Kent user group
Post #727949
Posted Wednesday, June 3, 2009 2:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 40,185, Visits: 36,592
Dave Ballantyne (6/3/2009)
Jeffrey ,
Can you post a full XML query plan of one of your queries that Seek ?.
If you could try to make the query as simple as possible though, just one column / variable.
We may be able to gleam a vital piece of information from that.


I'd also like to see the plans, but with at least 2 columns/variables in the where and, if possible, a couple of different plans based on different parameters passed.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #727958
Posted Wednesday, June 3, 2009 2:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 40,185, Visits: 36,592
TheSQLGuru (6/2/2009)

alter PROCEDURE Test4 ( @ProdID int = null, @Qty int = null)
AS
select TransactionID
from [Production].[TransactionHistory] -- with (index = [IX_TransactionHistory_ProductID])
where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here!
AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)
GO


Exec Test4 @ProdID = 790

4.16 cost with forced index, 11 IO
0.711 cost without forced index (CI scan), 792 IO

due to the mathematics of the optimizer (i.e. the MUCH higher cost associated with the known-to-be-not-sequential-io index seek/bookmark lookup the query plan cost of seeking/lookup 2 rows is MUCH higher than scaning the entire table despite significantly fewer total IOs.

Gail, I wonder if your larger table would still be more efficient doing the scan than with the forced seek?


It is. The cost of the forced index is way higher and the IOs are slightly higher.

CI Scan
Table 'TransactionHistory'. Scan count 3, logical reads 7367, physical reads 0
Cost 7.19

Forced index
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0
Table 'TransactionHistory'. Scan count 3, logical reads 8109, physical reads 0 -- 2641 key lookups.
Cost 35.02



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #727962
Posted Wednesday, June 3, 2009 5:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 1, 2010 4:19 AM
Points: 39, Visits: 150
good artical..
but it's good useful for the dynamic sql when using 'sp_executesql'
Post #728062
Posted Wednesday, June 3, 2009 9:20 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
Dave Ballantyne (6/3/2009)
Jeffrey ,

Can you post a full XML query plan of one of your queries that Seek ?.
If you could try to make the query as simple as possible though, just one column / variable.
We may be able to gleam a vital piece of information from that.



I'll put together a few execution plans and post the results. The query is fairly simple already and does not return a lot of columns. The join is a bit complex with multiple columns and a date range - but the execution plans are still fairly simple.



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #728307
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse