December 15, 2009 at 2:53 am
- Also keep in mind that calling a sproc without providing the correct parameters (DATATYPE !) can cause bad performing queries as well ! (caused by implicit conversions !!)
- In many cases, the way we execute a sproc using SSMS, is how we suppose the sproc will get used, and that will not necessarily be its actual usage !
Analyse all execution plans generated for your sproc to discover the issues.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 15, 2009 at 4:24 am
GilaMonster (12/15/2009)
It's one of the solutions. I referred you several posts ago to a 3-part series that I wrote on parameter sniffing - http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
Sorry, I missed that in your previous post. I'll check out your blog then. 🙂
Thank you again!
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
December 15, 2009 at 7:00 am
GilaMonster (12/15/2009)
It's one of the solutions. I referred you several posts ago to a 3-part series that I wrote on parameter sniffing - http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/Or, if you'd like some specific and targeted advice, post the query and, if you can get them, execution plans both when it's fast and when it's slow.
Good articles, that gave me some insight in this issue. I was aware of the parameter sniffing concept, but this helped clarify it.
It appears to me that it may be best to use the recompile option on these stored procedures. We've done that one the few earlier cases we've had of this issue, and I can't say we have experienced any noticeably performance degradations.
Do you have any thoughts or prior experience regarding performance issues when using the recompile option?
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
December 15, 2009 at 7:30 am
one-1016367 (12/15/2009)
Do you have any thoughts or prior experience regarding performance issues when using the recompile option?
It's the hammer approach. It'll work, but CPU usage will likely go up (all the compiles). I'd try OPTION (Optimise for) first if you know of a parameter that's common and gives good performance across the board.
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
December 15, 2009 at 9:46 am
GilaMonster (12/15/2009)
one-1016367 (12/15/2009)
Do you have any thoughts or prior experience regarding performance issues when using the recompile option?It's the hammer approach. It'll work, but CPU usage will likely go up (all the compiles). I'd try OPTION (Optimise for) first if you know of a parameter that's common and gives good performance across the board.
If you're lucky, and one particular operation is slowing the SP down with sampling issues, you could also go for the statement level recompile (i.e. OPTION (recompile) within the query statement).
It still is the hammer approach as Gail mentioned, just tends to be a somewhat smaller hammer.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 15, 2009 at 10:48 am
Okay, I'll give it some thought. The stored procedure lists transactions in the system by date and merchant - and depending on the size of the merchant the result can be anything from 10 to 50,000-100,000 rows, so doing optimize for on either date or merchant probably won't make much sense.
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
December 15, 2009 at 11:52 am
You never showed us the code. Question, in the code, are you using table variables??
December 15, 2009 at 11:57 am
Nope, no table variables. I'm not at work at the moment, and I didn't write the stored procedure myself so I don't remember the details. I'll paste the code tomorrow when I get back to work. 🙂
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
December 15, 2009 at 12:02 pm
one-1016367 (12/15/2009)
Nope, no table variables. I'm not at work at the moment, and I didn't write the stored procedure myself so I don't remember the details. I'll paste the code tomorrow when I get back to work. 🙂
Just something to check. I thought of it when you mentioned the number of rows that could be involved. Regardless of how many rows in a table variable, the Query Optimizer treats them as if there is only one row. With thousands of rows, that could eaily cause a problem.
December 16, 2009 at 2:01 am
Here's the stored procedure. (We do not do SELECT * , but I have removed/modified the column names to shorten the sp and avoid exposure (as this is a financial system) )
This is a SP used from our admin system, and thus not in constant use through the actual transactional system, so some additional cpu by adding RECOMPILE to it will probably not be a big issue.
I also note that the id from table2 should of course be put in a variable so that select is run only once. As I mentioned earlier I haven't written this SP myself. 😉
CREATE PROCEDURE [PxTransactionListByDatesAndMerchantID]
(
@Merchant uniqueidentifier,
@Date_From datetime,
@Date_To datetime)
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
SELECT
*
FROM
Table t WITH (NOLOCK)
WHERE
t.Created between @Date_From and @Date_to AND
((t.credit IN ( SELECT Id FROM Table2 WITH (NOLOCK) WHERE Id = @Merchant))
OR
(t.debit IN ( SELECT Id FROM Table2 WITH (NOLOCK) WHERE Id = @Merchant)))
ORDER BY
t.Created DESC
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
December 16, 2009 at 2:10 am
Did you ever test this ?
CREATE PROCEDURE [PxTransactionListByDatesAndMerchantID]
(
@Merchant uniqueidentifier
, @Date_From datetime
, @Date_To datetime
)
AS
BEGIN
SET NOCOUNT ON
-- SET LOCK_TIMEOUT 10000 -- not needed, you are using with (nolock) hints !!!
-- provide index on Created for table Table !
SELECT *
FROM Table t WITH ( NOLOCK )
WHERE t.Created between @Date_From and @Date_to
-- Hope T2.Id is indexed
AND exists ( select 1
FROM Table2 T2 WITH ( NOLOCK )
WHERE T2.Id = @Merchant
and (
-- is this the correct column ???
T2.Id = t.credit
and T2.Id = t.debit
)
)
ORDER BY t.Created DESC
END
- If you can, restrict the date range that can be covered between @Date_From and @Date_to
... Occasionally we discover "heavy" ...
Investigate on the distribution of rows on your t.Created between @Date_From and @Date_to
If someone just selects from '1900-01-01' to '2500-12-31' I would guess your system may suffer some overhead :ermm:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2009 at 2:20 am
No, as I said I haven't written this SP, nor have I attempted to optimize it.
When indexes are used the original SP runs relatively fast, but your example might speed things up even more, I'll give it a try. But doesn't this part require OR to give the same result?
and (
T2.Id = t.credit
and T2.Id = t.debit
)
the LOCK TIMEOUT is a standard "header" on all our SPs, regardless of content to ensure that we don't get any inifinite locks. In the admin part of the system we don't screen the stored procedures that carefully. But I agree that it wouldn't be required in this SP.
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
December 16, 2009 at 2:21 am
one-1016367 (12/16/2009)
No, as I said I haven't written this SP, nor have I attempted to optimize it.When indexes are used the original SP runs relatively fast, but your example might speed things up even more, I'll give it a try. But doesn't this part require OR to give the same result?
and (
T2.Id = t.credit
and T2.Id = t.debit
)
the LOCK TIMEOUT is a standard "header" on all our SPs, regardless of content to ensure that we don't get any inifinite locks. In the admin part of the system we don't screen the stored procedures that carefully. But I agree that it wouldn't be required in this SP.
Indeed, that must be an OR operation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2009 at 2:22 am
Oh, didn't see your other notes:
The date is restricted to maximum one month.
T2.Id is indexed.
The date on T1 is the clustered index.
Thorbjorn Kvam,
Project manager and DBA (design) at Payex (http://www.payex.com)
December 16, 2009 at 2:27 am
one-1016367 (12/16/2009)
Oh, didn't see your other notes:The date is restricted to maximum one month.
T2.Id is indexed.
The date on T1 is the clustered index.
How well organized is your T2.Id index ?
How well organized is your clustering index ? (cluster ratio)
- has your index (table) been rebuild lately ?
- Did you load the data in sequence ?
- Did someone add data that would cause inserts in between existing data (and not just add data to the "end" of the file)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply