November 27, 2012 at 6:16 am
If you have the full query, just attach it to your forum reply.
You may also want to take a look at dmv sys.dm_exec_sql_text(sql_handle | plan_handle)
and/or
(
plan_handle
, { statement_start_offset | 0 | DEFAULT }
, { statement_end_offset | -1 | DEFAULT }
)
to get your full query text and plan.
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
November 28, 2012 at 1:48 pm
I'd like to get you some sample data -- what is the best way to get it to you? (BAK file [would prefer], etc.)
November 28, 2012 at 1:53 pm
Thanks Johan
I tried the 2301 flag described in that article (option querytraceon 2301)
the actual plan it generated was WAY different from the actual without the flag, and identitifed a missing index
I would like to take that plan and force it into the query, but (per my previous posts) the plan XML seems to exceed a limit on length -- any thoughts as to how to work around that?
November 28, 2012 at 1:56 pm
the ddl and query are attached to this post:
November 29, 2012 at 6:28 am
If you're in for some Powershell, maybe the attached ps1 script can help out :w00t:
It will prompt for a sqlserver name ( servername\instance or just servername if you have a default instance )
and will write a consumption file and one file for every top (n) sqlplan. ( location: C:\temp\powershell )
It will execute the query using sys.dm_exec_text_query_plan, so you can get the full ( and long ) query plan.
Maybe this can get you a step further.
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
November 30, 2012 at 8:56 am
I am able to get the est plan and actual plan via other means (query analyser, dmv's) -- before I tyr running this somewhere, what will this script provide that is not available elsewhere?
December 3, 2012 at 12:01 am
actually .... nothing !
it only exports sqlplans and consumption data ( as stated in the query to be run by the script ).
( I must have misinterpreted your previous replies http://www.sqlservercentral.com/Forums/FindPost1390116.aspx )
Powershell is just a scripting means that can facilitate stuff for you. That's it.
But it has great potential because it is :
1) .net capable
2) you just reference the namespace / dll you want it to use and ... you can just do the things your devs are trying to, but without the hassle of visual studio. This way any dba will be capable of supporting the dev-teams way better because he can actually show them "their" code to be used, just not using their UI. :w00t:
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
September 23, 2015 at 6:11 pm
Just wondering, have you got to the bottom of this?
I am experiencing the same issue in our PROD environment as well. The performance was relatively fast in the TEST environment with the similar size of data volume. However, as soon as it get deployed to PROD, the performance of the stored proc degenerated by 3 hundreds folds.
September 23, 2015 at 6:33 pm
nancy.gs.chuah (9/23/2015)
Just wondering, have you got to the bottom of this?I am experiencing the same issue in our PROD environment as well. The performance was relatively fast in the TEST environment with the similar size of data volume. However, as soon as it get deployed to PROD, the performance of the stored proc degenerated by 3 hundreds folds.
The Devil can live in the data. I've just submitted an article on the subject but, let me ask, was your test data all the same in the larger tables? If it was, the optimizer will sometimes recognize that (especially where XML is involved according to my testing) there's just one row of different data no matter how many rows there are (can be confirmed by looking at the stats for the table. I refer to it simply as "grooved" or "smeared" data.). As a result, nasty fast results in test and the DBA comes looking for you when the code makes it to prod.
You could also be fighting the bad form of parameter sniffing or the obvious "different or no indexes" problems that people forget to check for. Also, check for any triggers on tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2015 at 6:42 pm
nancy.gs.chuah (9/23/2015)
Just wondering, have you got to the bottom of this?I am experiencing the same issue in our PROD environment as well.
You don't have the same issue here. You have different schema, data, query, plans and servers. Everything is different, and there is no magic solution to performance issues.
If you want to get help on this forum for your specific issue I would suggest you to start from creating a new topic and posting actual query execution plans from TEST and PROD environments.
September 24, 2015 at 1:26 pm
It looked like your plan timed out on generation. So it's just choosing whatever its on and running it. This is probably happening everytime the query is running and you're just getting lucky that poor performance isn't happening more often.
September 24, 2015 at 11:42 pm
Alex,
You're right. It may not be the same problem. It's just that the symptom is very similar. 😛
Apology for hijacking the thread.
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply