Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- create three tables with different column sizes
- CREATE TABLE dbo.t1(a nvarchar(32), b nvarchar(32), c nvarchar(32), d nvarchar(32));
- CREATE TABLE dbo.t2(a nvarchar(4000), b nvarchar(4000), c nvarchar(4000), d nvarchar(4000));
- CREATE TABLE dbo.t3(a nvarchar(max), b nvarchar(max), c nvarchar(max), d nvarchar(max));
- -- populate them with a bunch of rows, 100 times
- INSERT dbo.t1(a,b,c,d) SELECT LEFT(name,1), RIGHT(name,1),
- ABS(column_id/10), ABS(column_id%10)
- FROM sys.all_columns ORDER BY object_id;
- GO 100
- INSERT dbo.t2(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
- INSERT dbo.t3(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
- GO
- -- no "primed the cache in advance" tricks
- DBCC FREEPROCCACHE WITH NO_INFOMSGS;
- DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
- GO
- -- run the same query against all three tables
- GO
- SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
- FROM dbo.t1 GROUP BY a,b,c,d ORDER BY c,a DESC;
- GO
- SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
- FROM dbo.t2 GROUP BY a,b,c,d ORDER BY c,a DESC;
- GO
- SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
- FROM dbo.t3 GROUP BY a,b,c,d ORDER BY c,a DESC;
- GO
- SELECT [table] = N'...' + SUBSTRING(t.[text], CHARINDEX(N'FROM ', t.[text]), 12) + N'...',
- s.last_dop, s.last_elapsed_time, s.last_grant_kb, s.max_ideal_grant_kb
- FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
- WHERE t.[text] LIKE N'%dbo.'+N't[1-3]%'
- ORDER BY t.[text];
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement