November 1, 2010 at 4:55 pm
mymail.default (11/1/2010)
Craig,I will do it now, but just to make sure I understand:
This change is going to go on the test system right, and being executed on the test system right?
(The test system, BTW has no issues, it runs fine/quick)
Thank you.
Unfortunately, no. This test is something you'd want to run on the production system in a copy of the stored procedure that only you would access (Script out the procedure, and change the name to something like xyz_donttouchmeimtestonly_dba). You'd then feed it your own parameters in a query analyzer and see if it also takes a while.
The reason for this is because you don't care about the results being dirty reads, but your customers do. There's no inserts/updates in this proc overall that you've mentioned (other then the temp tables, they don't count).
However, this is a troubleshoot on the troubled system kind of test. Not what you'd usually prefer but since you've got different results on different systems, you have to run some of these tests on the troubled system.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 12:53 pm
Craig,
I made the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
and unfortunately it did not improve things at all...
Strange! There should be something else causing the issue.
One important thing I see right away querying the sysprocesses table is the SOS_SCHEDULER_YIELD in the lastwaittype
Thanks
November 2, 2010 at 1:48 pm
mymail.default (11/2/2010)
Craig,I made the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
and unfortunately it did not improve things at all...
Strange! There should be something else causing the issue.
Rats.
One important thing I see right away querying the sysprocesses table is the SOS_SCHEDULER_YIELD in the lastwaittype
Thanks
Hold that thought, digging. I'm not familiar with that. Apparently it belongs to the %Signal family. Digging through a SQLCAT white paper on it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 1:53 pm
What kind of CPU pressure is on your production box? It's a standard signal wait, well, for the class of them. I found this query to help measure signal waits in general on your prod:
(Code from : msdn blog )
Select signal_wait_time_ms=sum(signal_wait_time_ms)
,'%signal (cpu) waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
,resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms)
,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
From sys.dm_os_wait_stats
Is the Prod server dedicated to SQL Server, or is it sitting on the same box as IIS or any other heavy apps? Is the CPU overloaded? You need to look at this from a CPU pressure standpoint and figure out why your CPU is bottlenecked.
EDIT: And another alternate solution, from right here in SSC: http://www.sqlservercentral.com/Forums/Topic472093-146-1.aspx. UPDATE STATISTICS on all pertinent tables if you haven't done it in a while.
EDIT2: Alright, I may have decided to go barking WAAAAYYY up the wrong tree here. Can you grab the .sqlplans for QA and PROD runs and attach them here? I'd like to make sure the plans aren't different.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 3:58 pm
Thanks Craig.
The processer is a quad core with 16g ram.
I found the CPU for this stmt in question is 377678
November 2, 2010 at 4:08 pm
mymail.default (11/2/2010)
Thanks Craig.The processer is a quad core with 16g ram.
I found the CPU for this stmt in question is 377678
Which means nothing out of context, other than it's not a small process, which we already knew.
Please review the post above your last one. I will need answers to the majority of those to proceed in assisting you troubleshoot this remotely.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 6:16 pm
I have attached the plans, Craig.
Thank you much.
November 3, 2010 at 11:23 am
These are estimates, not actuals, but it still gives us some idea of what's going on. The actuals are more useful, however.
In the massive statement (SELECT DISTINCT, 5th statement, under the WHILE @pagefirst<@maxDetailCount), near the end:
estimated 942 rows, 9 bytes each.
In the production: 79,199 rows, 9 bytes each.
There is a significant data, or statistics, difference between your two environments. You assure me the data's the same so it must be statistics. Thus, run UPDATE STATISTICS <tablename> WITH FULLSCAN, ALL for each table involved in this process on your production.
Do this during offhours, btw. Create a script and let it run during the maintenance window.
In other news, the underlying query here had a problem optimizing:
StatementOptmEarlyAbortReason="TimeOut"
Also of interest is the warnings on DetailCounts_TestOnly:
<Warnings>
- <ColumnsWithNoStatistics>
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="OrderId" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="ProdCode" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="Descript" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="Location" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="LocationActual" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="Quantity" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="Price" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="DiscountTotalAmount" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="PrintGroup" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="ShippingPackageId" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="StatusName" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="TypeName" />
<ColumnReference Database="[MPXCanada]" Schema="[dbo]" Table="[DetailCounts_TestOnly]" Alias="[od01]" Column="Type" />
</ColumnsWithNoStatistics>
</Warnings>
Repeated indicators of stale statistics. My next step would be to do the statistics update I mentioned above.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 1:26 pm
Have you tried a recompile of the procedure on the server where it takes time to execute, could be a bad cached plan.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2010 at 7:09 pm
Craig and everyone,
After looking at the exec plan and the many self-left-outer joins, I rewrote the piece in essence getting rid of the left joins and used inner joins to achieve the same result. And the time cut down from 30 mins. to less than 3 secs.
Thanks a lot for your feedback and help, it was very useful.
Have a Great Thanksgiving,
God Bless.
November 23, 2010 at 7:41 pm
Thanks for the feedback, that's excellent news. Luck to you in the future.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply