-
-
Notifications
You must be signed in to change notification settings - Fork 0
/
adhoc-vs-prepared.sql
194 lines (167 loc) · 6.3 KB
/
adhoc-vs-prepared.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
-- Enrique Catalá Bañuls
--
-- Web: http://www.solidq.com
-- Blog: http://www.enriquecatala.com
-- Twitter: http://twitter.com/enriquecatala
--
--
-- por qué es importante erradicar ad-hoc
--
------------------------------------------------------------------------------------------------------
-- configuración
--
------------------------------------------------------------------------------------------------------
use tempdb
go
set nocount on
go
if exists(select 1 from sys.tables where name ='detalle')
drop table dbo.detalle
go
if exists(select 1 from sys.tables where name ='maestro')
drop table dbo.maestro
go
IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;
IF OBJECT_ID('dbo.Seq2', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq2;
GO
CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE dbo.Seq2 AS INT START WITH 1 INCREMENT BY 1;
create table dbo.maestro(
id_maestro int not null primary key default(NEXT VALUE FOR dbo.Seq1),
data varchar(512) not null default ''
)
go
-- detalle con clave ajena hacia maestro y su propio id gestionado con su propia secuencia
--
create table dbo.detalle(
id_detalle int not null primary key default(NEXT VALUE FOR dbo.Seq2),
fk_maestro int not null constraint fk_detalle_maestro foreign key references dbo.maestro(id_maestro),
data varchar(512) not null default ''
)
go
------------------------------------------------------------------------------------------------------
-- Inserción de unas cuantas filas
--
------------------------------------------------------------------------------------------------------
-- Metemos unas pocas filas
insert into maestro(data) values ('hola'),('mundo')
go 2000
declare @i int=1;
while (@i<1998)
begin
insert into detalle(fk_maestro,data) values (@i,'hola detalle'),(@i+1,'mundo detalle')
set @i+=2
end
go
--select * from dbo.maestro m inner join dbo.detalle d on m.id_maestro = d.fk_maestro
select * from dbo.maestro
select * from dbo.detalle
go
------------------------------------------------------------------------------------------------------
-- Ejecución parametrizada
-- 50k ejecuciones simulando aplicación corriendo
-- se lanza 50k veces la misma consulta cambiando el valor
-- MOSTRAR PERFORMANCE MONITOR
------------------------------------------------------------------------------------------------------
dbcc freeproccache --> nunca lanzar en producción esto!!!
go
declare @s1 nvarchar(max), @i2 int
declare @query nvarchar(1024), @queryExec nvarchar(1024)
set @query = N'declare @a int
select @a = 1
from dbo.maestro m
inner join dbo.detalle d on m.id_maestro = d.fk_maestro
where id_maestro=@p1'
declare @i int=1;
while (@i<50000)
begin
exec sp_executesql @stmt=@query,@params=N'@p1 int',@p1=@i
set @i+=1
end
go
-- Podemos ver un único plan de eejecución (una única compilación)
-- utilizado 49999 veces
-- Atentos al tamaño de caché de procedimientos utilizada
--
SELECT usecounts, cacheobjtype, objtype, size_in_bytes, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype LIKE 'Compiled Plan%' and text like '%from dbo.maestro%'
and text not like '%dm_exec_cached_plans%'
------------------------------------------------------------------------------------------------------
-- Ejecución AD-HOC
-- 50k ejecuciones simulando aplicación corriendo
-- se lanza 50k veces la misma consulta cambiando el valor
-- MOSTRAR PERFORMANCE MONITOR
------------------------------------------------------------------------------------------------------
-- Primero vaciamos la caché, para que no quede lugar a dudas
--
dbcc freeproccache
set nocount on;
declare @s1 nvarchar(max), @i2 int
declare @query nvarchar(1024), @queryExec nvarchar(1024)
set @query = N'declare @a int
select @a = 1
from dbo.maestro m
inner join dbo.detalle d on m.id_maestro = d.fk_maestro
where id_maestro='
declare @i int=1;
while (@i<50000)
begin
set @queryExec = CONCAT(@query, @i)
--print @queryExec
exec(@queryExec)
set @i+=1
end
go
-- Ahora vemos 49999 objetos en la caché de procedimientos
--
SELECT usecounts, cacheobjtype, objtype, size_in_bytes, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype LIKE 'Compiled Plan%' and text like '%from dbo.maestro%'
and text not like '%dm_exec_cached_plans%'
-- Seamos un poco conscientes con números :)
-- ¿Que me ocupan mis escasas 50k consultitas?
--
SELECT sum(size_in_bytes) as size_in_bytes,
sum(size_in_bytes)/1024 as size_in_kb,
sum(size_in_bytes)/1024/1024 as size_in_mb,
sum(size_in_bytes)*1.0/1024/1024/1024 as size_in_gigabytes
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype LIKE 'Compiled Plan%' and text like '%from dbo.maestro%'
and text not like '%dm_exec_cached_plans%'
------------------------------------------------------------------------------------------------------
-- Ejecución AD-HOC desde stored procedure
-- ojo, usar stored procedure o sp_executesql incorrectamente son igual de malos
--
------------------------------------------------------------------------------------------------------
-- Primero vaciamos la caché, para que no quede lugar a dudas
--
dbcc freeproccache
go
declare @s1 nvarchar(max), @i2 int
declare @query nvarchar(1024), @queryExec nvarchar(1024)
set @query = N'declare @a int
select @a = 1
from dbo.maestro m
inner join dbo.detalle d on m.id_maestro = d.fk_maestro
where id_maestro=1'
exec sp_executesql @query
set @query = N'declare @a int
select @a = 1
from dbo.maestro m
inner join dbo.detalle d on m.id_maestro = d.fk_maestro
where id_maestro=2'
exec sp_executesql @query
-- Como vemos, ya que se ha utilizado incorrectamente la parametrización, el plan de ejecución no se reutiliza
-- Lo mismo ocurriría en el caso de stored procedures que crean al vuelo una query...para eso mejor no hacerlos :)
--
SELECT usecounts, cacheobjtype, objtype, size_in_bytes, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype LIKE 'Compiled Plan%' and text like '%from dbo.maestro%'
and text not like '%dm_exec_cached_plans%'
go
-- ENSEÑAR DEMO DE CURSORES!!!!!!