Thursday, April 5, 2012

Database Accessibility


                There are going to be many times throughout the administration of databases that a DBA will need to control how the database is accessed.  Sometimes we need to shut it down altogether, or leave it open just enough to troubleshoot.  There are a few variations that can give you the end result for database accessibility.

Database State
                The database state controls whether or not the database is accessible, and the level of accessibility.  There are three options for database state
·         ONLINE – the database is accessible for user connections
·         OFFLINE – the database is shut down and can’t receive user connections
·         EMERGENCY – the database is READ_ONLY, and logging is disabled

Here are some examples in action:

Setting a database OFFLINE: (two variations for rolling back open transactions.  See code comments for descriptions of each)
use master
go

-- take database immediately offline
-- rollback all transactions right away
alter database YourDatabase
set offline
with rollback immediate
go

-- take database offline when all open trans are
--  committed or rolled back
-- rollback all transactions after 30 seconds
alter database YourDatabase
set offline
with rollback after 30
go

Setting a database ONLINE:
use master
go

alter database YourDatabase
set online
go

Setting a database in EMERGENCY:
alter database YourDatabase
set emergency
with rollback immediate
go


Database Updateability
                There are two options to control whether or not write operations can be executed on a database:
          ·         READ_ONLY – data can be read but not written
          ·         READ_WRITE – data can be read and written to (given appropriate privileges)

Put database in READ_ONLY:
alter database YourDatabase
set read_only
with rollback immediate
go

Put database in READ_WRITE:
alter database YourDatabase
set read_write
with rollback immediate
go


Database User Access
                There are no doubt times when you’ll absolutely need to change how many users and if only certain roles can connect to the database.  There are three options that control database user access:
          ·         MULTI_USER – all users are allowed to connect to the database (given appropriate privileges)
          ·         SINGLE_USER – only one user (with privileges to connect) can be connected at any given time
          ·         RESTRICTED_USER – only members of the following roles can connect to the database: db_owner (database role), sysadmin (server role), and dbcreator (server role)

Set database for MULTI_USER access:
alter database AdventureWorks2012
set multi_user
go

Set database for SINGLE_USER access:
alter database AdventureWorks2012
set single_user
with rollback immediate
go

Set database for RESTRICTED_USER access:
alter database AdventureWorks2012
set restricted_user
with rollback immediate
go

                In summary, this post has showed you numerous ways to control the way users, and who (and how many) is able to access the database.  Any comments, questions, or problems you can leave a comment below or email me at sqlsalt@gmail.com.

                As for the code to this post, it is kept in my SQLSalt GitHub repository, as follows:

Tuesday, April 3, 2012

The Procedure Cache


                The procedure cache is the portion of memory that SQL Server stores execution plans, to include query plans and execution contexts.  The life cycle of a statement executing starts with SQL Server first checking the procedure cache to see if a corresponding execution plan exists.  This caching technique allows the database engine to ensure the fastest amount of time for execution.  If SQL Server finds the execution plan, then it will reuse that plan without recompiling the statement at hand.  But if the database engine does not find a plan for the statement it will generate a new plan and store it in the procedure cache.

                In order to get some further details on what is stored in the procedure cache, there are a handful of useful DMVs to use in the search.  For instance, if you wanted to retrieve all of the stored procedure execution plans for a particular database, you could do this:

-- substitute 'YourDatabaseName' with the desired db
use YourDatabaseName
go

select
      db_name(st.dbid) as database_name,
      object_name(st.objectid) as name,
      p.size_in_bytes / 1024 as size_in_kb,
      st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id()

                Something very interesting (and to further fortify the beauty of stored procedures and SQL Server’s ability to reuse stored procedure exec plans) is to notice the use counts on Ad Hoc plans:

select
      p.size_in_bytes / 1024 as size_in_kb,
      p.usecounts,
      s.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) s
where p.objtype = 'adhoc'

                SQL Server increments the usecounts field every time it reuses that specific plan and is a good indicator of plan recycling.

Plan Recompilation
                There are many things that may cause plans to be recompiled.  There is a very detailed list (that I will summarize below) as a great reference from BOL.  Plans will be recompiled during the following occurances:
·         table or view changes by the referencing query plan text
·         index changes used by the referencing query (to include a dropped index)
·         statistics updates used by the referencing plan
·         sp_recompile execution
·         large number of changes to keys
·         significant growth of the number of rows in the inserted and deleted dynamic tables on tables with triggers
·         utilizing the WITH RECOMPILE option when executing a stored procedure

Working Example
            Here’s a working example using our trusty AdventureWorks database (with SQL Server 2008 R2).  Let’s first run the below query just to clear the proc cache and start from scratch (I know this goes without saying, but please do this testing on your test machine nowhere near production):

use AdventureWorks
go

-- start from scratch
dbcc freeproccache

select
      db_name(st.dbid) as database_name,
      object_name(st.objectid) as name,
      p.plan_handle,
      p.size_in_bytes / 1024 as size_in_kb,
      p.objtype,
      p.usecounts,
      st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where st.dbid = db_id()
and p.objtype = 'proc'

The result set should be empty.  Now, just for the sake of getting a plan cached, run the following code:

exec uspGetEmployeeManagers
      @EmployeeID = 1
go

                Now by running the same query from above (utilizing sys.dm_exec_cached_plans) WITHOUT the DBCC FREEPROCCACHE call (we don’t want to clear the cache this time), we should see results that look like this (fields omitted for brevity):
                name:                   uspGetEmployeeManagers
                plan_handle:     0x0500090076146E6C40C1389E000000000000000000000000
                usecounts:          1

                Go ahead and run the stored procedure again:

exec uspGetEmployeeManagers
      @EmployeeID = 2
go

                Run the SELECT against sys.dm_exec_cached_plans again and you’ll see results that look like this:
                name:                   uspGetEmployeeManagers
                plan_handle:     0x0500090076146E6C40C1389E000000000000000000000000
                usecounts:          2

                So the only thing that has changed is the usecounts counter has been incremented by one.  The plan_handle is the same and this just proves that SQL Server has reused this cached plan the second time we executed uspGetEmployeeManagers.

                Now what we want to do is cause a recompilation.  We will do this through the use of the system stored procedure sp_recompile:

exec sp_recompile 'uspGetEmployeeManagers'
go

                And now when we gather our plan data again, we’ll see changed parameters like this, with a completely new plan_handle and a usecounts that starts back at one:
                name:                   uspGetEmployeeManagers
                plan_handle:     0x0500090076146E6C4061439E000000000000000000000000
                usecounts:          1

                In this blog post I have briefly explained what the procedure cache is to include what is stored there and when plans will be recompiled.  With the above example, there are endless ways to see this in action.  The useful source code for this blog post can be found here: BufferPool_PlanCache_StoredProcsForDB.sql.  If there are any comments, questions, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.