Kann man den Nutzer aus dem Query Store auslesen?

Question

Hi,

kann man aus dem query store  per T-SQL abfragen, welcher User das query ausgeführt hat? Wenn ja, welche Tabellen muss man reinjoinen? Ich kenne nur:

sys.query_store_query_text
sys.query_store_query
sys.query_store_plan
sys.query_store_runtime_stats
sys.database_query_store_options#

kann hier aber keine Verbindung zum Nutzer finden, der sie ausgeführt hat.

Answer 1

Dazu muss man das sog. Auditing des SQL-Servers aktivieren.
Die Audit-Table enthält dann alle Einträge der überwachten Funktionen (wer hat was wann gemacht).
Beispiel "Select-Audit":
https://blogs.msdn.microsoft.com/sreekarm/2009/01/05/auditing-select-statements-in-sql-server-2008/

Dies sollte mit nachfolgenden Versionen natürlich auch gehen.
Man bedenke nur, dass hier sehr schnell sehr viele Daten zusammen kommen.

Einen Select-Trigger gibt es (leider) nicht.

Answer 2

Hallo Petra,

im ersten Abfragefenster diesen SELECT ausführen (Achtung: läuft sehr lange; ggfs. die number auf rows reduzieren)

declare @n int=10000000 --required number of rows
declare @text varchar(100)='Dummy' --yur required dummy text

;with cte
as
(
	select @text as text,1 as level
	union all 
	select @text as text,level+1 as level
	from cte
	where level<@n
)
select text from cte
 option (maxrecursion 0)

In einem zweiten Fenster kannst Du nachsehen, welcher User aktuell welche Abfrage ausführt (die erste Abfrage müsstest Du noch sehen, wenn Sie noch läuft):

SELECT
    p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, r.command,
    p.program_name, text 
FROM
    sys.dm_exec_requests AS r,
    master.dbo.sysprocesses AS p 
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE
    p.status NOT IN ('sleeping', 'background') 
AND r.session_id = p.spid

Die zweite Abfrage könnte man natürlich auch periodisch ausführen und dann in eine Tabelle mit Datum/Zeit schreiben.

Schönen Abend.

Answer 3

Hallo Jörg,

vielen Dank!

Bei Deiner Lösung verstehe ich 2 Dinge nicht:

- wozu genau brauche ich die 1. Abfrage?

- für mich sieht es so aus, als würde ich damit die aktuell laufenden Queries sehen. Was ich möchte, ist eigentlich ein zeitnahes (jedoch nicht in Echtzeit) Monitoring wer was abgefragt hat, sagen wir 1 Tag später, und dann nur filtern auf die Abfrage bestimmter Tabellen. Kann man das damit machen?

Viele Grüße,

Petra

Answer 4

Nein, das geht nur mit dem Audit-Journal.

Answer 5

Hallo Petra,

es ist richtig. Du siehst damit leider immer nur die aktuell laufenden Abfragen, und erhälst keine vollständige Darstellung einer Periode.

Die 1. Abfrage sollte Dir diese dann aktuell laufende Abfrage nur anzeigen, für den Fall, das sonst keine andere Abfrage gerade auf dem Server läuft, die angezeigt würde.

Schönen Abend.

Answer 6

Hallo Petra

Wie hier im Thread schon erkennbar, kann man die QueryStore tatsächlich nicht für User-Tracking verwenden.

Da ist der effizienteste Weg tatsächlich Auditing, was aus der „Sicherheits-Ecke“ kommt. Manchmal aber auch Extended Events als solches (Auditing basiert ja darauf), wenn bestimmte Attribute fehlen.

Live kann man natürlich jederzeit sehen, was wer gerade macht.

viel Erfolg

der Andreas

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform MCSE Data Platform
MCSM Charter Member, MCITP Charter Member etc.
www.SarpedonQualityLab.com (Founder)

Answer 7

Gerade aus Sicherheitsgründen ist das Auditing (auch in anderen Systemen erfunden).
Wenn man z.B. SOX-geprüft wird (wenn man mit den Amerikanern Geschäfte machen will), ist Auditing z.T. erforderlich, da in Problemfällen genau nachgewiesen werden muss, wer wann welche Daten angesehen und ggf. auch verändert hat.

Alternativ könnte man das Transaktions-Log heranziehen, wobei ich nicht weiß, ob da User o.ä. Informationen abgelegt werden. Je nach dem wie lange du dieses aufhebst, kann man dies regelmäßig auslesen und diese Information in eigenen Tabellen speichern:
https://docs.microsoft.com/de-de/sql/relational-databases/logs/the-transaction-log-sql-server
https://solutioncenter.apexsql.com/de/auslesen-einer-sql-server-transaktionsprotokolldatei/

Bzgl. der enthaltenen Informationen habe ich dazu nichts gefunden.

Answer 8

Hallo Petra,

abschließend noch ein Hinweis auf eine Software:

dbForge Event Profiler for SQL Server (free Product)

Einen Tag wirst Du damit aber ebenfalls nicht protokollieren können, weil dann die Datei zu groß wird.

Schönen Abend.

Answer 9

Hallo Petra,

abschließend noch ein Hinweis auf eine Software:

dbForge Event Profiler for SQL Server (free Product)

Einen Tag wirst Du damit aber ebenfalls nicht protokollieren können, weil dann die Datei zu groß wird.

Schönen Abend.

Lustig. Da hat das Devart-Tool tatsächlich ein paar Leute auf den Arm nehmen können.

Denn wie soll man es anders nennen, wenn ein Hersteller mit einem „kostenlosen Tool“ lockt, was sogar WENIGER kann, als das, was Microsoft mit dem Management Studio mitliefert. Eben die Möglichkeit Extended Event Sessions per GUI zu definieren. Und dann groß mit den Performance-Vorteilen gegenüber dem steinalten SQL Profiler zu werben, den Profis schon seit Jahren/SQL Server 2012 nicht mehr anfassen. (Ja, unter SQL 2008 ging das auch schon, nur nicht via GUI von MS – Da und nur da könnte das Tool Sinn machen.)

Also, einfach mit SSMS den Knoten „Verwaltung“ öffnen, und dort sich an den Möglichkeiten des modernen Tracing erfreuen. Ganz ohne externe Software ;-)

Das Audit-Logs schnell viel Platz benötigen ist klar. Da kommt es aber eher auf effiziente Ablage und dann Abfragemöglichkeiten an, und in diesem Fall noch mehr auf den Performance-Impact. Wie viel Platz man benötigt, hängt auch davon ab, wie geschickt man Filtern kann.

Ich hoffe man nimmt mir diesen Seitenhieb auf diese Bloatware nicht übel. Teilweise nehme ich das sicher auch als persönliches Thema auf, da ich 2012 die Ehre hatte, diese Technik erstmalig in Deutschland öffentlich zu zeigen und schulen, und seitdem immer wieder auf User-Group-Meetings und Konferenzen vielen das neue Tool nahegebracht habe. Es ist halt immer wieder Schade, zu sehen, wie manche sich mit der alten Technik abmühen oder dann von so einem Hersteller geradezu veralbert werden, der sein Tool einfach nur geschickter bewirbt al Microsoft sein integriertes.

in diesem Sinne, Happy Tracing

der Andreas

---

Andreas Wolter (Blog | Twitter)
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
MCM SQL Server 2008
MVP Data Platform MCSE Data Platform
MCSM Charter Member, MCITP Charter Member etc.
www.SarpedonQualityLab.com (Founder)