SHARE
TWEET

Window Aggregate Logical Reads

a guest Mar 16th, 2017 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. drop table dbo.CS;
  2. drop table dbo.T;
  3.  
  4. go
  5.  
  6. create table dbo.CS(CS bit not null, index IX_CS clustered columnstore);
  7. create table dbo.T(I int not null, P int not null);
  8. create index IX_T_P on dbo.T(P) include(I);
  9.  
  10. go
  11.  
  12. declare @NumRows int;
  13. declare @NumPart int;
  14.  
  15. set @NumRows = 100000;
  16. set @NumPart = 4
  17.  
  18. insert into dbo.T(I, P)
  19. select top(@NumRows)
  20.   row_number() over(order by(select null)),
  21.   row_number() over(order by(select null)) % @NumPart
  22. from sys.columns as C1,
  23.      sys.columns as C2,
  24.      sys.columns as C3
  25.  
  26. go
  27.  
  28. set statistics io on;
  29.  
  30. declare @I int;
  31. select @I = min(I) over(partition by P)
  32. from dbo.T
  33.   left outer join dbo.CS on 0 = 1;
  34.  
  35. set statistics io off;
  36.  
  37. /*
  38. Result on my machine
  39. Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
  40.  
  41. NumRows    NumPart   Logical reads in Worktable
  42. 100,000    4         2902
  43. 100,000    5         0
  44. 1,000,000  8         37535
  45. 1,000,000  9         0
  46.  
  47. */
RAW Paste Data
Top