Exam #3: 070-432 DBA TS

Hello everybody... this is my only post so far this year in 2011. Yes, I fell behind my schedule to finish the 6 certs in one year. I am half-way done though! I passed 3 exams... and it took about 2 years.

One reason for the delay is that I got a promotion at work. It came right after I had read and re-read the BI book ("70-448 Training Kit: SQL Server 2008 Business Intelligence Development and Mainenance" by Microsoft Press, which is one of the best technology books I've read). And because of the promotion I decided to switch routes and take the DBA certs first. So I put the BI book back on the shelf and read the DBA book instead.

So... here's my adventures with the DBA certification.

I read the DBA book which is "70-432 Training Kit: SQL Server 2008 Implementation and Mainenance", also by Microsoft Press. Excellent book, with great reasonable info, but the editing wasn't great so it had many inconsistencies... there is probably a newer edition out by now.

I went through all the questions on the CD... almost all of them. I took the exam on Dec 2 2011 and failed it! I WAS SHOCKED. And embarrassed... too embarrassed to blog about it at the time. My score was 622, but 700 was needed.

It's HARD! I had studied and learned so MUCH. But it seemed like nothing I studied was on the exam. For real... encryption and TDE were not even on the exam. The word encryption may have been mentioned once. The exam had a lot of questions on high availability. I should have known better than to study the encryption so much, it was very interesting and I just couldn't stop. But there wasn't a chapter in the DBA book on encryption (just one on security in general). But there were 4 chapters on high availability... HINT HINT.

Also, I didn't do a good job at making good guesses that day. There was one question that asked something about a log file and what folder would it be located in, I shouldda answered the path that had "LOG" in the path, but I didn't. So I got that one wrong.

Okay, I don't know if this is related or not. But at the beginning of the test, before the test starts, there's a question asking which DBMSs you're PROFICIENT at. I answered MS Access and SQL Server. I should have answered just MS Access... because during the rest of the exam I was feeling over-confident... and kept thinking that if I didn't know about something, then it must not exist. For example, in a question about the profiler and whether it can be used to monitor for deprecated features (or something like that), I answered no (basically), but sure enough it can! I was thinking if it can monitor for something like that, then I would know about it! But see, that attitude is why I got that one wrong. Also, did you know that a SQL job can be set to delete itself after the job has run?

Moral to the story that may apply to all exams about a software product: If you don't know for sure that it can't do something, then assume it can!

But one failure does not end the story!

The following week, I went through my notes and printed every question that I had pasted into notepad files from the CD. I discovered that I had skipped a chunk of questions from the CD... so no wonder I had failed the exam! So I went through all of those questions and pasted the interesting ones into a notepad doc. I then printed all my notes, and went through them all again and highlighted important stuff. I also researched further into a few things that didn't make sense.

* One thing I researched further was the logging that occurs in FULL verses BULK LOAD logging. I learned that you can flip a database between the two recovery models and it won't affect or interrupt your logs. And... this is important... a backup of your transaction log will be very large when you perform a bulk load (bcp, bulk insert, etc). The reason is because the backup of the t-log will contain the data that was loaded. Interesting huh? So the t-log itself won't contain the bulk operation, but the BACKUP of the t-log will. There was a question on that in the exam.

I then took the exam on Dec 21 and passed it... with 766, so barely getting the minimum of 700. But YAY, very glad I can move on to the next DBA exam for the PRO level. This time, I'm gonna order the Transcender study questions to get ready for it.

Oh, the exams now contain a Non-Disclosure Agreement (NDA). So I can no longer post questions from the exam here on my blog. :-(

I hope you all had a very Merry Christmas this past weekend! This week between Christmas and New Years is an excellent week for studying... not much going on in the office so it's a great time to read up on blogs and study material.

Until next year.

Exam #1 and #2: What I learned while Studying for 70-433 and 70-451

I learned some things while studying for the database development exams (70-433 and 70-451) that I plan to use on a regular basis on the job!

1. sp_spaceused 'tablename'
   Above statement will show the space used by an existing table.

2. sp_estimate_data_compression_savings 'dbo', 'myTable', NULL, NULL, 'PAGE'
   Above statement will show the space that could be saved if you implement PAGE (or ROW) level data compression on the table.

3. You want to list all objects (procs, views, functions) that reference dbo.mytablename. Be sure to include the schema name of the table (i.e. dbo).
   Select * from sys.dm_sql_referencing_entities ('dbo.mytablename', 'OBJECT')

4. You want to see when a procedure was last run... since the server was rebooted:
   
   select s.last_execution_time
   from sys.dm_exec_query_stats s
   cross apply sys.dm_exec_query_plan (s.plan_handle) p
   where object_name(p.objectid) = 'myProcedureName'

5. After studying the TRY/CATCH, here's what I've decided to use as my favorite CATCH statement:
   
   IF XACT_STATE <> 0
   ROLLBACK
   
   DECLARE @ErrorMessage varchar(max)
   DECLARE @ErrorSeverity int
   DECLARE @ErrorState int
   
   SET @ErrorMessage = ERROR_PROCEDURE() + ': ' + cast(ERROR_LINE() as varchar)
   + ': ' + ERROR_MESSAGE()
   SET @ErrorSeverity = ERROR_SEVERITY()
   SET @ErrorState = ERROR_STATE()
   RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

6. You may already know that parameter sniffing happens automatically... so the first time a query is compiled it is compiled for the variables you send it that first time. Or you can optimize a query for a certain value as follows...
   
   SELECT * from myTable WHERE myColumn=@t OPTION (OPTIMIZE FOR (@t=7);
   
   Kinda neat huh? Also, if you cannot update the procedure (maybe it is 3rd party software), then you can optimize the way the procedure is run by using the sp_create_plan_guide. Google for it!

7. Did you know that tables don't have a fillfactor? Only indexes do. And when you have a clustered index on a table, you can specify the fillfactor and then your table will have padded space in it. Interesting, huh?
   
   ALTER INDEX ALL on myTable REBUILD WITH (FILLFACTOR=60)
   This will first rebuild the clustered index with a fillfactor of 60% (i.e. 40% empty space). It will then rebuild the other non-clustered indexes with a fillfactor of 60%.

8. You want to identify indexes that are incurring maintenance overhead and are not being used much.
   
   SELECT * FROM sys.dm_db_index_usage_stats
   WHERE database_id = DB_ID()
   -- and object_id = object_id('myTable')
   ORDER BY user_seeks, user_scans, user_updates desc
   
   The user_seeks and user_scans tell you how often the index was found useful. The user_updates tells the amount of maintenance overhead the index has cost you.

9. You want to see how much fragmentation is in your tables or indexes. If it is 30% or more, then you'll want to rebuild the index (i.e. "Alter Index All on myTable REBUILD with online=on")
   
   SELECT avg_fragmentation_in_percent, object_name(object_id), *
   FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('CRM_BCS_Payment_Data_MIRROR'), null, null, null)

10. You want to list the queries with the highest average execution times.
    
    Select TOP 20 total_worker_time/execution_count as AverageCPUTime
    plan_handle, query_plan
    FROM sys.dm_exec_query_stats as qs
    CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle)
    ORDER BY 1 DESC

These are pretty common and helpful as well...

1. SET STATISTICS_IO ON
   Above command will also display the amount of disk activity that is generated by any T-SQL statements after they execute.

2. SET STATISTICS_TIME ON
   Above command will also display the number of milliseconds required to parse, compile, and execute any T-SQL statements after they execute.
   

3. SET SHOWPLAN ALL ON
   This command causes SQL Server not to execute T-SQL statements. Instead, it will show the estimated execution plan and compile-time details of how the statements would be executed.

There were also lots of things I learned about XML and XQUERY (@x.query, @x.value, @x.exist, and @x.modify), but I don't think I'll be using them much since we don't store XML in our databases at work.

There were tons of other little things I learned while studying for the exam. But these things listed above are the things I plan to keep with me :-)

Exam #2: 70-451 (PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008)

I took and passed my second exam! When I was taking the exam, I thought I was gonna score a 97% or so... since I thought I had everything right hehehe... but when I clicked on the submit button it churned for a second or two and gave me my score of 85%. I was disappointed! Okay, it's better than the 70% which is required to pass so it's all good. But I was really surprised by that low a score, because I really thought I had all the answers right! Wishful thinking I guess!

As you may know, Exam 70-451 is an extension of Exam 70-433. So it's best to take them back-to-back. You can see from a previous blog what I had studied for Exam 70-433, so all I did for Exam 70-451 was the following:

• I bought the Transcender Practice Exams for 70-451. The cost was about $221 (which included an exam voucher). I went through all 168 of these Transcender questions and took notes on the ones that were interesting. I highlighted and studied my notes.

Then I took the exam! Simple as pie. And here's my new logo for my resume...

Exam #1: Questions on Exam #70-433

I need to blog this now... before I forget. Everybody's exam is different, because they (randomly?) pull 50 questions for you from an inventory of several hundred possible questions. But here are some questions that were on my exam #70-433. (Somebody please tell me if I'll get in trouble with Microsoft for blogging about this!)

1. My first questions was on UNION ALL. Funny that I stumbled on this one at first, then went back and changed my answer. As you know, Union All will return all rows, whereas UNION will return only distinct rows. If you know that, you'll get this question right.
   
2. MERGE TOP 3 .... Of the 50 questions on my exam, this was my only question on the MERGE operation, which surprised me. I had studied the MERGE but I had never seen it with the TOP clause. The possible answers were 0, 3, 6, and 9. I guessed 3 and later learned that was correct! The TOP 3 clause will affect a maximum of 3 records total... this is true for INSERT, UPDATE, DELETE, as well as the MERGE.
   
3. To my surprise, I didn't get any questions about simple XML. In other words, the words "FOR XML RAW", "FOR XML AUTO", or "FOR XML EXPLICIT" were not even on my exam at all! But the exam is 12% XML, per Microsoft. For me, the XML questions were mostly about XPATH and XQUERY. Luckily, studying the examples of XPATH/XQUERY that came on the CD was enough to understand the basics and be able to answer the questions.
   
4. What file types do you feed into the Data Tuning Advisor (DTA)? There were 6 options. I think I got this one wrong... I chose XML, SQL, and something else. The more correct answer is this: trace files, trace tables, and SQL scripts.
   
5. One question I remember was straight from the practice exam. ALTER TABLE SWITCH was the answer. The question was: "You have two partitioned tables named Transaction and TransactionHistory. You need to archive one of the partitions of the Transaction table to the TransactionHistory table. Which method should you use?"
   

All the questions were multiple choice. A few questions were checkboxes where you choose more than one answer... but the question would tell you to "choose 2" or "choose 3" so that was nice.

It seemed to me like the questions were either easy SQL questions, or more difficult configuration questions. Maybe that's because I know SQL itself better than the components (Profiler, DTA, SQL Mail, etc) that go along with the SQL database development. I really feel that the 200 study questions on the CD that came with the book were excellent preparation for the exam... the training kit was an excellent bargain at $40.

The Goal

I've been working with SQL Server for about 12 years. And now I've decided I want to be certified. It'll be a learning experience for sure... but mainly, I want to be able to say I've done it. I'll blog a little about each exam. This is kinda like Julie & Julia... I'll be blogging as I go. But I won't blog that often, I'll blog once every few weeks or so. My goal is to pass all 6 SQL Server 2008 exams in the next year. Julie (in the movie) did it... she cooked all 365 recipes in a year. So I think I can pass 6 exams in a year. Stay tuned...

Exam #1: 70-433 (Technology Specialist: Database Development)

Slight confession.... I had passed my first exam (last week) before I started this blog. So I really only have 5 SQL exams to pass during the next one year. But common... that first exam is the biggest hurdle! It took me 6 months to get ready for this exam! Yes, I bought the book last November (2009) and read it in about a month, then took a break for the holidays, then started back studying in February of this year. I had a few weekends where all I really did was study the sample questions. And then I took the exam in June (2010).

And oh did I pass it... You only need 70% to pass the exam, well I scored 94% !! I'm pretty proud of myself for that :-)

So... because I passed my first Microsoft SQL Server 2008 exam, I have the MCTS (microsoft certified technology specialist) for database development. One test and I already get a real nice logo on my resume...

Here's how I prepared for this exam:

1) Buy the Microsoft SQL Server 2008 Database Development Training Kit (blue book with CD by Microsoft Press) for exam 70-433 for $40. Read the book and highlight anything you feel is important as you go. I didn't go through the labs, that wouldda taken forever. It took only about a month to read through the book. I then studied the info that I had highlighted.

2) Go through the 200 study questions that are on the CD. This is critical. And these questions are very accurate to what you'll see on the exam itself. It took me months to go through all these questions. I copied and pasted anything new into a notepad and saved it. I then printed these notes (about 30 pages worth) and read through them and highlighted anything new again.

Then I hoped for the best! (I'll blog about some of the specific questions later, so stay tuned!)