Search This Blog

2018-02-19

Kwikset Electronic Deadbolt Lock Review

Manufacturer: Kwikset
Product:  All electronic deadbolt locks, all smart locks

Battery operated devices has been around easily for more than 50 years.  In past 10 years, residential door locks have been slowly adapting the electronic locks due to the popularity of hotel key fob, and office key fob system.  More importantly, the introduction of bluetooth and WiFi in mobile apps that allows more transparent usage of door lock.  This also allow phone, and smartwatch be used as key to unlock the door.

Most of the residential locks are using AA batteries, unlike commercial models which uses power line with power adapter.  This blog post mainly to point out that the company that designed it, especially Kwikset, are not following the industry standard that battery operated devices should consider the depleted battery as 0.9V where the new battery is 1.5-1.6V.

I recently acquired 2 Kwikset 264 electronic deadbolt lock and found that the lock is complaining low battery when the battery is 1.3V.  This means it still has 0.4V to be considered depleted, yet it starts to stop to operate, and consistently buzz the alarm.  The lock is not able to lock, unlock, or auto lock.  Further investigation, this seems to be affecting the entire line of electronic locks:
  • Model 264, 275
  • Kevo series smart lock, e.g. Kevo Convert, Kevo Contemporary, Kevo Traditional
  • Premis Apple HomeKit Smart Lock
  • SmartCode 915, 916, 914, 913, 912, 911, 910, 909
  • Powerbolt 1, 2
This also happen to Honeywell battery operated 2-stage thermostat that is 5 yr old.  It also consider a 1.3V as depleted battery and shown low battery icon.

There are few factors why battery operated devices need to work in range of 0.9V - 1.6V:

  1. Industry standard - it is used to measure the battery capacity, and toys industry has long adopted that
  2. Fully used the energy stored in batteries
  3. Reduce waste due to not depleted batteries
  4. Extend longer operation of the devices
  5. Rechargeable battery can be used to further conserve the battery waste
I used Kirkland alkaline battery, and it last ~ 4 months.  I know that these battery hardly has 2000mAh, but if they can allow it to operate until 0.9V, it should able to last 6 months.  Curerntly, it only used up 42% of the battery's capacity, and 68% wasted.  As consumer, we would like the lock to operates longer, and won't get locked out by the electronic lock.  The landlord might gives the PIN code to tenant, while keeping the key for personal used.  Property manager also doesn't want to visit every house to swap the battery every 4 -6 months.

This is very important to operate above 0.9V, as all rechargeable AA batteries have 1.2V when fully charged.  The mAh capacity calculation that is advertised is also considering the depleted battery level as 0.9V.  This will made all rechargeable battery unable to be used in these battery operated devices, e.g. lock, thermostat.

I am a big fan of NiMH AA for last 20 yr, and their 2500mAh capacity and long life have been proven in my Panasonic phones to be very cost effective.

Hope that these manufacturers are able to see my blog post and revise their design.

2018-01-13

Windows: How to Disable Auto Startup

Windows: How to Disable Auto Startup of Any Programs

Overview

Windows Version: 95, 98, XP, Vista, 7, 2008, 2008R2, 2012, 2016

Many virus and applications will auto startup during OS boot up.  This includes:
  1. 3rd Party Firewall - an upgrade might block internet access, and you would like to temporary disable it
  2. virus
  3. Freemake upgrade notification
  4. Many Chinese ad-base software, e.g. Baofeng Storm Player, LETV, funshion, baidu, 迅雷影音 XMP
  5. Antivirus - you doesn't want it running any more to slow down your PC
Following are the areas you can check
  1. Start Menu > All Programs > Startup
  2. Windows Registry (regedit)
  3. Windows Services (services.msc)

Startup Menu

In Windows Start menu, where it will pop up by pressing Windows key on the keyboard, there is a folder called "Startup."  Any application shortcut will startup the program.

To remove it, right click on the shortcut icon, and click "Delete" to remove it.  This will permanently disable it.  You cannot undo it, unless it move it to other folder.

MS Config

Press Windows-R key on the keyboard, and enter "msconfig" to run this utility.

It provides quick and easy access to following which you can disable auto startup of supported programs:
  1. Windows Services
  2. Windows Registry for auto run programs
The disabled entries will remain on the screen, so you can undo them in future.

Windows Registry

This is the most common location where the auto startup is often configured, and it doesn't allow user to easily remove them

  1. HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\
    1. Run\
    2. RunOnce\
  2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\
    1. Run\
    2. RunOnce\
  3. HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\
    1. Run\
    2. RunOnce\
Wow6432Node folder is used by 32-bit applications on 64-bit Windows.  Many 32-bit applications will create the auto run entry under this folder

2017-12-25

BMW: Enabling/Disabling Auto Door Lock

BMW E46, E60, E90, E91, E92, E36, E39

You can enable auto door lock for BMW cars in 1980's - 2000's using the remote control key, and doesn't need to pay dealer to enable/disable the feature.

These cars can auto lock all the car doors after the car's speed is above 10 km/h.  This feature is to prevent when you are waiting in traffic light, and motorbike/cyclist will open your door and grab laptop, tablet, jewel, etc from you.

Please note that these car model year doesn't has auto unlock, but added at the later year series.  Again, this is a traditional safety that pass from older car generations.  The reason is to ensure that once the car's engine is turn off, and even the key is pulled, motorbike/cyclist won't be able to open your door and grab laptop, tablet, jewel, etc from you.

There are many wrong information in the internet that this feature can only be enabled using OBDII programmer (those that could control BMW ECU), and often to be the dealer.  By accident, I found that this feature can be enabled and disabled using the remote control key fob.

