August 9, 2017 at 5:52 pm
Hello All,
We run 2008R2 enterprise edition of MSSQL server.
There is SPID on the server that we plan to monitor (and run SQL profiler on).
Our goal is to determine what tables this process will go after for its selects and updates. We would also like to know that the tables its going after, is it utilizing existing tables on those tables and/or are there any recommended indexes we can create that will help.
Will the SQL profiler tool be adequate to gather this information or should I install something else to capture the needed info.
Thank you
August 10, 2017 at 5:48 am
First, don't use Profiler to run this. Instead, set up a Trace. Here's an article on exactly how to do that. The Profiler GUI uses a different kind of buffer within memory that can seriously negatively impact your server, so please, follow what Gail says and set up a Trace instead.
That said, yes, Trace will capture all the queries, assuming you go for rpc_completed and sql_batch_completed. It will show you the text of those queries and any stored procedures that they call. It's on you to then search that text and identify the tables being referenced. There is no other way to accurately capture the information about exactly which tables are being accessed, when, by how and by whom.
"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
August 10, 2017 at 8:53 am
Grant Fritchey - Thursday, August 10, 2017 5:48 AMFirst, don't use Profiler to run this. Instead, set up a Trace. Here's an article on exactly how to do that. The Profiler GUI uses a different kind of buffer within memory that can seriously negatively impact your server, so please, follow what Gail says and set up a Trace instead.That said, yes, Trace will capture all the queries, assuming you go for rpc_completed and sql_batch_completed. It will show you the text of those queries and any stored procedures that they call. It's on you to then search that text and identify the tables being referenced. There is no other way to accurately capture the information about exactly which tables are being accessed, when, by how and by whom.
To get the information on other objects associated with a table in question , you can right look at the list of 'dependent objects' (Right click the Table name from the explorer in SSMS and selected dependent objects) .
Grant , are you suggesting server side tracing ? The events mentioned are also provided in the Profiler. Like to know your views on running profiler from some other server to create the trace ,if the events are very few and the database is not very huge (or if the DB already has degraded performance). Thank you
Arshad
August 10, 2017 at 3:39 pm
Arsh - Thursday, August 10, 2017 8:53 AMTo get the information on other objects associated with a table in question , you can right look at the list of 'dependent objects' (Right click the Table name from the explorer in SSMS and selected dependent objects) .Grant , are you suggesting server side tracing ? The events mentioned are also provided in the Profiler. Like to know your views on running profiler from some other server to create the trace ,if the events are very few and the database is not very huge (or if the DB already has degraded performance). Thank you
Arshad
I already said, don't run this through Profiler. It has a different buffering mechanism that will cause memory pressure in your environment. Use the server-side trace scripts that I linked to. It doesn't matter that the events are the same, it matters how you're capturing them. Using the Profiler GUI to capture events will cause problems on a system that is already under pressure and will add pressure to other systems.
"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
August 15, 2017 at 4:17 am
Grant Fritchey - Thursday, August 10, 2017 3:39 PMArsh - Thursday, August 10, 2017 8:53 AMTo get the information on other objects associated with a table in question , you can right look at the list of 'dependent objects' (Right click the Table name from the explorer in SSMS and selected dependent objects) .Grant , are you suggesting server side tracing ? The events mentioned are also provided in the Profiler. Like to know your views on running profiler from some other server to create the trace ,if the events are very few and the database is not very huge (or if the DB already has degraded performance). Thank you
Arshad
I already said, don't run this through Profiler. It has a different buffering mechanism that will cause memory pressure in your environment. Use the server-side trace scripts that I linked to. It doesn't matter that the events are the same, it matters how you're capturing them. Using the Profiler GUI to capture events will cause problems on a system that is already under pressure and will add pressure to other systems.
Thanks Grant for elaborating.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply