Has anyone successfully turned on SQL Server auditing (extended events) to keep track of queries being run in a production environment while having the database still perform well? If you did, I'm interested in what configuration you used. We are specifically interested in auditing queries for specific Database Logins that are mapped to Database Users.
If not, what third party tools would you recommend in order to accomplish this in a production environment?
Database size is 2.2TB. This is a data warehouse that loads new data every day, so highly transactional. 24 cores, 256GB memory. We have the ability to hard delete (we've done it once), but soft deletes are just updates.