Whops I accidently deleted the original list of questions. Like a true data professional I don’t backup my blog. Here’s of the original questions.
Internals
1. What’s an example of a legacy LOB type v new LOB Type ?
2. Are Legacy LOB Types stored on/Off row by default
3. Are New LOB Types stored on/off row by default ?
4. How can you change this behaviour for legacy LOB Types?
5. How can you change this behaviour for New LOB Types?
6. If you change row storage for a LOB from “on row“ to “off row” on a 100GB database how long will it take ?
7. How big in bytes is the “version tag” for versioned rows and what does it store ?
8. How often to PFS pages appear in a data file?
9. How often to IAM pages appear in data file?
Index Strategies
1. A table has 1,000 pages with 100 rows per page. A query on an (select * from blah where MyColumn=1) column returns 500 rows and there is an index on MyColumn. Will it use the index if the index is non-clustered?
2. Can an index include LOB data types?
3. How big can the clustered Index Keys on a Materialised View be?
4. Recommend TWO Ways to get rid of unused indexes if you have a representative trace file
Log Internals
1. Is there such a thing as a non-logged operation ? If so, examples?
2. How Can you get minimally logged inserts on a clustered table with rows in it ? (specify two things to do)
3. A Developer issues a begin tran followed by an update and then a checkpoint. Are the changes written to disk in the checkpoint ?
4. How do you resolve a “checkpoint IO storm”
5. Someone creates a 1GB log file. How many VLF’s are there ?
6. Is a log backup smaller or larger with BULK_LOGGED than with FULL logging mode ?
7. Is rebuilding of an index in simple mode minimally logged?
8. Is re-org of an index in simple mode minimally logged?
9. You need to update a 100GB BLOB when in bulk logged mode. Can this be done minimally logged? If so, how?
10. If you switch from full to bulk and then MDF corrupts. Can you backup the tail log ?
11. How can you make the log file initialisation faster ?
Statistics
1. How many steps are in a stats histogram
2. Are histogram values left or right based?
3. What can cause a recompile (there are 14 events). List as many as you can.
