Go to Main page..

Operator Sort and Spill Level , Microsoft SQL Server (Sql.ru)





Operator Sort and Spill Level [new]
Hello world!
There are two tables. Both have a clustered index. The first contains 242575, and the second 97142 records.
 TABLE [ tmp ].[ T_abc_branch_ ]( [ DTST ] [ int ] NOT NULL , [ DTED ] [ int ] NOT NULL , [ Product_Key ] [ int ] NOT NULL , [ Branch_Key ] [ int ] NOT NULL , [ ABC_Количество ] [ nvarchar ]( 5 ) NOT NULL , [ Rating_Количество ] [ bigint ] NOT NULL , [ ABC_Доход ] [ nvarchar ]( 5 ) NOT NULL , [ Rating_Доход ] [ bigint ] NOT NULL , [ ABC_Оборот ] [ nvarchar ]( 5 ) NOT NULL , [ Rating_Оборот ] [ bigint ] NOT NULL , [ ABC_Клиенты ] [ nvarchar ]( 5 ) NOT NULL , [ Rating_Клиенты ] [ bigint ] NOT NULL , [ ABC_Частота ] [ nvarchar ]( 5 ) NOT NULL , [ Rating_Частота ] [ bigint ] NOT NULL , [ ABC_ОстаткиШт ] [ nvarchar ]( 5 ) NOT NULL , [ Rating_ОстаткиШт ] [ bigint ] NOT NULL , [ ABC_ОстаткиRUB ] [ nvarchar ]( 5 ) NOT NULL , [ Rating_ОстаткиRUB ] [ bigint ] NOT NULL , [ ABC_ВЕС ] [ nvarchar ]( 5 ) NOT NULL , [ Rating_ВЕС ] [ bigint ] NOT NULL , [ ABC_ОБЪЕМ ] [ nvarchar ]( 5 ) CREATE TABLE [ tmp ]. [ T_abc_branch_ ] ( [ DTST ] [ int ] NOT NULL , [ DTED ] [ int ] NOT NULL , [ Product_Key ] [ int ] NOT NULL , [ Branch_Key ] [ int ] NOT NULL , [ ABC_Number ] [ nvarchar ] ( 5 ) NOT NULL , [ Rating_Number ] [ bigint ] NOT NULL , [ ABC_Income ] [ nvarchar ] ( 5 ) NOT NULL , [ Rating_Income ] [ bigint ] NOT NULL , [ ABC_Turn ] [ nvarchar ] ( 5 ) NOT NULL , [ Rating_Turns ] [ bigint ] NOT NULL , [ ABC_Clients ] [ nvarchar ] ( 5 ) NOT NULL , [ Rating_Clients ] [ bigint ] NOT NULL , [ ABC_Frequency ] [ nvarchar ] ( 5 ) NOT NULL , [ Rating_Frequency ] [ bigint ] NOT NULL , [ ABC_RequireSt ] [ nvarchar ] ( 5 ) NOT NULL , [ Rating_OstatSt. ] [ bigint ] NOT NULL , [ ABC_Ostall RUB ] [ nvarchar ] ( 5 ) NOT NULL ] [ Rating_ Remain RUB ] [ bigint ] NOT NULL , [ ABC_VE ] ] 5 ) NOT NULL , [ Rating_WEC ] [ bigint ] NOT NULL , [ ABC_VOLUME ] [ nvarchar ] ( 5 )  NULL , [ Rating_ОБЪЕМ ] [ bigint ] NOT NULL ) CREATE UNIQUE CLUSTERED INDEX [ IC_U_T_abc_branch_:Год:Product_Key:Branch_Key: ] ON [ tmp ].[ T_abc_branch_ ] ( [ DTST ] ASC , [ DTED ] ASC , [ Product_Key ] ASC , [ Branch_Key ] ASC ) CREATE TABLE [ dim ].[ ABC_Branch_SKU ]( [ ABC_Key ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [ ABC_Количество ] [ nvarchar ]( 50 ) NOT NULL , [ ABC_Количество_ORD ] [ smallint ] NOT NULL , [ ABC_Доход ] [ nvarchar ]( 50 ) NOT NULL , [ ABC_Доход_ORD ] [ smallint ] NOT NULL , [ ABC_Оборот ] [ nvarchar ]( 50 ) NOT NULL , [ ABC_Оборот_ORD ] [ smallint ] NOT NULL , [ ABC_Клиенты ] [ nvarchar ]( 50 ) NOT NULL , [ ABC_Клиенты_ORD ] [ smallint ] NOT NULL , [ ABC_Частота ] [ nvarchar ]( 50 ) NOT NULL , [ ABC_Частота_ORD ] [ smallint ] NOT NULL , [ ABC_ОстаткиШт ] [ nvarchar ]( 50 ) NOT NULL , [ ABC_ОстаткиШт_ORD ] [ smallint ] NOT NULL , [ ABC_ОстаткиRUB ] [ nvarch NOT NULL , [ Rating_VOLUME ] [ bigint ] NOT NULL ) CREATE UNIQUE CLUSTERED INDEX [ IC_U_T_abc_branch_: Year: Product_Key: Branch_Key: ] ON [ tmp ]. [ T_abc_branch_ ] ( [ DTST ] ASC , [ DTED ] ASC , [ Print ] [ T_abc_branch_ ]] [ Branch_Key ] ASC ) CREATE TABLE [ dim ]. [ ABC_Branch_SKU ] ( [ ABC_Key ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [ ABC_Number ] [ nvarchar ] ( 50 ) NOT NULL , [ ABC_Number_ORD ] [ smallint ] NOT NU , [ ABC_Income ] [ nvarchar ] ( 50 ) NOT NULL , [ ABC_Income_ORD ] [ smallint ] NOT NULL , [ ABC_Turnover ] [ nvarchar ] ( 50 ) NOT NULL , [ ABC_Obor_ORD ] [ smallint ] NOT NULL , [ ABC_Clients ] [ nvarchar ] 50 ) NOT NULL , [ ABC_Clients_ORD ] [ smallint ] NOT NULL , [ ABC_Frequency ] [ nvarchar ] ( 50 ) NOT NULL , [ ABC_Frequency_ORD ] [ smallint ] NOT NULL , [ ABC_Leave ] [ nvarchar ] ( 50 ) NOT NULL , [ ABC_O ] [ smallint ] NOT NULL , [ ABC_RemainsRUB ] [ nvarch  ]( 50 ) NOT NULL , [ ABC_ОстаткиRUB_ORD ] [ smallint ] NOT NULL , [ ABC_ВЕС ] [ nvarchar ]( 50 ) NOT NULL , [ ABC_ВЕС_ORD ] [ smallint ] NOT NULL , [ ABC_ОБЪЕМ ] [ nvarchar ]( 50 ) NOT NULL , [ ABC_ОБЪЕМ_ORD ] [ smallint ] NOT NULL ) CREATE UNIQUE CLUSTERED INDEX [ IC_U_ABC_Branch_SKU:ABC_Key: ] ON [ dim ].[ ABC_Branch_SKU ] ( [ ABC_Key ] ASC ) ar ] ( 50 ) NOT NULL , [ ABC_RestoresUB_ORD ] [ smallint ] NOT NULL , [ ABC_WEC ] [ nvarchar ] ( 50 ) NOT NULL , [ ABC_ВЕС_ORD ] [ smallint ] NOT NULL , [ ABC_VOL ] [ nvarchar ] ( 50 ) NOT NULL , [ ABC_VOLUME ] [ smallint ] NOT NULL ) CREATE UNIQUE CLUSTERED INDEX [ IC_U_ABC_Branch_SKU: ABC_Key: ] ON [ dim ]. [ ABC_Branch_SKU ] ( [ ABC_Key ] ASC ) 