Auto Door LockSteps
Enable
  1. Ensure door is lock.  If not, press the LOCK button on the key fob
  2. Insert the car key into the door lock.  Unlock the door by turning clock wise
  3. Now the feature is enabled.  You can drive the car to above 10 km/h to confirm the door is auto lock
  4. You can only manually unlock & open the door using following methods
    1. Pull the inside door handle twice to open the door
    2. Press the door unlock button near to the handbrake
Disable
  1. Ensure door is unlock.  If not, press the UNLOCK button on the key fob
  2. Insert the car key into the door lock.  Lock the door by turning counter-clock wise
  3. Now the feature is disabled.  Get into the car by using key fob (UNLOCK button), or car key (door lock).  Drive the car to above 10 km/h to confirm the door is not auto lock

For E39, if you lock the trunk by turning the key to the lock position, which is horizontal, then you will also enable auto door lock

Annoying Behavior with Auto Lock

  1. Everyone needs to pull the door handle twice in order to open the door
  2. The rear trunk will be locked
  3. If the passenger side has nobody sitting, or rear seats, then the door(s) will remain lock.  So if someone going to get into it, you have to use the remote key fob to unlock the doors

2017-10-20

MS SQL Server: Verify Login Timeout

Product: MS SQL Server
Version: 2000 - 2016

Microsoft MS SQL Server pre-install with login timeout of 10sec.  Many developers or support personnel might have a chance to encounter login timeout while DBA might tell you that there is no login timeout, as they could be confused with other timeout values

There are 3 different timeout setting in MS SQL Server, so there is no surprise that a junior DBA mistaken one for other.

In the Internet, there is not much info about how to verify these 3 timeout setting, including login timeout.  This blog post is specifically to cover it.

How to Verify Using SELECT 

Uses following SELECT statement from any MS SQL Server client, such as Toad, DbVisualizer, SSMS, as well as programming.  This approach is not widely documented especially on verifying login timeout

select configuration_id, name, value_in_use from sys.configurations where configuration_id in (1519, 1541, 1520)

Sample output:
configuration_idnamevalue_in_use
1519remote login timeout (s)10
1520remote query timeout (s)600
1541query wait (s)-1

Note: Above is default values for new MS SQL Server installation

You can see clearly that "remote login timeout" is 10 sec for default MS SQL Server installation.  DBA often confused that with "query wait" which is never, or no timeout.

Pros:

  1. It is a regular SELECT statement that be easily written in any programming language
  2. It is easily executes in web application
  3. SELECT statement is 100% supported by any DB client, and utilities, e.g. Toad, Eclipse, Excel, DbVisualizer
  4. Does not require to engage DBA to verify it
  5. Execute once to see all 3 timeout values without hoping into various screens

Cons:

  1. People who don't understand SELECT statement syntax might find it a lots of keys to enter
  2. Not GUI base
  3. Requires to run in SSMS or DB client
  4. Junior DBA less familiar with this output, and might has more push back

How to Verify Using sp_configure

sp_configure statement can be used to both verify as well as set the database instance value.

Following statement can be used to verify 3 different timeout parameters.  You need to execute it 3 times as sp_configure command only allow to display 1 value

sp_configure 'query wait (s)'
go
sp_configure 'remote query timeout (s)'
go
sp_configure 'remote login timeout (s)'
go

Sample output:

Pros:

  1. Simpler syntax compare to SELECT statement
  2. Less typing
  3. Junior DBA more familiar with this output
  4. Statement is 100% supported by any DB client, and utilities, e.g. Toad, Eclipse, Excel, DbVisualizer
  5. Does not require to engage DBA to verify it
  6. Minor modification to the command will allow DBA to set the new value in real time

Cons:

  1. Cannot obtain all 3 values in 1 statement
  2. More effort in programming to integrate it, as well as get the 3 parameters
  3. Not GUI base
  4. Requires to run in SSMS or DB client

How to Verify Using GUI

This is the most commonly documented in MSDN or Technet.  All junior DBA are familiar with this approach, as there is no learning curve

Launch SSMS, open up the DB instance properties to verify these 3 timeout values


Pros:

  1. GUI driven, and no need to memorize any statement or syntax
  2. No typing requires
  3. Junior DBA very familiar with this screen, and no push back
  4. Quick & easy clicking on 2 screens to see all 3 timeout values
  5. No need to memorize any command, such as SELECT and sp_configure

Cons:

  1. Requires SSMS
  2. Not supported by any DB client
  3. Requires some skill to map the values in GUI to the sp_configure values
  4. Impossible to written in any programming language
  5. Impossible to execute in web application
  6. Might need to engage DBA if firewall blocks SSMS
  7. Needs to check 2 screens to determine all 3 parameters

2017-10-03

How to Maintain Old Cars?

Following is a preventive maintenance for old cars with budget conscious, as most old car owner are in control of their financial spending.  This mainly for 8 - 30 yr old Japanese car, i.e. Honda and Toyota, as they are more reliable and have shorter list than Germany and US cars.

They are break into priority, and severity

