By: Ahmad Yaseen | Updated: 2018-03-30 / Comments (3) / Related: More > kyselyn optimointi
ilmainen MSSQLTips Webinar: indeksointi suorituskykyä: kysely strategiat maksimoida SQL Server indeksit
harvat asiat vaikuttavat nopeutta ja suorituskykyä SQL lausuntoja SQL Server yhtä paljon kuin indeksit. Tämä istunto auttaa sinua ymmärtämään kyselyn suorituskyvyn perusteet suhteessa tuotantotietokannan indekseihin. Opetamme sinulle, miten voit selvittää, käytätkö hyviä indeksistrategioita ja miten indeksointistrategiasi vaikuttaa suoraan kyselyihin.
ongelma
tiedän, että SQL Server voi suorittaa kyselyjä rinnakkain, mutta miten voin kertoa, että kyselyssä käytetään rinnakkaista suoritussuunnitelmaa ja miten saan tietoa käytetystä CPUs-määrästä, kun kysely suoritetaan rinnakkain? Katso tästä vinkistä, miten.
ratkaisu
parallelismin käsite tulee ison tehtävän jakamisesta pienempiin tehtäviin, joissa jokainen pieni tehtävä annetaan tietylle henkilölle tai suorittimelle SQLServer-kyselyn tapauksessa suorittamaan osa päätehtävästä. Lopuksi jokaisesta pienestä tehtävästä saadut osittaiset tulokset yhdistetään yhdeksi lopputulokseksi. Voit kuvitella, kuinka paljon nopeammin tämä voidaan tehdä ja suorituskykyä saada, että voit canget käynnissä useita sarjatehtäviä samanaikaisesti rinnakkain, verrattuna torunning yksi iso tehtävä serially!
ennen kärjen läpikäymistä on hyödyllistä ymmärtää useita SQL Serverin parallelismikäsitteeseen liittyviä teknisiä termejä:
- Scheduler on fyysinen tai looginen prosessori, joka on vastuussa SQL Server-säikeiden suorituksen aikatauluttamisesta.
- työntekijä on säie, joka on sidottu aikatauluttajaan tietyn tehtävän suorittamiseksi.
- yhdensuuntaisuuden aste on niiden työntekijöiden tai jalostajien lukumäärä, jotka on määrätty rinnakkaissuunnitelmaan suorittamaan työntekijän tehtävä.
- suurin Rinnakkaisuusaste (MAXDOP) on palvelin -, tietokanta-tai kyselytasovaihtoehto, jota käytetään rajoittamaan niiden prosessoreiden määrää, joita rinnakkaissuunnitelma voi käyttää. Oletusarvo MAXDOP on 0, jossa theSQL Server Engine voi käyttää kaikkia käytettävissä suorittimia, jopa 64, kyselyn parallelexecution. MAXDOP-asetuksen asettaminen arvoon 1 estää useamman kuin yhden prosessorin käyttämisen kyselyn suorittamiseen, mikä tarkoittaa, että SQL Server-Moottori käyttää serialplania kyselyn suorittamiseen. MAXDOP-vaihtoehto voi ottaa arvon 32767 asti, jolloin SQL Server Engine käyttää kaikkia saatavilla olevia palvelinprosessoreita rinnakkaissuunnitelman toteutuksessa, jos MAXDOP-arvo ylittää palvelimessa olevien prosessoreiden määrän. Jos SQL Server on asennettu yhdelle processorserver, arvo MAXDOP ohitetaan.
- tehtävä on pieni työ, joka on osoitettu tietylle työntekijälle.
- Suorituskonteksti on se raja, jossa kukin yksittäinen tehtävä kulkee sisällä.
- Rinnakkaissivun toimittaja on osa SQL-Palvelinvarastointimoottoria, joka jakaa kyselyssä pyydetyt tietokokonaisuudet osallistuneiden työntekijöiden kanssa.
- vaihto on komponentti, joka yhdistää kyselyn rinnakkaissuunnitelmaan liittyvät eri lausekkeet yhteen, jotta saadaan lopullinen tulos.
päätös käyttää rinnakkaista suunnitelmaa kyselyn toteuttamiseksi riippuu monistatekijöistä. Esimerkiksi SQL Server on asennettava moniprosessoripalvelimelle, pyydetyn säikeiden määrän on oltava käytettävissä, jotta se voidaan täyttää, Parallelismin Enimmäisastetta ei ole asetettu arvoon 1 ja kyselyn kustannukset ylittävät aiemmin määritetyn Parallelismiarvon Kustannuskynnyksen.
tämän vihjeen tarkoituksena on hakea rinnakkaisten kyselykierrosten tiedot kyselyn varsinaisesta toteutussuunnitelmasta. Pakottaaksemme SQL Server-Moottorin suorittamaan lähetetyn kyselyn rinnakkaisella suunnitelmalla, asetamme rinnakkaisuuden arvon Kustannusrajan arvoon 0, varmistaaksemme, että kaikissa tapauksissa kyselykustannukset ylittävät rinnakkaisuuden arvon kynnysarvon ja pidämme parallelismin enimmäisasteen asetuksen oletusarvolla 0, jotta SQL Server-Moottori voi käyttää kaikkia käytettävissä olevia prosessoreita suorittaessaan kyselyämme, jotka ovat 4 prosessoria tässä demossa käytettävässä mymachine-laitteessa.
Parallelism-arvon Kustannuskynnys voidaan asettaa SQL Server Managementstudion avulla yhdistämällä SQL Server-instanssiin, napsauttamalla instancenimeä hiiren kakkospainikkeella ja valitsemalla Ominaisuudet-vaihtoehto. Alkaen Advanced sivun palvelimen Ominaisuudetwindow, vieritä alas Parallelism osio, josta voit ohittaa thedefault arvo kustannukset kynnys Parallelism vaihtoehto, joka on 5, tai Maxdegree Parallelism tarvittaessa, kuten kuvakaappaus alla:
Voit myös ohittaa Parallelismin kustannuskynnyksen oletusarvon käyttämällä sp_configurea. Jotta voit muuttaa Parallelism-arvon Kustannuskynnystä, sinun tulee käyttää sp_configurea, jotta Show Advanced-asetus voidaan ottaa käyttöön ensin, kuten alla olevassa komentosarjassa on esitetty:
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;GOEXEC sp_configure 'cost threshold for parallelism', 0;RECONFIGURE;GOEXEC sp_configure 'show advanced options', 0;RECONFIGURE;
Demo rinnakkaisesta Suoritussuunnitelmasta
palvelin on nyt määritetty demoa varten. Let ’ s luoda uusi taulukko käyttäen theCREATE taulukko T-SQL selvitys alla esitetyllä tavalla:
USE MSSQLTipsDemoGOCREATE TABLE ParallelDemo( ID INT IDENTITY (1,1) PRIMARY KEY, FirstName NVARCHAR (200), LastName NVARCHAR (200), PhoneNumber VARCHAR(50), BirthDate DATETIME, Address NVARCHAR(MAX) )
kun taulukko on luotu, täytämme taulukon 100K tietueet käyttäen theINSERT osaksi lausuntoja alla:
INSERT INTO ParallelDemo VALUES ('John','Horold','+96255889955','1987-01-08','Jordan - Amman - Mecca Street - Building 77')GO 50000 INSERT INTO ParallelDemo VALUES ('Michel','Anglo','+96255776655','1985-06-02','Jordan - Amman - Mecca Street - Building 74')GO 50000
nyt haluamme ottaa käyttöön ”Sisällytä todellinen Suoritussuunnitelma” ja sittenrun alla valitse kysely:
SELECT , , , , , FROM .. WHERE Address LIKE '%AMM%' ORDER BY BirthDate desc
kun tämä on valmis käynnissä, voimme tarkistaa suoritussuunnitelman luotu alkaenxecuting kyselyn, näet, että SQL Server Moottori päättää käyttää parallelplan suorittaa kyselyn, koska kustannukset kyseisen kyselyn ylittää Thecost kynnysarvo Parallelism arvo.
voimme nähdä alla, että suoritussuunnitelma on rinnakkaissuunnitelma, koska Yhdensuuntaisoperaattori ja myös keltaiset ympyrät, joissa on kaksi nuolta jokaisen toimijan alla, jotka suorittivat yhdensuuntaisesti.
toteutussuunnitelma ei ole pelkkä kaavio, vaan se sisältää arvokasta tietoa, joka kuvaa lähetetyn kyselyn toteutusprosessia. Voit tarkastella näitä tietoja napsauttamalla hiiren kakkospainikkeella suoritussuunnitelman valitse solmu-kohtaa ja valitsemalla Ominaisuudet-vaihtoehdon. Ominaisuudet-ikkunasta näet niiden prosessorien määrän, joita käytetään lähetetyn kyselyn käsittelyyn Parallelism-attribuutista, alla olevien sekalaisten osioiden alla:
tästä samasta ikkunasta myös tiedot rinnakkaisista kyselyketjuista näytetään ThreadStat-osiossa. Tässä osiossa voit tarkistaa parallel query Branches-attribuutin, joka näyttää kyselyn suoritussuunnitelman samanaikaisten suorituspolkujen määrän, numa Node ID: n, määritetylle Numanodille varattujen rinnakkaisten säikeiden määrän ReservedThreads-attribuutissa ja kyselyä suoritettaessa käytettyjen threads-attribuutin alla käytettyjen säikeiden määrän.
Ominaisuudet-ikkunasta voi päätellä, että SQL Server-moottori käytti kyselyn suorittamiseen 4-prosessoreita. Lisäksi kyselyn suoritussuunnitelmassa on yksi suorituspolku, jossa 4 säiettä varataan ja käytetään lähetetyn kyselyn suorittamiseen käyttäen rinnakkaista suunnitelmaa.
sama tieto voidaan johtaa myös XML-suoritussuunnitelmasta katsomalla ThreadStat-osiosta, kuten alla on esitetty:
Voit myös sukeltaa syvemmälle tarkistaaksesi I / O: n ja kunkin suorittimen käyttämät resurssit. Napsauta hiiren kakkospainikkeella executionplanin ryhmitettyä Indeksiskannaussolmua ja valitse Ominaisuudet. Näytetystä Ominaisuudet-ikkunasta voit laajentaa varsinaista I / O-tilastosolmua tarkistaaksesi kunkin säikeen suorittamien loogisten ja fyysisten lukutoimintojen määrän kyselyä suoritettaessa. Lisäksi voit myös tarkistaa rivien määrän, jotka haetaan kustakin säiettä, suorittimen kellonajan joka säiettä ja lopuksi suoritusaika, joka on kulunut kunkin threadsuorittaa tehtävänsä, kuten alla:
tulokset täsmäävät valitse solmun Ominaisuudet-ikkunan kanssa, jossa on 4 kyselytehtävien suorittamiseen osallistunutta työntekijäketjua ja yksi koordinaattorinluku, joka koordinoi työntekijäketjujen välillä.
on otettava huomioon, että aikaisemmat tiedot voidaan hakea vain varsinaisesta Toteutussuunnitelmasta, joka luodaan kyselyn suorittamisen jälkeen. Jos yrität etsiä näitä tietoja arvioidusta Suoritussuunnitelmasta suorittamatta kyselyä, tietoja ei näytetä, koska kysely on suoritettava, jotta voidaan määrittää niiden prosessoreiden määrä, jotka osoitetaan kyselyyn käytettävissä olevilta prosessoreilta kyselytutkimuksen aikana, kuten alla on esitetty arvioidulle Suoritussuunnitelmalle.
saada lisää tietoa SQL Server Parallelism
kun kysely on käynnissä, voit suorittaa sp_who2-komennon querysession ID: llä saadaksesi tietoa viestiketjuista, joita käytetään toimitettavan kyselyn suorittamiseen kyselyn suorittamisen aikana, kuten alla on esitetty.:
suoritussuunnitelmassa jokaiselle kyseisen suunnitelman operaattorille oli annettu numero ja aikataulu, jota käytetään sen suorittamiseen. Tämä tieto voidaan hakea valitsemalla sys.dm_os_tasks järjestelmäluettelonäkymä ja sen liittäminen SYS. dm_os_workersand sys. dm_exec_query_profiles järjestelmäluettelonäkymiin, kuten alla olevassa T-SQL-skriptissä:
SELECT OSTSK.scheduler_id, qp.node_id, qp.physical_operator_nameFROM sys.dm_os_tasks OSTSKLEFT JOIN sys.dm_os_workers OSWRK on OSTSK.worker_address=OSWRK.worker_addressLEFT JOIN sys.dm_exec_query_profiles qp on OSWRK.task_address=qp.task_addressWHERE OSTSK.session_id=58ORDER BY scheduler_id, node_id;
suoritetaan edellinen skripti ajettaessa SELECT statement kanssa ActualExecution Plan käytössä, tulos näyttää meille, että kolme suunnitelma operaattorit: Lajittele, suodata ja Clustered Index Scan kanssa ID kunkin operaattorin vieressä näkyy. Tämä suoritetaan neljä kertaa käyttäen neljää ajastinta. Rinnakkaisuusoperaattori, jonka tunnus on 0, suoritetaan kerran neljännellä ajoituksella liittääkseen allexecution-kontekstit yhteen viimeisessä vaiheessa, kuten alla on esitetty.:
Alla olen lisännyt solmun arvot, jotka vastaavat yllä olevia kyselytuloksia suoritussuunnitelman avulla.
Next Steps
- Check out the following resources:
- Specifying Max Degree of Parallelism in SQL Server for a Query.
- mitä SQL Serverin MAXDOP-asetusta tulisi käyttää.
- miten pakotetaan rinnakkainen Suoritussuunnitelma SQL Server 2016: ssa.
- SQL Server 2016 DBCC CHECKDB maxdopilla
viimeksi päivitetty: 2018-03-30
tietoa tekijästä
Ahmad Yaseen on SQL Server DBA, jolla on kandidaatin tutkinto tietokonetekniikasta sekä.Net-kehityskokemusta.Katso kaikki omat vinkkini
Aiheeseen liittyvät resurssit
- lisää tietokannan kehittäjän vinkkejä…
