SQL Server 2008 User temporär sperren bzw. Priorität herabsetzen

Question

Wir haben einen Datenbank Server mit mehreren Instanzen und > 50 Usern.

Seit der Einführung von Microsoft Power BI legen uns die Kollegen regelmäßig mit ihren umfangreichen Abfragen den Server lahm. Besonders am Vormittag ist das ein Porblem da wir da täglich eine größere Datenübernahme machen, die uns aufgrund der hohen Belastung leider desöfteren wegen Ressourcenproblemen abbricht.

Unsere Idee ist nun währen dieser Datenübernahme die Power BI User zu blockieren oder ihre Priorität so weit herunter zu fahren dass unsere Datenübernahme reibungslos funktioniert.

Leider habe ich keine rechte Idee, ob und wie das automatisiert zu machen ist.

Beim Googeln bin ich nicht so recht weiter gekommen.

Bin für jeden Tipp dankbar.

gruß

cheapy

Answer 1

Hi,

ich würde eher mal sagen, das ist ein organisatorisches und/oder ein auf zuwenig Ressoucen basierendes Problem bei euch.

Wenn die Kollegen euch den ganzen Datenbankserver lahmlegen können, sollte man Ihnen entweder mal eine Schulung verpassen oder sie nicht auf eurem primären Datenbankserver arbeiten lassen. Stellt ihnen doch ein eigenes System zur Verfügung, das dann periodisch vom Hauptdatenbankserver aus mit neuen Daten bestückt wird.

Klar kann man User auch sperren, fraglich ist aber, ob das wirklich zielführend ist.

DENY CONNECT SQL TO [BI_User1]
DENY CONNECT SQL TO [BI_User2]
DENY CONNECT SQL TO [BI_User3]
...

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

Answer 2

Dies ist halt das Fatale, wenn man Power-User mit BI in Unkenntnis der Datenbank auf dieselbe direkt loslässt.
Besser ist es, wie mein Vorredner schon schrieb, ein Datawarehaus (DWH) aufzubauen, dass regelmäßig synchronisiert wird (incrementell) mit dem die User dann machen können was sie wollen.

Natürlich sollte man dann bei langlaufenden Operationen Indizes anlegen usw., aber es stört den Betrieb nicht.

Da man in BI schön mit Drag/Drop was zusammmen schustern kann, muss die Datenbank das dann auflösen. Schnell ist da mal ein Crossjoin gebaut oder komplexe Gruppierungen und Aggregate über irgendwelche verjointen Felder für die kein direkter Zugriffsweg existiert.

Da kann man dann schnell von mangelnden Ressourcen sprechen obwohl bei vernünftiger Verfahrensweise und Planung eines DWH's die Ressourcen dann oftmals ausreichen.

Was den obigen Zweifel angeht, so kann ich das nur bestätigen.
Ich kann den BI-Usern nicht sagen: Nun habt ihr zwar ein schönes Power-System, aber das dürft ihr nur zwischen 16:00 und 18:00 Uhr und vielleicht am Wochenende (wenn keine Wartung angesagt ist) nutzen.
Mit BI will ich aber i.W. Livedaten (ggf. mit wenigen Minuten Verzögerung) abfragen um u.U. zielgerichtet darauf zu reagieren.

Die Alternative ware dann wieder, Nachts per Batch über SSRS schöne Reports zu erstellen, die ich den ganzen Tag über betrachten kann. Von aktuell und live ist da dann aber nichts mehr zu spüren.

Ich betreibe dieses Geschäft nun schon seit 2005;-).

Answer 3

Hallo,

diesen Ausführungen wie man es richtig machen soll will und kann ich nicht widersprechen.
Das was die Kollegen da treiben hab ich vor 7 oder 8 Jahren schon mal Access-basierend gemacht und mit vorbereiteten Daten.
Nun ist es aber so eine Sache mit den neuen Besen, die sich profilieren möchten ...