# Failure Priority Severity Part Cost Estimate Justification
1 Oxygen Sensor - front High High $50 - $100 It has 7-10 yr life, and will impact mpg
2 Oxygen Sensor - rear High High $50 - $180 It has 10-15 yr life, and will impact mpg.  The reason is has longer life compare to front sensor is due to it is further away from exhaust header, so it is under lower temperature
3 Transmission oil Low Low $20 - $120 For non-CVT car, you need to replace less frequent, which is ~ 5 yr, 60,000 miles, 96,000 km.  For CVT, replaces every 3 yr, especially for generation 1 - 2 of the CVT cars.  Honda tends to have CVT failure, and it is unknown whether yearly transmission fluid replacement will prevent it.  Transmission overhaul/repair cost $1000 even for Civic
4 Spark plugs High High $70 - $120 Cars starting 2000 are starting to use long live platinum plugs, which last until 10 yr, or only replace the one that starts miss firing to save cost.  I only waited max of 15 yr.  Platinum plug cost $15/each, so if you want to save cost, just keep 1 spare, and replace whichever one that start breaking.  Out of 4 or 6 plugs, some could last 20 yr
5 Timing belt High High $50 - $100 7 yr, or 110,000 miles, 176,000 km.  If the car's VIN starts with J (made in Japan), then it could last more than 15 yr
6Coolant LowLow $10 5 yr.  No urgency as engine won't suddenly overheat, and this is a gradual process.  Ensure the coolant is compatible with your manufacturer, which will be written on the rear label.  Coolant compatible with US made or Germany often not compatible with Honda & Toyota.  Wrong coolant type will degrade the life of the components
7 Boot - Front drive shaft CV boot Low Low $10 15 - 20 yr.  Replacing the boot when it shows early sign of crack.  After it cracked, then you have to spend $60+ to replace the shaft & boot.  There are left & right boot, so inspects both.  Shaft failure will cause sound when turning
8Tie rod end bushing Low Low $10 - $30 The rubber will torn after ~ 10 yr.  It affects the car alignment, and indirectly causing shorter tire life.  One new tire easily cost more than 1 tie rod
9 Wheel bearing Low Low $20 - $50 In 10 yr, 5% of the car might experience bearing failure.  It will affect mpg by 5%, as well as noise (louder as speed increase)
10 Car alignment Low Low $70 - $100 10 yr.  It shorten tire life due to uneven wear
11 Tire alignment Low Low $15- $40 If you are replacing tire every 5 yr, then perform tire alignment every 5 yr.  If there is a drive shaft, or toe rod failure, then it could causes uneven tire wear, so you need to perform tire alignment
12 Exhaust - Muffler Low Low $50- $150 Rust will build up under the car, and exhaust pipe as well as muffler are commonly the victim.  After 10 yr of rust build up, the pipe or muffler can have a hole anytime.  Knocks on the entire exhaust from exhaust header all the way to muffler at the end to detect any early sign of rust that will rust through and form a hole.  Knock gently or you will have to immediately replace the pipe/muffler if you crack it (as it could be 90% rust through)
13 Catalytic converter Low Low $100 - $180 (aftermarket), $2000 (original)After 10-20 yr, this sometimes last for the entire life of the car.  Its failure will affect mpg & engine power (incomplete combustion).  It will trigger post O2 sensor check-engine-light.  If you replaced your O2 sensor and it still triggering O2 sensor error code, then you know you need to replace this part.  The inspection can only be perform visually, and cannot use on-board computer sensors.  Remove it and see whether the platinum honeycomb has crack.  Shake it to hear any cracked metal.  90% of the time, this is accurate.   My 17 yr old Honda/Acura's unit still look like new.  Recommends to buy original part (> $2000) as after market often need longer bolts, different gasket, or length is a few mm different, which will delay the repair as you need to buy another bolts.  None original can be found 1/10 of the price, but are either having 50% of platinum honeycomb, or has <50 life="" of="" original.="" p="" the="">
Another point to indicate is for old car, when replacing rear/post O2 sensor that attach to catalytic converter, the rust might might crack through it where it is holding the O2 sensor.  There is a risk of breaking it when replacing O2 sensor.

Junk yard doesn't carry used catalytic converter for some reason
14 Exhaust - flex pipe or spring bolt Low Low $50 - $100After ~ 10 yr it is common that you will need to replace either the flex pipe or spring bolt before the catalytic converter.  Flex pipe typically torn and will leak a small amount of exhaust gas, which you can smell in sitting inside the car.  Spring bolt tends to last longer, but will snap when changing the engine oil pan gasket or catalytic converter when you touch it.  Spring bolt should cost $20 - $30
15 Engine valve cover gasket Low Low $12 Minor oil leak at the top of engine starting ~ 7 yr as the gasket get harden, and can't seal tight any more.  Some car can last until 15 yr old, but my recommendation is to change at 10 yr age.  It is cheap and quick to replace for all car models.  The slow leak will cause the engine slowly covered with thick dust.  When it started to leak more, then you can smell the burning engine oil smell inside the car.  There will be oil dripping on the floor as well.  By looking at the top of the engine, you can detect the leak which starts slowly, so you have ample of time to plan the budget
16 Engine oil pan gasket Low Low $10 ~ 10 yr is my recommendation.  It tends to occur few years after engine valve cover gasket leak.  The labor is 2 - 3 hr, but the part is cheap.  Some exhaust part will break, such as flexpipe/spring bolt upon replacement (they are blocking the oil pan) so it is good to replace them together.  Unlike valve cover gasket, you won't smell burning engine oil, but it will drip on the floor.  You need to ask technician to inform you when you do the oil change.  You have ample of time to plan the budget, as it starts slowly even after 20 yr
17 Upper control arm Low Low $30 - $100 ~ 10 yr the rubber bushing will crack, and entire arm needs to be replaced.  It affects car alignment, and uneven tire wear.  It is a cheap part
18 Lower control arm Low Low $30 - $100 ~ 10 yr the rubber bushing will crack, and entire arm needs to be replaced.  It affects car alignment, and uneven tire wear.  It is a cheap part
19 Engine valve cover PVC valve Low Low $3 ~ $15 yr, the carbon could build up in this small plastic valve on the valve cover (circulate the air back into intake boot).  If you use WD-40 or any spray to clean this valve every yr to remove the carbon, then it will last forever.  Else spends a couple dollar to replace it when it doesn't make a knocking sound on shaking.  This affects mpg as well as emission check
20 Vacuum hoses Low High $10 ~ 10 yr, the vacuum hose will be harden, and could affects various components.  For mine, it started with cruise control, as it can't keep the speed.  The hose is $1.50/ft, so you can buy as less as possible
21 Fast idle valve solenoid Low High Free This is specific to Honda old car.  At ~ 15 yr old, the valve will loosen, and needs to remove it and tighten it by screwing it in by 2-5 turns.  No need to replace any part, and all the gasket can be reuse.  Those gasket seems to last more than 20 yr
22 Radiator hoses Low High $15 ~ 15 yr, the radiator hose might become too soft with age, and could burst at random.  The hose is cheap.  Shorter one ~ $10, and longer one ~ $20.  One of them is lower and another is upper
23 Thermostat Low Low $10 ~ 15 yr old.  Buy only original the original thermostat, and don't buy higher temperature model (after market), as higher temperature will degrade other parts, such as PCV valve, radiator hose, although it could improve mpg and warmer heater air
24 Fuel filter Low Low $5 ~ 10 yr.  This is just preventive maintenance in case of poor fuel quality has cause some dirt accumulated.  I have never see car break down due to broken fuel filter.  There is no rubber gasket involve, and all metal.  No electronic pumps involve as well, so no electronic failure can cause car breakdown
24 Battery Low Low $0 - $300 Invests in a cheap 12V battery charger that can charge 1A or 2A.  Every 6 months, charge the battery with 1A/2A, and it will last for more than 10 yr.  If your battery is weak, or fully drain, charge it with 1A (or 2A), and don't use 3A or higher.  You might able to get more life from it, instead of thinking it is dead.
Avoid smart charger, as they confuse a weak or fully drained old battery as bad battery.  If you only have smart charger, then you need to buy another small 12V battery to charge in parallel to fool it

Other Unexpected Repair

Honda & Toyota are the most reliable car in the world, but there are still some random failure.  These could happen to any of the 10% car owner

  1. Fuel pump $70 - issue should be intermittent before it totally break
  2. Idle air control valve - only causes intermittent high idle, and poor mpg.  Intermittent before it get more consistent
  3. MAP sensor - issue should be intermittent before it totally break.  Car won't start on failure
  4. Motor mount $10
  5. Air cond leak $5 - due to aged o-ring
  6. Transmission clutch plate - gear stick or slip, and should be intermittent at the beginning
  7. Front/rear hood lift support - oil leak due to age ~ $20
  8. Starter motor $200- intermittent can't start car (no crack, no start).  Knock on the starter motor, and it should start, or prepare a small power tool battery (12V or 18V) to jump start it
  9. Air cond compressor $250
  10. Alternator $200 - can't charge battery (after turn off, can't turn on any more as battery is low)
  11. Fuel injector relay cracked solder $10 - occurs to most Honda prior of 2000.  Needs to resolder.  Intermittent issue
  12. Ignition distributor $10 - aged o-ring ($1), bearing ($10)
  13. Shock absorber $50
  14. Stabilizer bar bushing $4
  15. Ignition coil $20 - intermittent issue, random misfiring.  You can replace the one which is failing to save cost
  16. Tire air leak $100 - commonly due to bend rim, or rusted steel rim, so that the rubber can't seal the air tight, and slow leak.  Replaces with a used rim for $20 to save money

Preventive Maintenance Consideration for TCO (Total Cost of Ownership)

First thing to consider when maintaining an old car is first asks yourself how long you are going to keep the car, and your reason of keeping it
  1. If you are going to sell soon, then fix those oil leaks to increase its resale value.  Spends $100 - $150 for 3-stage polish to remove the swirl marks, as well as clean engine bay
  2. If you only care about only car breakdown, then repair the Severity High issues.  You can't plan ahead for those "unexpected" repair list
  3. If you going to keep for 5 year, then repair the Severity High, and asks technician to advise which of the Severity Low items that show early sign of failure
  4. If you are going to keep more than 5 year, then perform preventive maintenance of $300 (parts only).  Reserve another $300 for the rest.  The entire list is $600 (parts)

Tips to Save Money

  1. Learn some DIY online, e.g. car forum, and YouTube.  Some of those items are easy for DIY with regular socket wrench, and no special tools required
  2. If you want to get more complete tools, spend $200 - $400, as sending to mechanics can easily cost $300 per visit.  Assumes 1 hr cost $75, then 4 hr cost $300 (which is about 2 repair)
  3. If you owned a 7 yr or older BMW, Merc Benz, Audi, then $300 is just the labor cost for 1 visit, as the mechanics charge $100/hr
  4. Starts by DIY things that is easy to replace
  5. Spends $20 to buy a used car ramp, instead of car jack

2017-10-01

How to Choose Thermostat?

How to Choose a Residential Thermostat?

This post is about choosing and installing a residential universal thermostat for most homes in Canada and USA with central force air HVAC.  All the houses that build in 1980 and furnace and A/C sold since 1990 are using natural gas furnace and split system (force air) A/C with a big outdoor unit.  Heat pump, hot water heating, electric heat board are not cover in this post, as home builds in last 20 yr are not common to use those, especially I am talking about residential homeowner.

There have been so many "how to choose thermostat" articles in the Internet, and why do I need to write one again?  Following are the reasons:
  1. Most of them are not telling that modern HVAC is applicable to units that is 20 yr old.  Reader will think it only applicable to HVAC sell in last 5 yr
  2. None of them tell you how to identify the thermostat support multi-stage HVAC
  3. None of the thermostats installation/user guide explains the installation of multi-stage furnace/AC, as well as the wiring.  They all telling you to plug in what is existing to their
  4. Labels on furnace, and A/C do not tell how to connect to thermostat

Introduction

Furnace and A/C that sold ~ 20 yr ago are starting to become 2-stage (simply put as low and high speed blower), or 3-stage (low, med, high speed blower).  They equip with ECM blower motor that can run at slower speed when the room/house temperature is stables.  These are called multi-stage HVAC (furnace and/or A/C).  They are widely see in all HVAC marketing brochures, but sadly not properly connected, especially for a home owner who bought a used or new home.

I have lived in several houses, which built in 1980's (37 yr old), and some replaced their HVAC once, which probably has a 20 yr old HVAC or more commonly 10 yr old.  Some have 1-stage furnace, some have 3-stage furnace, and current one has 3-stage furnace + 3-stage A/C.  However, the thermostat is not connected to use those mult-stage feature.  Some doesn't even connecting the thermostat cable to the furnace controller board at all, except the base minimum 4 wires (G-fan, R-power, W-heat, Y-cold).

Problems

In reality, not much home owner know whether their house come with a multi-stage (2 or 3) furnace or multi-stage A/C.  Most homeowner bought an existing home, instead of new home, so it becomes norm that the all these houses might have replaced with a HVAC (furnace/AC) 20 yr ago, which are multi-stage.  It is sad that most of these multi-stage HVAC system is not properly connected to the thermostat, and resulting not utilizing these multi-stage features.  These HVAC are running as 1-stage system, which always run at high speed, except the first 5 - 15 min where it will try to run at low/med speed thinking that it will save energy.

Homeowner Frustration

Following is summary of my discovery in HVAC for 10 yr of homeowner experience:
  1. All furnaces have no clear label on its casing, and often have to go to basement and open the front cover to find its model #.  Sadly, it is impossible to determine whether it is multi-stage furnace from its label
  2. All A/C has no clear label on its casing to indicate it is multi-stage A/C, although it is easy to find its model # on the casing
  3. All thermostats are often have 4-wire connected to the thermostat, but it needs more, and have to hire electrical contractor to re-wire (fish the wire) and spends few hundred dollars, or few thousand dollars if stuck in the dry wall and need to remove/install/sand/paint
  4. All thermostat can only control 2-stage for furnace, and 2-stage for A/C, i.e. low, and high.  For high, the control board in the blower unit will spin at mid for 15 min, and high after that.  Latest smart/WiFi/bluetooth enabled brands, e.g. Nest, ecobee, Honeywell, which cost CAD$300+
  5. For thermostat to fully control all the 3-stage or multi-stage HVAC, you have to buy the manufacturer's specific thermostat, which has V wire connection in the thermostat.  It comes impossible when the manufacturer for furnace is different than A/C.  The V wire uses PCM (pulse code modulation), which is manufacturer specific
  6. 2-stage or more furnace requires additional 1 cable called W2, in addition of W.  This will certainly missed out in 4-wire thermostat cable.  This requires 6-wire thermostat wire
  7. 2-stage or more A/C requires  additional 1 cable called Y2, in addition of Y.  This will certainly missed out in 4-wire thermostat cable.  This requires 6-wire thermostat wire
  8. To control both multi-stage furnace and A/C, it requires 7-wire thermostat wire
  9. Battery-less (wall powered) thermostat requires additional 1 cable called C.  This will certainly missed out in 4-wire thermostat cable.  All smart thermostat requires C wire, as battery won't be able to operate WiFi for months, and could probably last only 1 day, if one exists
  10. Controller board in the furnace blower casing has mult-stage disabled by manufacturer as default.  Technician always leave it as default, and running as 1-stage instead of multi-stage.  So the W2 wire is always ignore for furnace, and Y2 wire is always ignore for A/C even if they are connected
  11. When multi-stage HVAC runs as 1-stage, they will run in low speed at first 5 min, mid speed at 5-15 min, then high speed after 15 min.  Thermostat can't control their speed at all
  12. Modern thermostats requires wall power, which is the C wire (min 5 wires).  So a re-wire is required (and spend money), or have to live with battery operated thermostat, AND can't control HVAC's multi-stage feature
  13. All the thermostat which mention about multi-stage, they often refer to heat pump, electric heater, unclear "second level heating," 

Research

If you have a 15 yr old or newer furnace & A/C, then you will most likely have 2 or 3 stage HVAC, with 2-3 speed ECM blower.  Following are the steps to do the research in order to choose the right thermostat:

Multi-stage Furnace Research

  1. Find out the model of the furnace - Go to basement and open up the top and bottom cover of the furnace unit.  There should be a white sticker and shows the model #.  Take a picture with your phone as you will need to refer to it often
  2. Find out the thermostat cable connections at blower motor - Before close the cover of the furnace blower cover, look at the edge of the PCB controller board for wire connections.  There should be a series of wires with label (on the PCB board): R - power, C - ground, G - fan, Y - cold, Y2 - cold (low speed), W - hot, W2 - hot (low speed), V - variable speed (or called modular in my model)
  3. Trace the cable to exterior of furnace - Above cable will connect all the way to the wall thermostat upstair.  Take a good look at the cable
  4. Count how many wires/conductors for thermostat wire at furnace blower motor - don't close the door and continue to examine the thermostat wire in #2.  Spread the wire out, and cut the sleeve if needed to find out how many wire (technically we called conductor) contains in it.  If you have 4 wire, then you have to re-wire to use battery-less thermostat or multi-stage HVAC
  5. Find furnace installation manual - most manufacturers do not have PDF copy of their manual, if you don't have yours.  Go to HVAC forum and asks people to give you the PDF copy, or call/email the manufacturer.  For 10 yr old model, manufacturer might not keep it.  They change their model after 10 yr, even internally has almost no change.  It is more often that their supplier, e.g. ECM motor supplier, replaces/upgrades some part rather than building a more energy efficient unit
  6. Enable multi-stage feature - read the furnace installation manual how to enable multi-stage feature.  For my model, it is my flipping 2 DIP switches to ON.  Manufacturer often leave it off, so that if only W wire is connected, then it operates as 1-stage, and if both W and V wire are connected, then it operates as multi-stage (support multiple stages instead of 2)
  7. For multi-stage furnace, there must be a W2, or V cable.  If it is not connected, then connect the existing wire from thermostat wire into W2.  Leave V alone, as we are installing universal thermostat.  Only manufacturer specific thermostat can make use of V, and not universal thermostat.  If you have both multi-stage furnace and A/C, then connects both W2 and Y2 to the thermostat wire
Figure 1: Left edge of the controller board are the thermostat wire connections labeled W, W2, Y1, Y2, G, C, R

Multi-stage Thermostat Research

  1. Go up stair to the thermostat and remove it, so that you can see the thermostat wires
  2. Take a picture for all the connected wires with labels.  Move them around so that the pictures clearly show the label for every wire.  It is helpful to refer to it whenever you swap another thermostat, including replacing a broken thermostat
  3. Count how many wires/conductors for the wire at the thermostat - it must match the number of wires in #4
  4. If the thermostat wire at the thermostat has 4 wires/conductors, then you have to re-wire to use battery-less thermostat or multi-stage HVAC
  5. Check your thermostat whether it has a V connection.  If it has, then it is a thermostat that capable of controlling multi-stage HVAC.  This is the thermostat that is specific for your HVAC.  If you replaced it universal thermostat, e.g. Nest, Ecobee, Honeywell Lyric (app name is Total Connect Comfort), then you will only able to control 1 low speeds, and mid-high (the blower will slowly increase from mid-high, then remain at high)
Figure 2: Universal Honeywell RTH8500D, which has W2 (2 stage furnace) and Y2 (2 stage A/C), as well as AUX (heat pump to be used as additional heater/A/C) and E (typically heat pump used as backup).  No Y cable so it can't fully control all the multi-stage/speed of the blower

Multi-stage A/C Research

  1. Find out the model of the A/C - Go outdoor and look at the bottom at each corner for a label which indicating the model of the A/C unit.  Take a picture with your phone
  2. Find the thin thermostat wire - trace the thin thermostat wire that comes out from the house to the A/C.  We need to open the cover to find out its wiring
  3. Open the cover where the thermostat cable is connecting to.  You should see a series of wires connecting to a board with label, C, Y, Y2, R.  Take a picture as you will need to refer to it often
  4. Count how many wires/conductors for the wire at the A/C - If it has only 2 wire, then you have to re-wire to use multi-stage AC feature
  5. Examine the controller board for Y2 or V wire.  If it is a 2-stage A/C, then there should be Y (regular 1-stage A/C, and treated as high speed), and Y2 (low speed).  If it is 3-stage or more, then there should be a V on the board
  6. Find A/C installation manual - most manufacturers do not have PDF copy of their manual, if you don't have yours.  Go to HVAC forum and asks people to give you the PDF copy, or call/email the manufacturer.  The reason for lack for PDF is basically the same like furnace
  7. Enable multi-stage feature - read the A/C installation manual how to enable multi-stage feature.  Manufacturer often leave it off, so that if only Y wire is connected, then it operates as 1-stage, and if both Y and V wire are connected, then it operates as multi-stage (support multiple stages instead of 2)
  8. For multi-stage A/C, there must be a Y2, or V cable.  If it is not connected, then connect the existing wire from thermostat wire into Y2.  Leave V alone, as we are installing universal thermostat.  Only manufacturer specific thermostat can make use of V, and not universal thermostat.  If you have both multi-stage furnace and A/C, then connects both W2 and Y2 to the thermostat wire

Architecture


Above architecture diagram shown how a universal thermostat will be connected to multi-stage furnace, and multi-stage A/C.  The controller board in blower motor unit (air sender unit) has wire termination for outdoor A/C unit, so all the thermostat wire connections are connected to thermostat.  There is no direct connection between wall's thermostat with outdoor A/C unit.

Please note about following label:
  • R can be labeled as Rc (24V for cold), or Rh (24V for hot)
  • There could be just 1 R instead of Rc + Rh
  • Both Rc and Rh often has a jumper cable connecting both together in the thermostat, because they are connecting to the same furnace air blower controller board
  • Y is Y1.  For single-stage A/C, you only connect to Y (or Y1), and Y2 is not connected
  • W is W1  For single-stage furnace, you only connect to W (or W1), and W2 is not connected

Choosing Thermostat

