Search This Blog

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 - $180After ~ 15 yr, this component is probably near end of life, and starting to affect mpg.  It will trigger post O2 sensor check-engine-light.  If you replaced your O2 sensor, then you know you need to replace this part.  Recommends to buy original part 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
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
  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 - 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 - 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 - can't charge battery (after turn off, can't turn on any more as battery is low)
  11. Fuel injector relay cracked solder - occurs to most Honda prior of 2000.  Needs to resolder.  Intermittent issue
  12. Ignition distributor - aged o-ring, bearing
  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 - 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

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

2017-01-16

Oracle: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Product: Oracle Database
Version: 12.1.0.2

Oracle 12c introduced a new parameter called PGA_Aggregate_Limit, which is a global PGA memory control to prevent RAM contention and virtual memory swapping to disk.

Previous parameter PGA_Aggregate_Target is only the prefer global PGA usage for the entire database instance, while some customers have been experiencing RAM contention due to this is a soft limit.

There is a known bug for PGA_Aggregate_Limit even you disable it by setting to 0.  You will continue to see following error:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

This bug will be visible on following conditions:

  1. Supplemental Logging is enable for the table
  2. Updating of Spatial column
  3. After install an unknown version of PSU or CPU patch (DBA does not disclose exact version)

The way to troubleshoot whether you affected by this known Oracle bug is as below:

1. In alert.log, confirm you are getting ORA-04036 which killing some DB sessions
2. Execute following from sqlplus

select value/power(1024,2) "MB" from v$pgastat where name = 'maximum PGA allocated';

2.1. Confirms it exceeded PGA_Aggregate_Limit size set in spfile.ora/pfile.ora
2.2. If it is below, then you are very likely hitting this Oracle bug
3. Review the generated trc log as shown in alert.log
3.1. The log will show the PGA memory usage
3.2. If it is below, then you are very likely hitting this Oracle bug
4. Determine the failing SQL statement and execute following from sqlplus and fill in the table name

SQL> desc user_log_groups
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 LOG_GROUP_NAME                            NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 LOG_GROUP_TYPE                                     VARCHAR2(19)
 ALWAYS                                             VARCHAR2(11)
 GENERATED                                          VARCHAR2(14)

SQL> select log_group_name, table_name from user_log_groups where table_name = '&table_name';

Note: Replace user_log_groups with dba_log_groups for all users

SQL> desc user_log_group_columns

 Name                Null?    Type
 ------------------- -------- ----------------------------
 OWNER               NOT NULL VARCHAR2(128)
 LOG_GROUP_NAME      NOT NULL VARCHAR2(128)
 TABLE_NAME          NOT NULL VARCHAR2(128)
 COLUMN_NAME                  VARCHAR2(4000)
 POSITION                     NUMBER
 LOGGING_PROPERTY             VARCHAR2(6)

SQL> select log_group_name, table_name, column_name from user_log_group_columns;

Note: Replace user_log_group_columns with dba_log_group_columns for all users

5. Confirm that the failing table contains spatial column

SQL> select data_type, table_name, owner from user_tab_columns where data_type = 'SDO_GEOMETRY';

DATA_TYPE       TABLE_NAME      OWNER
--------------- --------------- --------------------
SDO_GEOMETRY    CONTACTADDRESS  TEST1

Note: Replace user_tab_columns with dba_tab_columns  for all users

Workaround

Drop the supplemental logging for the failing table.  Several different syntax are listed below to make it more general

SQL> ALTER TABLE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL> ALTER TABLE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER TABLE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
SQL> ALTER TABLE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Note: The first statement should be sufficient, while others are for specific use case

If you want to disable it globally, then use following statement:

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


2017-01-04

MS SQL Server: Finding JDBC Version

Product: MS SQL Server
Version: 2008R2 - 2014

Every Microsoft JDBC JAR version bundled with 4 sqljdbc.jar files for 4 different versions of major Java JVM.  When you download the driver from Microsoft, it will include 4 JAR files, and you need to pick the proper JAR file according to your JVM version.

For example, following Microsoft JDBC 4.2 bundled with sqljdbc42.jar that needs JVM 1.8, sqljdbc41.jar that needs JVM 1.7, sqljdbc4.jar that needs JVM 1.6, and sqljdbc.jar that needs JVM 1.5.

https://msdn.microsoft.com/en-us/library/ms378422(v=sql.110).aspx

From this MSND, you can see that JDBC v4.2 has another versioning called JDBC Version Compliance for each file.

So when you received a file called sqljdbc.jar, it is hard to tell what version of Microsoft JDBC driver version it belongs to, but only able to tell it support JVM 1.5 or older.

Therefore, I developed following Java application that can display both the JDBC version, as well as JDBC Version Compliance

Sample Output

D:\temp\sqljdbc>C:\Language\jdk1.8.0_92_64\bin\java -cp sqljdbc42.jar;. JDBCVersion
JDBC Driver version: 4.2
Driver name: Microsoft JDBC Driver 6.0 for SQL Server
Driver version: 6.0.7728.100
Product name: Microsoft SQL Server
Product version: 11.00.5058

D:\temp\sqljdbc>C:\Language\jdk1.8.0_92_64\bin\java -cp sqljdbc41.jar;. JDBCVersion
JDBC Driver version: 4.1
Driver name: Microsoft JDBC Driver 6.0 for SQL Server
Driver version: 6.0.7728.100
Product name: Microsoft SQL Server
Product version: 11.00.5058

D:\temp\sqljdbc>C:\Language\jdk1.8.0_92_64\bin\java -cp sqljdbc4.jar;. JDBCVersion
JDBC Driver version: 4.0
Driver name: Microsoft JDBC Driver 4.0 for SQL Server
Driver version: 4.0.4621.201
Product name: Microsoft SQL Server
Product version: 11.00.5058

D:\temp\sqljdbc>java -cp C:\ClaimCenter7.0.7\lib\sqljdbc4.jar;. JDBCVersion
JDBC Driver version: 4.0
Driver name: Microsoft SQL Server JDBC Driver 3.0
Driver version: 3.0.1301.101
Product name: Microsoft SQL Server
Product version: 11.00.5058

D:\temp\sqljdbc>java -cp C:\ClaimCenter9.0.0\admin\lib\sqljdbc-4.1.jar;. JDBCVersion
JDBC Driver version: 4.0
Driver name: Microsoft JDBC Driver 4.1 for SQL Server
Driver version: 4.1.5605.100
Product name: Microsoft SQL Server
Product version: 11.00.5058

Source code

Filename: JDBCVersion.java
import java.sql.*;

class JDBCVersion
{
    public static void main(String[] args) {

        Connection conn = null;

        try {

            String dbURL = "jdbc:sqlserver://localhost\\SQLSERVER2012";
            String user = "sa";
            String pass = "sa";
            conn = DriverManager.getConnection(dbURL, user, pass);
            if (conn != null) {
                DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
System.out.println("JDBC Driver version: " + dm.getJDBCMajorVersion() + "." + dm.getJDBCMinorVersion());
                System.out.println("Driver name: " + dm.getDriverName());
                System.out.println("Driver version: " + dm.getDriverVersion());
                System.out.println("Product name: " + dm.getDatabaseProductName());
                System.out.println("Product version: " + dm.getDatabaseProductVersion());

            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}


Compiling Instruction

C:\Language\jdk1.8.0_92_64\bin\javac JDBCVersion.java

This will create JDBCVersion.class in current directory

Execution Instruction

1. Download MS SQL Server JDBC driver to C:\temp
2. Extracts all the JAR files into C:\temp\sqljdbc.  In typical usage, you will want to copy the JAR file that you would like to find out its version
3. Run and specify the JDBC jar files that you want to verify the version

C:\Language\jdk1.8.0_92_64\bin\java -cp sqljdbc4.jar;. JDBCVersion

where you have changed the current directory to where JDBCVersion.class resides

2016-12-15

PowerShell: Rename File but Keep Extension Name

Rename all the files in current directory by removing the whitespace character from the base filename
Dir | where { ! $_.PSIsContainer } | Rename-Item –NewName { $_.Basename.TrimEnd() + $_.extension } -verbose

Same as above, but includes all the subdirectories
Dir -recurse | where { ! $_.PSIsContainer } | Rename-Item –NewName { $_.Basename.TrimEnd() + $_.extension } -verbose

Rename all the files in current directory by appending "_old" to the end of the base filename
Dir -recurse | where { ! $_.PSIsContainer } | Rename-Item –NewName { $_.name + "_old" + $_.extension } -verbose

Rename all the files including subdirectories by inserting "old_" to the beginning of the base filename
Dir -recurse | where { ! $_.PSIsContainer } | Rename-Item –NewName { "old_" + $_.name + $_.extension } -verbose

Rename all the files including subdirectories by replacing "new" with "old" in the base filename
Dir -recurse | where { ! $_.PSIsContainer } | Rename-Item –NewName { $_.basename.replace("new","old") + $_.extension } -verbose

Rename all the *.trp files including subdirectories by removing the "00" at end of filename, i.e. replacing "00.trp" with ".trp" in the base filename
Dir -recurse -filter *.trp | Rename-Item –NewName { $_.name –replace “00.trp“,”.trp” } -verbose