January 24, 2015 at 1:40 am
Hi
we are on emergency state.
Application is very slow and some times give time out.
in Database , which thing Should I examin in first step?
and then :
we want to filter All sp's and dynamic tsql running on my server (with exec).
1- what is the best filter that give me object name and number of execution . (an sp come only one time on one execution. because I want to report from this result)
2- How can I get locked transaction or long time of them from profiler
thank you
January 24, 2015 at 6:47 am
If your server is already under stress, don't use Profiler on it. Since you're on 2014 (assuming this since you're in a 2014 forum), use Extended Events. They inherently cause less of a load. You should capture rpc_completed and sql_batch_completed events. Since you don't know why things are running slow, I can't suggest filters at this time, but that means you need to be prepared to deal with a lot of data. You should probably add the query_hash to your events too. You can use the Data Explorer window within SSMS to group by the query_hash or the object_id in order to see execution counts.
But, before you do any of this kind of intense monitoring, have you looked at blocking? Have you looked at wait statistics to understand exactly why the server is running slow? It might not be queries causing the problem (although that's one of the most common issues).
"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
January 24, 2015 at 6:57 am
Please say me more about :
"but that means you need to be prepared to deal with a lot of data. You should probably add the query_hash to your events too. You can use the Data Explorer window within SSMS to group by the query_hash or the object_id in order to see execution counts."
Query_hash ?
January 24, 2015 at 7:43 am
sm_iransoftware (1/24/2015)
Please say me more about :"but that means you need to be prepared to deal with a lot of data. You should probably add the query_hash to your events too. You can use the Data Explorer window within SSMS to group by the query_hash or the object_id in order to see execution counts."
Query_hash ?
Here's an introduction to extended events.
I said be prepared to have a lot of data because you don't know where your problems are coming from. That means you'll need to capture all queries until you can determine are you suffering from a particular database, a particular application, a particular set of queries, are they small queries called frequently or large queries that run forever. You just don't know what to filter for, so you can't filter. Instead, you'll capture all the remote procedure calls (rpc_complete) and queries run in a batch (sql_batch_complete).
Because the rpc_complete is going to have the object, the stored procedure, for you to group by, no additional information is needed. But, query batches don't naturally lend themselves to grouping since the query values can be different. That's why having the query_hash and the query_plan_hash added to your sql_batch_complete event as actions will gather additional data that you can group by. The query_hash and the query_plan_hash are referred to as the "fingerprints" of a query. They're a mathematical construct of the query itself and the resulting execution plan. Grouping by either or both of these values allows you to see aggregate behavior on queries.
"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
January 24, 2015 at 5:06 pm
sm_iransoftware (1/24/2015)
Hiwe are on emergency state.
Application is very slow and some times give time out.
in Database , which thing Should I examin in first step?
and then :
we want to filter All sp's and dynamic tsql running on my server (with exec).
1- what is the best filter that give me object name and number of execution . (an sp come only one time on one execution. because I want to report from this result)
2- How can I get locked transaction or long time of them from profiler
thank you
Open SSMS and then open the Object Explorer. Press the {f8} key to get there if it's not already open.
Right click on the server instance, select [Reports], then [Standard Reports]. From there, let your nose be your guide. Generally speaking, reports that are prefixed with the word "Activity" are associated with what's going on right now. Reports that are prefixed with the word "Performance" are associated with things that have happened in the past up until now. Some things that happened in the past may be missing from these reports if they've been driven out of cache or the machine has recently been rebooted or the SQL Server Service has recently been started.
Most of the reports don't require any setup because they're reading from the same DMV's that a lot of people write code for. There are some great tools to be had but these reports are free and great ways to do the quick'n'dirty thing to help you find problems and problem code.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2015 at 8:20 pm
Beyond the excellent suggestions already provided, you might investigate having some tools on hand to run at a moments notice to discover what is happening on your server. Once you set up things like the Extended Events Sessions, for example, save those queries and keep them in a "troubleshooting" project inside SSMS that you can open and run. And you can also save them off in a shared folder that the team can access. You can examine using well-known troubleshooting procedures like sp_whoisactive by Adam Machanic and make them part of your troubleshooting process.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply