June 3, 2014 at 8:17 am
Hi everyone,
I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
Is there any way to do what I'm looking for?
Thanks for any help/suggestion.
Mauricio
June 3, 2014 at 9:53 am
Mauricio N (6/3/2014)
Hi everyone,I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
Is there any way to do what I'm looking for?
Thanks for any help/suggestion.
Mauricio
What exactly are you trying to accomplish here?
If you run a server side trace and later save or upload to a table, you can see duration, reads, writes even CPU. But you don't suppose to run a trace just for the sake of run it.
What you're doing, in my opinion, will have some performance impact if yours is a live production server.
June 3, 2014 at 9:59 am
Hi,
thanks for your answer. This is just for a QA environment and it won't run in Live. What I'm trying to do is just save that information, we're continuously running queries that users send to us and we want to test them before apply them in Live. As we're using Multi Script we want to catch all the scripts running from that tool and save the information I described before.
Thanks again.
Mauricio
June 3, 2014 at 10:32 am
What I'm trying to do is just save that information, we're continuously running queries that users send to us and we want to test them before apply them in Live.
WHA?????
Why are your users providing you with queries?
_______________________________________________________________
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/
June 3, 2014 at 10:50 am
Mauricio N (6/3/2014)
Hi,thanks for your answer. This is just for a QA environment and it won't run in Live. What I'm trying to do is just save that information, we're continuously running queries that users send to us and we want to test them before apply them in Live. As we're using Multi Script we want to catch all the scripts running from that tool and save the information I described before.
Thanks again.
Mauricio
This approach is not correct. Your users should not be providing you with queries. They are users. What they should do, is tell you what's slow or what's not operating under what's considered acceptable performance for the business.
You tune up your store procedures, if needed, based on execution time , amount of resources needed to run the query, etc. And you use actual execution plans for that.
Ask to your developers what store procedure or query is the most important to them. Set the expectations, like what's acceptable in terms of execution time, etc. Then take it from there.
You can't log each and every query that hits the Dev server and the tune up. That's insane. And not the right approach.
June 3, 2014 at 2:16 pm
Hi SQL Lover,
my mistake, not user but developers. Let me try to explain how it works. We have RFCs and developers solve them providing us with queries that we have to analyze. We have multiple servers and databases with millions of rows. To execute the same query in multiple servers we use Multi Script. I'm not trying to save every query but only the ones from Multi Script. In case the query fails, for any reason, I want to inform the developer about that. But it could be that the query runs fine but slow, in that case I also want to inform developer. In practice, I think it won't be more than 100 queries daily.
Right now I can save every query executed from Multi Script, I only need to add time and rows affected. So, can it be done or not?
Thanks again.
Mauricio
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply