November 11, 2011 at 9:25 am
I am trying to troubleshoot a performance issue that is happening on our production SQL-2008 server.
A stored-procedure execution that used to complete in seconds has started taking more than 2 hrs to complete in the last few days.
The procedure involves joins between tables of two databases in the same instance: the database of the proc and another database.
Proc execution starts at 6 am daily.
As part of trying to reproduce this issue, I took a backup of the 2 databases in production at 5:40 am today.
At 6 am the proc started executing and ran for over two hours in production. There was no blocking during that time.
On the dev server I ran "dbcc freeproccache" and restored the db backups I had taken at 5:40 am.
I then ran the sproc; it ran in under a minute! 🙁
The dev server is a much lesser server than production with only 1 GB for max memory.
The prod server has max mem = 28 GB.
MAXDOP = 2 on both servers.
Why am I unable to reproduce the issue on the dev server?
What am I missing?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 9:29 am
Two things come to mind real quick:
Disk - fragmented, full, direct attached or SAN?
Other processes running at the same time causing blocking and resource contention.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 11, 2011 at 9:55 am
I'd compare query plans. Maybe the proc needs a recompile in prod.
---------------------------------------------------------------------
November 11, 2011 at 10:02 am
george sibbald (11/11/2011)
I'd compare query plans. Maybe the proc needs a recompile in prod.
But the odd thing is that after I run DBCC FREEPROCCACHE in dev and restore the prod backup to dev the query completes in seconds.
If that was the problem in prod, would it not be a problem in dev?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 10:03 am
Off the top of my head.
Index fragmentation
Statistics out of date
Different excecution plan
Different settings (arithabort, language etc)
November 11, 2011 at 10:41 am
I was able to collect the actual (live) exec plan of the proc, and I see a monstrous (18 billion) number of rows passed at some point in the plan.
I used SSMS Tools - http://www.simple-talk.com/sql/sql-tools/ssms-tools-pack-2.0/ - to analyze the exec plan and find the operators with the largest cost;
here they are, sorted by cost:
(1) Hash Match (Inner join) - cost 61%
Actual Rows: 351
Est. Rows: 625
(2) Index Scan (nonclustered) - cost 11%
Actual Rows: 2.8 million
Est. Rows: 6.6 million
(3) Index Scan (nonclustered) - cost 9%
Actual Rows: 8.7 million
Est. Rows: 8.7 million
(4) Merge join (inner join) - cost 8%
Actual Rows: 366 million
Est. Rows: 2 million
(5) Nested loops (inner join) - cost 3%
Actual Rows: 18 billion
Est. Rows: 2 million
It seems there is a huge discrepancy between actual and estimated rows for the last 2 operators above: the merge and nested loops join.
I guess I need to trace back from these operators to see which tables may be the ones with stale statistics?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 10:48 am
That would be a good start. You might try the tool SQL Sentry Plan Explorer. I find it many times more helpful in reading exec plans.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 11, 2011 at 10:54 am
I have been able to trace back from the 2 join operators to an index seek with hugely incompatible numbers of rows, Actual vs Estimated.
Here is a schematic of this portion of the plan with costs in parentheses:
Merge Join (8%) <--- Nested Loops (3%) <--- Index Seek, Nonclustered (1%)
The 2 join operators are (4) and (5) from my posting above.
Actual no. of rows for index seek: 18 billion
Estimated no. of rows for index seek: 39 thousand
There is a huge disparity between the actual and estimated number of rows for the index seek, but the cost is small (1%).
In any case, I will look into the stats of this index, and, perhaps after updating, this will allow the optimizer to come up with a better exec plan.
I will also look into recompiling the proc in production, as suggested.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 10:57 am
SQLRNNR (11/11/2011)
That would be a good start. You might try the tool SQL Sentry Plan Explorer. I find it many times more helpful in reading exec plans.
It's a good tool, and I have also been using it.
However, it only shows cached plans, which do not show the accuracy of cardinality estimates by the optimizer (actual vs estimated rows).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 10:59 am
Marios Philippopoulos (11/11/2011)
SQLRNNR (11/11/2011)
That would be a good start. You might try the tool SQL Sentry Plan Explorer. I find it many times more helpful in reading exec plans.It's a good tool, and I have also been using it.
However, it only shows cached plans, which do not show the accuracy of cardinality estimates by the optimizer (actual vs estimated rows).
Um no. I generate an actual exec plan and dump it into the tool to examine there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 11, 2011 at 11:06 am
SQLRNNR (11/11/2011)
Marios Philippopoulos (11/11/2011)
SQLRNNR (11/11/2011)
That would be a good start. You might try the tool SQL Sentry Plan Explorer. I find it many times more helpful in reading exec plans.It's a good tool, and I have also been using it.
However, it only shows cached plans, which do not show the accuracy of cardinality estimates by the optimizer (actual vs estimated rows).
Um no. I generate an actual exec plan and dump it into the tool to examine there.
Oh, I thought you were referring to the plan utility in Perf advisor.
I will certainly give SQL Sentry Plan explorer a try.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 11:36 am
Marios Philippopoulos (11/11/2011)
george sibbald (11/11/2011)
I'd compare query plans. Maybe the proc needs a recompile in prod.But the odd thing is that after I run DBCC FREEPROCCACHE in dev and restore the prod backup to dev the query completes in seconds.
If that was the problem in prod, would it not be a problem in dev?
exactly - having cleared proc cache in dev and then run the proc for the first time, a new query plan will be generated, and it runs fast, so perhaps a new one needs generating in prod. It would seem from your posts the current prod query plan is not optimum.
---------------------------------------------------------------------
November 11, 2011 at 11:39 am
george sibbald (11/11/2011)
Marios Philippopoulos (11/11/2011)
george sibbald (11/11/2011)
I'd compare query plans. Maybe the proc needs a recompile in prod.But the odd thing is that after I run DBCC FREEPROCCACHE in dev and restore the prod backup to dev the query completes in seconds.
If that was the problem in prod, would it not be a problem in dev?
exactly - having cleared proc cache in dev and then run the proc for the first time, a new query plan will be generated, and it runs fast, so perhaps a new one needs generating in prod. It would seem from your posts the current prod query plan is not optimum.
Thank you, I will then try this in prod and see what happens:
EXEC sp_recompile 'proc_name'
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 11:48 am
I am still confused about something.
Several of the tables involved in the sproc have their stats updated daily through a daily manual process.
These tables are found in the SQL statement that constitutes 99% of the proc cost.
If table stats are updated, isn't the SQL statement recompiled the next time it is run?
If that's the case, then the proc code gets recompiled daily anyway.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 11, 2011 at 12:35 pm
Still no luck after recompiling proc in production.
Proc run in production is still very slow.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply