Query timeout on ./p/buffer.listbuilder.rb

[leandrosardi]

Local Process Error: Adaptive Server connection timed out
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/adapters/tinytds.rb:67:in `cancel'
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/adapters/tinytds.rb:67:in `block in execute'
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/database/connecting.rb:249:in `block in synchronize'
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/connection_pool/threaded.rb:103:in `hold'
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/database/connecting.rb:249:in `synchronize'
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/adapters/tinytds.rb:32:in `execute' ./p/buffer.listbuilder.rb:150:in `block in process'
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/dataset/actions.rb:892:in `each'
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/dataset/actions.rb:892:in `_all'
/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/dataset/actions.rb:45:in `all' ./p/buffer.listbuilder.rb:48:in `process'
/Ruby22/lib/ruby/gems/2.2.0/gems/pampa_workers-1.1.42/lib/mylocalprocess.rb:131:in `run' ./p/buffer.listbuilder.rb:296:in `<main>'

[leandrosardi]

https://github.com/leandrosardi/tempora/commit/c7fe50d6b1c53f64a68790b458b0d7b643429e8e

https://github.com/leandrosardi/tempora/commit/8e28daf15c5b76f9e985833283cbc7a7e3093640

[leandrosardi]

Query timeout is happening when inserting on table [profile]

Drop non-used indexes

if not exists ( SELECT 1 FROM sys.indexes WHERE name='IX_liprofile__append6_pattern00_reservation_id__id' AND object_id = OBJECT_ID('profile') )
drop index IX_liprofile__append6_pattern00_reservation_id__id on [profile];
go

if not exists ( SELECT 1 FROM sys.indexes WHERE name='IX_liprofile__append6_pattern01_reservation_id__id' AND object_id = OBJECT_ID('profile') )
drop index IX_liprofile__append6_pattern01_reservation_id__id on [profile];
go

if not exists ( SELECT 1 FROM sys.indexes WHERE name='IX_liprofile__append6_pattern02_reservation_id__id' AND object_id = OBJECT_ID('profile') )
drop index IX_liprofile__append6_pattern02_reservation_id__id on [profile];
go

if not exists ( SELECT 1 FROM sys.indexes WHERE name='IX_liprofile__append6_pattern03_reservation_id__id' AND object_id = OBJECT_ID('profile') )
drop index IX_liprofile__append6_pattern03_reservation_id__id on [profile];
go

if not exists ( SELECT 1 FROM sys.indexes WHERE name='IX_liprofile__append6_pattern04_reservation_id__id' AND object_id = OBJECT_ID('profile') )
drop index IX_liprofile__append6_pattern04_reservation_id__id on [profile];
go

Find Indexes Fragmentation

pending

Rebuild all other indexes

ALTER INDEX ALL ON [profile]
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO

[leandrosardi]

Index usage information

Find and drop indexes who are not being used.

Reference: https://www.sqlshack.com/gathering-sql-server-indexes-statistics-and-usage-information/

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
       ,IX.name AS Index_Name
       ,IX.type_desc Index_Type
       ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
       ,IXUS.user_seeks AS NumOfSeeks
       ,IXUS.user_scans AS NumOfScans
       ,IXUS.user_lookups AS NumOfLookups
       ,IXUS.user_updates AS NumOfUpdates
       ,IXUS.last_user_seek AS LastSeek
       ,IXUS.last_user_scan AS LastScan
       ,IXUS.last_user_lookup AS LastLookup
       ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update