Answers and Links for the Questions Asked During the #24HOP / #SQL Denali Challenge

We would like to thank each and every one of you that participated in the #24HOP / #SQLDenali Challenge at the end of the recent 24 Hours of PASS Sessions. It was very exciting for us to see so many of you getting involved and trying your best to win!

Many of you told us that you had learned a lot as a result of this challenge and asked us to provide all the questions and answers along with the links to where the answers can be found. So here they are!

We have another blog post coming in a few weeks as a follow up to the two Fireside Chats with the SQL Server Engineers that took place each day after the last 24 Hours of Pass sessions. We’ll provide the audio recordings from both chats along with answers to any outstanding questions.

Questions from Day One of 24 Hours of PASS

Q #1: In data mining, which DMX function will you use to predict future values for time series data?

Answer: PredictTimeSeries()
Link: http://msdn.microsoft.com/en-us/library/ms132167(v=SQL.110)

Q #2: What does LUN stand for?

Answer: Logical Unit Number
Link: http://en.wikipedia.org/wiki/Logical_Unit_Number

Q #3: What are the names of the public Targets available in Extended Events?

Answer: Either the SQL Server 2008 R2 or SQL Server Code Name “Denali” answers:

SQL Server 2008 / 2008 R2	Denali
asynchronous_file_target	event_file
ring_buffer	ring_buffer
synchronous_counter	event_counter
pair_matching	pair_matching
etw_classic_sync_target	etw_classic_sync_target
asynchronous_bucketizer	Histogram
synchronous_bucketizer

Q #4: What #PowerPivot #SQLDenali feature allows u to define groups of tables for breaking down a large model into targeted ones?

Answer: Perspectives. To turn this one, users have to switch the addin to Advanced Mode, and then use the ‘Perspectives’ button on the ribbon to define these. Once defined, the PowerPivot field list allows selection of the perspective.
Link: http://technet.microsoft.com/en-us/library/hh272057(SQL.110).aspx

Q #5: What is the result of issuing the following commands from SQLPS.exe?

Answer: This will list the instances of the SQL Server Database engine on the local computer
Link: http://technet.microsoft.com/en-us/library/cc281947(SQL.100).aspx

Q #6: Name the 3 types of data flow components.

Answer: Source, Transformation, Destination
Link: http://msdn.microsoft.com/en-us/library/ms140080(v=SQL.110).aspx

Q #7: When you see yellow exclamation mark on the operator symbol in a graphical Showplan, what does it mean?

Answer: It means the plan has a warning about this particular operator. One example of such warning is missing statistics on the object referred in the operator. You can see exact text of the warning by hovering mouse over the operator or by looking at properties window while selecting the operator in question.
Link: http://msdn.microsoft.com/en-us/library/ms178071.aspx

Q #8: What is the difference between multi-site clustering support pre-Denali and in #SQLDenali?

Answer: Pre-Denali multi-site clustering was possible and supported and required both a third-party stretch VLAN as well as a cross-site SAN replication technology. In Denali you no longer need a stretch VLAN and can use an IP OR dependency between IP subnets.
Link: http://msdn.microsoft.com/en-us/library/ff878716(v=SQL.110).aspx

Q #9: Question: what version of #SQLServer introduced Reporting Services?

Answer: SQL Server 2000 (add on to SQL Server 2000 – was included in the install in SQL Server 2005)
Link: http://www.microsoft.com/presspass/press/2004/jan04/01-27ReportingSvcsLaunchPR.mspx

Q #10: In #SQLDenali what is the only memory allocation NOT governed by “max server memory”?

Answer: Direct Windows Allocations
Link: http://blogs.msdn.com/b/sqlosteam/archive/2011/01/04/sql-server-memory-manager-changes-in-denali.aspx

Q#11: What is the difference between the affinity mask and affinity64 mask configuration options?

Answer: affinity mask to bind the first 32 processors & affinity64 mask to bind the remaining processors
Link: http://msdn.microsoft.com/en-us/library/ms188291.aspx

Q #12: How can you determine whether a query execution operator uses the vector-based Batch processing mode in #SQLDenali #SQLApollo?

Answer: Position the mouse pointer over the execution operator. The yellow popup box that appears has a field called Estimated Execution Mode. This tells you whether the execution mode is Batch (the new mode), or Row (the original mode).
Link: http://msdn.microsoft.com/en-us/library/gg492088(v=SQL.110).aspx

Questions from Day Two of 24 Hours of PASS

Q #1: Take advantage of the latest hardware by using ___ including multithreading & linear scaling w/ multiple cores.

Answer: Vertipaq engine or just Vertipaq
Link: http://msdn.microsoft.com/en-us/library/ee210639.aspx#IMBI

Q #2: What feature introduced in #SQLServer 2008 allows you to easily enforce uniqueness on non-NULL values?

Answer: Filtered Indexes
Link: http://technet.microsoft.com/en-us/library/cc280372.aspx

Q #3: In #SQLDenali, you can eliminate planned downtime on Hyper-V environment using ____

Answer: Live Migration
Link: http://www.microsoft.com/sqlserver/en/us/future-editions/denali-mission-critical-confidence.aspx

Q #4: What version of ReportBuilder ships with Reporting Services 2008 R2?

Answer: Report Builder 3.0
Link: http://msdn.microsoft.com/en-us/library/dd207008.aspx

Q #5: In the database design context, what does DRI stand for?

Answer: Declarative Referential Integrity
Link: http://en.wikipedia.org/wiki/Declarative_Referential_Integrity

Q #6: Using a DMV such as sys.dm_os_waiting_tasks is important to identify bottlenecks in SQL Server. This DMV can of course be used to identify bottlenecks on TempDB, but what is the easiest way to filter the results only related to TempDB?

Answer: The database ID of TempDB is always 2, so the following query will only show wait queue related to TempDB.
select  *
from sys.dm_os_waiting_tasks
where resource_description like ‘2:%’
Filtering on the wait_type will help you to identify different kinds of bottlenecks.
Link: http://msdn.microsoft.com/en-us/library/ms188743.aspx

Q #7: What does adding OPTION (RECOMPILE) to a query do?

Answer: Forces SQL Server to create, use then discard a plan for the query.
Link: http://msdn.microsoft.com/en-us/library/ms181714.aspx

Q #8: What is the name of the NuGet package that gives you the Code First feature?

Answer: EntityFramework
Link: http://msdn.microsoft.com/en-us/data/aa937723

#9: Which #SQLDenali Reporting Services tool will you use to create data alert definitions?

#10: Assuming trace flag 1204 is not already on, what will DBCC TRACEON (1204); do?

Answer: Nothing.  (The flag needs to be set globally – the above command does not set it globally).
Link: http://msdn.microsoft.com/en-us/library/ms188396.aspx

Q #11: What are the supported operators for a PBM condition on a string field?

Answer:  =, != , LIKE, IN, NOT LIKE, NOT IN
Link: http://msdn.microsoft.com/en-us/library/bb500340.aspx