November 3, 2015 at 7:09 am
Hi Guys,
I am at a new client which I don't know very well yet...
What they have in place:
The PDAs have a SQL Server compact edition database. The users insert data on them on a daily basis.
The PDAs are then sincronized (using merge replication) .
The sincronization is between the database of the PDAs and a central database which is on a Central Server.
The problem is that they are having a lot of timeouts.
Can you guys advise on what to search to try to see why this timeouts are happening?
Also, as front end they are using SQL Server Compact Server agent Statistics.
Thank you
November 3, 2015 at 9:12 am
Timeout doing what? Synchronization?
Can you expand a bit or, even better, include an actual error message/portion of log file?
-- Gianluca Sartori
November 3, 2015 at 10:18 am
Thing is:
Since 23 of october the sincronization between the PDAs and the central database as became slower than usual.
Sincronization was taking in average 7 secunds and now is taking around 24...
Aparentely we did not change anything...
I was undering if it could be database fragmentation....
But I don't think so..
November 3, 2015 at 10:22 am
Ok, so it's synchronization timeouts.
The agent logs in the subscriber contain useful information. I would start from there.
It could be that some articles take longer to synchronize for some reason, but you won't find it if you don't start from the logs.
-- Gianluca Sartori
November 3, 2015 at 10:31 am
Where can I find the agent log of the subscribers?
November 3, 2015 at 10:50 am
are you refering to:
TransferIN Threads Available1
TransferIN Threads Busy0
TransferOUT Threads Available1
TransferOUT Threads Busy0
Sync Threads Available2
Sync Threads Busy2
Max Threads Per Pool20
Sessions Since Restart399
Total Active Sessions6
Total TransferIN Requests Queued0
Total TransferOUT Requests Queued0
Total Sync Requests Queued0
which I found on the SQL Server Compact Server Agent Statistics
I also have:
Total Replication Syncs5
Total RDA SubmitSQLs3
Total RDA Pushes0
Total RDA Pulls0
Average .IN File Size840
Average .OUT File Size3956827
Total Completed Operations8
Total Incomplete Operations0
Total Sync Thread Time (seconds)594
Total Transfer Thread Time IN (seconds)0
Total Transfer Thread Time OUT (seconds)0
Total Sync Queue Time (seconds)0
Total Transfer Queue Time IN (seconds)0
Total Transfer Queue Time OUT (seconds)0
November 3, 2015 at 10:50 am
On the subscribers, path C:\Program Files\Microsoft SQL Server\100\COM\replmerge.log
-- Gianluca Sartori
November 3, 2015 at 10:51 am
spaghettidba (11/3/2015)
On the subscribers, path C:\Program Files\Microsoft SQL Server\100\COM\replmerge.log
The subscribers are the PDAs... I don't have access to them...
November 3, 2015 at 10:56 am
I don't know how to help you then.
Do you have one of those subscribers available for troubleshooting?
Can you set up a subscriber (your desktop computer would do as well)?
-- Gianluca Sartori
November 4, 2015 at 12:59 am
They (stakeholders) told me to focus on the server performance and issues instead of the PDA side.
Maybe theys believe that the problem is related with that.
I'm thinking in measuring the statisticts for the database that is being replicatited.
At the same time I will try to see if we have the database fragmentated and also how are the indexes.
I will put the results here so you can help me understand.
Any advices on what should I measure?
Thank you
November 4, 2015 at 1:34 am
I am using this query to see the long running querys:
SELECT TOP 100
ProcedureName = t.text,
ExecutionCount = s.execution_count,
AvgExecutionTime = isnull( s.total_elapsed_time / s.execution_count, 0 ),
AvgWorkerTime = s.total_worker_time / s.execution_count,
AvgExecutionTime_sec = isnull( s.total_elapsed_time / s.execution_count, 0 ) / 1000000.0,
AvgWorkerTime_sec = s.total_worker_time / s.execution_count / 1000000.0,
TotalWorkerTime = s.total_worker_time,
MaxLogicalReads = s.max_logical_reads,
MaxLogicalWrites = s.max_logical_writes,
CreationDateTime = s.creation_time--,
--CallsPerSecond = isnull( s.execution_count / datediff( second, s.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
WHERE t.dbid = db_id()
ORDER BY 4 DESC
November 4, 2015 at 2:06 am
Results show:
ExecutionCountAvgExecutionTimeAvgWorkerTimeAvgExecutionTime_secAvgWorkerTime_secTotalWorkerTimeMaxLogicalReadsMaxLogicalWritesCreationDateTime
5379459703502300237.94597000035.0230020001751150141320964238822015-11-03 14:55:17.570
2122136981167766712.21369800011.677667000233553355033912015-11-03 08:15:29.763
211656166916502311.6561660009.16502300018330047209652712015-11-03 08:13:58.740
212032188842948112.0321880008.429481000168589621721902015-11-03 08:17:04.480
3787278368877277.8727830006.88772700020663181799406802015-11-03 20:39:13.200
67708143465118647.0814340006.5118640004362949522042755302015-11-03 12:03:20.533
1602434460233446.0243440006.0233440006023344277612011452015-11-03 19:57:28.317
1580533257633285.8053320005.7633280005763328299370835532015-11-01 17:21:56.840
3556731855439835.5673180005.54398300016631951277070702015-11-03 19:41:05.813
2559081951687955.5908190005.168795000103375906675502015-11-03 08:15:26.550
155668447950281906.6844790005.028190000779369579205888885562015-11-03 12:39:40.740
3501328649696175.0132860004.96961700014908852276392642015-11-03 19:41:05.843
9729152847537167.2915280004.75371600042783444287597055412015-11-03 13:03:35.940
31528785046508785.2878500004.650878000144177246188306832982015-11-03 10:51:16.177
5393862532023833.9386250003.20238300016011916182342736052015-11-03 12:19:34.567
46358659631282653.5865960003.128265000143900231277596181692015-11-03 12:15:10.193
5294356827379562.9435680002.737956000136897839700402015-11-02 16:28:30.753
25295580924891022.9558090002.48910200062227559162926202015-11-03 10:43:55.950
214328353821674653.2835380002.16746500046383752433063240702015-11-03 13:03:36.320
1211312021131202.1131200002.113120000211312044657312015-11-04 01:28:40.567
2339269419791133.3926940001.9791130003958226423302015-11-03 08:16:12.137
2245414019496112.4541400001.9496110003899222221712592015-11-03 14:10:50.727
2209862017230982.0986200001.7230980003446197179146492015-11-03 14:10:50.760
608187274616502531.8727460001.650253000100335437840397038412015-11-03 13:03:16.353
455185298216087461.8529820001.60874600073197986899260146392015-11-03 09:49:13.683
2210812015925912.1081200001.59259100031851822812702015-11-03 08:16:49.437
10156868914301811.5686890001.430181000143018181468727852015-11-03 13:03:36.010
59161914313657901.6191430001.3657900008058161035193423172015-11-03 19:07:27.510
4136557813380761.3655780001.33807600053523054384102015-11-02 15:26:07.023
67137510812174721.3751080001.217472000815706666066181092015-11-03 12:37:37.403
4131007410833111.3100740001.083311000433324724696502015-11-04 07:00:42.067
2110606310305581.1060630001.030558000206111751476502015-11-02 15:18:24.063
2450117406110298181.1740610001.029818000252305631422897225472015-10-31 12:38:37.570
511008628778501.1008620000.877850000438925128372827602015-11-02 15:15:03.023
211220648580481.1220640000.85804800017160971046612015-11-03 08:17:43.627
1178531347827280.8531340000.782728000915792363660867712015-11-03 12:17:43.417
411570667825441.1570660000.782544000313017828411642892015-11-02 13:33:11.700
657669207427960.7669200000.742796000482817612974112015-11-02 00:29:36.953
28107957267331470.7957260000.73314700020601438202940622015-11-03 09:28:19.880
327295107243530.7295100000.724353000231793252926502015-11-03 13:06:59.203
3037781337177930.7781330000.7177930002174914392943602015-10-31 02:31:55.790
57188417172410.7188410000.71724100035862052065473192015-11-02 15:26:36.367
66547597197134940.7597190000.71349400047475955312978322015-10-31 02:31:55.787
167456676989140.7456670000.6989140001118263910542702015-11-03 10:44:14.650
33798191336932430.8191330000.693243000234246997514319223182015-11-03 08:43:55.377
27895456675380.7895450000.6675380001335076402702015-11-03 08:16:58.440
1146056485622950.6056480000.5622950006410166733967542792015-11-03 12:39:40.850
27895455270290.7895450000.5270290001054059340712015-11-03 08:15:30.140
15430315090290.5430310000.509029000509029822782015-11-02 15:21:54.833
58326474902280.8326470000.49022800024511401421912015-11-03 14:10:45.347
34590264140230.4590260000.4140230001242071415502015-11-03 20:37:10.260
844696344016530.4696340000.40165300033738930522402015-11-03 13:47:44.080
15540313950220.5540310000.395022000395022479902015-11-03 23:31:41.013
3206247733784560.6247730000.378456000121105929342989242015-11-03 12:03:22.563
13550203550200.3550200000.355020000355020813802015-11-02 15:21:54.823
1643699963376470.3699960000.3376470005537416827530482015-11-03 12:04:04.940
3154945583126940.4945580000.312694000984986356799694502015-11-02 12:18:26.260
32990172943500.2990170000.2943500008830502495702015-11-03 15:01:12.833
8213135402846090.3135400000.28460900023366436713945023242015-11-03 12:02:13.513
88783107022762690.3107020000.276269000245271829115598902015-11-03 09:49:46.690
42487642487640.2487640000.2487640009950575454110662015-11-03 07:59:45.657
2082528992292340.2528990000.229234000476807266756652962015-11-03 09:49:09.860
452329912266350.2329910000.2266350001019858349993612015-11-03 12:56:04.587
22265122265120.2265120000.2265120004530256542502015-11-03 13:03:55.477
1342269832207060.2269830000.2207060002957469049551482015-11-03 09:49:06.073
23410192140120.3410190000.21401200042802440898122015-11-03 14:10:50.757
222410592125570.2410590000.2125570004676267247312015-11-03 12:59:07.303
7222327912050690.2327910000.205069000148060468144092492015-11-03 12:04:18.587
11860101860100.1860100000.18601000018601019667302015-11-03 23:31:41.977
51816101814100.1816100000.18141000090705251425402015-11-02 15:26:51.443
22000111685090.2000110000.1685090003370193170202015-11-02 15:18:26.670
4871737861593210.1737860000.159321000775894342452802015-11-03 12:15:22.327
21720091570090.1720090000.1570090003140181081222015-11-02 15:18:26.640
41545081545080.1545080000.1545080006180353355502015-11-04 07:00:46.213
21705091505080.1705090000.1505080003010173743302015-11-03 08:13:56.753
11480081480080.1480080000.1480080001480083802015-11-02 15:21:56.113
701544801422790.1544800000.14227900099595702120102015-11-03 09:49:53.810
2031482691361550.1482690000.136155000276395812606962015-11-03 12:01:02.123
1661391221313320.1391220000.131332000218012482596112015-11-03 12:10:11.433
44461408811278080.1408810000.1278080005682364962120902015-11-03 09:28:26.263
311359751271040.1359750000.127104000394022531313742015-11-03 10:51:14.700
22530141260070.2530140000.12600700025201431766112015-11-03 14:10:50.777
11120061120060.1120060000.112006000112006363332792015-11-01 01:00:04.230
19841096561093170.1096560000.1093170002168864012346302015-11-01 00:14:39.623
19771078911078750.1078910000.1078750002132691962440702015-11-04 00:14:14.717
40181073471069260.1073470000.1069260004296325712346002015-11-01 00:01:25.700
261119291060820.1119290000.106082000275815753029842015-11-03 10:51:09.893
7031260151053210.1260150000.1053210007404123511975122015-11-03 12:10:52.923
40091052961044030.1052960000.1044030004185529422440702015-11-04 00:01:18.747
33266851036720.3266850000.1036720003110183616852015-11-03 16:13:30.807
21035051035050.1035050000.10350500020701172072015-11-03 12:51:01.553
111062781021870.1062780000.102187000112406452941382015-11-03 15:53:19.373
21020051020050.1020050000.1020050002040112522302015-11-02 16:29:17.487
19771030861018110.1030860000.1018110002012815142347402015-11-02 00:13:45.553
40121002051001930.1002050000.1001930004019779892347302015-11-02 00:01:22.297
769100788995270.1007880000.09952700076536378996702015-11-03 23:54:14.687
12998920986950.0989200000.09869500012731728996202015-11-04 00:22:51.403
198005980050.0980050000.098005000980052043302015-11-04 02:33:22.260
401193048930310.0930480000.0930310003731513432316802015-11-03 00:01:18.730
197893684927410.0936840000.0927410001834424922317102015-11-03 00:12:45.050
November 4, 2015 at 2:36 am
I also have used this query:
SELECT top 50 creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
November 4, 2015 at 2:41 am
which produced this results:
creation_timelast_execution_timetotal_physical_readstotal_logical_readstotal_logical_writesexecution_counttotal_worker_timetotal_elapsed_timeavg_elapsed_time
2015-11-03 14:23:54.7232015-11-03 14:23:54.967075765014640266878393468783934
2015-11-03 14:14:42.6702015-11-03 14:14:42.9576408660119701116489371164893711
2015-11-03 14:14:36.0372015-11-03 14:14:36.263347987013760215814432558144325
2015-11-03 14:55:17.5702015-11-03 19:26:45.70716319418756729515517511501418972985137945970
2015-11-04 07:28:40.0772015-11-04 07:35:29.41701221560219171095429110527145552
2015-11-04 07:28:36.0502015-11-04 07:35:26.13321121740360277404434938382424691912
2015-11-04 08:19:46.7932015-11-04 08:19:47.28013355606498301190050862420738424207384
2015-10-12 06:25:47.6502015-11-04 07:29:34.6830160502517501045972829418389131
2015-11-03 04:45:02.5232015-11-04 04:45:03.77019232173173782164189403329590416647952
2015-11-03 08:15:29.7632015-11-04 02:32:40.3674807610063112233553352442739712213698
2015-11-03 08:17:04.4802015-11-04 02:34:03.057476243442402168589622406437612032188
2015-11-03 08:13:58.7402015-11-04 02:31:06.2733921419201612183300472331233311656166
2015-10-28 08:18:07.9272015-10-28 08:18:07.933101101670511189891113376481148565611485656
2015-11-04 05:00:32.9032015-11-04 05:00:32.9031245971251600116390921027658710276587
2015-10-31 10:37:38.3402015-10-31 10:37:38.343070030181804685884918588491
2015-10-28 08:18:07.9302015-10-28 08:18:19.420223402236501291216584864858486485
2015-11-03 10:01:39.0032015-11-03 10:01:39.02003446673091140002382974748297474
2015-11-03 20:39:13.2002015-11-03 20:40:27.6408239818030320663181236183507872783
2015-11-01 01:00:09.8572015-11-01 01:00:12.83021913552441398851737842274084237408423
2015-11-03 13:03:35.9402015-11-03 19:26:43.807696591008039119571942783444656237537291528
2015-11-03 12:03:20.5332015-11-03 22:02:56.9801451228239920674362949524744561377081434
2015-11-03 12:39:40.7402015-11-03 22:26:11.867112110824061975455469015577936957910360942606684479
2015-11-03 19:57:28.3172015-11-03 19:57:28.3370277612011451602334460243446024344
2015-11-03 17:20:04.1302015-11-03 17:20:04.140089111113206459173385917338
2015-11-01 17:21:56.8402015-11-01 17:21:57.7800299370835531576332858053325805332
2015-11-03 08:15:26.5502015-11-04 02:32:55.030211121335060210337590111816395590819
2015-11-03 19:41:05.8132015-11-03 19:57:17.707083104700316631951167019555567318
2015-11-03 10:03:54.2832015-11-03 10:03:54.30003436083312138802254233105423310
2015-11-03 10:51:16.1772015-11-03 23:31:43.67701709351123424311441772461639233755287850
2015-11-04 09:33:12.2002015-11-04 09:33:12.203012472901207511950372885037288
2015-11-03 19:41:05.8432015-11-03 19:57:23.210082899706314908852150398605013286
2015-11-02 07:51:32.4032015-11-02 07:51:48.090052394002914152291415224570761
2015-11-02 07:51:37.1802015-11-02 07:51:37.310087540177604544832564483256
2015-11-04 07:16:30.8372015-11-04 07:35:21.6431317878400313169753131747534391584
2015-10-30 15:00:38.3202015-10-30 15:00:38.453015481401352120143022464302246
2015-11-03 05:00:19.4432015-11-04 09:20:00.8530357219853632907783637418054279315
2015-10-30 15:14:17.7932015-10-30 15:14:18.000015614401349320040652324065232
2015-11-03 16:23:29.8132015-11-03 17:20:43.710013602199711379074523953726
2015-11-03 12:19:34.5672015-11-03 20:10:03.8400700952914236516011916196931263938625
2015-10-30 14:54:11.2072015-10-30 14:54:11.263015485401347819937942173794217
2015-10-30 15:02:29.6072015-10-30 15:02:29.670015485201352620136102063610206
2015-10-30 15:15:12.8732015-10-30 15:15:12.937015614801343919536092063609206
2015-10-30 15:08:52.7672015-10-30 15:08:52.827015603201347619836062063606206
2015-11-03 12:15:10.1932015-11-03 20:30:52.170063767746134660461439002311649834363586596
November 4, 2015 at 2:56 am
What are you trying to display?
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply