Plan Installation

Microsoft Skill Measured: This objective may include but is not limited to: evaluate installation requirements; design the installation of SQL Server and its components (including drives and service accounts); plan scale up vs. scale out basics; plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth; manage the technologies that influence SQL architecture (including service broker, full text, and scale out); design the storage for new databases (drives, filegroups, partitioning); design database infrastructure; configure a SQL Server standby database for reporting purposes; Windows-level security and service level security; Core mode installation; benchmark a server before using it in a production environment (SQLIO, Tests on SQL Instance); choose the right hardware

What I see

-          Installation requirements

-          Service accounts

-          Scale up vs. scale out

-          Database shrinking, growing, autogrowth, growth monitoring

-          RAID

Installation Requirements

                For a complete listing of installation requirements, please see MSDN’s Hardware and Software Requirements for Installing SQL Server 2012.

Service Accounts

                For a complete reference on all service account considerations regarding Virtual Accounts, domain accounts, permissions granted during setup, etc. please see the BOL reference on Configure Windows Service Accounts and Permissions documentation.

Scale Up vs. Scale Out

                To learn the differences between the two different scalability models, see this Wikipedia reference.

Database Shrinking, Growing, Autogrowth, and Growth Monitoring

                As a general rule of thumb, routine database shrinking is not recommended.  Shrinking the log is sometimes necessary if, for instance, you are in full recovery mode and transaction log backups haven’t been taken frequently enough and the log has grown out of control.  As per Paul Randal’s blog article on the topic, it can cause index fragmentation.  Please reference Paul’s article for reasoning and proof against data file shrinking.

                Conversely, database file growth is a necessary action during the lifetime of a database.  There are two ways to control this:  Manually (with ALTER DATABASE… MODIFY FILE…), and automatically (setting autogrowth).  The manual process is simple specifying a new (larger) file size:

use master;

go

alter database AdventureWorks2012

modify file

(

       name = ‘AdventureWorks2012_Data’,

       size = 200MB

);

go

The automatic way is by setting autogrowth with the FILEGROWTH file specification.  When this is set, the respective file grows by a certain byte increment, or percentage increment:

alter database AdventureWorks2012

modify file

(

       name = ‘AdventureWorks2012_Data’,

       filegrowth = 20MB

);

go

With the above setting, when the database file with the logical name of ‘AdventureWorks2012_Data’ becomes full, it will automatically grow that file by 20 MB (provided it hasn’t reached MAXSIZE).  This can be specified as KB, MB, GB, TB, or a percentage (%).  In order to monitor file growth, there are a couple of avenues.  You can use a SQL Server Agent Alert to notify when a log file has grown.  Also, you can utilize Extended Events to capture log file growth, and perfmon exposes a counter for this.  As for data file growth, you can monitor data file size and used space to get a view on growth and, if manually done, when it needs to happen.

RAID

                See this BOL reference on different RAID configurations.

References/Documentation

-          MSDN reference on Hardware and Software Requirements for Installing SQL Server 2012

-          BOL reference on Configure Windows Service Accounts and Permissions

-          Wikipedia reference on Scalability

-          BOL reference on RAID Levels

Implement Error Handling

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: implement try/catch/throw; use set based rather than row based logic; transaction management
What I see:
·         try/catch/throw
TRY/CATCH/THROW
                There are times that as a database developer you may want to catch errors and handle them accordingly.  This could include just letting them silently fail, or logging the parameters of the error, or re-throwing an error.  SQL Server allows us to do this very operation with the TRY…CATCH block.  If the code inside the TRY block throws an error, the CATCH block will be executed to handle the aforementioned error.  Below is an example:
begin try
       select 1/0;
end try
begin catch
       select
              error_message() as error_message,
              error_number() as error_number,
              error_severity() as error_severity,
              error_state() as error_state,
              error_line() as error_line
end catch
I use a blatant error (dividing by zero) to shift execution to the CATCH block, which simply selects the specific error parameters for viewing.  The built-in system functions of the CATCH block make available the specific (and appropriately named) portions of the error.  They are extremely useful if you do want to get the finer view of what happened to cause the CATCH block to execute.
The THROW statement is the successor to the RAISERROR() function.  It allows us to do just that:  THROW errors.  The syntax is as follows:
throw 50001, ‘My Example Error Message’,1;
If the THROW statement is within a CATCH block, then parameters don’t need to be supplied:
begin try
       select 1/0;
end try
begin catch
       throw;
end catch
This allows us to re-THROW the error that caused the CATCH block to execute.
References
·         BOL reference on TRY…CATCH
·         BOL reference on THROW
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Evaluate the Use of Row-Based Operations vs. Set-Based Operations

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: when to use cursors; impact of scalar UDFs; combine multiple DML operations
What I see:
·         when to use cursors
·         impact of scalar UDFs
When to Use Cursors
                Cursors are a funny thing in SQL Server.  Many times, data professionals come from software development backgrounds.  And as programmers, we like to think row-by-row with data.  We are extremely comfortable with for loops, while loops, and other cursory language features.  And then we step into the world of the RDBMS.  We try to transfer our programming knowledge directly to database development and administration.  So often times, in the infancy of our data careers we opt to go with cursors because they are familiar ground.  While this is an understandable route, it is often the wrong one.  We need to think of data as a set-based entity, as opposed to a collection of rows.  Cursors treat the data just like that…row-by-row.  But the optimizer and SQL Server in general are much more streamlined to deal with sets instead of looping through individual rows.  As a general rule of thumb, I tend to only use cursors when set-based operations and DML statements are absolutely impossible, or when the set-based workaround is so cumbersome and unmaintainable that it because of SQL nightmare.  There is no hard and fast rule, as there is definitely going to be a time when you run into a situation when a cursor is appropriate, but it definitely should not be a daily occurance.
Impact of Scalar UDFs
                The performance impact of scalar UDFs is the performance implication that comes along with the optimizer calling the UDF each time for ever row returned.  This could lead to a notoriously bad performance problem that often comes with scalar UDFs.  For further information, read this informative post on SQL Blog by Alexander Kuznetsov.
References
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Manage Transactions

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: mark a transaction; understand begin tran, commit, and rollback; implicit vs. explicit transactions; isolation levels; scope and type of locks; trancount
What I see:
·         mark a transaction
·         begin tran, commit tran, rollback tran
·         implicit vs. explicit transactions
·         isolation levels
·         @@trancount
Mark a Transaction
                SQL Server allows us to mark transactions in order to leverage specific point recovery to a particular transaction.  For instance, with the AdventureWorks database say you mark a transaction when you modify particular data:
use AdventureWorks2012;
go
begin tran ProductionUpdate withmark
       update HumanResources.Department
       set name = ‘Production Modified’
       where DepartmentID = 7;
commit tran ProductionUpdate
And then you further modify this same data:
update HumanResources.Department
set name = ‘Production after Mark’
where DepartmentID = 7;
Once the log is subsequently backed up, you now have the option to restore to the committed transaction named “ProductionUpdate”.  You can accomplish this by doing the following (provided you have the correct full recovery model backups available):
restore log AdventureWorks2012
from disk = ‘C:\YourBackupDir\AW_postMT.trn’
with
       recovery,
       stopatmark = ‘ProductionUpdate’;
go
Now by running the following query, you can see that we have restored the database to the committed portion of the marked transaction:
use AdventureWorks2012;
go
select *
from HumanResources.Department
where DepartmentID = 7;
BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN
                These three T-SQL statements are used with explicit transactions.  BEGIN TRAN tells SQL Server that an explicit transaction is starting.  It can be a named transaction, and marked (as explained above).  Subsequently, COMMIT TRAN signifies the end of a transaction by doing just that; committing it.  ROLLBACK TRAN will undo the data modification that happened during the transaction.  These explicit transaction statements are used in order to adhere to the ACID principle, particularly atomicity.  You can ensure that transaction integrity leads to data integrity.  Take the following example:
begin tran
       update HumanResources.Department
       set Name = ‘Production’
       where DepartmentID = 7;
       if is_rolemember(‘db_owner’, user_name()) = 1
              commit tran
       else
              rollback tran
The above is a relatively useless example, but it shows through the use of explicit transactions how BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN function.  It does an UPDATE of data, and if the current database user isn’t in the db_owner role, it rolls back the modified data.  Otherwise it commits the UPDATE.
Implicit vs. Explicit Transactions
                We have already talked briefly about using explicit transactions (see above), but conversely SQL Server allows us to utilize implicit transactions.  When you are operating with IMPLICIT_TRANSACTIONS ON for a particular connection, there are a handful of statements that automatically start a transaction, and that transaction will be open until either committed or rolled back.  To show an example of implicit transactions, see below:
use AdventureWorks2012;
go
set implicit_transactions on;
go
update HumanResources.Department
set Name = ‘Eng’
where DepartmentID = 1;
– now disconnect this connection
–     (i.e. close the query window)
– open a new query window and execute the below code.
– you will notice that the initial transaction was
– never committed.  This is because with IMPLICIT_TRANSACTIONS ON
– you need to commit the transaction in order for that to reflect
use AdventureWorks2012;
go
select *
from HumanResources.Department;
Isolation Levels
                SQL Server transaction isolation levels are a relatively in depth portion of locking and transactions.  You should have a thorough understanding of all the pessimistic and optimistic isolation levels.  Please see BOL for reference.
@@TRANCOUNT
                The system function @@TRANCOUNT returns the current open transactions.  It will be incremented by one for BEGIN TRAN, decremented by one for COMMIT TRAN, and appropriately set to zero for ROLLBACK TRAN.  See below for an example in order to view the return of @@TRANCOUNT with different variations of explicit transactions:
begin tran
       select @@trancount
       begin tran
              select @@trancount
              begin tran
                     select @@trancount
              commit tran
              select @@trancount
       commit tran
       select @@trancount
       begin tran
              select @@trancount
       commit tran
       select @@trancount
commit tran
select @@trancount
References
·         BOL reference on @@TRANCOUNT
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

The EVENTDATA() Function

    The use of the EVENTDATA() function within SQL Server allows us to extract valuable and necessary information pertaining to auditing and triggers, such as Event Notifications and DDL triggers.  The Database Engine strategically provides the capturing mechanism with a handful of well-structured data.

What is the format of this data?
    The format of the provided data is XML.  When working with EVENTDATA(), one of the best tools that you can use is the XML Schema Definition to reference when looking for the elements you’d like to query and capture.  This XSD can be found at the following link: SQL Server EVENTDATA() XSD file.  This definition is dauntingly vast, but there’s a neat little trick.  In order to quickly search through this XML Schema Definition file, you simple need to search for the keyword “EVENT_INSTANCE_EventType“.  For instance, say you are creating a DDL Trigger for the CREATE_PROCEDURE event.  Search for the text “EVENT_INSTANCE_CREATE_PROCEDURE”, and you will be brought to your desired event and containing elements:

<xs:complexType name=”EVENT_INSTANCE_CREATE_PROCEDURE”>
 <xs:sequence>
  <!– Basic Envelope –>
  <xs:element name=”EventType” type=”SSWNAMEType”/>
  <xs:element name=”PostTime” type=”xs:string”/>
  <xs:element name=”SPID” type=”xs:int”/>
  <!– Server Scoped DDL –>
  <xs:element name=”ServerName” type=”PathType”/>
  <xs:element name=”LoginName” type=”SSWNAMEType”/>
  <!– DB Scoped DDL –>
  <xs:element name=”UserName” type=”SSWNAMEType”/>
  <!– Main Body –>
  <xs:element name=”DatabaseName” type=”SSWNAMEType”/>
  <xs:element name=”SchemaName” type=”SSWNAMEType”/>
  <xs:element name=”ObjectName” type=”SSWNAMEType”/>
  <xs:element name=”ObjectType” type=”SSWNAMEType”/>
  <xs:element name=”TSQLCommand” type=”EventTag_TSQLCommand”/>
 </xs:sequence>
</xs:complexType>
This is the bulk of the information you’ll need in order to start utilizing the EVENTDATA() function.
Using the EVENTDATA() function
    Say you want to create a DDL Trigger in order to gather event information for the CREATE PROCEDURE command.  In the aforementioned paragraph, we already have laid out the elements that we can use and capture, as well as their names and types.  In the interest of an example, let’s create a database and some basic objects to illustrate this trigger.
use master;
go
create database EventDataDemo;
go
use EventDataDemo;
go
create table DdlAudit
(
       EventType nvarchar(128) null,
       DatabaseName nvarchar(128) null,
       SchemaName nvarchar(128) null,
       ObjectName nvarchar(128) null,
       LoginName nvarchar(128) null,
       UserName nvarchar(128) null,
       SqlText nvarchar(1024) null,
       AuditDateTime datetimenull
);
go


Now that we have the audit table setup, we can create the DDL Trigger that will be capturing and handling the CREATE PROCEDURE event.  This is one of those times where SQL Server XML knowledge comes in handy, as we’ll be relying heavily on the use of the XML value() method and an XQuery to extract our desired data.
create trigger DdlCreateProc
on all server
for create_procedure
as
       declare @eventdata xml = eventdata();
       insert into EventDataDemo.dbo.DdlAudit
       select
              @eventdata.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(128)’),
              @eventdata.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘nvarchar(128)’),
              @eventdata.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘nvarchar(128)’),
              @eventdata.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(128)’),
              @eventdata.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘nvarchar(128)’),
              @eventdata.value(‘(/EVENT_INSTANCE/UserName)[1]’, ‘nvarchar(128)’),
              @eventdata.value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,
                     ‘nvarchar(1024)’),
              getdate()
go
To test out our audit, simple create a stored procedure and query the DdlAudit table.
use EventDataDemo;
go
create procedure dbo.MyTestProcedure
as
       select 1;
go
select *
from DdlAudit;

    The above example shows a good use of the EVENTDATA() function, and how you can extract valuable information from it under given circumstances.  If there are any comments, questions, or issues please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Optimize Queries

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios in which they would be used
What I see:
·         understand statistics
·         query hints
·         statistics IO
·         join types (HASH, MERGE, LOOP)
Understand Statistics
                Statistics are the way that SQL Server records and uses the data distribution for tables and indexes.  They allow the query optimizer to choose an appropriate plan based off of row count, histograms, or page density.  Fresh statistics are necessary for the process to make the best possible decision, but stale statistics can fool SQL Server into thinking it has found the best plan, when in fact it is a sub-optimal plan.  For a great read and more information on statistics, see this Idera post by Donabel Santos on Understanding SQL Server Statistics.
Query Hints
                Query hints are a way to tell the optimizer what to do, regardless of what the optimizer might have done originally.  A few popular query hints are KEEP PLAN, MAXDOP, OPTIMIZE FOR, and RECOMPILE.  For instance, MAXDOP will override the configured instance max degree of parallelism.  RECOMPILE will cause SQL Server to discard the query execution plan after the query has completed as opposed to persistently storing it for later use.  Please see BOL for a full list of Query Hints and corresponding explanations.  All of these query hints are probably fair game on the exam, so a cursory knowledge of what they do will benefit you.
STATISTICS IO
                The set statement, SET STATISTICS IO, is used to output statistics regarding disk activity for the executed T-SQL queries.  To see a working example of this, execution the below T-SQL and view the Messages window to see the disk/cache activity:
use AdventureWorks2012;
go
set statistics io on;
select *
from HumanResources.Department;
set statistics io off;
This gives us information such as scan count, logical reads (from the data cache/memory), physical reads (from disk), read-ahead reads (read from disk to cache for future page reads), and the LOB equivalents to the aforementioned statistics.  This is a great way to see if a query or a subset of queries is hitting the disk too often.
Join Types
                There are three particular join types the optimizer can choose to utilize:
Hash Join – this join takes the smaller of the two sets to join and makes a hash table and fits that in the memory grant.  Then it takes the other set and probes by computing a hash value for each row and comparing it to the hash table.  To see this join in action, utilize the following query (notice a relatively small table that can easily fit into memory as a hashed table):
use AdventureWorks2012;
go
select *
from HumanResources.Department d
inner join HumanResources.EmployeeDepartmentHistory edh
on d.DepartmentID = edh.DepartmentID;
The execution plan should look like the following:
Merge Join – this join goes through the inputted rows only once, and this can show performance gains through sorted data:
use AdventureWorks2012;
go
select *
from Person.Person p
inner join Person.BusinessEntity be
on p.BusinessEntityID = be.BusinessEntityID;
The execution plan will resemble the following:
Loop Join – this join does just as its name states: one of the data sets will have every row of data iterated for each row of the other data set:
use AdventureWorks2012;
go
select
       p.LastName,
       bea.AddressTypeID
from Person.Person p
inner join Person.BusinessEntityAddress bea
on p.BusinessEntityID = bea.BusinessEntityID
where bea.AddressTypeID = 5;
The execution plan for the above query:
References
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.