Before performing the main request, I update the statistics.
 STATISTICS [ DWH_Staging_Area ].[ tmp ].[ T_abc_branch_ ] [ IC_U_T_abc_branch_:Год:Product_Key:Branch_Key: ] WITH FULLSCAN UPDATE STATISTICS [ DWH_Staging_Area ]. [ Tmp ]. [ T_abc_branch_ ] [ IC_U_T_abc_branch_: Year: Product_Key: Branch_Key: ] WITH FULLSCAN
 STATISTICS [ DWH ].[ dim ].[ ABC_Branch_SKU ] [ IC_U_ABC_Branch_SKU:ABC_Key: ] WITH FULLSCAN UPDATE STATISTICS [ DWH ]. [ Dim ]. [ ABC_Branch_SKU ] [ IC_U_ABC_Branch_SKU: ABC_Key: ] WITH FULLSCAN
Main query script
		 ABC_ . SELECT ABC_ .  ABC .* ABC_Key , ABC . *
		 [ DWH_Staging_Area ].[ tmp ].[ T_abc_branch_ ] AS ABC FROM [ DWH_Staging_Area ] . [ Tmp ] . [ T_abc_branch_ ] AS ABC
		 [ DWH ].[ dim ].[ ABC_Branch_SKU ] AS ABC_ JOIN [ DWH ] . [ Dim ] . [ ABC_Branch_SKU ] AS ABC_
			 ABC .[ ABC_Количество ] = ABC_ .[ ABC_Количество ] AND ON ABC . [ ABC_Number ] = ABC_ . [ ABC_Number ] AND
				 ABC_Доход ] = ABC_ .[ ABC_Доход ] AND ABC . [ ABC_Income ] = ABC_ . [ ABC_Income ] AND
				 ABC_Оборот ] = ABC_ .[ ABC_Оборот ] AND ABC . [ ABC_Turn ] = ABC_ . [ ABC_Turn ] AND
				 ABC_Клиенты ] = ABC_ .[ ABC_Клиенты ] AND ABC . [ ABC_Clients ] = ABC_ . [ ABC_Clients ] AND
				 ABC_Частота ] = ABC_ .[ ABC_Частота ] AND ABC . [ ABC_Frequency ] = ABC_ . [ ABC_Frequency ] AND
				 ABC_ОстаткиШт ] = ABC_ .[ ABC_ОстаткиШт ] AND ABC . [ ABC_Ostatt ] = ABC_ . [ ABC_OstatTit ] AND
				 ABC_ОстаткиRUB ] = ABC_ .[ ABC_ОстаткиRUB ] AND ABC . [ ABC_RostRUB ] = ABC_ . [ ABC_RepairRUB ] AND
				 ABC_ВЕС ] = ABC_ .[ ABC_ВЕС ] AND ABC . [ ABC_WEACH ] = ABC_ . [ ABC_WEIGHT ] AND
				 ABC_ОБЪЕМ ] = ABC_ .[ ABC_ОБЪЕМ ] ABC . [ ABC_VOLUME ] = ABC_ . [ ABC_VOLUME ] 
		 ( RECOMPILE ) OPTION ( RECOMPILE )	
