Beware the impact of Auto Update Stats kicking in unexpectedly..

Interesting catch today with someone complaining of some API latency a week ago for a couple minutes.  IGNITE shows some 50 processes blocked at this time:

blocked sessions

Head blocker is a SPID (694) from a SOAP server.

head blocker

The SQL being executed by the SOAP server is actually an AUTO STATISTICS update kicking in….    By default we have auto update statistics enabled on all databases..   Looks like cpi_tracking_guid_3 hit some internal threshold that required the stats updating..     We disabled the daily update stats job but never disabled auto update of stats on the DBS (this happens when 20% of data changes in a table or a sproc recompile thinks it needs updated stats).

statman

This is interesting – the first time I’ve seen an auto update stats operation have impact…

 

 

 

SQL:

 

SELECT StatMan([SC0], [SB0000])

FROM

(

SELECT TOP 100 PERCENT [SC0],

step_direction([SC0]) over (order by NULL) AS [SB0000]

FROM

(

SELECT [i_api_id] AS [SC0]

FROM [dbo].[cpi_tracking_guid_3] TABLESAMPLE SYSTEM (1.891471e+000 PERCENT)

WITH

(

READUNCOMMITTED

)

) AS _MS_UPDSTATS_TBL_HELPER

ORDER BY [SC0], [SB0000]

) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

Image | Posted on by | Tagged | Leave a comment

Work Life Balance/Effectiveness

In an effort to achieve more Work Life Balance or effectiveness as its now known I have changed my core working hours from 6am – 3pm.  I’ve always been an early bird so these hours suit me just fine.

Its wonderful coming to work at 6am.  No traffic, great parking spots and brilliant sunrises. The cafeteria opens at 7am and I have first pick of the fresh oatmeal, eggs and bacon.

Some positive benefits have already been realized.  I’m making progress on my training workshops due to the undisturbed 3 hours focus time of 6am – 9am and I got to attend for the first time my 8 year old boy’s basketball practice yesterday afternoon.

so far so good but it remains to be seen if I can still fend off those meeting requests that come in for after 3pm.

Posted in Professional Development | Leave a comment

Disk Perfmon Counters Explained (?)

Confused by disk throughput, latency, queue?  This will explain the Windows disk counters. Thank you Thomas Kejser:

Perfmon Counters

Posted in Hardware | Leave a comment

Snoqualmie Summit Skiing

Tried to upload my first GoPro video but WordPress does not allow..    Anyway here’s a cool pic of the kids:

1527083_10152132774755027_1110334229_n

Posted in Seattle, skiing | Leave a comment

New SQL Server SKUs with SSD coming soon

Just been informed of 3 new SQL Server SKUs that have been designed for this year.  The SKU engineers have finally come to the table with a design that provides both the capacity and the performance we need.

All of these are coming with 20 Cores (40 with Hyper-threading):

Small SQL-V2: 8 900gb SAS drives, 2 800gb SAS SSD drives, 64gb RAM

Large SQL-V2: 25 900gb SAS drives – 50% more space than existing SKU, 128gb RAM

Large Storage-V2: 50 900gb SAS drives – twice the storage as Large SQL-V2, 128GB RAM

The dual 800GB SSD drives are pretty exciting. I cant wait to benchmark test them.

These new SKUs with DAS should allow us to pull some of the lower performance servers off the VMAX SAN.  VMAX expensive so any candidate for DAS is welcome.

Posted in Hardware, SQL Server | Tagged , | Leave a comment

2013-11-13 Perils of SSIS Logins Transfer

9pm – Harman gets me on Lync. He’s running some SSIS Logins transfer wizard for one of the new TAKO SQL servers we’re preparing to migrate to tomorrow and he’s now lost SQL access!

Great! looks like the wizard has dropped the builtin\administrators and a bunch of other logins.  what’s more he cant logon to the server to investigate.  So, I help the poor guy out (after all he is working evenings at my request):

Its the old trick – start sql in single user mode, add yourself as sysadmin ……

Resolution


Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer’s local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

http://msdn.microsoft.com/en-us/library/dd207004(v=sql.100).aspx

 

Posted in SQL Server | Leave a comment

Sanya team building offsite

Just 3 hours flight from Shanghai is the seaside resort of Sanya which is also China’s southern most city and lies in the Hainan provence. It can be freezing n Shanghai but Sanya is 25 – 30 degrees C all year around. I am writing this blog entry on the return trip. Face and body feeling tight from the sunburn and 2 days on the beach.

 

Day 1 arrive Sanya and stay at the Pullman hotel and resort. Sharing room with a compete stranger is always a bit weird but I suppose we do all work for the same company and it is a work trip after all.

Arrive at 4 pm and still have time before 6pm dinner so head down to one of the immaculate pools. The water is heaven temperature. I’ve taken my goggles so I can do a few lengths for exercise. A bunch of my Chinese colleagues are already enjoying the water and some of the more senior staffers have already been there a night. It was really funny watching some guys swim because most of them have not been taught to swim as well us people like me who come from a childhood that involved swimming, beaches and pools. I grew up with a pool in our garden and all my neighbours and friends did too. I suppose learning to swim is not a priority in a country where most people live in apartments and don’t view swimming as a leisure activity for the masses. In any case I was complemented on my great swimming and I am by no means a good swimmer.

Dinner in the banquet hall was the usual round tables with Chinese faire. Some sea food. cold chicken, sweet and sour pork, chicken soup, mushrooms, fried balls, whole steamed fish etc. There were some stage games of charades which were really tough to follow when all Chinese. Trying to guess childhood pictures of some of the team leads was fun. We also got a singing and dancing performance by some of the more talented guys from the team. Our famous singer even received flowers on stage from some of his admiring fans!

As with most Chinese dinners it abruptly ended on time at 8:30 and people departed. I joined a few colleagues and took a taxi to an area of town where supposedly there were lots of bars. We got dropped off in a strange area with what looked like hostess bars and unfriendly establishments with young men outside sitting at tables not drinking and staring at us as we walked by. Eventually we found what looked to be a nice outdoor bar on a marina and there were western looking patrons so we gave it a try. The beers were good and Seth ordered some chicken fingers which interestingly came coated in coconut.

So, we’re on our 3rd beer when the girls at the table next door all stand up suddenly at start looking nervously at the floor. I’m thinking there is a mouse or rat or something but then I see a massive cockroach scuttle across the wooden decking. We get up and try to stamp on it but it gets away and then we see another and another… I look up at the fluorescent light and see one crawling down the wire. A scream rings out from another table on the deck. I glance around and see a massive cockroach crawling up a ladies naked arm which her boyfriend is trying to brush off.. Enough is enough in fact I’m getting the jitters just writing this…We hastily settle our bill and leave.. On the way out we are still stomping on cockroaches when a Russian playing pool shouts at us to stop like the insects are his friends, even though his mate is also stomping on them…. Totally gross and weird… Won’t go there again … In bed by 11pm. Room-mate back much later with a serious case of noisy diarrhoea no doubt caused by the baijiu on the beach which I heard about the next day.

day 2 team building

30 degree hot and humid and we get given long sleeve black golf shirts as our team building attire for the beach! XXL was still too small.

5 teams of 30 each. Let the games commence. Now I know these type of events are there for people to gt go know each other and for people to show and demonstrate their leadership skills. It’s tough for me to take charge without a firm grasp of the language but I did see true leaders come to the fore. Huddling in a circle and sitting on each others laps was a bit weird but the bamboo catapult and water balloon exercise was good fun. Thankfully Team building ended at 12 and after a shower we all piled into coaches and headed to another restaurant for lunch. It was on 2 floor of a hotel next to the beach but our banquet room had no view. Another opportunity to meet new people and eat the same Chinese faire. The deep-fried sausage balls were the best and the water melon at the end is always refreshing although a little predictable. They sell lovely looking mangos everywhere but never serve them in a restaurant or at the breakfast buffet which is a little strange.

We had the rest of the day off which I spent waking a long walk up the beach. Now I heard that this particular stretch of beach is not the nicest in Sanya. Yalong bay is supposedly nicer. Having said that it definitely has potential and the areas of the beach in front of the resort hotels are well maintained. However, once you go over the road cordons to the public areas it’s not so nice. There are so many scooters, sidecars, 4 wheelers and beach buggies racing up and down the beach that’s gentle stroll is fraught with danger. Having to constantly look over your shoulder and dodge vehicles is no fun – especially on a beach of all places. The sand is packed hard and this makes it suitable for driving. Those poor crabs having to scuttle into their holes whenever a vehicle approaches. Some of the resorts along the beach are really luxurious. The Howard Johnson for example is massive with lovely pools and manicured lawns. A popular spot for newly wed photos. With quality resorts come high prices too. Spa RMB1000++, buffet dinner RMB300++ – the “++” denoting the extra 15% service charge that is added to everything. A colleague was very upset after having to pay RMB60 for a bowl of noodles in the hotel restaurant.

There are few restaurants on the beach. Most of them are across the busy road which runs the length of the beach. In fact all the resorts have a busy road between it and the beach with some hotels having underpasses to get to the beach. The restaurants are all the same with assorted fish tanks outside where you can choose some fresh fish, shellfish, sea cucumbers for them to cook. None of it was to my liking so I ended up buying some Pringles, cashews and dried coconut for my dinner.

Sanya is definitely a full on Chinese resort. It seems to be popular for corporate events, honeymooners, newly rich, posers, and old men with young girlfriends and wives. The few foreigners I did see were all Russian. I suppose it’s close to Russia so that’s the attraction. I even noticed a few shop front signs in Russian and was told that the Russians have invested heavily in Sanya. Property is till booming with construction and sales offices everywhere. In fact, in some places there are so many apartment blocks it looks like the Costa Blanca or even Hong Kong with so much density.

Some of the group went out for more seafood or to bars. Some went to rooms to play cards. I asked if they gambled when they played cards and was given a very serious “NO, gambling is forbidden in China” answer!

I would have to say my most pleasant experience and memory of the trip was when a 3 man (2 man, 1 woman) band were singing in the lobby of the hotel and came around to the guests taking requests from a plastic binder. As we were a group of men we chose a non romantic song, REM “Losing my religion”. They played and sang it perfectly from the first note to the last, 2 guitars and a cha-cha shaker. I think the trio weRE Filipino. Man, they were good. We weren’t sure whether to tip them or not. We opted for Chinese custom and did not tip but I felt bad about it.

Would I go back to Sanya? For a corporate event – yes. but with my family at my own expense? No. There are too many cheaper and better options in SE Asia.

I’m ending this blog post 2 days later. The sunburn is now peeling but I still have some great memories of the Commerce Team Offsite Sanya 2012!

Observation made on the flight back: Chinese do not wear their seat belts and air hostess do not make you wear them.

20120303-174155.jpg

Posted in China Cool, Microsoft China | Tagged | Leave a comment