My experience doing the online proctored exam

With the MCSA exams being retired at the end of January and COVID restrictions ramping up in my country causing the test centers to be closed, I had no other option but to go online.

I was worried about this as I heard stories from my colleagues that the slightest unexpected sound might disqualify you. So I looked up a guide on how it all goes down – this one was most informative:
https://docs.microsoft.com/en-us/learn/certifications/online-exams

I have a family with small kids, so they had to go for the duration of the exam. Not nice to kick them out in the winter, but they took one for the team. I was also hoping no one will ring a bell, start to drill the shared walls, or randomly scream below my window (which are all frequent events).

With that in mind, I got to it. First of all, you download and run the system test program. This is the same program, that will be used on the day of the real exam. You will get an Access code for the test (and later a different one for the exam).

It will check your microphone, internet, and the webcam feed.
Next, you will need to close all applications and processes you possibly can.
Anecdotally I once forgot to close an app and when I got to the “Launch simulation” it told me that one app hasn’t been closed and this incident has been logged.

Prerequisites

  • Computer (duh) preferably desktop.
    • They namely warn about VPN’s, proxies, and company computers with firewall rules, etc.
  • One valid government-issued Id (in my case driver’s license)
  • Mobile phone for verification (selfie, photo of Id, photos of the room, point of contact in case of e.g. internet outage).
    • The mobile phone was my biggest point of confusion – you cannot turn it off, because the proctor might contact you, but you also cannot have any random sounds? How am I supposed to control who calls me? I had the phone outside of my reach and screen down as per guidelines, but it was buzzing constantly with messages from company chat.
  • Webcam and microphone
  • A closed room where no one will disturb you
  • Clean work area – no papers, pens, jewelry, other screens, etc.
    • Nothing that could be used to cheat or copy the questions. I have two monitors on my stand – I had to turn the secondary away from me and unplug it. Also, there was a mess in the rest of the room as my ‘office’ is sort of a storage area.

On the day of the exam

You can start the process between 30 minutes before and 15 minutes after the registered time. Go to the bathroom in advance, you cannot during the exam.

You will go to your certification dashboard and it will show in the Appointments section. You will get the real access code this time and start the verification process. You will be prompted to use a mobile phone and go to the website and take a selfie, a photo of your Id against a dark background with no glare (both sides), and then 4 photos of your work area. Facing the desk, behind the desk, left and right.

I recommend starting as early as possible because I had some technical difficulties. First of all, my primary mobile browser is Firefox and I couldn’t get past the selfie stage – it just failed silently. Only after switching to chrome, the selfie camera showed an overlay with an oval area, where my face should be.

The second problem was with taking photos of my room – I took the first one and on the second it said – internal memory error. I’ve tried 3 times with no luck, so I restarted the phone and also changed the camera app from custom to default – that did the trick.

After completing verification, accepting the terms and conditions, and all that jazz, it went straight to the exam. I could see my camera feed at the top of the screen – your face must not move outside the camera scope, otherwise, you will be disqualified. Also, there is a Whiteboard option (similar to the piece of paper you would get at the test center) and a chat with the proctor.

I was expecting the greeter to give me some basic info and also slowly move my web camera from left to right to show my work area (like the official video said), but nothing happened.

Final thoughts

The test itself was not that much different than in the test center. But the feeling of being watched the whole time made me super itchy and I had the urge to scratch all the time. I was also mindful of not putting my hands over my mouth when thinking or mumbling to myself as these might be mistaken for a cheating attempt.

Overall I was more worried about failing for things outside my control (like the mobile phone or noise) than for my lack of knowledge. Given a choice, I would pick the test center the next time – even though that means a 2-hour commute one-way for me.

Silver bullets in SQL: Part 1, TYPE directive in FOR XML

Foreword

“It depends” is the DBA’s most frequent answer and for a good reason. Most of the time giving good advice really depends on many variables. A good DBA must follow up by explaining “why” it depends.

Another often repeated mantra is “There are no silver bullets” everything is a trade-off, for example parallelism might decrease query duration but increases CPU usage.

Well, this blog post (and hopefully series) will try to cover a few of those very rare silver bullets. Now I’m cheating a bit in my definition, these tips will be along the lines “did you know this existed?” and the alternative is writing the query in a sub-optimal way. Let’s get to it then!

TYPE directive in FOR XML

XML is a very polarizing topic in the SQL community, some developers might be lucky and never run into it. But love it or hate it, it is there and will be for a while so we might as well use it in the most efficient manner.

Let’s say our goal is to generate an XML document from a query and pass it to an application or Service Broker, etc. We’re gonna use the FOR XML query. I’m testing this in a fresh SQL Server 2019 instance in the master database.

I’m gonna generate a small XML into an xml variable both with and without the TYPE directive. I’ll repeat the experiment but save it into the varchar(max) and nvarchar(max) variables.

/* xml variable, no TYPE directive */
DECLARE @xmlNoType xml
SET @xmlNoType = 
(
	SELECT *
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document')
)
SELECT (DATALENGTH(@xmlNoType) / 1024.0) / 1024. AS SizeMB
GO
/* xml variable, TYPE directive */
DECLARE @xmlType xml
SET @xmlType = 
(
	SELECT *
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document'), TYPE
)
--SELECT (DATALENGTH(@xmlType) / 1024.0) / 1024. AS SizeMB
GO
/* varchar(MAX) variable, no TYPE directive */
DECLARE @varchar varchar(MAX)
SET @varchar = 
(
	SELECT *
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document') 
	/* 
		Cannot use TYPE: 
		Implicit conversion from data type xml to varchar(max) is not allowed. Use the CONVERT function to run this query.
	*/
)
SELECT (DATALENGTH(@varchar) / 1024.0) / 1024. AS SizeMB
GO
/* nvarchar(MAX) variable, no TYPE directive */
DECLARE @nvarchar nvarchar(MAX)
SET @nvarchar = 
(
	SELECT *
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document')
	/* 
		Cannot use TYPE: 
		Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
	*/
)
SELECT (DATALENGTH(@nvarchar) / 1024.0) / 1024. AS SizeMB
GO

/*
	XML size			0.59 MB
	varchar(max) size	0.90 MB
	nvarchar(max) size	1.80 MB
*/

Results on my machine are the following – first the size. Unsurprisingly both with and without TYPE directive, the XML size is the same – roughly 0.59 MB
varchar(max) variable is 0.9 MB and nvarchar(max) is the double 1.8 MB.

Let’s look at the CPU and elapsed time.

We can see that using the TYPE in our query saved some 6k reads, also the CPU and duration is half of the query without the TYPE. The non-xml variables are not doing bad performance wise, but also more reads and larger data type size.

Let’s compare the actual execution plan:

In the query without the TYPE there is an implicit conversion. The TYPE directive is saying that I want to generate an XML document. Without that I’m generating an nvarchar(max) and then implicitly casting it to XML.

DECLARE @whatIsThis SQL_VARIANT

SET @whatIsThis = 
(
	SELECT *
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document')
)

That returns error message:

Msg 206, Level 16, State 2, Line 5
Operand type clash: nvarchar(max) is incompatible with sql_variant

I’ve repeated the tests but doing a cartesian product with several values to enlarge the XML. These are the results for 6 MB and 21 MB XML respectively. The varchar(max) size was 20 MB and 70 MB (nvarchar is doubled).

We can see that with an increasing document size, the benefits are even greater for such a little change. Thus earning its place in the Silver bullets series. I’ve run into processes that generated XMLs that were over a hundred MBs large and the savings were vast.

Next time, I’ll talk again again about XML trick, but instead of generating, we will be parsing.