March 6, 2018 at 12:27 pm
Jay@Work - Tuesday, March 6, 2018 12:23 PMLynn Pettis - Tuesday, March 6, 2018 12:20 PMOkay, just curious, what does this return:
SELECT
[t].[charge_ctr]
, [t].[transaction_date]
, SUM([d].[AMOUNT]) AS 'SumAmt'
, [t].[status]
, COUNT(*) AS 'RecCnt'
FROM
[dbo].[nucChargeTransaction] [t]
WHERE
[t].[transaction_ctr] = 50469380
AND [t].[status] IN ('C', 'F')
GROUP BY
[t].[CHARGE_CTR]
, [t].[TRANSACTION_DATE]
, [t].[STATUS];charge_ctr transaction_date SumAmt status RecCnt
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 -60.000000 C 1
(1 row(s) affected)
Is this what the other query returns?
March 6, 2018 at 12:37 pm
Lynn Pettis - Tuesday, March 6, 2018 12:27 PMJay@Work - Tuesday, March 6, 2018 12:23 PMLynn Pettis - Tuesday, March 6, 2018 12:20 PMOkay, just curious, what does this return:
SELECT
[t].[charge_ctr]
, [t].[transaction_date]
, SUM([d].[AMOUNT]) AS 'SumAmt'
, [t].[status]
, COUNT(*) AS 'RecCnt'
FROM
[dbo].[nucChargeTransaction] [t]
WHERE
[t].[transaction_ctr] = 50469380
AND [t].[status] IN ('C', 'F')
GROUP BY
[t].[CHARGE_CTR]
, [t].[TRANSACTION_DATE]
, [t].[STATUS];charge_ctr transaction_date SumAmt status RecCnt
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 -60.000000 C 1
(1 row(s) affected)
Is this what the other query returns?
No, your version did not have the join to nucCharge therefore you were only getting the amount and count for a single transaction. When joined to nucCharge we get the total amount which, if fully paid, would be 0 and we get the count of all the transactions that made up the charge
charge_ctr transaction_date status
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 0.000000 C 5
(1 row(s) affected)
March 6, 2018 at 12:51 pm
Jay@Work - Tuesday, March 6, 2018 12:37 PMLynn Pettis - Tuesday, March 6, 2018 12:27 PMJay@Work - Tuesday, March 6, 2018 12:23 PMLynn Pettis - Tuesday, March 6, 2018 12:20 PMOkay, just curious, what does this return:
SELECT
[t].[charge_ctr]
, [t].[transaction_date]
, SUM([d].[AMOUNT]) AS 'SumAmt'
, [t].[status]
, COUNT(*) AS 'RecCnt'
FROM
[dbo].[nucChargeTransaction] [t]
WHERE
[t].[transaction_ctr] = 50469380
AND [t].[status] IN ('C', 'F')
GROUP BY
[t].[CHARGE_CTR]
, [t].[TRANSACTION_DATE]
, [t].[STATUS];charge_ctr transaction_date SumAmt status RecCnt
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 -60.000000 C 1
(1 row(s) affected)
Is this what the other query returns?
No, your version did not have the join to nucCharge therefore you were only getting the amount and count for a single transaction. When joined to nucCharge we get the total amount which, if fully paid, would be 0 and we get the count of all the transactions that made up the charge
charge_ctr transaction_date status
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 0.000000 C 5
(1 row(s) affected)
You did say that both tables in the original query are the same table, that you are joining back to itself, correct?
March 6, 2018 at 12:58 pm
Lynn Pettis - Tuesday, March 6, 2018 12:51 PMJay@Work - Tuesday, March 6, 2018 12:37 PMLynn Pettis - Tuesday, March 6, 2018 12:27 PMJay@Work - Tuesday, March 6, 2018 12:23 PMLynn Pettis - Tuesday, March 6, 2018 12:20 PMOkay, just curious, what does this return:
SELECT
[t].[charge_ctr]
, [t].[transaction_date]
, SUM([d].[AMOUNT]) AS 'SumAmt'
, [t].[status]
, COUNT(*) AS 'RecCnt'
FROM
[dbo].[nucChargeTransaction] [t]
WHERE
[t].[transaction_ctr] = 50469380
AND [t].[status] IN ('C', 'F')
GROUP BY
[t].[CHARGE_CTR]
, [t].[TRANSACTION_DATE]
, [t].[STATUS];charge_ctr transaction_date SumAmt status RecCnt
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 -60.000000 C 1
(1 row(s) affected)
Is this what the other query returns?
No, your version did not have the join to nucCharge therefore you were only getting the amount and count for a single transaction. When joined to nucCharge we get the total amount which, if fully paid, would be 0 and we get the count of all the transactions that made up the charge
charge_ctr transaction_date status
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 0.000000 C 5
(1 row(s) affected)
You did say that both tables in the original query are the same table, that you are joining back to itself, correct?
You asked "Two are the tables nucChargeTransaction and NUCCHARGETRANSACTION the same tables?"
Which I thought strange 🙂
They are the same tables - however nucCharge and nucChargeTransaction are not. There are a one to many deal
March 6, 2018 at 1:02 pm
Jay@Work - Tuesday, March 6, 2018 12:58 PMLynn Pettis - Tuesday, March 6, 2018 12:51 PMJay@Work - Tuesday, March 6, 2018 12:37 PMLynn Pettis - Tuesday, March 6, 2018 12:27 PMJay@Work - Tuesday, March 6, 2018 12:23 PMLynn Pettis - Tuesday, March 6, 2018 12:20 PMOkay, just curious, what does this return:
SELECT
[t].[charge_ctr]
, [t].[transaction_date]
, SUM([d].[AMOUNT]) AS 'SumAmt'
, [t].[status]
, COUNT(*) AS 'RecCnt'
FROM
[dbo].[nucChargeTransaction] [t]
WHERE
[t].[transaction_ctr] = 50469380
AND [t].[status] IN ('C', 'F')
GROUP BY
[t].[CHARGE_CTR]
, [t].[TRANSACTION_DATE]
, [t].[STATUS];charge_ctr transaction_date SumAmt status RecCnt
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 -60.000000 C 1
(1 row(s) affected)
Is this what the other query returns?
No, your version did not have the join to nucCharge therefore you were only getting the amount and count for a single transaction. When joined to nucCharge we get the total amount which, if fully paid, would be 0 and we get the count of all the transactions that made up the charge
charge_ctr transaction_date status
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 0.000000 C 5
(1 row(s) affected)
You did say that both tables in the original query are the same table, that you are joining back to itself, correct?
You asked "Two are the tables nucChargeTransaction and NUCCHARGETRANSACTION the same tables?"
Which I thought strange 🙂
They are the same tables - however nucCharge and nucChargeTransaction are not. There are a one to many deal
This is the query in your original execution plans:
select t.charge_ctr,
t.transaction_date,
sum(d.AMOUNT),
t.status,
COUNT(*)
from nucChargeTransaction t (nolock) -- This and
INNER JOIN NUCCHARGETRANSACTION d (nolock) -- this look the same to me
ON d.CHARGE_CTR = t.CHARGE_CTR
where t.transaction_ctr = 50469380
and d.status in ('C','F')
group by t.CHARGE_CTR, t.TRANSACTION_DATE, t.STATUS
March 6, 2018 at 1:11 pm
Lynn Pettis - Tuesday, March 6, 2018 1:02 PMJay@Work - Tuesday, March 6, 2018 12:58 PMLynn Pettis - Tuesday, March 6, 2018 12:51 PMJay@Work - Tuesday, March 6, 2018 12:37 PMLynn Pettis - Tuesday, March 6, 2018 12:27 PMJay@Work - Tuesday, March 6, 2018 12:23 PMLynn Pettis - Tuesday, March 6, 2018 12:20 PMOkay, just curious, what does this return:
SELECT
[t].[charge_ctr]
, [t].[transaction_date]
, SUM([d].[AMOUNT]) AS 'SumAmt'
, [t].[status]
, COUNT(*) AS 'RecCnt'
FROM
[dbo].[nucChargeTransaction] [t]
WHERE
[t].[transaction_ctr] = 50469380
AND [t].[status] IN ('C', 'F')
GROUP BY
[t].[CHARGE_CTR]
, [t].[TRANSACTION_DATE]
, [t].[STATUS];charge_ctr transaction_date SumAmt status RecCnt
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 -60.000000 C 1
(1 row(s) affected)
Is this what the other query returns?
No, your version did not have the join to nucCharge therefore you were only getting the amount and count for a single transaction. When joined to nucCharge we get the total amount which, if fully paid, would be 0 and we get the count of all the transactions that made up the charge
charge_ctr transaction_date status
----------- ----------------------- --------------------------------------- ------ -----------
3901610 2017-06-19 00:00:00.000 0.000000 C 5
(1 row(s) affected)
You did say that both tables in the original query are the same table, that you are joining back to itself, correct?
You asked "Two are the tables nucChargeTransaction and NUCCHARGETRANSACTION the same tables?"
Which I thought strange 🙂
They are the same tables - however nucCharge and nucChargeTransaction are not. There are a one to many dealThis is the query in your original execution plans:
select t.charge_ctr,
t.transaction_date,
sum(d.AMOUNT),
t.status,
COUNT(*)
from nucChargeTransaction t (nolock) -- This and
INNER JOIN NUCCHARGETRANSACTION d (nolock) -- this look the same to me
ON d.CHARGE_CTR = t.CHARGE_CTR
where t.transaction_ctr = 50469380
and d.status in ('C','F')
group by t.CHARGE_CTR, t.TRANSACTION_DATE, t.STATUS
My apologies, I made an assumption of what the code would be doing rather than re-checking the original post. Yes it does appear to be joining to itself.
While this has been going I went into SSMS, expanded nucChargeTransaction and selected to rebuild the indexes - despite the highest level of fragmentation on any of them only being 3%
The query now runs instantly just like it does in production!
Is it possible to have a corrupt index or similar?
March 6, 2018 at 2:47 pm
Jay@Work - Tuesday, March 6, 2018 1:11 PMI went into SSMS, expanded nucChargeTransaction and selected to rebuild the indexes - despite the highest level of fragmentation on any of them only being 3%The query now runs instantly just like it does in production!
Is it possible to have a corrupt index or similar?
No, not likely to be a corrupt index, probably just the statistics being out of date or a bad sample of statistics. when you rebuild the indexes, it also rebuilds the statistics, so that's why it magically works better even though little to no fragmentation. Bad statistics can be just as big of a problem for the optimizer that parameter sniffing is. It's all about what the optimizer thinks will happen.
March 6, 2018 at 3:05 pm
Chris Harshman - Tuesday, March 6, 2018 2:47 PMJay@Work - Tuesday, March 6, 2018 1:11 PMI went into SSMS, expanded nucChargeTransaction and selected to rebuild the indexes - despite the highest level of fragmentation on any of them only being 3%The query now runs instantly just like it does in production!
Is it possible to have a corrupt index or similar?No, not likely to be a corrupt index, probably just the statistics being out of date or a bad sample of statistics. when you rebuild the indexes, it also rebuilds the statistics, so that's why it magically works better even though little to no fragmentation. Bad statistics can be just as big of a problem for the optimizer that parameter sniffing is. It's all about what the optimizer thinks will happen.
Hmm I wonder how then? The indexes in dev get optimised weekly by an agent job (Ola Hallegren script). The last automated run was march 2 which claimed success. The last manual run was March 4 when I ran the job to see if this was the issue. This succeeded right before my eyes.
March 6, 2018 at 3:38 pm
Jay@Work - Tuesday, March 6, 2018 3:05 PMHmm I wonder how then? The indexes in dev get optimised weekly by an agent job (Ola Hallegren script). The last automated run was march 2 which claimed success. The last manual run was March 4 when I ran the job to see if this was the issue. This succeeded right before my eyes.
If you are using Ola's scripts - then it should only be selecting indexes that need to be rebuilt. Since this table doesn't need to be rebuilt - the statistics are not getting updated.
Do you have a separate job running to update statistics on a regular basis?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 6, 2018 at 3:43 pm
Jeffrey Williams 3188 - Tuesday, March 6, 2018 3:38 PMJay@Work - Tuesday, March 6, 2018 3:05 PMHmm I wonder how then? The indexes in dev get optimised weekly by an agent job (Ola Hallegren script). The last automated run was march 2 which claimed success. The last manual run was March 4 when I ran the job to see if this was the issue. This succeeded right before my eyes.If you are using Ola's scripts - then it should only be selecting indexes that need to be rebuilt. Since this table doesn't need to be rebuilt - the statistics are not getting updated.
Do you have a separate job running to update statistics on a regular basis?
Good point, I hadn't considered that. You are correct it will rebuild/reorganise/ignore based on the level of fragmentation. When ignored the stats are missed.
I don't but I will now!
That said, when I ran some code to check the date the stats were last updated it said March 2 which would coincide with the index optimisation job.
So who knows *shrug*
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply