Dlaczego zapytania SQL Server nie używają więcej niż 7 MB / s dysku I / O na dysku

Mam dysk SSD, który przy użyciu testu IOmeter wykazuje wydajność powyżej 200 MB / s. Jednak po uruchomieniu dowolnego zapytania SQL z komputera lokalnego monitor zasobów systemu Windows nigdy nie pokazuje IO dysku powyżej 7 MB / s. Dotyczy to nawet zapytań, których uruchomienie zajmuje ponad 2 minuty. Co może być wąskim gardłem, ponieważ używa tylko 7 MB / s z dysku SSD?

Biegnę:

Windows Server 2012 Standard
SQL Server 2008 R2
Intel i7 3820
32 GB pamięci RAM
dysk SSD Sandisk

sql-server sql-server-2008-r2 hardware

— Dean MacGregor
źródło

może dane są już w pamięci RAM i nie jest potrzebny dostęp do dysku?

— gbn

@DeanMacGregor - W jaki sposób spożywasz wyniki? Jeśli w SSMS, co jeśli spróbujesz opcji odrzucenia wyników. Czy to coś zmienia? Możesz także spróbować przyjrzeć sys.dm_os_waiting_taskssię działającemu zapytaniu, aby sprawdzić, czy napotyka inne typy oczekiwania.

— Martin Smith

Czy 200 MB / s dla odczytów o swobodnym dostępie (losowe odczyty dla bloków 4 KB)? Wydaje mi się, że taka jest zazwyczaj baza danych. Czy zapytanie zapisuje na dysk (plik tymczasowy, tymczasowy zestaw wyników lub tabela)?

@DeanMacGregor - Aby wyciągnąć to z równania, możesz przypisać wynik do zmiennych skalarnych (przykładowe zapytanie). DECLARE @Name VARCHAR(10), @High int; SELECT @Name=name, @High = high FROM master..spt_values. Tak więc żadne wyniki nie są wysyłane z powrotem do klienta, ale plan i zamówienia będą nadal takie same.

— Martin Smith

Załóżmy, że interpretujesz ASYNC_NETWORK_IO czeka na oznaczenie, że problem jest związany z siecią? To (zazwyczaj) nie jest. Najprawdopodobniej @MartinSmith zasugerował (dwukrotnie), że SSMS lub używana aplikacja nie zużywają wyników tak szybko, jak SQL je obsługuje. Postępuj zgodnie z jednym z sugerowanych sposobów pominięcia zużycia wierszy, a uzyskasz prawdziwy (r) obraz maksymalnej przepustowości we / wy.

— Mark Storey-Smith

Z łańcucha komentarzy wygląda na to, że interpretacja ASYNC_NETWORK_IOczeka, co oznacza, że problem dotyczy sieci. To (zazwyczaj) nie jest.

Jak @MartinSmith zasygnalizował (dwukrotnie) najbardziej prawdopodobnym wyjaśnieniem tego jest SSMS lub aplikacja, której używasz, nie zużywając wyników tak szybko, jak SQL Server je obsługuje. Wykonaj jedną z sugerowanych metod, aby usunąć zużycie wierszy z pomiaru, a uzyskasz prawdziwy (r) obraz maksymalnej przepustowości we / wy:

Na wypadek, gdybyś jeszcze tego nie zrobił, musisz oczywiście DBCC DROPCLEANBUFFERSupewnić się, że dane są odczytywane z dysku, a nie z bufora bufora. Obowiązują zwykłe zastrzeżenia „tylko podczas testu, nie rób tego w aktywnym środowisku na żywo” itp.

Uzupełnienie kilku innych komentarzy:

Podczas wykonywania zapytania, które zwraca 9 milionów wierszy, użycie procesora pozostanie na poziomie około 13%, a 9% można przypisać serwerowi sqlserver ... czy nie zwróci wyników szybciej niż 3 minuty, jeśli wszystkie dane byłyby w pamięci RAM?

Co dokładnie testujemy tutaj, jak i dlaczego? Jeśli twoje zapytanie z 9 milionami wierszy jest czymś innym niż a, SELECT * FROM dbo.SomeTablewtedy pojawia się 1001 czynników, innych niż tylko surowa przepustowość We / Wy.

Twój Intel I7-3820 to 4-rdzeniowy procesor. Jeśli twoje zapytanie testowe nie generuje planu równoległego, byłbym zaskoczony, gdybyś mógł zrzucić ponad 20% wykorzystania procesora z systemu.

3 minuty na zwrócenie 9 milionów wierszy są bardzo podejrzane i sugerują, że nie otrzymujemy pełnego obrazu tego, co testujesz. Domyślam się, że jest to przypadek nieoptymalnego (nierównoległego) planu zapytań, wypełnionego operatorami pętli zagnieżdżonej, ciągnącymi miliony wierszy, tj. Nie tylko jedną tabelę SELECTdo weryfikacji zużycia IO.

Sugeruję:

SELECT *przetestować tylko IO przez SQL Server.
Nowe pytanie z planem wykonania zapytania, jeśli chcesz dowiedzieć się, dlaczego nie nasyca IO.

— Mark Storey-Smith
źródło

Właściwie to wystarczy wybrać * z dbo.sometable. Przepraszam, że nie wspomniałem o tym wcześniej; miałem na myśli to, że mogłem uruchomić to zapytanie z dowolnej tabeli. Geneza mojego pytania polegała na tym, że zauważyłem, że IO dysku było znacznie mniejsze niż się spodziewałem, nawet po prostym zapytaniu „podaj mi dużo danych”. Moją główną obawą było to, że jeśli dyskowe operacje we / wy są tak niskie, można poprawić wydajność, jeśli podstawowa przyczyna niskiej operacji we / wy dysku zostanie zrootowana.

— Dean MacGregor