When it is executed, a query plan is used, in which the Sort operator merges data onto a disk, although the cardinality is calculated correctly. Tell me what is the reason?
Re: Operator Sort and Spill Level [new]


Attached file Size - 132Kb
Re: Operator Sort and Spill Level [new]
And how will the sequel know how many lines will be in the end? If your statistics are not for the fields for which the connection is going, well, and if in truth I do not see a problem. It may be banal lack of memory. Alternatively, you can remove the RECOMPILE and see what's wrong with the plan.
Re: Operator Sort and Spill Level [new]
So ne climbs into memory!
Re: Operator Sort and Spill Level [new]
when the server especially wants to sort,
He can explicitly stick hash join in request
Re: Operator Sort and Spill Level [new]
Allandenton
And how will the sequel know how many lines will be in the end? If your statistics are not for the fields that are connected
Data spits in the Sort statement before making the connection.
Allandenton
It may be banal lack of memory.
How to check it?
Re: Operator Sort and Spill Level [new]
Yasha123
when the server especially wants to sort,
He can explicitly stick hash join in request

That you, that you, now apologists of "without screw" programming.
Re: Operator Sort and Spill Level [new]
Share the execution plan in sqlplan pzhl format
Re: Operator Sort and Spill Level [new]
Yagrus2,

