May 26, 2016 at 6:14 am
Sorry to bring technical to the thread, but I have a quick question that I need answered so I can counter a "Are you sure it's the code because performance is inconsistent?" question.
Can someone link me to a thread / website / etc. on how to pull up cached plans for a specific stored procedure so I can (hopefully) find the parameters it was run with in the past few days?
We've got an upgraded to 2012 slow proc problem (similar to this) that someone refuses to believe is a mainly a code problem because an update stats and recompile of the proc fixed the issue for exactly 2 days before it started going downhill again. And I think Grant is correct in this case, that the proc is so poorly coded that no number of recompiles or update stats will leave it fixed.
May 26, 2016 at 6:38 am
Brandie Tarvin (5/26/2016)
Sorry to bring technical to the thread, but I have a quick question that I need answered so I can counter a "Are you sure it's the code because performance is inconsistent?" question.Can someone link me to a thread / website / etc. on how to pull up cached plans for a specific stored procedure so I can (hopefully) find the parameters it was run with in the past few days?
We've got an upgraded to 2012 slow proc problem (similar to this) that someone refuses to believe is a mainly a code problem because an update stats and recompile of the proc fixed the issue for exactly 2 days before it started going downhill again. And I think Grant is correct in this case, that the proc is so poorly coded that no number of recompiles or update stats will leave it fixed.
To get the plan out of cache you can use a query like this:
SELECT deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,DEFAULT,DEFAULT) AS deqp
WHERE deqp.objectid = OBJECT_ID('dbo.AddressByCity');
It won't show the parameters used to call the plan. That information isn't stored anywhere unless you capture it using ExEvents or trace. It will show the compile time parameter values. That can help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 26, 2016 at 6:50 am
Grant Fritchey (5/26/2016)
Brandie Tarvin (5/26/2016)
Sorry to bring technical to the thread, but I have a quick question that I need answered so I can counter a "Are you sure it's the code because performance is inconsistent?" question.Can someone link me to a thread / website / etc. on how to pull up cached plans for a specific stored procedure so I can (hopefully) find the parameters it was run with in the past few days?
We've got an upgraded to 2012 slow proc problem (similar to this) that someone refuses to believe is a mainly a code problem because an update stats and recompile of the proc fixed the issue for exactly 2 days before it started going downhill again. And I think Grant is correct in this case, that the proc is so poorly coded that no number of recompiles or update stats will leave it fixed.
To get the plan out of cache you can use a query like this:
SELECT deqp.query_planFROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,DEFAULT,DEFAULT) AS deqp
WHERE deqp.objectid = OBJECT_ID('dbo.AddressByCity');
It won't show the parameters used to call the plan. That information isn't stored anywhere unless you capture it using ExEvents or trace. It will show the compile time parameter values. That can help.
Thanks, Grant. That tallies with what I've been seeing from other code I have, where it's been giving me nothing on the query itself. But I was hoping I was just looking at it wrong.
SELECT Text, cp.size_in_bytes, plan_handle, *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Proc'
--AND cp.usecounts = 1
AND Text LIKE '%MyProc%'
ORDER BY cp.size_in_bytes DESC;
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE usecounts >= 1
AND text LIKE '%MyProc%'
AND objtype = 'Proc'
ORDER BY usecounts DESC;
May 26, 2016 at 7:50 am
Hey Threadizens . . .
The other day (inspired by a couple of SQL Saturday speakers), I started my own blog.
My most recent entry tackles the very-often-asked-here-on-SSC question, "how do I get started with SQL Server[/url]?"
Feel free to take a peek and let me know what you think! (And if you have anything to add to my post, feel free to comment!)
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
May 26, 2016 at 8:01 am
Ray K (5/26/2016)
Hey Threadizens . . .The other day (inspired by a couple of SQL Saturday speakers), I started my own blog.
My most recent entry tackles the very-often-asked-here-on-SSC question, "how do I get started with SQL Server[/url]?"
Feel free to take a peek and let me know what you think! (And if you have anything to add to my post, feel free to comment!)
A man of many talents! very cool.
May 26, 2016 at 8:37 am
Ray K (5/26/2016)
Hey Threadizens . . .The other day (inspired by a couple of SQL Saturday speakers), I started my own blog.
My most recent entry tackles the very-often-asked-here-on-SSC question, "how do I get started with SQL Server[/url]?"
Feel free to take a peek and let me know what you think! (And if you have anything to add to my post, feel free to comment!)
Very cool
May 26, 2016 at 8:40 am
Ray K (5/26/2016)
Hey Threadizens . . .The other day (inspired by a couple of SQL Saturday speakers), I started my own blog.
My most recent entry tackles the very-often-asked-here-on-SSC question, "how do I get started with SQL Server[/url]?"
Feel free to take a peek and let me know what you think! (And if you have anything to add to my post, feel free to comment!)
Congrats, good stuff!
š
May 26, 2016 at 9:17 am
Ray K (5/26/2016)
Hey Threadizens . . .The other day (inspired by a couple of SQL Saturday speakers), I started my own blog.
My most recent entry tackles the very-often-asked-here-on-SSC question, "how do I get started with SQL Server[/url]?"
Feel free to take a peek and let me know what you think! (And if you have anything to add to my post, feel free to comment!)
Nice.
May 26, 2016 at 1:36 pm
I'm really starting to hate the SSIS Catalog...
Two servers, same permissions on both. On one, the user can deploy their project no problems (with the deploy project wizard.)
On the other, the deployment fails and the error in the messages is "Failed to deploy the project. Fix the problems and try again later.:External component has thrown an exception."
So helpful...
All the Googling finds is for large projects where the error indicates a timeout, adding indexes to a couple tables...
May 26, 2016 at 1:48 pm
jasona.work (5/26/2016)
I'm really starting to hate the SSIS Catalog...Two servers, same permissions on both. On one, the user can deploy their project no problems (with the deploy project wizard.)
On the other, the deployment fails and the error in the messages is "Failed to deploy the project. Fix the problems and try again later.:External component has thrown an exception."
So helpful...
All the Googling finds is for large projects where the error indicates a timeout, adding indexes to a couple tables...
Iām right there with you.
May 26, 2016 at 2:20 pm
jasona.work (5/26/2016)
I'm really starting to hate the SSIS Catalog...Two servers, same permissions on both. On one, the user can deploy their project no problems (with the deploy project wizard.)
On the other, the deployment fails and the error in the messages is "Failed to deploy the project. Fix the problems and try again later.:External component has thrown an exception."
So helpful...
All the Googling finds is for large projects where the error indicates a timeout, adding indexes to a couple tables...
Is everything equal on both servers (versions, OS etc.)?
š
May 26, 2016 at 2:24 pm
Who has the crystal ball this time?
May 26, 2016 at 4:35 pm
Lynn Pettis (5/26/2016)
Who has the crystal ball this time?
Well, someone must have done something to help. The OP posted "this works now" but didn't provide the solution. He could have switched to a stored procedure, but my guess is that he substituted NULLs for unwanted columns. Now I'm expecting to see a post similar to "Hey, I've got this function I won't post and it's reading from a bunch of tables it doesn't need to read from. Please fix."
May 27, 2016 at 4:36 am
Ed Wagner (5/26/2016)
Lynn Pettis (5/26/2016)
Who has the crystal ball this time?Well, someone must have done something to help. The OP posted "this works now" but didn't provide the solution. He could have switched to a stored procedure, but my guess is that he substituted NULLs for unwanted columns. Now I'm expecting to see a post similar to "Hey, I've got this function I won't post and it's reading from a bunch of tables it doesn't need to read from. Please fix."
:laugh:
(unfortunately you are most likely correct)
May 27, 2016 at 7:36 am
djj (5/27/2016)
Ed Wagner (5/26/2016)
Lynn Pettis (5/26/2016)
Who has the crystal ball this time?Well, someone must have done something to help. The OP posted "this works now" but didn't provide the solution. He could have switched to a stored procedure, but my guess is that he substituted NULLs for unwanted columns. Now I'm expecting to see a post similar to "Hey, I've got this function I won't post and it's reading from a bunch of tables it doesn't need to read from. Please fix."
:laugh:
(unfortunately you are most likely correct)
And we will blindly throw out ideas until one of them (but we don't know which one) will magically work and everything is unicorn farts again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 ā Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 54,316 through 54,330 (of 66,549 total)
You must be logged in to reply to this topic. Login to reply