Einige der betroffenen User, seinerzeit Benutzer meines Tools, haben mir auch schon ihr Leid geklagt, nützt aber nix, Ober sticht Unter. :-(

Gruß

cheapy

Answer 4

Dann warte mal ab, welche meldung du beim Deaktivieren von Usern von diesen bekommst.
Nach dem Motto: "Jetzt habe ich schon einen schnellen Porsche und darf nur durch die Anwohnerstraße fahren!".

Answer 5

Hi,

Nun ist es aber so eine Sache mit den neuen Besen, die sich profilieren möchten ...

Einige der betroffenen User, seinerzeit Benutzer meines Tools, haben mir auch schon ihr Leid geklagt, nützt aber nix, Ober sticht Unter. :-(

das spricht IMHO noch mehr für eine suboptimale Organisation.

Gib den BI Usern ihr eigenes System und sorg dafür, dass die Daten dort so zeitnah, wie es eben bei dir möglich ist, zur Verfügung stehen. Dann können die sich ihr eigenes System lahmlegen und nicht deines.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

Answer 6

Hallo,

ich würde es auch so machen wie von Stefan und bfuerchau bereits ausgeführt.

Eine andere Idee ist die Anzahl der gleichzeitigen Benutzerverbindungen zu limitieren.

https://docs.microsoft.com/de-de/sql/database-engine/configure-windows/configure-the-user-connections-server-configuration-option?view=sql-server-2017

Dazu sind dann Absprachen notwendig, wer morgens und wer nachmittags mit PowerBI abfragen darf.

Schönen Tag. 

Answer 7

Und wenn die BI-User am System hängen, sind die ERP-User dann ausgeschlossen?
M.a.W., wenn das Controlling eine Statistik abfragt, hat das Lager Stillstand?

Und bei sog. Connection-Pools bringt dies gerade aber auch überhaupt nichts, da es dann generell nicht so viele aktive Verbindung gibt und die BI-User dann auch noch bevorzugt werden.

Answer 8

Hallo

wenn es "mehrere Instanzen" gibt, liegt die Vermutung nahe, dass Eine Enterprise Edition in Verwendung ist?

Dort kann man mithilfe des Resource Governors die Ressourcen entweder pro Datenbank, Login-Name oder Applikationsname auf bestimmte Maximalwerte beschränken.

Dabei ist der Max CPU Percent sicher das hilfreichste Setting. In den neueren, auch noch supporteten Versionen, kommt dann auch Datenbank-IO und CPU-Capping hinzu.

So kann das aussehen:

--- Create a resource pool for production processing  
--- and set limits.  
USE master;  
GO  
CREATE RESOURCE POOL pProductionProcessing  
WITH  
(  
     MAX_CPU_PERCENT = 100,  
     MIN_CPU_PERCENT = 50  
);  
GO  
--- Create a workload group for production processing  
--- and configure the relative importance.  
CREATE WORKLOAD GROUP gProductionProcessing  
WITH  
(  
     IMPORTANCE = MEDIUM  
);  
--- Assign the workload group to the production processing  
--- resource pool.  
USING pProductionProcessing  
GO  
--- Create a resource pool for off-hours processing  
--- and set limits.  

CREATE RESOURCE POOL pOffHoursProcessing  
WITH  
(  
     MAX_CPU_PERCENT = 50,  
     MIN_CPU_PERCENT = 0  
);  
GO  
--- Create a workload group for off-hours processing  
--- and configure the relative importance.  
CREATE WORKLOAD GROUP gOffHoursProcessing  
WITH  
(  
     IMPORTANCE = LOW  
)  
--- Assign the workload group to the off-hours processing  
--- resource pool.  
USING pOffHoursProcessing;  
GO  

Quelle: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/create-and-test-a-classifier-user-defined-function?view=sql-server-2017

Wenn man ein von diesem System getrenntes Datawarehouse besitzt, hat man die Ressourcen damit automatisch getrennt. Bei effizientem ETL-Designsind Latenzen (Zeit-Unterschied zwischen OLTP und OLAP-System) im Bereich von 5 Minuten leicht zu erreichen.

Ansonsten ist natürlich immer abzuwägen, ob man die bestehenden Ressourcen erhöhen, oder Nutzer beschränken sollte.

Viel Erfolg

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)