what version of the sequel do you have?

can try experimenting with variations
 ( maxdop 1 ) option ( maxdop 1 )
 ( querytraceon 7470 ) option ( querytraceon 7470 )
Re: Operator Sort and Spill Level [new]
Yasha123
when the server especially wants to sort,
He can explicitly stick hash join in request

I tried, but such a request (hash join) cost more than the original (merge join + sort).
Re: Operator Sort and Spill Level [new]
felix_ff
Yagrus2,

what version of the sequel do you have?


Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27
Copyright (c) Microsoft Corporation
Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Re: Operator Sort and Spill Level [new]
msLex
Yasha123
when the server especially wants to sort,
He can explicitly stick hash join in request

That you, that you, now apologists of "without screw" programming.

in its place, 2008 R2 occasionally took down the roof,
massively merjil where else hadh flaunted the day before, he himself had chosen.
naturally, with sorting of both tables (huge tables).
nailed hinty, good data in the table just added.
Re: Operator Sort and Spill Level [new]
Yagrus2
Yasha123
when the server especially wants to sort,
He can explicitly stick hash join in request

I tried, but such a request (hash join) cost more than the original (merge join + sort).

Yes, do not care about the cost.
run and make sure.
you see what volumes he decided to sort?
Re: Operator Sort and Spill Level [new]
here by the way and the answer from what suddenly got merge.
because hash see you suddenly went up.
still he sanely explained, in honor of what the rise in price
Re: Operator Sort and Spill Level [new]
Allandenton
Share the execution plan in sqlplan pzhl format


cкачать Attached file ( 111.sqlplan - 119Kb) download
Re: Operator Sort and Spill Level [new]
Yasha123
Yes, do not care about the cost.
run and make sure.
you see what volumes he decided to sort?

I do not understand you.
I simultaneously launched two requests. The first in hint on HASH JOIN. Through viewing of Execution plan I see such picture

Attached file Size - 36Kb
Re: Operator Sort and Spill Level [new]
yes, by the way, and not 2008 it was,
a 2012
I keep everything.
I can give to anyone who wants to break his head,
what the hell hash got it.
Actually, the hash was 3.5 times faster
and did not climb in tempdb

+

Warning: The join order has been used.
Table 'TAB_PROCEDURE_T'. Physical reads 0, read-ahead reads 0, lob physical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TAB_TABELLONE_DATI_AGGIUNTIVI_T_day'. Scan count 9, logical reads 137616, read physical reads 7, read read 7, logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Physical reads 0, read-ahead reads 155434, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1903553 row (s) affected)

(1 row (s) affected)

SQL Server Execution Times:
CPU time = 39484 ms, elapsed time = 47835 ms.

Table 'TAB_TABELLONE_DATI_AGGIUNTIVI_T_day'. Scan count 9, logical reads 137792, read physical reads 10, read logical 10, read logical 0, logical reads 0, lob physical reads, read 0 0 lob.
Table 'TAB_PROCEDURE_T'. Scan count 9, logical reads 3534693, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan, read, read, 0, read, 0, read, 0, lob, read, 0;
Table 'Worktable'. Scan, read, read, 0, read, 0, read, 0, lob, read, 0;

(1903553 row (s) affected)

(1 row (s) affected)


