Charmatzis

 -- Unused Indexes
 select
	indexrelname as index_name,
	idx_scan,
	idx_tup_read,
	idx_tup_fetch
from
	pg_stat_all_indexes
where
	idx_scan = 0

Charmatzis

SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint c
   /* enumerated key column numbers per foreign key */

Charmatzis

Reading tiles using RIO

<GDAL_WMS>
    <Service name="TMS">
	<ServerUrl>https://api.mapbox.com/v4/mapbox.satellite/${z}/${x}/${y}.jpg?access_token=******</ServerUrl> <!-- Map -->
    </Service>
    <DataWindow>
        <UpperLeftX>-20037508.34</UpperLeftX>
 20037508.34

Charmatzis

{
	"baseLayer": {
		"tilejson": "2.2.0",
		"name": "base",
		"version": "1.0.0",
		"scheme": "xyz",
		"tiles": [
			"https://a.tile.openstreetmap.org/{z}/{x}/{y}.png"
		]
	},

Charmatzis

select  index_id
  ,     index_type_desc
  ,     index_depth
  ,     index_level
  ,     page_count
  ,     record_count
from sys.dm_db_index_physical_stats (DB_ID(),
OBJECT_ID('Table_Name'), DEFAULT, DEFAULT, 'DETAILED')
go

Charmatzis

WITH dataCTE
AS
(	
	SELECT allocated_page_page_id, next_page_page_id, previous_page_page_id
	FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.TEMP_POS_TERR'),1, NULL, 'DETAILED')
	WHERE is_allocated = 1
	AND page_type_desc = 'DATA_PAGE'
)
,pageCTE
AS

Charmatzis

USE MyDatabase
GO

EXEC sp_spaceused N'User.ContactInfo'
GO

Charmatzis

--http://blogs.lobsterpot.com.au/2018/02/13/sql-server-cpu-usage-by-session/
DECLARE @sessionsCPU TABLE 
	(
	 session_id SMALLINT NOT NULL,
	 cpu_time INT NULL ,
	 initialCPUFlag BIT NOT NULL
	)
  
INSERT INTO @sessionsCPU
SELECT 

Charmatzis

SELECT
  CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 

Charmatzis

Introduction

This is a help page for settinh up Zeppelin with geotrellis and geotrellis-spark-sql and continue with analysis.

Steps

Prestep

Set properties for EMR cluster