Monday, April 24, 2017

AIX: from process ID, to physical/virtual memory used, to TCP connection (Half-baked draft)

# ps -ef | grep -i java
xlisten8  8388648        1   1   Apr 11      - 24:58 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8  9633838        1   1   Apr 11      - 25:44 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8  9961686        1   1   Apr 11      - 32:53 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 10682506        1   0   Apr 11      - 15:14 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 10748116        1   0   Apr 11      - 28:20 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 11600048        1   1   Apr 11      - 62:18 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 13041852        1   1   Apr 11      - 29:10 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar
xlisten8 14745762        1   1   Apr 11      - 15:22 java -cp sasqconfig.jar:sasqjava.jar:sasqjdbc4.jar -Xms256M -Xmx256M -jar JLC.jar

# svmon -P -O summary=basic,unit=MB | head -n 4 ; svmon -P -O summary=basic,unit=MB | grep java
Unit: MB

-------------------------------------------------------------------------------
     Pid Command          Inuse      Pin     Pgsp  Virtual
11600048 java           1679.27   256.48   340.14  2041.33
 9961686 java           1651.38   256.41   330.53  2005.05
 9633838 java           1613.63   256.43   341.31  1978.62
13041852 java           1593.01   256.38   369.52  1998.18
 8388648 java           1585.89   256.39   343.54  1966.96
10748116 java           1430.42   256.29   209.10  1684.59
10682506 java            928.71   256.25   157.50  1130.74
14745762 java            928.30   256.25   158.09  1130.82

# netstat -Aan | grep ESTABLISHED | grep -v 127.0.0.1 | grep -v tcp4 | grep -v tcp6
f100050000c393b8 tcp        0      0  10.xxx.xx.xxx.48323   10.xxx.xx.xxx.50321    ESTABLISHED
f100050000f583b8 tcp        0      0  10.xxx.xx.xxx.48316   10.xxx.xx.xxx.50505    ESTABLISHED
f100050001f4e3b8 tcp        0      0  10.xxx.xx.xxx.48311   10.xxx.xx.xxx.50844    ESTABLISHED
f100050001ea6bb8 tcp        0      0  10.xxx.xx.xxx.48314   10.xxx.xx.xxx.56565    ESTABLISHED
f100050001c773b8 tcp        0      0  10.xxx.xx.xxx.48332   10.xxx.xx.xxx.50531    ESTABLISHED
f100050001ea2bb8 tcp        0      0  10.xxx.xx.xxx.48361   10.xxx.xx.xxx.50839    ESTABLISHED
f100050000f6b3b8 tcp        0      0  10.xxx.xx.xxx.50707   10.xxx.xx.xxx.50419    ESTABLISHED
f100050000de4bb8 tcp        0      0  10.xxx.xx.xxx.33387   10.xxx.xx.xxx.50505    ESTABLISHED

# rmsock f100050000c393b8 tcpcb
The socket 0xf100050000c39008 is being held by proccess 9633838 (java).
# rmsock f100050000f583b8 tcpcb
The socket 0xf100050000f58008 is being held by proccess 14745762 (java).
# rmsock f100050001f4e3b8 tcpcb
The socket 0xf100050001f4e008 is being held by proccess 9961686 (java).
# rmsock f100050001ea6bb8 tcpcb
The socket 0xf100050001ea6808 is being held by proccess 10748116 (java).
# rmsock f100050001c773b8 tcpcb
The socket 0xf100050001c77008 is being held by proccess 13041852 (java).
# rmsock f100050001ea2bb8 tcpcb
The socket 0xf100050001ea2808 is being held by proccess 11600048 (java).
# rmsock f100050000f6b3b8 tcpcb
The socket 0xf100050000f6b008 is being held by proccess 10682506 (java).
# rmsock f100050000de4bb8 tcpcb
The socket 0xf100050000de4808 is being held by proccess 8388648 (java).

Oracle 12cR2 vs SQL Server 2016: Histogram types



In Oracle, there are 4 histogram types:
frequency
height-balanced
top-n frequency
hybrid
Frequency histogram: for low numbers of distinct values. If ndv < bucket count limit, then each ndv gets a histogram bucket. If ndv > limit, frequency histogram is disqualified.
Height-balanced histogram: histogram buckets with approximately the same number of rows in each bucket
Top-n Frequency: lowest value, highest value, n-2 most popular values in between as bucket end-points.
Hybrid: Top-n Frequency not qualified because N end-point values do not cross threshold to qualify.

In SQL Server 2016 (as has been the case for several major versions), one histogram type is available.  Of the histogram types available in Oracle 12cR2, it is most similar to the hybrid histogram.  For a nullable column, a NULL histogram bucket will be available.  For non-null values, up to 200 additional buckets are available. The desired number of buckets cannot be specified. For values of particular significance, or in order to accommodate skew in data, filtered statistics can be created.  For filtered statistics to reliably be consulted by the optimizer, a query filter predicate must match the predicate of the filtered statistics. 

Resources

March 2017 Whitepaper (1.1 mb pdf) 
Understanding Optimizer Statistics with Oracle Database 12c Release 2