cкачать Attached file ( merge_hash_carla.zip - 31Kb) download
Re: Operator Sort and Spill Level [new]
Yagrus2,
what is not clear?
You are watching the score in parrots.
and I'm telling you to run both queries and compare the execution times.
real time
Re: Operator Sort and Spill Level [new]
Yasha123,
The size of the table being sorted is 36,133 MB
Re: Operator Sort and Spill Level [new]
Yagrus2
The size of the table being sorted is 36,133 MB

Generally yes, your tables are hilovaty.
In my case, 27GB was in the main table.
nevertheless, he was impatient to sort
Re: Operator Sort and Spill Level [new]
Yasha123,

you have estimated plans attached: (
interested, but did not give specifics.
Re: Operator Sort and Spill Level [new]
felix_ff
Yasha123,

you have estimated plans attached: (
interested, but did not give specifics.


Konkterika in statistics of performance under the spoiler.
I enclose what is preserved.
I already have another client
nevertheless, I remember exactly that the plans flew en masse.
and, most likely, it was after the introduction of page compression
on a number of tables.
they did not want to remove the compression, but they demanded an urgent return to the previous execution time.
so I had to beat the hints.
server configuration is 100% unchanged,
the data in the tables from one day to another in a hundred or thousand lines were added.
Re: Operator Sort and Spill Level [new]
+ the estimated were saved,
so that you can see the assessment when choosing a plan.
this incidentally was just an atypical case where the hash was worth less
but he did not choose him anyway.
in other cases, he also believed that the hash is more expensive.
now still rummage, if I find I will dump it here.
-
this case is doubly strange because it has to choose with a lower cost,
but in reality it was not so
Re: Operator Sort and Spill Level [new]
So there merge misses in the assessment 50 times
Re: Operator Sort and Spill Level [new]
Yasha123
Yagrus2,
and I'm telling you to run both queries and compare the execution times.
real time
Through HASH JOIN, the runtime is shorter.
Script Time
	
 ABC_ . SELECT ABC_ .  ABC_Key , 
	 ABC .  DTST ,	
	 ABC .  DTED ,	
	 Product_Key	
	 Branch_Key
 #q2 into # q2
 [ DWH_Staging_Area ].[ tmp ].[ T_abc_branch_ ] AS ABC FROM [ DWH_Staging_Area ] . [ Tmp ] . [ T_abc_branch_ ] AS ABC
 [ DWH ].[ dim ].[ ABC_Branch_SKU ] AS ABC_ JOIN [ DWH ] . [ Dim ] . [ ABC_Branch_SKU ] AS ABC_
   ABC .[ ABC_Количество ] = ABC_ .[ ABC_Количество ] AND ON ABC . [ ABC_Number ] = ABC_ . [ ABC_Number ] AND
	 ABC_Доход ] = ABC_ .[ ABC_Доход ] AND ABC . [ ABC_Income ] = ABC_ . [ ABC_Income ] AND
	 ABC_Оборот ] = ABC_ .[ ABC_Оборот ] AND ABC . [ ABC_Turn ] = ABC_ . [ ABC_Turn ] AND
	 ABC_Клиенты ] = ABC_ .[ ABC_Клиенты ] AND ABC . [ ABC_Clients ] = ABC_ . [ ABC_Clients ] AND
	 ABC_Частота ] = ABC_ .[ ABC_Частота ] AND ABC . [ ABC_Frequency ] = ABC_ . [ ABC_Frequency ] AND
	 ABC_ОстаткиШт ] = ABC_ .[ ABC_ОстаткиШт ] AND ABC . [ ABC_Ostatt ] = ABC_ . [ ABC_OstatTit ] AND
	 ABC_ОстаткиRUB ] = ABC_ .[ ABC_ОстаткиRUB ] AND ABC . [ ABC_RostRUB ] = ABC_ . [ ABC_RepairRUB ] AND
	 ABC_ВЕС ] = ABC_ .[ ABC_ВЕС ] AND ABC . [ ABC_WEACH ] = ABC_ . [ ABC_WEIGHT ] AND
	 ABC_ОБЪЕМ ] = ABC_ .[ ABC_ОБЪЕМ ] ABC . [ ABC_VOLUME ] = ABC_ . [ ABC_VOLUME ] 
 ( RECOMPILE , HASH JOIN ) OPTION ( RECOMPILE , HASH JOIN )	
 row(s) affected) ( 242575 row (s) affected)

 row(s) affected) ( 1 row (s) affected)

  SQL Server Execution Times:
    ms, elapsed time = 405 ms. CPU time = 1435 ms, elapsed time = 405 ms.
 SQL Server parse and compile time: 
    ms, elapsed time = 5 ms. CPU time = 5 ms, elapsed time = 5 ms.
 ABC_ . SELECT ABC_ .  ABC . ABC_Key , ABC .  ABC . DTST , ABC .  Product_Key , Branch_Key into #q3 FROM [ DWH_Staging_Area ].[ tmp ].[ T_abc_branch_ ] AS ABC JOIN [ DWH ].[ dim ].[ ABC_Branch_SKU ] AS ABC_ ON ABC .[ ABC_Количество ] = ABC_ .[ ABC_Количество ] AND ABC .[ ABC_Доход ] = ABC_ .[ ABC_Доход ] AND ABC .[ ABC_Оборот ] = ABC_ .[ ABC_Оборот ] AND ABC .[ ABC_Клиенты ] = ABC_ .[ ABC_Клиенты ] AND ABC .[ ABC_Частота ] = ABC_ .[ ABC_Частота ] AND ABC .[ ABC_ОстаткиШт ] = ABC_ .[ ABC_ОстаткиШт ] AND ABC .[ ABC_ОстаткиRUB ] = ABC_ .[ ABC_ОстаткиRUB ] AND ABC .[ ABC_ВЕС ] = ABC_ .[ ABC_ВЕС ] AND ABC .[ ABC_ОБЪЕМ ] = ABC_ .[ ABC_ОБЪЕМ ] OPTION ( RECOMPILE ) DTED , Product_Key , Branch_Key into # q3 FROM [ DWH_Staging_Area ]. [ Tmp ]. [ T_abc_branch_ ] AS ABC JOIN [ DWH ]. [ Dim ] . [ ABC_Branch_SKU ] AS ABC_ ON ABC . [ ABC_Count ] = ABC_ . [ OC_C . [ ABC_Income ] = ABC_ . [ ABC_Income ] AND ABC . [ ABC_Turnover ] = ABC_ . [ ABC_Turnover ] AND ABC . [ ABC_Customers ] = ABC_ . [ ABC_Customers ] AND ABC . [ ABC_Frequency ] = ABC_ . [ ABC_Customer ] ABC_StatusPt ] = ABC_ . [ ABC_StatchesNom ] AND ABC . [ ABc . 
 row(s) affected) ( 242575 row (s) affected)

 row(s) affected) ( 1 row (s) affected)

  SQL Server Execution Times:
    ms, elapsed time = 1723 ms. CPU time = 4946 ms, elapsed time = 1723 ms.
 SQL Server parse and compile time: 
    ms, elapsed time = 0 ms. CPU time = 0 ms, elapsed time = 0 ms.
Re: Operator Sort and Spill Level [new]
Well, I'm talking about.
sometimes he wants to sort even where not needed, and he sorts.
and when I don’t want him to do this, I put in a hint.
you know your data better
on my for example, it was known
that there will only be more of them, and what better hash join.
so hint and now there
Re: Operator Sort and Spill Level [new]
Yasha123,

author
what the hell hash got up


So have revised the cost since 2014.
Re: Operator Sort and Spill Level [new]
2012 server is discussed here.
Re: Operator Sort and Spill Level [new]
Tell me, what formula hash join for is obtained Estimated Number of Rows?
What can be done to improve the assessment?
Re: Operator Sort and Spill Level [new]
Yagrus2,

Attached file Size - 22Kb
Re: Operator Sort and Spill Level [new]
Yagrus2,

you do not have anything that would help sql adequately calculate the assessment.



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