As shown in above architecture diagram, the you must know:
  1. Whether your furnace is multi-stage, and wires C, W, W2 are available at the thermostat wall unit
  2. Whether your A/C is multi-stage, and wires C, Y, Y2 are available at the thermostat wall unit
  3. If wires are not connected, is there a spare wires that you can connect C, W2, and Y2 to the air blower controller board (air sender unit)?
  4. If you need to re-wire, find out the quote for that.  1' of 8-wire thermostat wire cost CAD$1, and labor is extra.  If dry wall needs to be cut, then find out the quote to patch the drywall, including painting.  If you have basement's ceiling covered, then the cost will be more to remove and patch them as it is impossible to re-wire
  5. If you have both units are 1-stage system, then you need to find out whether you have 4-wire (no C wire), or 5 wire (with C wire) to power the thermostat.  C stands for common ground, which used together with R (24 Vac) wire to power the thermostat, so that it doesn't need battery
  6. Do you want WiFi thermostat?  It will need wall power, and won't run on battery
  7. If you have C wire available to connect to your thermostat as wall power, you can still consider buying a thermostat that support battery, so that during power failure, the thermostat setting still available.  Most high end thermostat has a coin battery (might not be replaceable) to retain its setting, while low end model don't
  8. If you want to control humidifier, then the thermostat wire will need to have 1 - 2 extra wire
  9. If you want to have outdoor temperature, then the thermostat will need to have 1-3 extra wire, as well as you need to buy the accessory for that thermostat.  Smart thermostats are using Internet to obtain the outdoor temperature, so no longer relies on thermometer sensor

Choices #1: No C wire (only has 4-wire), No multi-stage HVAC

This thermostat can't utilize multi-stage HVAC.  It is rare to find it as this become less common.  Smart thermostat is not going to work
  1. Buy battery operated thermostat without C wire connection
  2. Buy battery operated thermostat with C wire connection.  C wire is future proof, in case you decided to re-wire it with 8-wire thermostat cable

Choice #2: With C wire (only has 5-wire), No multi-stage HVAC

This thermostat can't utilize multi-stage HVAC.  Most low end thermostat will work, which is cheap and commonly available.  Smart thermostat will work
  1. Buy battery operated thermostat without C wire connection
  2. Buy battery or wall power thermostat with C wire connection.  C wire is future proof, in case you decided to re-wire it with 8-wire thermostat cable
  3. Buy smart thermostat

Choice #3: No C wire (only has 5-wire), 1 multi-stage HVAC

Either you have 1 multi-stage furnace, or A/C, but not both.  Most mid model thermostat can be battery operate without C wire, and they have C wire connection for future use.  Smart thermostat is not going to work
  1. Buy battery or wall power operated thermostat with 2H1C (2H means it has W and W2 wire connection to the furnace) if you have multi-stage furnace, or 1H2C (2C menas you have Y and Y2 connection to the A/C) if you have multi-stage A/C
  2. Buy battery or wall power thermostat with 2H2C (this means it has W, W2, Y, Y2) so it is future proof for multi-stage furnace + A/C
  3. Skips 3-stage thermostat models as those are used to control split A/C system with heat pump, which is not common in Canada, USA

Choice #4: With C wire (has 6-wire), 1 multi-stage HVAC

The thermostat choice is same as #3, but buy a unit without battery.  Smart thermostat will work
  1. Buy smart thermostat

Choice #5: No C wire (only has 6-wire), 2 multi-stage HVAC

Smart thermostat is not going to work
  1. Buy battery operated thermostat with 2H2C (this means it has W, W2, Y, Y2).  Recommend to buy a unit with C wire for future proof

Choice #6: C wire (only has 7-wire), 2 multi-stage HVAC

Smart thermostat will work
  1. Buy thermostat with 2H2C (this means it has W, W2, Y, Y2)
  2. Buy smart thermostat

Multi-stage HVAC Wiring Preparation

  1. Smart thermostat needs C wire.  Ensure C (common ground) wire is available and can be connected to thermostat.  Measure the Volt (AC) between R and C at the thermostat, and it must show 24V (same voltage as the small transformer in the furnace blower case)
  2. Follows furnace's installation guide to enable multi-stage furnace, else the thermostat can't control the low speed furnace.  This means activating both W (aka W1) and W2 wiring
  3. Follows A/C's installation guide to enable multi-stage A/C, else the thermostat can't control the low speed A/C.  This means activating both Y (aka Y1) and Y2 wiring
  4. Press the special key in the thermostat (for smart thermostat, it is configuration menu), and configures the thermostat to operate with 2-stage furnace (conventional 90% high efficiency) and 2-stage A/C (conventional).  If you only have one of those, then choose 2 for one, and 1 for another one

Wire Connections

You need 7 wires to connects a multi-stage furnace + A/C, e.g. R, C, G, W1, W2, Y1, Y2
  • If you have a multi-stage furnace, but single-stage A/C, then connects W1, W2, Y1, but leave Y2 empty (not connected)
  • If you have a multi-stage A/C, but single-stage furnace, then connects W1, Y1, Y2, but leave W2 empty (not connected)
  • If the furnace has 3-stage (3 speed) or more (as of today, they have only 3 speed), universal thermostat still going to connect to W1 (also called as W) and W2
  • If the A/C has 3-stage (3 speed) or more (as of today, they have only 3 speed), the same thing apply, universal thermostat still going to connect to Y1 (also called as Y) and Y2

Wire Connections Limitation

If you reluctant to spend money to re-wire the thermostat wire, and you don't have spare, then you are not going to make use of multi-stage HVAC.  There is currently no wireless solution available in the market, although this is possible to have one.

Wire Connections Workaround