Three part Oracle Database Histogram series from Jonathan Lewis

Another three part Oracle Database Histogram series from Jonathan Lewis

Two part Oracle Database Histogram series from Mohamed Houri

Friday, April 21, 2017

SQL Server 2016: 1 GB memory grant to create an *empty* CCI?


CREATE TABLE dbo.MyFact_heap 
( [Key] bigint NOT NULL, 
[Type] nvarchar(50) NOT NULL, 
Id nvarchar(50) NOT NULL, 
AltKey_1 bigint NOT NULL DEFAULT -1, 
AltKey_2 bigint NOT NULL DEFAULT -1, 
AltKey_3 bigint NOT NULL DEFAULT -1, 
AltKey_4 bigint NOT NULL DEFAULT -1, 
AltKey_5 bigint NOT NULL DEFAULT -1, 
AltKey_6 bigint NOT NULL DEFAULT -1, 
AltKey_7 bigint NOT NULL DEFAULT -1, 
AltKey_8 bigint NOT NULL DEFAULT -1, 
AltKey_9 bigint NOT NULL DEFAULT -1, 
AltKey_10 bigint NOT NULL DEFAULT -1, 
String_1 nvarchar(300) NOT NULL DEFAULT N'*Default', 
Numeric_1 numeric(18,2) NULL, 
Date_1 date NULL,
TimeValue time NULL, 
Int_1 int NULL, 
String_2 nvarchar(300) NOT NULL DEFAULT N'*Default', 
Tiny_1 tinyint NULL DEFAULT 0, 
Tiny_2 tinyint NULL DEFAULT 0, 
Tiny_3 tinyint NULL, 
Tiny_4 tinyint NULL, 
Tiny_5 tinyint NULL, 
Tiny_6 tinyint NOT NULL DEFAULT 1 ) 


CREATE CLUSTERED COLUMNSTORE INDEX CCI__MyFact 
ON dbo.MyFact_heap

Requesting the actual plan for the CREATE, I find this in the plan XML.  1197064 as the SerialDesiredMemory, the RequiredMemory, the RequestedMemory.


That's a big grant for something that operates on 0 rows.  When creating many CCIs concurrently, that's a problem - queries can timeout.

Might have to use Resource Governor creatively to allow high concurrency CCI creation.

Grant request is smaller if only one column in the CCI.  So I guess could create a smaller CCI and add columns.  That's also kinda painful.  I'll ask on #sqlhelp, see if anyone has something clever...

*****
So far the only things to come up on Twitter were the min_grant_percent and max_grant_percent query hints introduced in SQL Server 2012 SP3.  But...

"Note These two new query memory grant options aren't available for index creation or rebuild."

https://support.microsoft.com/en-us/help/3107401/new-query-memory-grant-options-are-available-min-grant-percent-and-max-grant-percent-in-sql-server-2012

Later I found this while looking around.


"Starting with SQL Server 2016, you can create the table as a clustered columnstore index. It is no longer necessary to first create a rowstore table and then convert it to a clustered columnstore index."

Hmmm. Here's a great post from Pedro Lopes (Twitter @sqlpto), which includes some details for using Extended Events in memory grant investigation.




So far, it looks like creating the clustered columnstore inline with table creation, as below, avoids a memory grant.  (That makes sense.)  Whereas creating a clustered columnstore index on an empty rowstore table requires a memory grant - at the same size as up to 1 million rows for the table! (That's a surprise.)

If you'll be creating CCIs at fairly high concurrency, you may also want to consider creating the CCI inline with table create, as below, in order to avoid a potential bottleneck in memory grants.


CREATE TABLE dbo.MyFact 
( [Key] bigint NOT NULL, 
[Type] nvarchar(50) NOT NULL, 
Id nvarchar(50) NOT NULL, 
AltKey_1 bigint NOT NULL DEFAULT -1, 
AltKey_2 bigint NOT NULL DEFAULT -1, 
AltKey_3 bigint NOT NULL DEFAULT -1, 
AltKey_4 bigint NOT NULL DEFAULT -1, 
AltKey_5 bigint NOT NULL DEFAULT -1, 
AltKey_6 bigint NOT NULL DEFAULT -1, 
AltKey_7 bigint NOT NULL DEFAULT -1, 
AltKey_8 bigint NOT NULL DEFAULT -1, 
AltKey_9 bigint NOT NULL DEFAULT -1, 
AltKey_10 bigint NOT NULL DEFAULT -1, 
String_1 nvarchar(300) NOT NULL DEFAULT N'*Default', 
Numeric_1 numeric(18,2) NULL, 
Date_1 date NULL,
TimeValue time NULL, 
Int_1 int NULL, 
String_2 nvarchar(300) NOT NULL DEFAULT N'*Default', 
Tiny_1 tinyint NULL DEFAULT 0, 
Tiny_2 tinyint NULL DEFAULT 0, 
Tiny_3 tinyint NULL, 
Tiny_4 tinyint NULL, 
Tiny_5 tinyint NULL, 
Tiny_6 tinyint NOT NULL DEFAULT 1,
INDEX CCI_MyFact CLUSTERED COLUMNSTORE )