responses screen Mirror is pending for some clients

[leandrosardi]

This is happening again:

Adaptive Server connection timed out
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/adapters/tinytds.rb:67:in `cancel'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/adapters/tinytds.rb:67:in `block in execute'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/database/connecting.rb:249:in `block in synchronize'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/connection_pool/threaded.rb:103:in `hold'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/database/connecting.rb:249:in `synchronize'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/adapters/tinytds.rb:32:in `execute'
Y:/lib/framework.rb:1195:in `update_drl_minutely_stats_records_to_first_mirror'
./p/_jobs.prospecting.9.rb:143:in `block in update_drl_minutely_stats_mirror'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/dataset/actions.rb:892:in `each'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/dataset/actions.rb:892:in `_all'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/sequel-4.28.0/lib/sequel/dataset/actions.rb:45:in `all'
./p/_jobs.prospecting.9.rb:97:in `update_drl_minutely_stats_mirror'
./p/_jobs.prospecting.9.rb:237:in `process'
C:/Ruby22/lib/ruby/gems/2.2.0/gems/pampa_workers-1.1.42/lib/mylocalprocess.rb:131:in `run'
./p/_jobs.prospecting.9.rb:256:in `<main>'

Failing Query

declare @from int;
declare @size int;
declare @to int;

set @from=70000;
set @size=500;
set @to=@from+@size;

select *
--select count(*)
from (
    select x.*, newid() as id, db_name() as [db_name], row_number() over (order by x.ininvite_hit_time asc) as rownum --' if from.strip=='euler..drl_minutely_stats
    from euler..drl_minutely_stats x with (nolock /*index(IX_drl_minutely_stats__id_client__ininvite_hit_time)*/)
    where id_client='1D436463-209E-4476-836A-6C734A140AEF'
    and x.ininvite_hit_time is not null
    --and x.ininvite_hit_time > dateadd(dd,-14,getdate())
) as v
where v.rownum>=@from
and v.rownum<=@to

Possible solution

1. Mirror the first X records only

2. Mirror nore thant X days aged records only

3. Reorganize index

ALTER INDEX IX_drl_minutely_stats__id_client__ininvite_hit_time ON euler..drl_minutely_stats
REORGANIZE;
GO

4. Rebuild index

ALTER INDEX IX_drl_minutely_stats__id_client__ininvite_hit_time ON euler..drl_minutely_stats
REBUILD;
GO

[leandrosardi]

Server has 16MB RAM, but you are limiting SQL to 5MB.

You can use GUI or the script below

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'12000'
GO
RECONFIGURE WITH OVERRIDE
GO

The index for that particular query (and current column list ) would be

CREATE NONCLUSTERED INDEX [IX_drl_minutely_stats__id_client__ininvite_hit_time] ON [dbo].[drl_minutely_stats]
(
[id_client] ASC,
[ininvite_hit_time] ASC
)
INCLUDE
(
-- !!!! 33 columns!!!!
id_search, id_pipeline, id_message, id_lnuser, id_result, id_profile, ininvite_start_time, ininvite_end_time, inmessage_start_time, inmessage_end_time, inreply_time, inreply_positive, wlt_time, id_message_2, id_message_1, inmessage_1_start_time, inmessage_1_end_time, gct_1, gct_2, gct, gct_1_id_user, gct_2_id_user, gct_id_user, snapshot_time, searchable, location, industry, company, name, visit_time, click_time, create_time, mirror_time
)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = ON,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

[leandrosardi]

cal you please grant me with the following permissions?

USE master
GO
--- Grant permission to view Sql Server Logs
Grant EXECUTE ON master.sys.xp_readerrorlog TO dev
GO

-- permissions to see Agent Jobs

USE [msdb]
GO
CREATE USER [dev] FOR LOGIN [dev]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [dev]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [dev]
GO

[MyKarpenko]

ALTER INDEX IX_drl_minutely_stats__id_client__ininvite_hit_time ON euler..drl_minutely_stats REORGANIZE; GO Rebuild index ALTER INDEX IX_drl_minutely_stats__id_client__ininvite_hit_time ON euler..drl_minutely_stats REBUILD; GO I have to comment on that explicitly. And need more details about the server. If you have SSD drives, you don't care much about index rebuild. You care about STATISTICS rebuild. If you rebuild an index, as a side effect, you update statistics and cache your data to RAM.

I am working on an initial action list I recommend to perform.

[MyKarpenko]

After the memory was increased and the index added the server behaves better. You might still see some queries be slow but overall it is better. I don't see excessive disk reads and see a lot of NULL (which is expected) Before

after

There is still room for improvements of overall server performance and some particular queries after that

[MyKarpenko]

@leandrosardi can you please confirm that the index was changed?