If you have some spare wires, pull an extra thermostat wire (with 4 wires or more), but not sufficient to connect to all, then you have some workaround
  1. Only use multi-stage furnace, wire W (aka W1) and W2 - this only need 6 wires, i.e. R, C, G, W1, W2, Y1
  2. Only use multi-stage A/C, wire Y (aka Y1) and Y2 - this only need 6 wires, i.e. R, C, G, W1, Y1, Y2

2017-07-17

MS SQL Server: Ver 2014 – CXPACKET – Parallelism

Product: MS SQL Server 2014
Version: Base version, SP1, SP2 (latest), or SP2 with various patches (as of Jul 2017)
Edition: Standard, Enterprise

This post is about MS SQL Server 2014 parallel query bug that rarely & randomly occurs which causes long execution, but not widely documented yet.

Problem Description

A query that used to take 18 sec suddenly switched its execution plan and took more than 16 hr.  The data volume only increased by 100 rows, although it was joining 10 tables.  The largest table size was 40,000 rows, while the rest is < 10,000 for each table, yet the bug hit with 100 rows increase in 4 out of 10 tables, which is consider < 0.1% of the table size.

Using SSMS Activity Monitor, one can see there are many sessions with same Session ID, as well as the WaitType=CXPACKET.

Backup/restore this DB and try in MS SQL Server 2014 Standard Edition, Enterprise Edition, vanilla version, SP1, SP2, SP2 with latest Jul 2017 patch, VMware, physical server, etc, all are producing the same result.

The behavior is consistently reproducible by running the query through SSMS even remove the WHERE clause.

Analysis

CPU is consistently 40% (of multi CPU) immediately when the query fire, and last for entire day.   Modified the query to have TOP 1, and the query took ~ 1 min

Disabled parallelism by setting "Max Degree of Parallelism" to 1, and it is 50% better, and not using parallel query execution in the plan, but still slower than before

In Activity Monitor, one can see there are multiple sessions (all with same Session ID) due to parallelism query execution plan (the more CPU core you have, there more Session ID it will spawn).  Except 1 is waiting on SOS_SCHEDULER_YIELD. This occurs consistently with 40% total CPU utilization for > 16 hr (for something that used to take 20 sec a day ago).

Isolated that there is no storage sub-system issue, Windows patch, DB patch, DB startup parameter change, antivirus patch, virus, firewall, network issue, etc.

A closer look at the SQL execution plan reviews that all spawned Session ID are in SUSPENDED mode with waittype=CXPACKET, expect with waittype SOS_SCHEDULER_YIElD that is running.

Tied to limit the parallelism to 1 in the SQL scope like folow:

SELECT ...[columns]... FROM [table1] INNER JOIN [table2]..... WHERE ...[conditions] ...  OPTION (MAXDOP 1, RECOMPILE)

Although the SQL execution plan is not using parallelism, the execution time is still > 15 min (cancelled early).

Ref: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option

Re-gathered all indexes using following procedure, and no change in the behavior
Ref: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

Checked database, and confirmed it is not corrupted.  No different:
DBCC CHECKDB

Updated statistics for all tables.  No different:
sp_updatestats

Checked sp_who2 that there is nothing blocking, and no deadlock
sp_who2

Found it spawned 16 child sessions, 15 waiting for CXPACKET for entire 16 hr

Examined the query execution plan (by choose any of the child session ID) and see 40% - 50% of cost for Parallelism (Repartition Streams), and one of the index has 100x more rows in the Estimated Number of Rows.

What if only select TOP 1?  How long it will takes?  So let's try it out
SELECT TOP 1 ...[columns]... FROM [table1] INNER JOIN [table2]..... WHERE ...[conditions] ...

It turns out it took ~ 1 min with only 1 row.  So there is something very wrong with the query optimizer in MS SQL Server 2014, which we can't workaround it by more CPU, set DOP=1, set  DOP=8, set DOP=# CPU core, rebuild index, faster storage, update statistics.  As the number of rows increase progressively, it may just flip and suddenly take 5000x longer to run.

Last resort, force DB engine to use SQL Server 2012 query optimizer, using trace flag 9481
SELECT ...[columns]... FROM [table1] INNER JOIN [table2]..... WHERE ...[conditions] ...  OPTION (QUERYTRACEON 9481, RECOMPILE)

BINGO!  Query took 20 sec to run.  The execution plan is not using any of the parallel query execution, and no CXPACKET waittype at all (in Activity Monitor).

Ref: https://support.microsoft.com/en-ca/help/2801413/enable-plan-affecting-sql-server-query-optimizer-behavior-that-can-be

Depending on the application, if there is noway to specify this trace flag in query, or session, then it can only specified in the global scope as startup parameter


Conclusion: MS SQL Server 2014 has bug in the query optimizer engine, which will take 5000x to 10000x to run the same SQL with 0.01% of row increase.  We are talking about the timing 1000x which far exceeds extraction of all the relevant tables in the join (table full scan or index full scan).

I consider this as a bug due to the fact that it took much longer than disregard DOP is 1 (disable), 2, 8, 16, max up to CPU core, the SQL will take 5000x longer to run, even the SQL is not using parallel query execution.

This SQL Server 2012 cardinal estimator compatibility level also can be set by setting the database compatibility level to SQL Server 2012 (110), which limits the entire database not to leverage SQL Server 2014's feature.

For SQL Server 2016, you can set database compatibility to 2016, yet uses "alter database scoped configuration set legacy_cardinality_estimation = on" to gives an equivalent effect of trace flag 9481.  This can be set in the UI


Anyway, I still consider this as temporary workaround given the SQL takes 5000x longer to run