Din seria “Microsoft SQL Server 2005 in actiune”

Astazi episodul pilot :

“Explorarea teritoriului SQL Server 2000 cu Database Tuning Advisor”

O productie “Admindate & Inf, www.diadmin.ro” , 2006

Scenariul si regia de Gheorghe Ciubuc

   Dupa cum ati aflat din anumite surse “Database Tuning Advisor” este mostenitorul averii lasate de raposatul “Index Tuning Wizard” de la SQL 2000 si, asa cum reiese din denumire face cele mai nimerite propuneri pentru imbunatatirea performantei uneia sau mai multor baze de date vis-à-vis de niste date numite “workload” pe care i le pui la dispozitie.Primul lucru la care te gandesti cand vezi “workload” ar fi ca se foloseste rezultatul , stransura facuta de SQL Server Profiler – un tool care asta face,masoara ce se petrece la un moment dat pe server. Este adevarat insa , acum ma gandesc sa utilizez un script SQL cu cele mai nastrusnice fraze SQL (un fel de desagi cu pietre pe spinare de magar) astfel ca sa simulez lucrul intensiv pe server. Scriptul folosit este urmatorul:

USE Adventureworks;

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail sod

GROUP BY SalesOrderID

ORDER BY SalesOrderID ;

SELECT ProductID, OrderQty, UnitPrice, LineTotal

FROM Sales.SalesOrderDetail

WHERE UnitPrice < $2.00

COMPUTE SUM(OrderQty), SUM(LineTotal) ;

SELECT ProductID, OrderQty, UnitPrice, LineTotal

FROM Sales.SalesOrderDetail

WHERE UnitPrice < $5.00

ORDER BY ProductID

COMPUTE SUM(OrderQty), SUM(LineTotal) BY ProductID

COMPUTE SUM(OrderQty), SUM(LineTotal) ;

Intru pe Microsoft SQL Server Management Studio -> Tools -> database Engine Advisor ->File ->New Session. Apoi am grija sa fac urmatoarele setari:

·        La “Session name” pun un nume de sesiune oarecare de exemplu « toamna 2006 »\

·        La Workload cu butonul pe « File » aleg scriptul de mai sus ;

·        Aleg baza de date « AdventureWorks » si numai tabelele « «SalesOrderDetail » si « SalesOrderHeader »

·        Pe tab-ul « Tuning Options » deselectez « «Limit tuning time », selectez « Do not keep any existing PDS »

·        La tab-ul “Advanced Options” pun “Generate online recommendations where possible” si “OK”.

   Pentru lansare se merge in meniu la Actions -> Start Analysis. Dupa ce sistemul isi termina treaba se merge pe tab-ul « «Recommandations » .Recomandarile propuse in 2005:

1)DROP INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] WITH ( ONLINE = OFF )

2)CREATE STATISTICS [_dta_stat_610101214_7_5] ON [Sales].[SalesOrderDetail]([UnitPrice], [ProductID])

3) CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderDetail_6_610101214__K1_9] ON [Sales].[SalesOrderDetail]

([SalesOrderID] ASC

)

INCLUDE ( [LineTotal]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]

4) CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderDetail_6_610101214__K5_K7_4_8] ON [Sales].[SalesOrderDetail]

(

[ProductID] ASC,

[UnitPrice] ASC

)

INCLUDE ( [OrderQty],

[UnitPriceDiscount]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]

5) CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderDetail_6_610101214__K7_4_5_8] ON [Sales].[SalesOrderDetail]

(

[UnitPrice] ASC

)

INCLUDE ( [OrderQty],

[ProductID],

[UnitPriceDiscount]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]

6) DROP INDEX [IX_SalesOrderHeader_SalesPersonID] ON [Sales].[SalesOrderHeader] WITH ( ONLINE = OFF )

7) DROP INDEX [IX_SalesOrderHeader_CustomerID] ON [Sales].[SalesOrderHeader] WITH ( ONLINE = OFF )

   Si performanta se poate imbunatati cu 75%.Mai departe , ma intreb oare ce recomandari imi da tool-ul din 2005 pentru o baza de date similara rezidenta pe un server SQL 2000? Pentru a incerca sa raspund singur la asta (fara sa-l pun pe Cristi Lefter) pregatesc terenul astfel:

• Tot in Microsoft SQL Server Management Studio intru pe AdventureWorks click dreapta - >Tasks ->Export Data …(cred ca ati observat ca ma apuc de un export de baze de date) ->trec de sursa ->am grija sa aleg serverul SS2K, sa-i creeez baza de date AdventureWorks si asa mai departe ->Finís
• Am grija ca sa modific scriptul de mai sus acela cu desagii de pietre stiind ca in 2000 nu avem scheme pe nicaeri (se foloseste ownerul “dbo”)Dupa ce parcurg niste pasi asemanatori ca mai sus iata ce recomandari sunt propuse pentru 2000

 1) CREATE STATISTICS [_dta_stat_757577737_5_7_9_4] ON [dbo].[SalesOrderDetail]([ProductID], [UnitPrice], [LineTotal], [OrderQty])

2) CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderDetail_10_757577737__K1_K9] ON [dbo].[SalesOrderDetail]

([SalesOrderID] ASC,

[LineTotal] ASC)

Trebuie sa amintesc contextul in care s-au facut recomandarile:

• Wizardul nu a putut importa coloana calculata SalesOrderDetail.LineTotal
• In baza de date AdventureWorks nu existau indecsi (nu au fost adusi de wizard)

Dupa ce reconstruiesc LineTotal ca o coloana calculate sistemul propune:

 3)SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

CREATE STATISTICS [_dta_stat_757577737_5_7_9_4] ON [dbo].[SalesOrderDetail]([ProductID], [UnitPrice], [LineTotal], [OrderQty])

4)      la fel ca 2)

      Si performanta se poate imbunatati cu 75%.Asa cum am precizat tabelele implicate nu detin alti indecsi , deci acestia nu au fost adusi prin export. Sa incerc acelasi lucru in conditiile unei baze de date AdventureWorks rezidenta pe SS2K insa cu toti indecsii de pe SS2K5.Construiesc indecsii lipsa dupa care reiau executia sesiunii; si, credeti-ma pe cuvant, recomandarile facute sunt identice cu cele precedente.

La final ca in orice film care se respecta, concluziile sunt sugerate si nu explicit spuse.

Rezultatele pot fi salvate intr-un document XML (File ->Export Session Results…) ; dar despre aceasta si modul in care se pot utiliza si accesa fisierele XML rezultate intr-un episod viitor.

SFARSIT

Regia ………………………………………………………………..Ciubuc Gheorghe

Scenariul(dupa un document Microsoft)……………………………Ciubuc Gheorghe

Producator………………………………………………………….. Ciubuc Gheorghe

O productie Admindate & Inf, www.diadmin.ro”