April 28, 2011 at 7:09 am
Okay. A lot of times people don't think of event notifications or extended events and I think that's where MS wants us to go instead of SQL Trace, so I mention it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2011 at 7:15 am
Jack Corbett (4/28/2011)
Okay. A lot of times people don't think of event notifications or extended events and I think that's where MS wants us to go instead of SQL Trace, so I mention it.
I like the idea, I just don't see how I can use it :-D.
April 28, 2011 at 7:20 am
Yeah, I get that. I often wish there was a simpler way to get the type of information you are after.
Perhaps Adam Machanic's sp_whoisactive running on a schedule and storing results?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2011 at 7:49 am
Tx, I had forgotten about that script... That is definitely something that could help!
Actually this seems like a better download link :
April 28, 2011 at 8:06 am
Just got a reply from Adam and there's no way to use his script to do what I need (unless I'm prepared to start an infinite loop and kill it after a few minutes).
I somehow don't see much of an improvement vs tracing the plans...
Still opened to ideas.
TIA.
April 28, 2011 at 8:42 am
just my 2ct.
Since you aim for minimal prod sys interference .... let's keep it simple.
How about lightweight tracing just for the the sp_prepare and sp_execute statements (of course capture to file ( high speed I/O disk )) for a certain sample time. (not the plans at this time)
Then restore a fullback of your db ( or a PIT restore to also test your drp 😉 ) to a test server and replaying the load to be able to capture the plans.
Would that be representative/reliable enough or worth the try ?
It should be at least KISS enough if you have a spare test server.
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
April 28, 2011 at 8:51 am
ALZDBA (4/28/2011)
just my 2ct.Since you aim for minimal prod sys interference .... let's keep it simple.
How about lightweight tracing just for the the sp_prepare and sp_execute statements (of course capture to file ( high speed I/O disk )) for a certain sample time. (not the plans at this time)
Then restore a fullback of your db ( or a PIT restore to also test your drp 😉 ) to a test server and replaying the load to be able to capture the plans.
Would that be representative/reliable enough or worth the try ?
It should be at least KISS enough if you have a spare test server.
Love it. Been asking for a sandbox for 6 months. Maybe now that we have a new 98 TB san that he'll spare me a freaking 40 GB for the DB :w00t:.
I've never had any luck with trace replays. As far as I can see there's no template for replay. Got any good articles to point me in the right direction?
April 28, 2011 at 9:12 am
http://msdn.microsoft.com/en-us/library/ms174901%28v=sql.90%29.aspx
AFAIK: 3ways:
- the bol profiler way: http://msdn.microsoft.com/en-us/library/ms189604%28v=SQL.90%29.aspx
- the dta way: database tuning advisor ( been a while since I used that tool so I don't know how far that will get you.)
- or load the trace file(s) into a table and inject the captured statements yourself towards your test instance.
to capture the plans, filter that trace on the tool name you are using if you aren't using a dedicated instance.
temp objects will be a problem with this replay scenarios because you might not have captured them in the trace.
The advantage of the 3th option is you can already filter your top percentage to target on based on the captured cpu/read/write/duration goal you focus on.
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
April 28, 2011 at 9:21 am
Thanks Albaza.
I'll put in an official request for my toy DB and I'll go from there.
We have a test environement but I can't overwrite it at will :crazy:.
April 28, 2011 at 10:55 am
Ninja's_RGR'us (4/28/2011)
Thanks Albaza.I'll put in an official request for my toy DB and I'll go from there.
We have a test environement but I can't overwrite it at will :crazy:.
Todays managers buzz word: "virtualize it" :blink:
( they just forget your hosting box needs to be appropriately sized to cope with the sum of the VMs )
Albaza 😉
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
April 28, 2011 at 10:59 am
ALZDBA (4/28/2011)
Ninja's_RGR'us (4/28/2011)
Thanks Albaza.I'll put in an official request for my toy DB and I'll go from there.
We have a test environement but I can't overwrite it at will :crazy:.
Todays managers buzz word: "virtualize it" :blink:
( they just forget your hosting box needs to be appropriately sized to cope with the sum of the VMs )
Albaza 😉
Right on the money for my case here. The final nail in the coffin was that the previous san was filled to the brim. Now with a 100TB extra room we should be able to wiggle a coupld more GB on that server!!!
April 29, 2011 at 3:21 pm
Ninja - If the reports running these mystery querries are Crystal Reports or Business Objects Reports you are in for some fun.
I worked in a large environment that ran on SQL 7 and then SQL 2000 for years and had to work out issues caused by these reports querries and how they ran.
Sounds familar to what you are descriping when you see no trace data for SQL_Prepare and SQL execute.
The current best way for "seeing" the querries or exact SQL that the reports or any SPID is truely running actually can be found in the information here.
This covers the table-valued function that replaces the system function fn_get_sql:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/61b8ad6a-bf80-490c-92db-58dfdff22a24.htm
This covers how to get the Plan for any handle:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/9d5e5f59-6973-4df9-9eb2-9372f354ca57.htm
Now all you have to do is put the information together in those two articles and make some querries that extract this data and insert into some other tables.
You will need to run these often before during and after the reports execute to capture all the actual SQL text that is being passed and executed, along with colleting the Plans that go along with each SQL Handle for each Execution of the SQL Text. After doing this for a few days and monitoring or loging what reports are running as you collect the data you should get everything you need to determine what is happening.
Keep you from having to build such a big Sandbox to still have to go through a bunch of test execution and data collection.
😎
April 29, 2011 at 4:40 pm
SanDroid (4/29/2011)
Ninja - If the reports running these mystery querries are Crystal Reports or Business Objects Reports you are in for some fun.I worked in a large environment that ran on SQL 7 and then SQL 2000 for years and had to work out issues caused by these reports querries and how they ran.
Sounds familar to what you are descriping when you see no trace data for SQL_Prepare and SQL execute.
The current best way for "seeing" the querries or exact SQL that the reports or any SPID is truely running actually can be found in the information here.
This covers the table-valued function that replaces the system function fn_get_sql:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/61b8ad6a-bf80-490c-92db-58dfdff22a24.htm
This covers how to get the Plan for any handle:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/9d5e5f59-6973-4df9-9eb2-9372f354ca57.htm
Now all you have to do is put the information together in those two articles and make some querries that extract this data and insert into some other tables.
You will need to run these often before during and after the reports execute to capture all the actual SQL text that is being passed and executed, along with colleting the Plans that go along with each SQL Handle for each Execution of the SQL Text. After doing this for a few days and monitoring or loging what reports are running as you collect the data you should get everything you need to determine what is happening.
Keep you from having to build such a big Sandbox to still have to go through a bunch of test execution and data collection.
😎
Sounds amazing (tho I haven't read the articles yet :hehe:). No the ERP is MS Dynamics Nav but I'm afraid it's the same sad story as yours. It's heaven to code in for a developper. You get tons done with just 1-2 lines of code. The problem is that everything is OO >>> RBAR. The performance on the bigger reports is iffy at best. I have to reverse engineer and then add a ton of data for dashboards (like show me the whole freaking DB fom these 20 angles).
Picture a system with only 100-ish reports being executed daily on a 17 GB db but that amounts to 1+ TB of page read. In that same period, twice as many users can't even log 100 GB of page reads in the ERP, that includes the reports :w00t:. And that's as optimized as I can make those reports without going to SSAS and OLAP.
February 16, 2020 at 5:28 am
Hi, In MS Dynamics NAV you have the Client Monitor which will tell you exactly which query has been executed with which parameters and even the sql plan it's being using. There is also a client monitor toolkit which uses the data from the Client monitor to analyze which query has been executed the most and how long the total time is.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply