July 12, 2021 at 3:14 am
is it possible to Let SQL profiler and Extended event can't capture the sql statement?
if not , is there any better trick let user uneasily capture the sql statements you execute? ( aside from encryption SQL scripts) thanks so much!
July 12, 2021 at 10:55 am
It is possible for Extended Events to miss an event. The tool is designed so that it puts minimal load on a system. Part of that includes the ability to drop events if it looks like capturing them would hurt performance. However, you can actually use a setting to force it to capture every event, no matter how many resources it takes. This is strongly NOT advised to be used, but it is there.
There are four, and really only four, ways built into SQL server to capture query metrics. The DMVs show aggregated metrics for queries in cache. Query Store aggregates query metrics for a given database over a defined time period. Profiler/Trace uses an old, bolted on, method for capturing query metrics in detail. Extended Events are built into the engine itself for capturing query metrics in detail.
That's it. Those are your choices. DMV = Aggregation in cache. Query Store = Aggregation by database & hour (default behavior). Trace = Detailed, but very resource intensive. Extended Events = Detailed. Pick the one that best suits your needs.
"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
July 12, 2021 at 4:33 pm
I've found that the biggest reason for SQLProfiler not capturing items is someone writing the wrong filter.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2021 at 7:38 pm
I read your question a little differently than Grant and Jeff, so I just wanted to confirm what you were looking to do.
If I understand your question correctly, you want to allow someone access to Profiler and/or Extended Events, but you want to restrict it so they cannot see the TSQL that was executed.
If I understand your question correctly, there is no way (that I am aware of) to do this. This is by design as both Profiler and Extended Events are designed to help debug problems with the system. In order to do this, you would need to be able to see all sorts of metrics and data, including but not limited to the TSQL being executed.
I think the opposite question comes to mind - why does a user need to run Profiler or Extended Events if they are not troubleshooting or tuning (generally DBA related duties)?
Now, if I am misinterpreting the question, then both Jeff and Grant tackle this question well - system resource can result in data being missed and filters can result in data being missed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 12, 2021 at 10:57 pm
I read your question a little differently than Grant and Jeff...
I didn't read it that way so went back and read it again... and, yeah... I agree... you might have interpreted the question quite right.
I have to say that if someone has privs to execute SQLProfiler, they probably have privs to set it up which means that they also have privs to see the "text data" column, which is where the code lives.
All of that leads to a larger question... what is the OP trying to do and why do they need hide the code being executed?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2021 at 11:54 am
No arguments. Your interpretation may be right. But then, what are we trying to see here? Query performance but not queries? How then do you correlate the performance, "An unidentified query is running poorly," to the solution "Change this bit of T-SQL, update these statistics, create this index, etc."? Without the ability to see the code, then this is just a weird exercise.
"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
July 14, 2021 at 2:23 pm
I read your question a little differently than Grant and Jeff, so I just wanted to confirm what you were looking to do.
If I understand your question correctly, you want to allow someone access to Profiler and/or Extended Events, but you want to restrict it so they cannot see the TSQL that was executed.
If I understand your question correctly, there is no way (that I am aware of) to do this. This is by design as both Profiler and Extended Events are designed to help debug problems with the system. In order to do this, you would need to be able to see all sorts of metrics and data, including but not limited to the TSQL being executed.
I think the opposite question comes to mind - why does a user need to run Profiler or Extended Events if they are not troubleshooting or tuning (generally DBA related duties)?
Now, if I am misinterpreting the question, then both Jeff and Grant tackle this question well - system resource can result in data being missed and filters can result in data being missed.
Thank you Mr. Brian Gale! what you understand is correct.
as my friend is developing a software system, the backend of the system is sql server , there are some stored procedures and views in the sql server, he doesn't expect users can capture some scripts of stored procedures while users use sql profiler or extended event.
so I have this idea, thanks!
July 14, 2021 at 8:46 pm
Mr. Brian Gale wrote:I read your question a little differently than Grant and Jeff, so I just wanted to confirm what you were looking to do.
If I understand your question correctly, you want to allow someone access to Profiler and/or Extended Events, but you want to restrict it so they cannot see the TSQL that was executed.
If I understand your question correctly, there is no way (that I am aware of) to do this. This is by design as both Profiler and Extended Events are designed to help debug problems with the system. In order to do this, you would need to be able to see all sorts of metrics and data, including but not limited to the TSQL being executed.
I think the opposite question comes to mind - why does a user need to run Profiler or Extended Events if they are not troubleshooting or tuning (generally DBA related duties)?
Now, if I am misinterpreting the question, then both Jeff and Grant tackle this question well - system resource can result in data being missed and filters can result in data being missed.
Thank you Mr. Brian Gale! what you understand is correct.
as my friend is developing a software system, the backend of the system is sql server , there are some stored procedures and views in the sql server, he doesn't expect users can capture some scripts of stored procedures while users use sql profiler or extended event.
so I have this idea, thanks!
Anyone with sysadmin privs would be able to override any and all of such trickery.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2021 at 9:17 pm
My opinion - I would try to restrict access to profiler and extended events down as far you as possibly can. If the end user can't run profiler or extended events on the server, you don't need to worry about restricting anything inside those tools (also not sure how you would restrict them).
Sysadmins would be able to run it by default and by requirement, but I'd keep my sysadmin user list small. My theory is that if someone has sysadmin permissions, they are agreeing to being one of the people who I will call to help with disaster recovery and they better show up to help when the day comes. If you want sysadmin permissions, you better be ready to fix major issues on the system. If you have the permission level where you could completely hose the instance, you are on the hook for fixing it too.
I do my best to follow the least privilege model for security - if you can do your job without sysadmin, you are not a sysadmin. In your scenario, if you are not a sysadmin and don't have ALTER TRACE permissions, you can't run profiler on the system, so it solves your problem. If the user is not a member of sysadmin and doesn't have VIEW SERVER STATE and ALTER EVENT SESSION permissions, they can't create or view extended events.
Sources - https://www.sqlservercentral.com/blogs/extended-events-permissions
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 15, 2021 at 3:17 pm
Instead of trying to trick out how extended events works, simply expose the data collected in a different location, a file system or something. What's important is not running Extended Events. What's important is the data that they collect. Same with Trace or anything else. Expose the data, not the tool.
"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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply