MS SQL Server and Windows XP

University of Konstanz
Lehrstuhl:
Database & Information Systems Group
Prof. Dr. Marc H. Scholl
Betreuung:
Christian Grün
Matthias Röger, Thomas Zink; Information Engineering
1
Einleitung _____________________________________________________________ 4
2
Plattform und Konfiguration ______________________________________________ 5
2.1
Hardware und Betriebssystem ______________________________________________ 5
2.2
Datenbank ______________________________________________________________ 6
2.2.1
2.2.2
2.3
2.3.1
2.3.2
2.3.3
2.4
3
Installation SQL Server __________________________________________________________ 6
SQL Server Features_____________________________________________________________ 6
TPC Benchmark H (TPC-H) _______________________________________________ 8
TPC-H Kompilieren_____________________________________________________________ 9
TPC-H Tabellen erstellen und importieren __________________________________________ 11
TPC-H Queries generieren_______________________________________________________ 13
Zusammenfassung _______________________________________________________ 14
Einführung Datenbank-Tuning ___________________________________________ 16
3.1
Was ist Query-Optimierung _______________________________________________ 16
3.2
Der Query-Optimierer ___________________________________________________ 16
3.3
Statistiken und Indizes ___________________________________________________ 17
4
Einfluss der Arbeitsspeichergröße auf die Ausführungszeit_____________________ 20
5
Hinweise für den Optimierer: Hints________________________________________ 22
6
5.1
Index-Hinweise__________________________________________________________ 22
5.2
Join-Hinweise ___________________________________________________________ 23
Index ________________________________________________________________ 24
6.1
Einführung _____________________________________________________________ 24
6.2
Einsatz von Indizes ______________________________________________________ 24
6.3
Arten des Index _________________________________________________________ 24
6.3.1
6.3.2
6.3.3
6.3.4
6.3.5
7
Gruppierter Index______________________________________________________________ 25
Nichtgruppierter Index __________________________________________________________ 25
Eingesetzte Indizierungsverfahren _________________________________________________ 25
Füllfaktor ____________________________________________________________________ 26
Änderungen / Neuaufbau Index ___________________________________________________ 26
6.4
Unterstützendes Feature zur Performance-Steigerung: Missing Indexe ___________ 26
6.5
Analyse: Anwendung verschiedener Indizes __________________________________ 28
6.6
Fazit Index-Analyse ______________________________________________________ 30
Join-Verfahren ________________________________________________________ 31
7.1
Einführung _____________________________________________________________ 31
7.2
Implementierung von Join-Operationen ____________________________________ 31
7.2.1
7.2.2
Sort-/Merge-Join ______________________________________________________________ 31
Hash-Join ____________________________________________________________________ 31
7.2.3
7.3
Praktische Analyse Join-Verfahren _________________________________________ 32
7.3.1
7.3.2
7.3.3
7.4
8
Geschachtelter (nested) Loop-Join_________________________________________________ 32
Sort-/Merge-Join ______________________________________________________________ 32
Hash-Join ____________________________________________________________________ 33
Geschachtelter (nested) Loop-Join_________________________________________________ 34
Fazit Joins______________________________________________________________ 35
TPC-H Query Tuning ___________________________________________________ 36
8.1
Queries ________________________________________________________________ 36
8.2
Datenbanken ___________________________________________________________ 37
8.2.1
8.2.2
8.2.3
8.2.4
8.2.5
8.2.6
8.2.7
9
Primary und Foreign Keys _______________________________________________________ 38
Anwendung (Missing) Indexe ____________________________________________________ 38
Tuning ______________________________________________________________________ 40
Analyse Query 1 ______________________________________________________________ 41
Analyse Query 20______________________________________________________________ 43
Analyse Query 21______________________________________________________________ 45
Ergebnisse ___________________________________________________________________ 48
Fazit _________________________________________________________________ 50
10
Literaturverzeichnis___________________________________________________ 51
11
Anhang_____________________________________________________________ 52
11.1
Listings ________________________________________________________________ 52
Listing 1: setVars.cmd ________________________________________________________________ 52
Listing 2: prepTPCH_SQL.cmd _________________________________________________________ 52
Listing 3: querygen.cmd _______________________________________________________________ 54
Listing 4: indexgen.cmd _______________________________________________________________ 54
11.2
Querypläne _____________________________________________________________ 55
!
#
$
%
()*
! + ! #
,
. )*/+!
!
)* 0 1+
) + +/
3 4 !
)*
&
)*! 5
,
!
!!
1 )*
!!
+
7
/ , +
,
2
$
#
+!!
;(* , 1+ #
,+,
2
)*
=
2
6 )*
3 4 !
/=*
)* 3 4 ! 1
/!
1
& > 3 4 ! /= /
2
1+
- @4
!!
*
()*
/
* +
)*
4 , )*
!1 )*
1+ >
)*
> 4!
2
!)*
!/
/
+
#
;+)
!
/*
3 4
!
!
! ' !/
4 , )*
+ !
! )*
8
?
!!
)*
/=
+ *
)*
/
5 *
&#
!!
2
-
#
/
+
& 6 )*
8 9 ' : )*
, /
1+ ,+ 4! <
/
&
+ &
,!
,
&
+ !
/=
/=*
4 ,
&'
?
4 )*
8
,
#
,8
2
4 )*
/
/=*
#
1+
)* +
'
? ! )*, * 2 , 1
4!(
/ & # 3 4 ! )*
#
,
2
!)*
)*! 5
*?
!
6
! )*,
)* ! )* A+
/=
!! & #
/=*
4!(
/
&
1+
2
,= /
* !
&'
-
& $
+
*
8 9 ' 1+
&'
)*
/+
3 4
?
! )*
+
,
*
+
;
!
&
)*! 5
B
3 4 !
)* > /+
/=
&
+
/
&
"
!
/+
3+ /
>
3 4
!)* '
)*! 5
!
()*
+/
: )*
&
/+!
*
! 8
+
,?
-
,
,
8 9':
9+ 4 C )
) + +/ B
+
)*
!!
'
,+
#
)*
!! &
B
?
+
+/
8
-
&
& ,+ /
! )*
!!
D *!+ E" "E F . &" 7' 0
D ' G < G 7:
.## EE 0
D'
7: 8 . & 7 H 0
D ' # # . 8 GE FH B
* )
;+!
! ),
, +
2
)*
+
+
I4
! :
B
+
E" :
,
>
;
2
&
+
( ! )*
2
, 1 2 +
)*2
!)*
+ ) 1
)*
8
2
#
/+
0
+ !!
+
)* 1+!! (
(
)*
2
&
/ /=*
!! , 1
/=
-
!-
(
4
= 2
/!
!
"
)*
*
44+
#
!!
$
K'3 LM @ 9 M 9'>6 N L 8
P
) + - + PQ + J
N9
9+
+!
N9+
+!N
K'3 LM @ 9 M 9'>6 N L 8 N9
PB 8+3 !! 1 ) 8
+ PQPG
P
9+
+!
N9+
+!O
+
6 8N9
K'3 LM @ 9 M 9'>6 N @ ;8B
P#
!
PQ + J
G
N
K'3 LM @ 9 M 9'>6 N L 8 N9
P <I
+ PQ + J
///
P8)48
B # ! -PQ + J
G
P )8
B
C9* ),PQ + J
K'3 LM9I
P6 +
PQ
6 8MI
+ J
N +/
G
N
) + +/ NB
9+
+!
N
O
+ N -
1 ) N8)4 4N
+ O
O
G
) + +/ NB
+
N9
+ N <4!+
N# , +4N9!
&
)
J
D ; *!
D D ' !/
D> <
!!
& #
4B
O
2
!
/+
3+ /
+
K'3 LM9I
6 8MI
N9+ +!
PB 8+3 !! 448
+ PQPG
P
!N# , +4O
!
!
!
+
!
#
,
% &'
(
2
!!
!
#
,>
@4 +
2
(5 =
+
=
?
)*2
"#$ "
!! +
)* ! )*2
(*! J
>
B
) + +/
& +
:
&
:
!
,
!!
1 /=
;
2
!!
&:&
&
> /+
+
3+ 4+
#
+
,+ +
*
#+ !+
? ! )*,
) + +/ #+ !+ 9
* 4JHH
& ) + +/ &)+ H +
S GGE# TS9 #U 4! -! Q
!+
H
! & 4<R/
/
&
* ! &
+ 2
4 )*
J
/
!- Q :
)*/+!
S:T G G "#:T
)*
,
!!
+ 2
J
!)*
;9
%
#
)*
/!
•
<
•
G 7:
•
7:
•
,
•
,
*
+
/
!G
+
+2
! )+
<
<
! #
!
I
=
*
+
*
)* ( ,
&#
+
! )*
! )*
#
+!!1
=
!
, ?5
+8
"#
-
+
+ /=
)*
E
!
/+
3+ /
•
/
#
!!
)*/+!
& 4<&
7
+
4+ H <4+
=
,
!!
/
? ! )*,
!
J * 4JHH
+
+
; , +
) + +/ &)+ H C!H4 + /+H/
&
:
>
: )* ( ,
#
, ?5
)* 2
*
/ !! & #
)* ( ,
/ G 7:
+
+ *
/
: )*
,& 3+
!!
+
1 /=
+ =
+
!
"#
2 !)*
#
, )* ,
* )* ! )*
/+
)
1+ )*!(
; , +
&#
=
;+
/=
* !
(5
:
8++! &
1+!! ; , +
)* =
2
&:&
$
)*/+!
,
/
J
* 4JHH
T S T S
$ J
& ) + +/ &)+ H + !+
) )U 4! -! Q W/! !
! % J
* 4JHH
& ) + +/ &)+ H +
) S"G T " U 4! -! Q
$
!+
* !/ )* B ,
#
8++!
*?
1
)* ),
. )*/+!
H
! & 4<R/
!- Q
H
! & 4<R/
!- Q )S E T
,
3+
2
+
! 8++!
/=*
+
!
T)G E
)* !!
!
,
7
/!
<4
? ! )*,
)*
+8
#
4+
& <4+
! (
!
)* V +)*
! % 2
!)*
G ) "" T T TG
ET/ ")E/ T
)*
)*
"
/
H)+ 4
1+
* 4 ()*! )*
!)*
* 4 ()*! )*
/
2
0&
)*
8++! /=
+ /!()*
/=
3+
, +
!!
)*
4* )* :
&# ' 4 /
+
#
, 1
!
#
,+ V , & + *?
&:&
#
, 1 2
'
/=
1
44 2
!
:
,
+
!! 1+
)* 1
&
B
*
!!
&$
)
!
*?
!!
+ /
)* ! )* #
,+ V , & '
(*!
& &
8
!! 2 )*
>
&#
4* )* @
/!()*
? ! )*
6
!
2 )* +*
3
& $
/=*
1+
* !
"
? ! )* & #
8++! ,
!*( /
2
)*
/
! )*
(
! 5 2 &:&
)*
+ /
1+ >
&
! )* ; , +
8++!
? ! )*, 2 )*
4 )*
- !(
!
& #
,?
4* )* +
8 < /+
!!
&
:
I
'
4 , )*
1
!
!
)*
4
/
*
2 +
=
)* )* !!
&%
3+
+
2
V
!
* !/ )*
2
)* *
1
)* ), 2
/
/ /
,
+
&
!
/+
3+ /
,?
=
!
+
!
,
6 )*/+!
J
!
'
=
!
4 !* /
)*
4* )*
/=
&# @
/!()*
#
,7
)* /
+!! &
' (
=
+
:
)* ! )*
G
//
*
J
+
4 &
!!
!
1
1+
/
1
? ! )*2
&
-
/=
4 )*
!
:
#
I
& +
)*
#
*
&
1
"
2 #
/
2 +
)* ,
&
)*
)*
!!
!!
2
)' (* +
)*
,8 9'#
4 )*
! &#
' !/ 8 <
,
+ &E&G2
!
-4 )*
/
!
#
,
*
)*/+!
/ /=*
#
+
)* 1
,?
G
&
)*
)* :
,#
&
S
!
/+
3+ /
+
# 8
!!
!
*
*
)* ! )* 3
! ( & +
*(! &:&
?5
>6 >8
E&
&
#
( .# 02 (*
,!
8
!!
7>@ 6
/= /
7 ?5
#
,1
,?
)* 1 )*
:
)*
),
V
!
+)*
, !
/ , + . ;0 *
2
!)*
3
!
! 4!
&
; ; , + 1+ G
&:&
#
7 ?5 1+ G 7:2
; ; , + 1+ G
#
/ G
7: 1
?5
= &
/
)* ( ,
1+
1
<4
+
/
<& "
4 )*
?5 *
; ; , + 1+ G
(*! &
# > !! +
>
!
)* 1
#+ !+
,+ 4 !
& #
#+,
+ @4 +
(
+)* /= (
/ *!&
)*
4(
!!
+4
&
6 )*
!
/=
3+ /
+
/= (
,+ 4 !
&
8
!
' (*
!!
)* +*
=
)*
-#
)* X
/
7
)* )*
&# 8 9'
!!
!
/=
$ !4! /+
#
, /!
)* & @ +*!
)*
2 !(
)* 8 9 '
2
)( 2
*
%+,, )*!
+// )* ! )* )*
)*
4+
&
-
*
,
!
!!
/
,
4 ),
*
+
8 !!
&I
2 ,+
( +)*
(*
;+!
)* 1 /
% .)
1+
2
@
-!$+.
#
& 6 )*
'
1 0
)* 1
@
&N8 9'
4 ),
%
=
&#
&
T
!
/+
1
B
3+ /
@4 +
+ !
; *!
!
+
,
V
-
)* 1
! 9FF <4
!
/=* J
!
+
+)*
11
,
)*
8 9'
)*! / *!2
)*
,
!
4
+V , #
&$
/ )* 3+ 4 !
J /
&#
! 5 )* V +)*
+ E ?// 2
& &
/+!
!
!
+ 9+ 4 !
,!
+
!
,+ 4 !
& #+)*
, /!
)* 4
J
# /
),!
+ !
,+ 4 !
+
,
@4 +
B
+
:
!!
*$
)(
B
4
+1"
*
+1"
&6
* !!
9&
&)-
&)+
/=
&B
)*
"
2 +!!
8 9'
#
3 %
; *!
!
)*
2
/
!!
2
!!
/=*
/=
1
Y
!!
"
!
2
+
)* 1+ *
%&
#+
!+
/+! 2 +
?
8 9'
,
<
!
? ! )*, &
,
" 0
&B
+)*
/
, /!
@4 +
/
/=*
)*
)* &
)*! 5
> /+
)*
I
1
,
2
1+ (
& + )* * !
+!!
2
+
? ! )*,
3+ 4 ! 1+
(*!
)* = ! )*
!
2$
*2
J
&
G
!
/+
3+ /
+
J
8
1"
&
#
8
"
*
=
!!
' (* '
!!
/=
& Z
/7
4 )* 4!
,
#
,
)* ! 1
! ,
: )* ( ,
1+ " 7:
/=
!!
>
/*
#
#
, !
,
& > @ V , <4!+
, 2 8 !! 2 >
C!
&"
2
"
#
"
,
,+ 4 !
?
2
1
/
8
& #
#
/=*
1"
[
* ,+ /+
3+ <
1
&
, !!
!!
%%&
8 9'
= )* #
, ?5
/
7 ?5 1+ G 7:
)*
*
&
)* 8 <
,?
2
1
/
Y
2
+)*
%&
!
)*
!!
"
/
8*
&
GG
!
/+
:
#
,
3+ /
!!
2 /
!!
$
+
#
)&& #
,!
+
) 4
)*
, &
/=
[
)*
1
!!
!
@4 +
V +)* /=
2
%#
=
8
?
&#
/
)*
!!
4
/=*
,?
& !!
#
-4 # 8 )* ,
&#
: +
*
*
)*!+
2
)*
=/
8
, +
)*
&
X
1+
"
+
=
3+
+ !
/=!!
,?
2 =
+)*
1+
"
I < ;+
4 )*
J + !
)*
,
&#
1
)*
; *!
/ /=* 3+
+ ! : / *!2 !)*
!! 8
!!
2
:
)*
2 + +,+!! 2
1+!!
(*! & $
, )* ) 4
#
!
&8 9'! / *
#
%& )*
/+
!
X
!2
# 8 8>
!
&
2
!!
7@
)*
!
+
2,
8 !!
8++! &%
!! & : 1+
8
!!
#
4
2
*
$ )* 2
!)*
$ !
& * !/ )* // *
)*/+!
!!
B
+ ;+
,+ 1
J
!"#$%&
!"#$%&
'
! ( 4
' !/
4+
>6
8 .:>0
!!
/=* &
' !/
:>
= )* ;+
,
#
*
+
)*
)*
+,$
# #
'
(
!!
2,
2 /= V
8
"
$
&
&
?
#
!
#
)*
8
!!
+
4+
3+
=
+
2
=
+
+
!!
:I 3
&%
!
&
+,$
' 6
:4
7
%
&%
-. "/#0 %123&!
&45
%3#$!3&5%19!4&
6
&4,!3&5%19!4&
,%!8
;
<
*(! /= V
8
!! /+!
J
G
!
/+
3+ /
,
=
)*2
:I 3 >6
I < ;+
&
,+ 1
#
)* ,?
J
< -
+
8
8
)*
*
!!
' !/ 1+
72 =9%4 2>#3?+&322 7
& & ' (* #
)* !$+. -&
/=* & I
/=*
!
& &"
/
/
& # #
&"
/
/ J
>
4&
#
@ @
$"1953
/
&
7 BC
<
! + +)* X
/=*
8
!!
*
=
!(
2
+
$ )* 2
#
+!!
: / *!
;
#
/
&
1+ *
,
/=
<
8
2
!)*
+)*
&"
5 &%#
!+
1
=
-
7
+2 !+
$ !
V +)* / *!& # *
/+!
&%
)*
2 =
,?
<A
)* *
=
'
/+!
+ & +
!! 2 )*!
(
! )*
)*+
2
:
)*
)* 2 1+
)*! 5
)*
;+
,
<
+)* )*
1+
*
2 1+
?)*
+
X
J
1+
/+
)*
/
+
8
2
1+
& #
)* =
+, -
*
+, -
SET
> 8
=
7@
*+ 4!
+ )+
)*!+
+
G
!! !! SET
&
8
'@ B
@6Y
7@
1+
SELECT
3
6 M\
28
8]
8 @ B 9@ I 6 8 Y
7@
@ 4 + V +)* / 6
:
/
)* ! &
3+ <
1
&
8 # 8 ;@
8 L #Y
&
!!
!
+!!
1
2
"
8 9 ' 4!
2
/
^
,+
#
1
)*+
V +)*
&
1
G
!
/+
3+ /
+
#
7@
,
; , +
J
1+ 8 9 ' 1
- <&
_
_`
; , +
1 ! _% _
<
.
).
/+
)+ 1
.
3 1 .% " //%
% 1% /
0
&
/+
J # -2 B
1+
4+
*
7
#
#
2
/=
,
0 7
4
&#
8
&
<
)*
&:&
)*
2`% 2
/+
!!
*
/=
,
&
+
*&
/ , +
,2
1&
$
; !
,
2
! )*
1
8
2
+ *2 L
+
#
#
*
/
)*
substring(
str from
-
)*
/+!
#
)*
,
Z
)* =
)* ! ' !/
& 9-
+
!+
!!
*
" 0
J
J
& 8 9' +
"&
"
! )*
)*!= !
< )* &
3+
!
&
" &;
!+
8
!!
J
/+
! )*
)*
8 9 ' : )*
, ?
/=
,= /
6
&
9!!
& # /+!
4/ *!
)*
,
!!
= ! )* +)*
,+ 4 !
!!
2
!)* 1+
&#
>
!!
+
!!
,
=
/= ) 4
2
(*!
%&
&
!! &
#
E& 8
!!
/
!!
!
,&
/+
#
&
/= 8
*
+
!
+
)*
)* +
, 1
G&
#
=
.
#
&
8
substring(str,
% )
for % )
/+
) 42
.
* *
,
4+
&
&
!)*
+
%4 48 9'M
)*
&)
0&
1+
"&
) 4
& 1+!!
!
2
G"
!
/+
4
)*!=
*
3+ /
1+ &"
1+
*
!
8 9'
!&
+
.
!!
*
*
?5
/=
8 !
!
8
%C Y
&)
!2 +
+)*
0& #
< *
)*
S
/=* ! )*
G
/=*
#
&
,8
/
&
0
!*'
#
*1,
$ !
#
,
2
!
?5
#
+4
& '
*?
,=
1+
+
8
, +
)*
& >
)*
*
#
, /
2 +
8
5 *
*
4 ,
1+ >
1 )*
A+ > 4!
1+
2
1+ @ 4
'
+
)*
4 )* 0& #
<4
+
2
)*
#
,
+
* 1+
? ! )*,
#
* +
)*
/=*
/=
*
8
4 , )*
!!! &
$
@4
)*
2
5 *
+
!!
*
+
4(
&'
/=*
-
!!
3+ <
! )* 1 ,=
, !
,?
#
%
V
,?
/
)*
&
)*
!
1+!!
;+! 2 ,
& +
/!
&
/
#
,
- @4
.#:
/
!
& )*
V
)* +// !
2 +,
!)*
)*
)*
!
)*
! , 1 (
:
#
( 2
!)*
*
)*
()*
)* 2
!( 2
!)*
>
+
, > /+
+
/=*
4!
)*
+)*
)* ;(!! 2
,
)*
/
1+
*?
& '
)*
I
)*
. &:&
)* ( ,
2
,
& 6
5 *
@4
,
*
2
V
&:& >
/=
6
2
4 )*
,=
& B (*
2
*
V +)*
)*
+ )
4 )*&
)* *
>
2
!)*
)*
A+
/*
! )*
/ ! )*
+//
,
+
! )* #
2
8
5 *
1+!!
,
(*!
*1,
#
>
*
#
0
#:
@4
/ ,?
*
/
*
! )*
2
3+ 4+
+4
!
V
+
!
#
:
/
/=
'
V +)* !!
*
!!
*
,
>
!
2
J
/
! +
8
/+
& '
*?
&:&2
8
& +
/
)*
/=!! 2 /=
=
& #
+
! !
+
1 $
01
2
!
@4
+
)* ! + *
&
)*! 5
!
;+
+
(*!
&
- @4
)*/+!
!
1 !
;(!!
2
=
!)*
/
1+
! , +
)* 2
)*/+!
01
!!
@4
*?)*
+5 8 !
@4
+
)*
)* & >
*
)*
1+ *
/:
1+ 1+ *
!
=
)*
=
!)*
2
)*
/=*
,
4!
&7=
(*! 2
* /= ,?
GE
/=*
#
,8
&:& / *!
& )*
*
,
1
+ ! )*
2
!)*
/=*
)*
+
)*
!2
*
/
B
7 /*
#
,
+
)*
!
1
! (5
(
?5
8 !! &
>
/=
()*
)*
&&
"
>
*!
>
2
/1
)*
4!
& #
!
*
! ;!
*+)*2 &*&
-4!
)*/+!
/ /=* 2
I
- @4
!)* H > < . ,?
+!!2 ,?
&:&
, =
8
&
6
,
5
,6
2 +
!
8
*
!!
+
2
5
1+
,
! )*
/=
+ ) 1
)*
;+!
& # *
/
,
? ! )*
, !!
,
&:&2
,
, !
+
)* V
!
/
)*
,?
2
:
!)*
/=
)*
#:
&
+
*
! )*
/!
/
! )*
)*
/
/+
)
5 /= 4 ! 2
!)*
B*
3!
!
& >
+
! , 1 (
/=
#
(
* !/
> <
/
2 +
&6
> < ! , 1 ( 4 !
)*
)*
+!! &
#:
!
2 ,
%% 3
9
8
!!
! *! 1+
!
)*
/
+4
'( / , 1
!
4 !* /
' +
%823 / 3
3&
1
1
8 !! 8: 8!
4 !
!
+
)*
*
>
:
/ 1 )*
!! 2 >
2 4 !
,
* !
3
4 )*
/
X
2
* ! & #
+/+
8
/=*
2
>
#
/!
/
+
&
+5
!
+*
)*
*
, > /+
(*!
? ! )*,
1+ :
- @4
!! 2
+
!!
4!
(
*2
>
/
,
+
2
<
!! 2
(*!
/=*
!! +
1
!
#
O /=
3
)*
+
(
V +)*
>
+
? ! )*
,+
0
4/!
* !
!
2+
&
(*!
2
4 !
&
)*
+
)* , !!
7 $ #!
!#!8 !8+
)* /= !! >
&
, > /+
+
2
!)*
!
, 2 1
&#
/
)*
, > /+
+
!
B
2
)*/+!
!
!! 2
!)*
, =
1+
&#
> <
* !
4 !
/
8 9 '#
,J
)*
+
!!
!!
& &
&'
> <
2
G
/=*
#
#
,8
' +
.
V
!
!!
)*
4 !
2
2
J > M * 4M
)+
31
!
4 !
MC
.#
2
*2
+ !
5
.> /+J V
)* /
8 !
)*
0
#
*!
)* +
!(
/J STGET
(
!)* /=
+ *?*
!!
.
,
!!
! (
. !!
>
$/J /=
<&0
!
4 !
4 !
/
+
M '> # 8 J 2
T
M '> # 8 J S .> /+J
; 4!
)*
> <
( ,?
/
(
;+! 2
)* !
/(!! 2
)* +
! )*
> /+
+
!
2 &:&
1 ! 8 //
/
#
% & G&GTT .
# 6 7 (8 89(:2
+
7
' +
=
. : 4 !
(, 8#0 ; + GG0 +
*!
8 //
)*
& G&GTT
GG& &GTT 2
!)*
# 6 7 ( #0 ; + G STT #
(
! /
= & #
/ T 1 )*
#
.+*
+
7
& G&GTT 0
4 !
+ 6 * 8# 6 7 (8#0 ; + 1
! &# !
4 !
)7 8# 6 7 (8#0 ; +
+44 !
B
* !
' +
)*
J + ,+
# )* )*
T ! )* B
/V
8
)*
& G
& &GTT &
)*
2
!
1! 0J
/
.#
, 2 V +)*
/ !! > < 4 ! J /=
(
*
/2
1 ! > <
(
/
,?
& A ?5
!+
)* )* ! )* ( 2
+
4 )*
& #
*
)*
)*
+ +
!
,+4/ 2
!)*
V
$
//
/
; 4!
! )*
1
&0
>
-5
/!
2
!)*
)* ,+ ,
-
* 1+ > < 4 ! 4 (/<
# )* 2
+ ! , 1
!
/=
J GG& A !
!!
*!
*
STGET .> /+J V
/ !! *?* &0
* 1+ > /+
+
+
4 ! =
! ,
* 0J
7
!)*
#
+ !
,
(
5
,
2,
7! )**
/
!+
+
' !/
)*
! )*
:
)*
/
GS
/=*
)*
/=*
#
4!
,8
+
+
+!! +
)*
)* &
!
2 +
>
<
GT
/!
4 )*
-
?5
2
>
#
3 4 !
,4 //
/
? ! )*,
*2
:
/
,
+/ !
,
/+
)
)*
4 )*
#
>
* !
7
&
I
2
# )*! /
' 4 4 )*
?5 .
! )*
/=*
,
+!!
/=*
/=
! 1+
34
7 ?5
!
7 ?5
/=*
! .GE
)* ( ,
)*/+!
)*
)*
2
?5
)*
,
-
#
#
4 )*
$
//
*
:0
<4
: !,
,4 //
/
!
/=
/+!
/
+5
+ 0
I
<
/=*
)*
$ << '
*
-4! 2
!)*
)*/+!
! )* #
(*
/=*
-4!
# )*
V
!
?
#
7 ?5 +
4 )*
* !
,?
2
! + ? ! )*2
9 )*
!
&
*
!+
=
+5 #
<
$
//
/
; 4!
V
! 1
1 ,=
+
&
1 !
* &
/
/
: )*
,
G
2 +
< ! .G 7:0 ? ! )*
/=
!!
& #
!! J
G
- GG
/!
2
.9 )* 0
2 G 2 GG
/=*
4 )*
! )* 1 ,= & :
*
' 4 4 )*
?5 ,
/=*
4!(
$
,+
! )*J
7 ?5
#
,
4 )*
* !
/
!
!)*
! )*
+ ,
7
!
< !
)* :
/ 4 )*
!!+,
V
/!
/
+
!
!
2 ,?
!!
,
V
2
4 )*
1+
&#
!!
; 4!
!
2 +
& # #
,
()*! )*
!-
B
/
<
,=
& :
1+
!- GG
,
/ /=*
* / )*
!
2
,4 //
2
!
-G
-G
2 * 1+ 2
=
&
!)* 4 + ! !+
*+!
$
//
/=
2 ,?
;+!
,
/!
4 )*
?5
/
/=*
&
S
&
&
S
> 7
,
$ <'
&
- GG
)* ! )*2
#
V
!
!
!
G&
G &
/ /=*
J
/=*
4!
&
G &
7
* !
/=
2
4
=
/ )*
-G ,
+!! 2
=
&
#
,
2
?5
&
7 ?5
/!
!)*
)*
!+,
*
!
//
;(!!
/=
/
(*
! )*
+
/+
)* 1+
4 )*
)
/=*
*
#
1 !
2
& +
V
4 !
!
#
1+ *
#
1+
4 )*
*
,&>
+
* / )* /
! )* #
#
1
(
+
;(!!
! )* ,=
#
,4 //
4 )*
!+
4!
!
;
!!
!+
:
/
2 ,
+
1+
=
)*
)*
G
'
/=
@4
J'
5
/
' !/
+
1,
'
6
.'
2
,
'
$
> /+
=
!+
$
)*
)*
)* ,?
2 +
; !! )*
!
,+
2
/=
B *!
/=
* 2
/ )* )*! )*
&
7=
+
+
!)*
; !
) 1
/+
=), )*
()*! )*
&:
)*
! )*
)*
@4
)*/+!
3 4 !
V +)*
#
!! 1
2
*
#
'
,?
2
+)*
5
/
1+ '
+
:
4!
! + +4 + !
)*/+!
: 4 !
2
6
7 /*2
2
&:&
)* /
,
)
1
)*
/=*
4!(
&
&
/+
,?
)*
=
/=*
/+!
? ! )*, 2
- @4
/=*
* &#
'
1
* 2
/=*
)*
'
* ! & # @4
)*
+4
!
/=*
4!
)* & # *
+!!
@4
)* +4
!
/=*
4!
(*! &
)* +4
!
*?
&:&2
@4
)* !!
B *!
+4
!
/=*
4!
?
'
=
)*
'
0 *
4 )*
! %'
4(
!
Z
/=
1+
1+
+
5 *
>
2 1+ @ 4
2+
/
!
' !/
'
2
!-
4 =/
+
+
/=*
&#
,?
& '
,+
! ) : / *! 1+
,
V
'
2
7 44
!)*
2
&
!
! ) : / *!
&
7*
# )*
> <'
1+
2
!)*
V +)* 1+ @ 4
+/!
!
6 )*/+!
# #
!!
'
1+
:
4 !
,
J
*
? ! )*,
4 )*
/=*
(*!
)*
&
>
9+
2
& +
<'
!)*
2
+
*
2
( ! )*
-
)*
+!!
>
+
Set showplan_text on
Go
Select *
From aufgaben with (index(i_startdatum))
Where startdatum < ‘15.10.2007’
>
&
<'
!+
@4
1
!
2
&
2
GO
>
< 3
2
<
/+
USE testdb
# )*
>
,+
= ! )*2
, )* 1+
- /
*
'
/=
?)*
+*
@4
J'
, *
)*
? ! )*,
*
@4
/+!
*
'
2
>
<
(*! 2
J
USE testdb
GO
Set showplan_text on
Go
Select *
From aufgaben with (index(0))
Where startdatum < ‘15.10.2007’
5
8
#
/!
> 4!
!(
*
7 44
'
/
/=*
2
!)*
+
A+
2
A+
/=*
'
&
A+ @ 4
+
2
3 4
' !/ ,
*
&#
! %A+
!
/*
,
A+
(*
&
$
'
=
!)*
! , 1
!)*
>
+
/+!
=
@4
> 4!
0
* &'
&
A+
8
!!
:
:
/=*
4 !
'
2
2
!)*
! ) : / *!
4!
!+ /=
*?
,
2 !+
!! A+
'
2
!)*
! )*
1 )*
- 1= 2
* /+! 1+
&:&
+
)*
&
!
J
USE testdb
GO
Set showplan_text on
GO
Select m_nachname, m_qualifikation
From aufgaben join mitarbeiter
on mitarbeiter.m_nr = aufgaben.m_nr
Where aufgaben.bezeichnung = ‚DB-Tuning’
option (merge join)
#
)*/+!
4 ,
! )*
/=*
)*
!
'
3 4 !
> <
2
*
)*
* / )*
!
)*
!)*
& :
&
8*
>
4 , )*
,
I
* +
)*
& $
>
/
)*
2
>
<
9
7
9
/
>
>
3 4
+
<
!/
()*
> /+
!-
- @4
2
#
+
/=*
)*
4 !! + *
#
*
)*
,+
1+ +
8*
/
&B(
>
)*
&:
* ,!
#
,
&
8 !!
: )* 1 ! )* !( &
/(*
& #
#
,?
)*
+
1+
&
=
)*! 5
!
>
>
1+ >
,
;+!
2 +
&
4
;=
!
7
1+
H@ 4 @ 4
/+
)
)*
*
2
+
?
* +
/=*
@4
1
)*
+ !
,
4 )*
2
; )* )*
8*
2 )*!(
: )*
= )*
; )*
4 )*
*!
: )*
! / 2 /
< )* 1+ *
2 =
)*
/=
)*
:=)*
)* ! )* ,
+ ! 2 V +)* *
I / 2
)* *
,+ 4!
:=)*
! 4 5
)* +* > < J
,
!+
*! + +*! 1+ !+
4 !
+
*! 1+
)*
!
//
>
)* 1+ 4*- )*
! )* +!!
/
)*
*
*J
),
: )*
!
>4
&
9
#
!
1+ >
,+
: ! ) , ! )*& /
,
> <
// /
#
, +/ !
+
)*!
& /
*
>
! 2
B , 2
)*
$
// * 2
+5
!
/
1
)* , 2
, !! * ! & $
*?* )* (*
, !
7 / * 1+ $
// ,+!! +
& >
8
!!
?5 ,! 2
+/ !
= * 4 , > <
? &>
;(!!
,+
=
2
8
!!
/ )* C
!!
)* )* & #
+
!
!!
! +
)* & )* 8
!!
(
! , 1 (2
&:&
4 !
%7 )*! )*
%
%;
; !! 2 =
> <
2 +/
! )*1
!
&
+
!! )* ! +
/
2
)* ? ! )*,
+!)* >
? ! )* 1 !
1+ (
4 +/
2
1+ + 1 !
?
'
4
!! 8++! * !/ )*2 !)*
4 )*
:
/
B
* ! 4 ,
*(! 2
!)* 4 !
/
&
9&
#
()*
2
!
+
, 1 (
+
2 1+
? ! )*&
)* 2 +
=
*( /
7
!! 1
( ! )*
)*
1+
11
>
/=
2
& #
11
)*
2&
)*
"
>
<
9&
: ,,
7
44
> < .9!
> <0 ,
8 ! /+
)* 1 ! )*
,
+
> <
: )*
/
8 ! /+
)*2 ! +
(
! 2
> <2
V
6
)*
!!
/
&#
(
!+
44
+
/
; 4!
4 )* & #
1+ > <
:
)* /
+
+ !
)*2
(
+)*
)*
/
!
!
2 )*+ +
1+ !
,?
&#
)*
$
//
+ 1 ,!
& # 6 )*
! )* ?5
$
/
2
!!
/!
8
/ (
2
*( /
B
1 )*+
=
? ! )* & )* ,
= ! )*
V
!
44
> <V 8
!!
!
( )*!= ! !
(5
2 +/
)*
:
)*
!
&
9&
:
#
;
,,
7
)*
44
> < . + 9!
> <0
#
!!
/=* 2
!
2 !)* > <
/ !)* #
4+
)*
)*
I
)*
44
> <J
8
!!
*
!
1+ > <
=* 2
!+
'
/=
: )*& ,?
*
)*
44
>
/=
8
!!
9&&
6
+
)*
+)* 1 )*
/*
1+
(
#
,?
*
?
2
4+ !
>
!+
1
#
7 44
<
/
*
<
%
)*
/ )*
2 +
2
!
/
*
.
/
2
; !
,
2 !)* 1+ >
*?*
7 )*
,+
+
=
>
<
> <
! , 1 (&
!!
( ! )*
+
8
!!
()*! )* 4 )*
>* ! 1
)*
!
&
& % )*
44
1+
!
)*
&
>
<
+
&:
/=*
-
&
,+
<
,
)* 2
2
),
& #
; !
&#
+
!
=
)*
)*
; !
* !
#
> < 4 ! Y
( ! )*
4 !
2 ! + 6 )* )*!=
*
8
,+
4
/
; !
,
4 !
>
.4
>
>
,
% 44
1 /* &#
J
&'
1
2 +
!4*
)* +
> < )* 4*- )*
= ! )*
+
!
2
=),
!
!
9!
> <
& +
*
/= +4
+
!!
&:
44
> <
/
/
>
4 !
!4 !
2
2*
</+
/= 2 !!
* !
! ) 3!
! !
/
4 !
+
:!
B*
%
3!
! !
)*!+
:
&
>
<
#
> <
*(! !! 4 !
4 )*
- )*
*
> <& # $
//
/
#
(
, !! * !
#
B
* 4JHH
> /+
&
/
#
& #
*
!!
!!
/ !!
+
!
!
& !!
+
!2
#
2 +
9+1
,
>
<
&
+
8*
) + +/ &)+ H
H!
> < /
)*
-H GSS S & 4<
) + +/ @ !
#+,
J
9 & - %/
+
/
*( / 2 /
> <2
!
!
X
! +1 !
4/!
'
1+
@ 4 + 8 #+!6 ; <
& #
,
+
1 ! +
4 )*
V
!
> <
!
1+
,
2
)*
#
*( / (
2
* +
! /=
,= /
X
2 &:& ;+ 1+
2
2
> < V
!
+
2
#
&3
V +)*
7
1+
* 2
)*
#
)*
*
)*
;=!!/ , +
2
G a
*&# *
+ !
)* !!
* > <
(
/
4
2
+
4=
!
; 4!
/+
! )*&
>
<
#
2
4
+!!
2 /=
)* 7
!)*
+
!
,
> <
2*
)*
* )* ! )*
!!
&X
)*
= ! )* )*
,
4(
#
/
&
!)*
2
+!!& > &:& )*+
,!
+
+*
*
,+
(
2
$
// & A
( =
9&5 <
=;
# )*
#
(
&'
=
; =7 8 2 ; 6 ;) #: 8>
I
*
/
!+
9-
+
:I > #
@4 +
2 +
)* @
, 1
1
>
7
#
/
$
(
2
# ! ; 8:
!! >
/
& ?)*
+
4
02 1
@ 4 + #> : &
2 +*
*
. &*&
/=
:!(
%
>
)*
2
> <
4
( ! )* @ 4 +
8 #= &
:!
/
,
*"
#
;
*
)*
%;
<4
+
/= / *!
! / 2
6?
"
1
>
!!
>
2
<
>
<
7
!)*
= ! )* '
/ / *!
>
/
!
1
, & #
+
1+!!2
- @4
)* +4
!
/
4!
, 2
4 )*
>
)*
!
&#
/
@4
!()* 2
1+
/
4!
!! 2
!)* >
/=
4 )*
& #
> /+
+
=
>
!
+
)*
/=* 2 )*
>
)* 1+ *
&
"
+
!? )*
)*
4*- , ! )* @
@4 +
@ 7 6 >$ & I
/=
2
&
E
>
<
-4! !(
4 )* & >
!)*
>
*+ 4!
! 2
' !/
>
> /+
V
!
2
)*
1+
*(! 2
1+
4* )*
)*
- @4
,
>
/
/=* & #
( ! )* > /+
+
+
/=*
I
+
)*
!!
2 +
;
!
! / *!
2
- @4
2
!)*
/+!
6 )*/+!
+
,
4 !* /
!!
/
)*
+
<
&
!)*
/+
/ *!
)*
!
>
#
> /+
%
,
4+
6
=
!
!
)*
-G
)*
)
&
&
8 9 'J
*+ 4! 2
/ *!
>
!)*
!
)*
b
J
>
< c
2
>
<
> :
6
4 !*
@4
<
>
,
2
)*!
8
!! % >6 >8
/
M@ # 3 L2 M 8 6 # # >9
6
+)*
)*!+
/+
)
/
? ! )* ( 2
M 8I 6 ; 7
M#> 9@ I 6 8 !
= &#
!
> <
=
E .d0 a
&
!
)*!+
/+
>
)
1+
#
! (
)*
95
<
@ M@ #
G a
+
& #
&
*
2
2
/+
;
)
2
3 4
*
! S& & %
=
@ M@ #
)*
)*
# 8
*
.
8
0 >
!
<
62
>
)*
/=*
,
1
)*
>
/
)* &
:
*!
X
! )*
-
/
/=*
+
>
)*/+!
/ /=* & >
)*!
#
!/+!
& & /
!
&
I
8
!! @ #
/
3 L
@ M9I 83 L
&
)*
*2
%Z
)*
2
)*! 5
&:& X
)*
- E . & &02
+
$
// /
8
2
!)*
)*
$
A+ ;+! & #
)*
8-4
1+ >
' !/
: !,
V
!
!
*
()*
/=*
- E +*
/=*
1+ "2
,
.8-4 G0& ;=
()*
#
)*
44
> <
2 !)*
!! 4 !
B*
3!
!
),
Z
)*
2
/=*
4 , )* ,
/=*
! )*
# )*/=*
8-4 G
&#
)*!
/=*
4!
2
!
> < 1+ @ 4
)*
+
! )*
/=*
)*
* 1
&#
V +)*
1+
* ,+
2
> <
/=*
()*
)*
.8-4 0
' !/
+
'>6 8
@4
!
> <
*
&#
!
/=*
* ! * !
&
!!
*
/+! 2
)*
> <
!! )*
1
!
&
# )* (
> <2
)*
.8-4 0& #
5
/=*
!-
(*!
' !/
)*!
,
,
=
2
2
+
S
>
<
8-4
"
44
> < /
.8-4 0
! &'
V
!
/=*
)*
! )*
/=*
- * 4 ()*! )* :
)* /
)* /=*
+4
!
+
&#
8-4 " +)*
)*
)*
)* ! )*
! , 1 (
1+ 8M /
1+ 2 .8-4 0 ,!( &
3!
8-4 E
!
&
)*
* !
8-4
* +5
,
)*!
* !
>
>
> <,
)*
*
4 !2
<
<
/=*
@4
/+
44
>
/
>
>
<
8-4 T
//
/=*
&
' !/
2 +
)* &
>
)* !!
<
/
2
4
!
/ !!
4 !
B*
3!
!
!
/ !!
4 !
B*
3!
!2
(*!
44
>
>
<6
<2
.> M * 4
0
"0 7 44
>
<
/!M * 4
0 7 44
>
<
/!M# )+
E0 $
2
< )*
44
>
<
/ !!
4 !
B*
3!
!
44
>
<
/ !!
4 !
B*
3!
!2
(*!
0$
2
<6
S0 $
2
T0 9+1
> /+
!
9+1
8 !!
,=
!
<2
< )*
0 6 )*
>
2
>
0 6 )*
>
2
44
> < / !! 4 !
B*
3!
8-4 1+ @ 4
! (
/=*
4!
,+
! )* / ) & 2" ,
1 ,=
)*
)*!
& # # //
8-4
)*
+ !
! )*
44
> < .8-4 0
! )*&
$1
G0 @ *
/
.8-4 "0
& # )+
1+ @ 4
&# 7
* /=
2
/=
44
> <
)* !!
/=*
! 8-4 !(
2G .8-4 "0
! , 1 (
> < / ( ! )*
4 !
B*
8-4 2 1+ @ 4
)*
(*! 2
8-4 G +* > <
;+! *
& )* *
'>6 8
2
> <
& # /=*
/=*
1+ "2
,
/
) 7
&
+
@
!!
44
)*
;= 8-4 S
!! & #
7
)*!+
& #
/=*
! )*
8-4
+)*
44
> < .8-4 S0
! !
2
2
8
>
>
+
+ # )*/=*
/=
/=*
44
>
<
/ !!
4 !
B*
3!
!
<
54
!-
+ !
5
*
' !/
8
,
' +
!! %
&
)*(
' +
+ !
J
5
!J
T
>
<
+ !
5
!8A
$
B%CB
!8
B%&B
!8
1
B%<C
54
8
6 )*/+!
1+ : !,
99
!-
1
)*
2
%
* !/
* +
I
:
6
J &DEF<DGF'
!!
!)*
)*
! )*
! )*
!!
!
' !/
J
7*2
*
)*
/=*
7
!
=
*
4 =/
,
8*
>
2 +
,#
& # )*
!)* !+* 2 >
)*
V
!
V
!
)*
& 6 = ! )*
1
*!
, 2 +
,
!!
=!
, 2
)*
1+
1+ *
#
*(
)*
/
=
&
)*
)*
1+ >
!- V +)*
-4!(
=
+)* !
)* +4
& > +!)* ;(!! ,
)* )* !
/
- @4
/ !! %1+ '
' !/
/=*
4!(
/&
! )*J # 1+
!
2
+
1 !( 2 +
'
2
1+
,
)*!(
2 +
,+
)*
7 /=*!2
)*
4 , )*
!)*
!)*
H
)* ( ,
1+ >
)* /
1
!!
- @4
+
/+
!
) 7
/=*
1+ 8-4
(*!
8-4
! 2
4!(
E
!2
A+
/*
@
8
@
*A
/
( ! )*
)* / 1+
! + ! #
,
*
! +
#
,
1 , =4/ & #
+
= 01
. )*
0&
*
1
& #
)* ,?
8 4 !
1 )*
1 )* +!
& //
! + *
!+
! + ! #
, &
/+!
,
: )*
/=
8 )* , ,
- @4
,?
/1
!
2
@
!!
2
)*
)*
!
> 4!
1+
3 4 ! & %A+ '
= )*
& #
2
!)*
V
!
8
=
@
#
A+
+
+
•
+ H
•
' * A+
•
7 )* )* !
)* ,
A+ 2 !)*
& #
/=*
A+
)*
2
'
2 +!!
,+
(5
*
A+ @ 4
+
> 4!
)* ,
7
&
,
#
? ! )*, 2
+
, =4/
/+!
' !/
!+ ! +
=
! +
)*
+
/=
/+!
A+
*1,
> 4!
)*! 5
,
,
V
8
!
J
A+
.
0 ++4 A+
"
*=?
*8
/*
1 !( / 4
4 !!
4 !
8 !!
/
,
1+ !
0& >
1+
* /+!
!! 1
*
+
&>
.
/=
!
+
B
!!
)*
)*! / & Z
&
B
A+
! )*
)*
4 !
! +
. 0
. 0
+
1+ 2
!
!
3+ 4! < (
1+ @ .e . 0e F . 0e0& + !
)*2
! )* /
2
!!
)*
2
+
! +
+
)*
()* +
=
2
; !! +)*
( ! )*
3+
V
!
+
1+ @ .e . 0e !+ e . 0e0
& @ .e . 0e !+ e . 0e0
*
,+
=
& # ' 4 ,+
/,+ =
+
!! &
@
*8
!
+
8
!!
! +
' *8
#
/*
!
4 )* & >
)*!
!
!!
' 4 4 )*
/*
=
+)* )* +
1+ !
' * A+
' *; , +
& #
/
4 )*
V
! )* ' * ; , +
* !
, 2 +
! )*
+ H
&
!
A+
! )*
)* & 7 *
(
/
/ * 2 +/
,!
4 !
' *
1+
2
@ .e . 0eFe . 0e0&
8
!!
+5
G
A+
/*
@ & :
)
+$ ,*8
# :
> 4!
)* )* !
++4 A+
)*! /
1 ! )* J +
/= V
8 4 !
( 5
! +
)*! / & #
)* ! )* B
A+
4 !
! )*
? ! )*0& #
/*
+* 1+ *
>
! + ! . 0
( 5
! . 0
)* 2
(
/
; !! V +)*
4 /+
&B
( 5
# )*! /
)* &
>
)*/+!
/*
@&
A+
)*
>
2
)*
- G"
- !(
!
/
1
)* . )*
* !
&B
@ .e . 0e [ e . 0e0&
2
/*
)*
V
!
2
=
Y
+ 1+!! (
!-
1
! )*
* /= V
)*
A+
/*
)*
/
!
/+!
*A
!8 9'
!-
/
7! )**
%3 /
/
8
1+
/
!! %
1
2 +
( H;
&
' +
#
:
)*
A+
/=*
V
!
J
8
:
*
<
4 !
)* )*
4 , )*
8
/
)*/+!
-
/
>
!
/+
J
!
#
4 !
4 !
!
8 !!
!
!!
%
)*
)*!=
/
!
&>
2 +
B*
)*
! ) 8 !
:
:
* * 1+ 2
8 !! %
)*
! , 1 ( 1+ 2 G
/
@&
" *=?
*8
$ ()*
@4
)*
; !
1+ '
1
!
2
+
0?
" 0@
1 , =4/ & #
8
$
1+ !
2
)*
8 ! )
8
!! V
!
1+
,?
.
* 1+
* 0& # , > <
!
2/
/
8 !
2 !)*
Ea
7
,+
)* & )*
+
GT a
7
,+
+
A+ ! !
@4
+
/=*
4!
0
GT a
: )* & # 7
/=*
/H !
/+!
)*
/
/
&STG&ET $ !
&
8
!!
)*
+
!
)
.
!!
+
=
/
/=*
2
4!
!!
=
8
)*!
)* ! )* /
! ,
(
2
,
&
A+
/*
+
H>
>
()*
!)*
=
.
# )*
+
B
2/ I&&&
/
&
4!
)*/+!
)*!=
- !
* =
8 !! 2 +
)
2 !)* T" a
! )* ,=
& # )*
9!
> <
@4
+
,+ 4!
/ !!
A+ @ 4
+ ! +!)*
Ga
+
H>
#
,
=
.*!
/=*
1 ,= &
@&
*8
! ()*
,+
+
> < 1+ !
&# *
/+!
8 ! ) 2
!)*
/*
1
)* 3+
!&
!4
V
!
)* ! )*2 /
! )* )* !!
7
/=*
)*
* ? 2
& I
+
3+
&
)!
>
<
)*
,
8
)* /=*
9!
1+ &S ,
8
!! 1+
+
1
)*
!
!
2
)
> <
4 )*
2 +
2/ 'IIG
*
)*
! )*
' * A+
/*
/
8
!!
3+
1+ S a /= )*
1+ " a & >
+
%
4 )* & #
/=*
! )*
/ 2S
* 2
,
)*
! )* ' * A+
1+ 2
,
A+
/*
=
)
.
2/ C&<'
6 )*/+!
,+
+)* !
! )* ' * A+
/*
2 !!
!
> <
! 2 +
/
8 ! )
)*
! )* )* !!
> <
)*
2
)*/+!
-4!
)* ! )* & #
*
+
,=
7
/=*
/
+)* GT
;+! 2 +
1+ " a
3+
/> < )* H > < )
"a /
' *
)*
/ !! &
=
)
.
2/ '<F
@&& :
)
+$ ,*8
:
++4 A+
4+
> < (*
/=*
+)* 2
, 4
!
( & # *
/=* & #
9!
> < )
/=*
1+ E2
,
&
7
# )*
)* )*
!
- 1=
4!
+
2 +
.*!
- +*
0
.
++4 A+
4 )*
/
1+
/=*
9!
TE a
!
)*
> <
2/ GIGB
)!
,
)*
>
>
<
< )*
!+
!!
/
4 !
/ &
)* ,+ 4!
"
A+
/*
@-
%
:
B *!
8
)*
A+
2
8
!)*
!!
>
#
B
+
+
&$
2 +
!
,
/=*
&:&
:
-*
)*
)!
/=*
+
+4
8 !!
/
4 +/
& )* ,
+
,= /
1+
+
/+!
+
*
>
<
,?
;+!
&
/
*
!
*
*+
1+ /
$
1+ !
=/ 8
=
(
2
/=*
,?
&
8
2 ,?
+)*
> <
2
!!
4 !
#
)*
& '
*?
+
)*2 +
5 *
)*
! )* B )* ! ,
5 *
)*
!
2
J
4 !
!)*
2 +
1+
/
/+,
/
6
// ,
2
!)*
( ! )*
)*
+
B )* !
A+
*+
*
/+
! )* +
/
A+
! )*
2
4 !
1+
1+ *
)*
*
8 9'
-8
B
' (* #
B
1+
#
? ! )*,
3 4 !
& #+)* 1+
/=*
(*!
1 ! )*
B
2
/=* &
!
' !/ 1+ &% & 7 (*!
) 4
/=*
+
* !/ )*2
/=*
&$
?
! 8++!& >
/+!
9+
1
+
#
,
a
&
,8
B
2
? ! )*,
3+
+
4
4 )* & #
! /
=
* !(
3+
+
/
a
>:
B
'
!(
2
! )* 8
*
/=*
/
* ! &
4 !!
=
4* )*
"
3+
+ ! 2
+
+
)*
2
* 21 !
*
4 )*
!
&#
"
* ! )*
4 !
1+
&#
1
)*
= !
(
!)*
2
+
4
? ! )* 2
)*
1
*( / *
7I > ,
+ )
!
*
2
B
!
+
-
<
#
/( ! )* 8
*
2
2
/=*
! )*
,
I
)*
&I
= )*
*! / )* *
2
!!
7@
#
,+4
- G
)* J *
1
&
*
!
; *!
!
&# 9
8 >B
& C! #
. &:&
*
&%0
/=*
&
B
2
)*
2 +
G / )*
&
!!
!!
)*
3 4
- G
/+!
>B
!!
,
! G
+
*
7@
*
G
!
/=*
( ! )*
* )* ! )*
! (
/=* & #
)* )* ! )*
G
/ )*
( /
4+
,
!
, *
/=*
-& I
/
! )*
* /+! ! / 2
@4 + G
( &
: +
*
!! 2
/
* !
98
! )*
/=*
V +)*
+!!
* 1+ *
/
&:&
& #
,
! 1+
)*
/
2
: / *!
, & '
2
8
/
:4
E
6
)*
)*
/+!
d#
F
-
?)*
/+!
-
9
>;
(A
E
=
! )*
: / *!
V
: / *!
$"$%&
#
<
* &B
/=
J
6
7
-
6
!
)*
<
#%%
3" '
9
4
8
<
!!
&%
3+
+1"
-
*(!
+
<
6
!
7
++!
7
<
!
/
)*
>$%&
D
6
!*
&#
! V +)* 1
7
7@ G 1+
*
; !!2
! )*
/
:
2,
F
!
2 /=*
*
!
1
J
E
8 9'
-8
>$%&
D
:4
-
E 6
(A E
)*
d>
<
>$%&
9 >;
: / *!
( ! )*
6
/+!
+
+
< 72
GEHI E
)*
.
( A E <
(A E
2>#3?+&322 7
; ); (A
2
! /
)+ 2
&%
&:&
!
=),
,
)*! /
a
/1
6
6
7
!
!2
Y*
*
/=
&
J
7)
7
9
!! +
* !
)*+
!!
)* !
)& #
7
)* ),
1
$"1953
E C
)*
<
)*
d $ 1+
=
-
<
;+
+)*
: / *! !
)*
<
>;
(A
E
<
2 +
6
7
)*
E
)
6
: / *!
0&
'(!/
+ 2
4G + /=
GEHI
)*
7
; *!
*(! 2
"
,
/+!
;+
J
network_packet_size:num_tx:total_time:avg_time:avg_tx_per_sec
#
,+
8
/=
$ !
+
" TE& #
, +
!-
;+
*!
!
8
2
)*
, +
G
!
I
1
1
&#
)*
+ ,M4 ), M
2
G
&#
2
&#
B
M < )*
, V
/=*
2 ,?
*
,
! 1
2 ,?
,
?5 " TE
*! G
1+!!
,?
*
()*
#
+ 4 M +G & <
+)*
+
! ,
)+
/
2
5
;!
)*
/
& + !
* ! 2 ( ! )*
> /+
! /
)* 6 I &
B
*
/
,
SET
/=
!
X
! )*
!
,?
J
/
2
!
9 $J
! )*
8
X
,+
=
+
)*!= ! +
>
#:> <
#
=
+
2 +
+ +*! )*!=
4+
)*!=
! !
)*
& + ,+
/
,
/*
2
#
*
/!
! )*
/
,2 >
)* 8
,
(
,
2 J
+
!!
( ! )*
2 J
9 $
)*
1 #
!
J
&
;
)*!=
!
!
&
>
!
)* >
&
!! &
#
,
= 4 =/
&
3 4
!!
/
! +)*
)*! 5
2
+*
>
/
/+!
<
& >
)*
&
( ! )*
!+
/
#:3 - > <
;(!!
1?!!
8 9'
-8
B
8 9 ' /=*
- <!
+
)*
!
&
# : / *!
%
#
)* ,+ 4
&
!
)*
: / *!
!!
8
& #
)*!= !
& #
+)* !(
)* =
)*!=
!
/& B
3+
+*
=
2
! 1 ! +
+
:> cat dss.ri | sed -e "s/CONNECT TO TPCD;//;s/TPCD.//;s/ADD/ADD CONSTRAINT
<name_hier>/;s/FOREIGN KEY [a-zA-Z_1-9]*/FOREIGN KEY/;s/COMMIT WORK;/GO/" > createKeys.sql
4 !
/=
8 8 : 8 9#&6 8>@ 6
## ;@ >76 3 L 6 8>@ 6 M;3G .6 M
9@
>8 B @ 3Y
1
(
(
B
:
2
!
/=*
*
/
)
8 9#&
7>@ 6 Y
! )* 8
ALTER TABLE NATION
## 9@ 6 8 >6 8 b
M*
7@
!!
7>@ 6 3 L0
)* !
=
;
c ;@
>76 3 L .6 M
7>@ 6 3 L0
/
+)*
)*!=
)!
!
>
V
)*!=
!
)?
1+
>
4!
4 /
+
)
7>@ 6 Y
4
/
& #
8
!!
!
/!
/=
&
7
=
- ! < ! #+,
2 !)* >
+
2 /=
- / *!
+
& 7
<MissingIndexesc 8
.1 !& 3 4
! E&"0& #
SET SHOWPLAN_XML ON
GO
2
! /
1+
-
)* )* =
!!
)* ! 2
- G&
; !!
,
-4!(
&%
+
!
/ OFF
4
4 )*
/
/=*
/=*
4!
4!
=),2
) 4
!! 2 +
/
& # @ 4 + SHOWPLAN_XML !
& 6 )*/+!
)*
<ShowPlanXML
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Build="9.00.3042.00">
) c
b: )* C
b: )*c
b
c &&&
b
- ! 9 )*
!
QP" P 9+ 4 ! 8
QP" P 9+ 4 ! 9 I QP" P 9+ 4 !
+ -QP"GEPc
> < c
b
b
> <7 + 4 > 4 ) QPES& GT Pc
b
> <#
QPK8 9'OP )*
QPK +OP 8 ! QPK >6 >8 OPc
b9+!
7 + 4I
QP>6 I >8LPc
6
QPK M '> # 8 OP 9+!
> QPGGP Hc
b9+!
bH9+!
7 + 4c
b9+!
7 + 4I
QP>6 9 I # Pc
b9+!
6
QPK M I 6 8>8LOP 9+!
> QP P Hc
6
QPK M 8 6 # # >9 OP 9+!
> QPEP Hc
b9+!
b9+!
6
QPK M#> 9@ I 6 8OP 9+!
> QP P Hc
b9+!
6
QPK M8 OP 9+!
> QPSP Hc
6
QPK M 8I 6 ; 7OP 9+!
> QPTP Hc
b9+!
6
QPK M >6 8 8I OP 9+!
> QPG P Hc
b9+!
bH9+!
7 + 4c
bH
> <c
&
* ,
/= V
+ !
-4!
Version="1.0"
S
8 9'
-8
bH
bH
> <7 + 4c
> < c
&&&&
4 )*
"
#
#
)*
!
)*
4* )*
&
/=*
4!(
4 )*
+ !
#
8
% *
3+
>
& C!4! 2 + !(
!
!
)* 4 + ! !+ 4
& '
!!
!!
. 4 & )
> < & C!02
- <
)*
&
/=*
4!(
)* 2 ,
!)* !! 1
4
:> cat %PLAN%\*.sqlplan | sed -n '/MissingIndexes/,\/MissingIndexes/p' | sed -e
"s/<MissingIndex[a-zA-Z]*>//" -e "s/<MissingIndexGroup Impact=[0-9."">]*//" -e
"s/<MissingIndex Database=""\[%DBNAME%\]"" Schema=""\[dbo\]"" Table=""\[/\nCREATE NONCLUSTERED
INDEX IX_/" -e "s/\]"">/_<field>\nON <table>\n/" -e "s/<ColumnGroup Usage=""INEQUALITY"">/(/"
-e "s/<ColumnGroup Usage=""EQUALITY"">/(/" -e "s/<Column Name=""\[//" -e "s/\]""
ColumnId=""[0-9]*""//" -e "s/\/>/,/" -e "s/<\/ColumnGroup>/)/" -e "s/<ColumnGroup
Usage=""INCLUDE"">/\nINCLUDE (/" -e "s/<\/MissingIndex>/;/" -e "s/<\/MissingIndexGroup>//" -e
"s/<\/MissingIndexes>/\nGO/" >> %DBDIR%\createMissingIndexes.sql
#
3+
+
!
<
*
!(
!!
/+!
MissingIndexes
-
<J
CREATE NONCLUSTERED INDEX IX_PARTSUPP_<field>
@6 b ! c
.
M
83 L2
0
>6 9 I # .
M I 3 L2
M I
L9@ 82
0
Y
7@
#
1+
4
1+
4
+
)* ( ,
% * =*
* 2
2 (* ! )*
)*!= ! 2
!! +)*
X
1+ *
&$
;+
)*
* ! )*2
=
; !
8 !!
(
_2_1+
_0_ /
&
+ !
*
2
> < C - 4 / )*
*(
!
>
3 & # *
/= 1 )*
!
+
,+ 4
12 &*&2
)*
1
>
/
! )* 8
!!
! 2
)* +!!
( ! )*
&
>6 9 I # 4 !
+
*
> <=
*
4 !
&$
,?
>
! )* +5
Y
#
, )* ( ,
" 7:
8
)* & +
*!
>
+//
2
/
,+ 4
1
&:
1+ >
+!!
!
)* ! , 1 (
2 !+
! , 1
4 !
2 +
&
+!!
' !/ 2
>
,!
+)*
: *
2
>
1 !
1+ 3+ /! ,
<
!!
&8
*
)*
()*! )*
/
& , 4
2
&:&
$
*
/
8
!( ! )*
1+ # 4! ,
!- 1+
&
T
8 9'
-8
B & '
6 )* 3
!
, *
G&
)* /=
8
-4!(
&
/+!
!!
/
/= #: !
,
!!
& MissingIndexes
3+ /! ,
*
&
<
"& MissingIndexes
#:3 - > <
!
&
,
E&
& #
S&
T&
,
'
.#
1+
<
/=
#:>
<
#:3 - >
<
1
#
3+ /! ,
)*
> <
!! #
X
!
/
, 0
!
! # 4! ,
/
Y MissingIndexes
*+!
,
*
2
#:3 -
&
< )*
/
- )*
!!
&
>
&
&
!
&
,
*
4 /+
1 )* & #
+
B + ,/!+
- G f
/=*
! )*
&
, Y !? )*
,
+
/3 !)*
3 - 1
,
2
* 1+ 2
#:3 - & A
#
/
,
#: !
, &
! )*
&
! 2
#:3 - &
>
/
,
2 )*!=
J
&
/
!
GG& 7 *
#
*
MissingIndexes
G &
*
/= #: !
/
# 4! ,
>
!!
#:3 - &
#: !
G& & /= #:>
! )* !!
/( ! )*
)*
B + ,/!+ *
V
2
/ )*
,
*
!!
!
1
!!
&
,
1
/+!
!!
J
)*
!! &
"
8 9'
#
-8
/
2
)* !
#
4(
)* )* ! )*
/
/
V
!
#
, &
* )* !!2
)* )* ! )*
/
>
& # )* /+!
)*!= !
>
2 )*
/+! 1+
2
)*! 5! )*
!
)*!= ! & $
(
= ! )*
2 *
+)* 1+
2
3+
+
)*!= !
>
?5
/+
)
&B
*
)*
; !! 2
/ !+
I
)*
&
4/ *! )*2
!
)*
)* &
(*!
- G2
G& #
#
//(!! ,
& +*
- G / !! #
,
*
! )*
/=*
+*
1+
)*!= ! +
>
,
+
(
&
- *
!+ !
8
+
/+
)
5
/ #:> <
- G )*! 5! )* *
*?)*
)*
&7
*+*
)*
!! )*
2
()*! )* +!!
)*
8
)*
& #+)*
- G
+ +*! / #: !
!
)* /
#:3 - > <
)*! )*
/ &: +
//(!!
2
)*
/
)*!= ! +
>
* ! )* 1
2
+
,2
> <
,
1+ *
/
+1 !$
? 2
/#: ! &
6 )*/+!
B
:
$
/
!!
- 2
#
- G / !! 1+ !!
:
)*
/
2
! /+
+
1+ >
)*
;!
!-
(*!
1 !
/
*
2
44
+
8 9'
&
=
*
; !
8
!!
=
#
1 !! +
7 @ I :L
)* )* +
/+
&#
- /
! +1
)*! 5! )* G&G &GTTS 1 )* //
+
&
/&
@ #
:L
)*
SELECT
!M
!M!
/! 2
2
.!MC
.!M <
.!M <
-0
MC -2
4 ) 0
M
M4 ) 2
4 ) [ .G !M )+ 00
M
)M4 ) 2
"G
8 9'
-8
.!M <
4 ) [ .G !M )+
1 .!MC
-0
1 MC -2
1 .!M <
4 ) 0
1 M4 ) 2
1 .!M )+ 0
1 M )2
)+ .[0 )+ M+
0 [ .G F !M <00
M)*
2
; @
!
B'
!M * 4
bQ
. -2 "2_GTTSHG H G_0
7 @ I :L
@ #
!M
!M!
/! 2
!M
!M!
/! 2
Y
:L
7@
#
#
8
)*
7
!!
, +
G 7:
*(! =
a #
:& #
)*
)
=
4 )*
/=
& #
V +)*
A+ & # :
! )* 1+
)!
>
= ! )* *(
1+
=
)* 2
"2_GTTSHG H G_0 +!!
3+
1
+
)*!(
#
#
,
! )* <
!
2
/ !!
+
!
G 7:
-
8
,
)* /
!M * 4
< / !M * 4
4 +/
*!
)+ 2
bQ
.
,?
; !!
-2
&#
&
(*! /+!
/=*
4!(
/
1
)*
#
, J
G& #: !
& #:3 -
& #:>
"&
#:3 - >
<
<
/ #:3 1+
# 3+
1
! )*2
; !
,
6
&>
> < /!
)*
3+
=
/ 1
( )*!=
!
)*
/ !M * 4
3+
>
<
M!M * 4
1
' *
)*&
2 +)*
M!M+
, -M!M!
7
)*
)* &
8 ! )
#: ! Y
)*
- )*
/ 2
> <
+
* !
#:> <
#:3 - > < 2
*
&#
3+
/2 +)*
,?
2
)!
> < / !M * 4
2
)*
; !! J
"
8 9'
:
-8
8
!!
2 +
)+
/=!! 2 +
! , 1 (
*
*
+*! )*
' 4 4 )*
* !
/*
! 1 ,+
&
B
#
)*
5 2
#
* !
-
C
! )*
,
2
&$
&#
2
1+
B'
8
)*
8-4 6
!!
/
1+
:
/
!!
! 1 +5
,
=
!!
8-4 A &
98
M
M
2
; @
44!
+
2
B'
M 44, -
.
98
4 M 44, ; @
4
44
B'
4 M4
, -
.
98
4M4
, -
; @
4
B'
4M
!, _
C
a_
0
4 M 1 !C - c .
98
& [
.!MC
-0
; @
!
B'
!M4
#
!, 6
)*/+!
-P2 9 8
8-4
) +
, - Q 4 M4
*
+ #
, -
)*
-
/
4 1+ B + 2 3&J P@ @ 4
2 +!& 2 6 +& 2 4
GTS 2
"" "ET
"
8 9'
-8
!M 44, - Q 4 M 44, !M * 4
cQ _GTTE G G_
!M * 4
b
.- 2G2_GTTE G G_0
0
0
M
M
@ #
+ , -Q M
+ , Q _9 6 # _
:L
M
Y
7@
!-
/=*
4!( 2 +
/+
!!
/
)*/+!
/ #:3 )*!= ! !!
/
8
V +)* >
1+ *
2 +
)*!=
* , > <
! 2
(*!
8 !
: 4 ! /= 2
)*
> <
*(
,
), ! ,?
&6
/=*
4! 1+ #:>
; !
M M
, -&
> <
+
2 + 1 ,=
)*
/=*
)*
/=*
!
>
%
<
& %,H
! )*
2
1
/ #: !
J
!!
6
++4 1
&
! 1+
+ & /
8
!!
)
( )*!= ! & #
1+
!
>
< /
)*
/=
+*! #:> <
!
)* #:3 - > <
)*/+!
#
2 +
)*/+!
#
)*
J
""
8 9'
-8
B
9 2
#
)* * /
8
!!
/
; @
/ 2
2
/
!+
1
)*
!J
! V +)* 1+ 8-4 A
A+
2 +)*
#&
*
V
98 g
98
M
)+
2
.[0
; @
44!
!
+
2
!G2
2
+
B'
M 44, - Q !G&!M 44, +M+
, - Q !G&!M+
, +M+
Q _;_
!G&!M ) 4
c !G&!M)+
< .
98
[
; @
!
!
B'
! &!M+
, - Q !G&!M+
, ! &!M 44, - bc !G&!M 44, 0
+
<
.
98
[
; @
!
!
B'
! &!M+
, - Q !G&!M+
, ! &!M 44, - bc !G&!M 44, ! &!M ) 4
c ! &!M)+
0
M
M
+ , -Q M
Q _ I_
+ , -
7 @ I :L
"
8 9'
-8
M
@ #
:L
)2
M
Y
7@
)* *
!+*
*
)*
I
)*
I
! )*
@4
+
J
1! ,
$ '<%
!
J
1! ,
$ '<%
9 $
J
1! ,
$ '<%
)*
2 !)*
)*
)*
/
/=*
4!( &
- !(
8 !!
/7
&
4 !
7 ?5
J
2
"E
8 9'
-8
J
1! ,
$ '<%
9 $
2
//(!!
/+
: *
!
8
!!
&:
/ #:3 - > <
! )* > < -4 1
& / #:3 - > <
* V +)*
+ +*! )*!= ! !
)*
>
/=
1
/=
! )* 8
!!
1 )*
> < -4 /=
/
&#
= ! )* / !2
?5 8
!!
=
1 )*
>
* / )*
)
2 + )* !! : // =
)*
&
*
: 4 ! /= 2
)*
- @4
3+
+ 1 )*(
, &
#
/ )*
?
*
2
<
/ #:>
+4
2
!)*
-2
2
>
<
>
>
<'
,
1
&#
<
*
.1 !& 3 4
! 0
! )*
/
/=
'
2
( )*!=
* /+!
! 1+
5
+
J
& #
98
M
)+
2
.[0
; @
44!
!
+
2
!G
*.
<.> M >6 >8
MM I
3 L002
2
+
"
8 9'
-8
B'
M 44, - Q !G&!M 44, +M+
, - Q !G&!M+
, +M+
Q _;_
!G&!M ) 4
c !G&!M)+
< .
98
[
; @
!
!
*.
<.> M >6 >8 M M I
B'
! &!M+
, - Q !G&!M+
, ! &!M 44, - bc !G&!M 44, -
3 L00
0
+
<
.
98
[
; @
!
!
*.
<.> M >6 >8
MM I
3 L00
B'
! &!M+
, - Q !G&!M+
, ! &!M 44, - bc !G&!M 44, ! &!M ) 4
c ! &!M)+
0
M
M
+ , -Q M
Q _ I_
+ , -
7 @ I :L
M
@ #
:L
)2
M
Y
7@
#
/+!
!
7
B
#
#
: !,
* 1+
P/+ ) P
!
: !,
*&#
1+
@4
1 ! )*
1 : !,
/ #:3 - >
0&
B
/ #:>
4 = ! )*
)* @ 4
<
< 2
2
-.
@
)*/+!
/=*
)*
1 (
(* &
!
*
)*
)*
@4
)*
!
! )*2
/=*
)*
"S
8 9'
-8
!!
V +)*
,
9!
4 !
8
5
!
*
1+
*
/
2
- G2
+!!
/ #:>
<
+)* ! )*
)*
)* @ 4
7 4* 2
(* ! )*
* !
(*
- G
,
- G
)* *
/
4 )*
!
&
/!
/ #:3 - > < &
! )* &
-"
T (* !
& # )* (* ! )*
,?
&
"T
;
D
%
#
) + +/
/( ! )*
, 4
)*
'
!
)* )*
1
;(!!
(*!
=
? ! )*,
)* ' !/ 2
+
@4
!!
) 4
> < < *
> 4+
<4+ ; , +
)* /=
)*
)*
B *! 1+ *
>
)*
!)*
)*
!!
#
,
+ 2
@4
+
? &>
)*
+// !!
8
: !
&
#
!B
#
E
* 4JHH
8
*
,+
+
=
&# > /)
,!
)*
3+
+ ! ++!
&>
- !
& ;= @ 4
8
,1
/ )* & 3+
!!
+
? ! )*,
2 +
&:& )*
)* /
=
& B = )*
(
+)*
#
#
,& !!
*
)* !!
/
2
!)* & &
& '
!
+)*
! )* @ 4
+
!2
/
& $
/
)*
#
/=
!! 2
!)*
/=
,?
/V
; !!
4/ *!
E
) +
+)
/+
) 9+ ) !_
(
5
1+
,
)*
& 4)&+ H 4)*H
2 4 , )*
*
+!
)*
)*
;
+5
4 52
+
* +
)*
)* 1 ! 6
!
/
3
,+
&
1
C
)*
$
-
%
!
-
>
-
, 4 % )* )
3+
2 +/& # &
-
-
5
+
' !/
!
+ !
! 28 4+
#
A+*
1
<4
&
> 4!
+ +/#
) '& )*+!!2 B
EH &
<
(, 2
*
* +4
J #: 2 @ )! 2
2I 1
(
1 2 B ! -U +
&
7 *, 2
-
2
)7
B '> >
+
!
+ 2
*
, *
2
&
-
1
f
-
1
f#
1
f
/
'
/=*
2 4
)* /=
+
, &1 ! 2 #
2
+ B
,+1 )2
! -
E&
! 2 : ), B ++ -2
&
;
J 4!
-
A
-
-
)*2 +/
U
44+
! 2
3+ )*2
E&
!!
<4
#+ !+
* 4JHH
& ) + +/ &)+ H + !+
S GGE# TS9 #U 4! -! Q
1
* 4JHH
-
*
1
-
-
+
&
#+)
+
) + +/ &)+ H
H
H!
! & 4<R/
-H
!- Q :
S:T G G "#:T
G& 4<
1 ;
),
* 4JHH
& ) + +/ &)+ H + !+
T S T S ) )U 4! -! Q W/! !
H
! & 4<R/
!- Q
1 8++!,
* 4JHH
& ) + +/ &)+ H +
) S"G T " U 4! -! Q
H
! & 4<R/
!- Q )S E T
#6
* 4JHH
&
1 + !
) + +/ &)+ H
#6
* 4JHH
&
9 # #+,
) + +/ &)+ H
-
8 9 ':
* 4JHH
-
9* 4JHH
!+
#+,
H!
+
H!
+
-H G
-H
;9
GE
T)G E
G ) "" T T TG
ET/ ")E/ T
G"& 4<
& 4<
)*
,
& 4)&+ H 4)*H
&)-
&)+ H
G
*
2
$
>
) 4
+1"
$
)*/+!
&
#
! //(*
2
:
6
: )* ) 4
4+ +
+1"
)*
/ !
2
< *
,
!!
1+ #
1+ #
1
2
2)
!!
J
,2 8
!!
& #
2 +
/
A
@echo off
REM #################################
REM # set all Variables needed
#
REM #################################
REM # this script sets all variables and prepares directories
REM ##################################
REM #--------------------------------REM # HOME, the base directory, if not specified the users home is extracted from registry
REM # WD, the working directory, ie the parent for all subdirs
REM # DBDIR, database directory, here the database will be created and all scripts go here
REM # QDIR, TBLDIR, the directories where the sql queries and the tables go
REM # DBNAME, the name for the database
REM # TPCHDIR, TPCHVER, directory where tpch will be downloaded and installed, the Version
REM # number is appended to the directory name and the download name, to allow multiple
REM # versions
REM #--------------------------------set HOME=
IF NOT DEFINED HOME FOR /F "skip=4 tokens=2*" %i IN ('REG QUERY
"HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders" /v Personal') DO set
HOME=%j
set WD=dbtuning
set DBDIR=%HOME%\%WD%\db
set QDIR=%HOME%\%WD%\sqlqueries
set TBLDIR=%HOME%\%WD%\tables
set PLANDIR=%HOME%\%WD%\sqlplans
set DBNAME=TPCH
set TPCHDIR=%HOME%\%WD%\tpch
set TPCHVER=20070105
set SQLEXPRESS=%COMPUTERNAME%\SQLEXPRESS
REM create directories
mkdir %WD%
mkdir %DBDIR%
mkdir %QDIR%
mkdir %TBLDIR%
mkdir %PLANDIR%
mkdir %TPCHDIR%_%TPCHVER%
$
6, ,' ( E"#$
@echo off
REM #################################
REM # prepare TPCH and SQLSERVER
#
REM #################################
REM # this script prepares all directories, downloads and compiles TPCH,
REM # generates all tables, generates the sql server database,
REM # extracts and modifies create table and create index statements from the TCPH files,
REM # generate the bulk insert statements and imports all tables into the sqlserver db
REM ##################################
cd %TPCHDIR%_%TPCHVER%
REM get tpc-h
wget http://www.tpc.org/tpch/spec/tpch_%TPCHVER%.tar.gz
tar xvzf tpch_20070105.tar.gz
REM alter Makefile and compile
cat makefile.suite | sed 's/CC
=/CC=gcc/;s/DATABASE=/DATABASE=SQLSERVER/;s/MACHINE
=/MACHINE=LINUX/;s/WORKLOAD =/WORKLOAD=TPCH/' > Makefile
*
make
REM generate tables and change unix lf to windows cr/lf
dbgen -vfF -s 1 -Oh
mv *.tbl %TBLDIR%
for /F "tokens=1,2 delims=." %i in ('dir /b %TBLDIR%') do mv %TBLDIR%\%i.%j %TBLDIR%\%i.tmp
for /F "tokens=1,2 delims=." %i in ('dir /b %TBLDIR%') do sed 's/$/\r/' %TBLDIR%\%i.%j >
%TBLDIR%\%i.tbl
rm %TBLDIR%\*.tmp
REM generate database file
echo CREATE DATABASE [%DBNAME%] ON PRIMARY >> %DBDIR%\createDB.sql
echo ( NAME = N'%DBNAME%', FILENAME = N'%DBDIR%\%DBNAME%.mdf' , SIZE = 3072KB , FILEGROWTH =
1024KB ) >> %DBDIR%\createDB.sql
echo LOG ON >> %DBDIR%\createDB.sql
echo ( NAME = N'%DBNAME%_log', FILENAME = N'%DBDIR%\%DBNAME%_log.ldf' , SIZE = 1024KB ,
FILEGROWTH = 10%) >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo EXEC dbo.sp_dbcmptlevel @dbname=N'%DBNAME%', @new_cmptlevel=90 >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) >> %DBDIR%\createDB.sql
echo begin >> %DBDIR%\createDB.sql
echo EXEC [%DBNAME%].[dbo].[sp_fulltext_database] @action = 'disable' >> %DBDIR%\createDB.sql
echo end >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET ANSI_NULL_DEFAULT OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET ANSI_NULLS OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET ANSI_PADDING OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET ANSI_WARNINGS OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET ARITHABORT OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET AUTO_CLOSE OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET AUTO_CREATE_STATISTICS ON >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET AUTO_SHRINK OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET AUTO_UPDATE_STATISTICS ON >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET CURSOR_CLOSE_ON_COMMIT OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET CURSOR_DEFAULT GLOBAL >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET CONCAT_NULL_YIELDS_NULL OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET NUMERIC_ROUNDABORT OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET QUOTED_IDENTIFIER OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET RECURSIVE_TRIGGERS OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET AUTO_UPDATE_STATISTICS_ASYNC OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET DATE_CORRELATION_OPTIMIZATION OFF >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET PARAMETERIZATION SIMPLE >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET READ_WRITE >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET RECOVERY SIMPLE >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET MULTI_USER >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo ALTER DATABASE [%DBNAME%] SET PAGE_VERIFY CHECKSUM
>> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo USE [%DBNAME%] >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
echo IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [%DBNAME%] MODIFY FILEGROUP [PRIMARY] DEFAULT >> %DBDIR%\createDB.sql
echo GO >> %DBDIR%\createDB.sql
REM extract create table sql statements and add GO statements
cat dss.ddl | sed -e 's/ DATE / DATETIME /;s/);/);\nGO/' > %DBDIR%\createTables.sql
*
REM extract create keys sql statements and adapt to sql server TSQL
cat dss.ri | sed -e 's/CONNECT TO TPCD;//;s/TPCD.//;s/COMMIT WORK;/GO/' >
%DBDIR%\createKeys.sql
REM create bulk insert sql file
for /F "tokens=1,2 delims=." %i in ('dir /b %TBLDIR%\*.tbl') do echo BULK INSERT %i FROM
'%TBLDIR%\%i.%j' | sed -e "s/tbl'/tbl'\nWITH (\n\tFIELDTERMINATOR = '|',\n\tROWTERMINATOR =
'\\\n'\n)\nGO\n/" >> %DBDIR%\bulkinsert.sql
REM generate sql server database
sqlcmd -S %SQLEXPRESS% -i %DBDIR%\createDB.sql
REM generate sql server tables
sqlcmd -S %SQLEXPRESS% -d %DBNAME% -i %DBDIR%\createTables.sql
REM bulk insert all tables
sqlcmd -S %SQLEXPRESS% -d %DBNAME% -i %DBDIR%\bulkinsert.sql
EOF
$
&6F
@echo off
REM ##########################################
REM # generate queries and transfor to t-sql #
REM ##########################################
REM # this script generates all queries and alters them for sql server tsql syntax
REM # as far as possible with regex
REM #
REM # The following changes have to be made by hand:
REM # in each query move the set rowcount with go above select
REM # in q22 change subtring(str from pos for len) to subtring(str, pos, len)
REM # in some queries the dateadd has to be corrected, replace date with unit and insert , s
REM # ie dateadd('1998-12-01' - '92' day -> dateadd(day, -92, 1998-12-01)
REM ###########################################
cd %TPCHDIR%_%TPCHVER%
REM copy queries
cp %TPCHDIR%_%TPCHVER%\queries\* %TPCHDIR%_%TPCHVER%
REM generate queries and transform to t-sql
FOR /L %i in (1;1;22) do qgen -avx %i | cat | sed -e "1i\--QUERY %i" -e "s/go/GO/" -e "s/set
showplan on/SET SHOWPLAN_XML OFF;\nGO/" -e "s/set noexec on/SET NOEXEC OFF;/" -e "s/set
rowcount -1/SET ROWCOUNT 0;\nGO/" -e "s/date '/Dateadd(/" -e "s/interval//" > q%i.sql
REM clean up
FOR /L %i in (1;1;22) do rm %i.sql
mv q*.sql %QDIR%
echo
echo
echo
echo
echo
echo
Queries genererated
BEWARE! Following changes have to be made by hand:
in each query move the set rowcount with go above select
in q22 change subtring(str from pos for len) to subtring(str, pos, len)
correct dateadd, swap the date with the unit and insert commas
ie: dateadd('1998-12-01' - '92' day => dateadd(day, -92, 1998-12-01)
EOF
$
-6
7
@echo off
REM ##########################################
REM # extract indexhints from sqlplans
#
REM # and generate CREATE INDEX statements
#
REM ##########################################
REM # from sql server xml sqlplans (.sqlplan) this script extracts the missingIndex
REM # information and reformats it to a t-sql CREATE INDEX statement. Not everything
REM # can be done automatically, however.
REM # manual changes needed:
REM ##########################################
REM extract and parse
for /L %i in (1;1;22) do sed -n '/MissingIndexes/,\/MissingIndexes/p' %PLANDIR%\%i.sqlplan |
sed -e "1i\--PLAN %i" -e "s/<MissingIndex[a-zA-Z]*>//" -e "s/<MissingIndexGroup Impact=[09."">]*//" -e "s/<MissingIndex Database=""\[%DBNAME%\]"" Schema=""\[dbo\]""
Table=""\[/\nCREATE NONCLUSTERED INDEX IX_/" -e "s/\]"">/\nON\n/" -e
"
*
"s/<ColumnGroupUsage=""INEQUALITY"">//" -e "s/<ColumnGroup Usage=""EQUALITY"">//" -e
"s/<Column Name=""\[//" -e "s/\]"" ColumnId=""[0-9]*""//" -e "s/ \/>/,/" -e
"s/<\/ColumnGroup>//" -e "s/ <ColumnGroup Usage=""INCLUDE"">/\nINCLUDE/" -e
"s/<\/MissingIndex>//" -e "s/<\/MissingIndexGroup>//" -e "s/<\/MissingIndexes>/\nGO/" >>
%DBDIR%\createMissingIndexes.sql
sed -e "1i\use [%DBNAME%]" %DBDIR%\createMissingIndexes.sql
REM done
echo createMissingIndexes.sql created!
echo BEWARE! following manual changes needed:
echo the indexes must have a unique name, some are redundant
echo after the ON statement the tablename must be inserted
echo the fieldnames must be put in ( )
echo.
EOF
#
( ! )*
3 4& S& %#
;+
,G
-4!(
,
& &% %
/=*
0 +
! 4#
&
8 9'
-4!(
/
)* /=*
1
#
!-
,
/
+
)*
.1 !&