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!)