Orphaned Users

You backed up your Microsoft SQL Database and restored it on another system, but the user id’s you’ve used before can’t access the restored database.  This is a summary of how to quickly configure the restored SQL Server database so that your previous user id’s can access the database properly.

When transferring a database to a new server using backup and restore or when detaching and re-attaching the database, the links for the database users are broken. The SQL User information stored in the “master” database in the original server is usually not moved because the target server instance may already have users in it’s databases. You need to just add your database users to the “master” database.

DECLARE @Username VARCHAR(100),
        @cmd      VARCHAR(100)

  SELECT username = name
  FROM   sysusers
  WHERE  issqluser = 1
     AND (sid IS NOT NULL
          AND sid <> 0x01)
     AND Suser_sname(sid) IS NULL
  ORDER  BY name

OPEN userlogin_cursor
FETCH NEXT FROM userlogin_cursor INTO @Username

      SET @cmd = 'ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']'
      FETCH NEXT FROM userlogin_cursor INTO @Username

CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor 

TRY_PARSE (Transact-SQL)

This new function in SQL Server 2012 returns the result of an expression, translated to the requested data type, or null if the cast fails. Use TRY_PARSE only for converting from string to date/time and number types. You should continue to use CAST or CONVERT for general type conversions. There is a certain performance overhead in parsing the string value, so make sure the conversion is worth the cost.

SELECT TRY_PARSE('SeniorDBA' AS datetime2 USING 'en-US') AS Result

SELECT TRY_PARSE('Monday, 10 February 2014' AS datetime2 USING 'en-US') AS Result 

2014-02-10 00:00:00.0000000

Visual Studio with MSDN Version Comparison

Visual Studio

There are several versions of Visual Studio available from Microsoft, so you need to select the product that best meets your development needs. This table helps illustrate the difference in the available versions. With an included MSDN subscription, you get access to thousands of Microsoft products and additional services—so you have everything you need for designing, developing, and testing your applications on a variety of platforms.

 Visual Studio Version Comparison

You can also get more information from Microsoft here.

PARSE (Transact-SQL)

This new function in SQL Server 2012 returns the result of an expression, translated to the requested data type. Null values passed as arguments to PARSE are treated in two ways:

  1. If a null constant is passed, an error is raised. A null value cannot be parsed into a different data type in a culturally aware manner.
  2. If a parameter with a null value is passed at run time, then a null is returned, to avoid canceling the whole batch.

Use PARSE only for converting from string to date/time or number types. You should continue to use CAST or CONVERT for general type conversions. There is a certain performance overhead in parsing the string value, so make sure the conversion is worth the cost.

SELECT PARSE('Monday, 10 February 2014' AS datetime2 USING 'en-US') AS Result

2014-02-10 00:00:00.0000000

EOMONTH (Transact-SQL)

This new function in SQL Server 2012 returns the last day of the month that contains the specified date, with an optional offset. This used to be a more difficult in earlier versions of SQL Server using a variety of functions to get to the same answer as just this one statement.

SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';
SELECT EOMONTH ( @date, -1 ) AS 'Last Month';

Here is the result set.

This Month

(1 row(s) affected)

Next Month

(1 row(s) affected)

Last Month

(1 row(s) affected)

Interpreting Graphical Execution Plan Icons

These icons are displayed in the SQL Server Management Studio when displaying the graphical execution plan and represent operators used by SQL Server to execute statements. For more detailed information, see this Microsoft article on SQL Server 2008 R2

Execution plans are not displayed for encrypted stored procedures or for triggers.

When we create a sql query and pass that statement to SQL Server, it goes to is the relational engine first. As the sql arrives, it passes through a process that verifies the sql was written correctly and that it is properly formatted. This process is called query parsing. If a query fails to parse correctly because of a typo, then parsing stops and SQL Server returns an error. The output of the Parser process is a parse tree, or query tree. The parse tree represents the logical steps required to execute the requested query. If the T-SQL string is a data definition language (DDL) query, instead of a  data manipulation language (DML) statement, it will not be optimized during this process.

If the T-SQL string is a DML statement and it has parsed correctly, the parse tree is passed to a process called the algebrizer. The algebrizer resolves all the names of the various objects, tables, and columns in the query string. The algebrizer outputs a binary called the query processor tree, which is then passed on to the query optimizer. Part of what is passed to the query optimizer is a hash value that represents the query. The query optimizer attempts to find this hash value in it’s history, and if it does then it just uses the previous plan to execute the query. If it doesn’t find a similar hash value, it creates a new query plan. This technique is intended to reduce the overhead required by the query optimizer to generate a new plan for each query.

The query optimizer is a very complicated piece of software that attempts to return query results using the least amount of time possible. Once the results are returned, you can use the execution plan to uncover how the results were generated and potentially make changes to improve the speed of the results. These graphic images can be difficult to understand at first, but it is important that you earn them to provide the best technical support possible.

Execution Plan Example


This example screen shot shows some of the icons which you might see in our execution plans.

Operators that are executed in parallel are displayed with the following parallel process icon above the operator. Parallel process icon

Icon Operator
Arithmetic expression operator icon Arithmetic Expression
Assert operator icon Assert
Bitmap operator icon Bitmap
Bookmark lookup operator icon Bookmark Lookup
Clustered index delete operator icon Clustered Index Delete
Clustered index insert operator icon Clustered Index Insert
Clustered index scan operator icon Clustered Index Scan
Clustered index seek operator icon Clustered Index Seek
Clustered index update operator icon Clustered Index Update
Collapse operator icon Collapse
Compute scalar operator icon Compute Scalar
Concatenation operator icon Concatenation
Constant scan operator icon Constant Scan
Delete (Database Engine) operator icon Delete
Delete scan operator icon Deleted Scan
Spool operator icon Eager Spool
Filter (Database Engine) operator icon Filter
Hash match operator icon Hash Match
Hash match root operator icon Hash Match Root
Hash match team operator icon Hash Match Team
Insert (Database Engine) operator icon Insert
Inserted scan operator icon Inserted Scan
Iterator catchall operator icon Iterator Catchall
Spool operator icon Lazy Spool
Log row scan operator icon Log Row Scan
Merge interval operator icon Merge Interval
Merge join operator icon Merge Join
Nested loops operator icon Nested Loops
Nonclustered index delete operator icon Nonclustered Index Delete
Nonclustered index insert operator icon Nonclustered Index Insert
Nonclustered index scan operator icon Nonclustered Index Scan
Nonclustered index seek operator icon Nonclustered Index Seek
Nonclustered index spool operator icon Nonclustered Index Spool
Nonclustered index update operator icon Nonclustered Index Update
Online index insert operator icon Online Index Insert
Parameter table scan operator icon Parameter Table Scan
Remote delete operator icon Remote Delete
Remote insert operator icon Remote Insert
Remote query operator icon Remote Query
Remote scan operator icon Remote Scan
Remote update operator icon Remote Update
RID lookup operator icon RID Lookup
Row count spool operator icon Row Count Spool
Segment operator icon Segment
Sequence operator icon Sequence
Sequence project operator icon SequenceProject
Sort operator icon Sort
Split operator icon Split
Spool operator icon Spool
Stream aggregate operator icon Stream Aggregate
Switch operator icon Switch
Table delete operator icon Table Delete
Table insert operator icon Table Insert
Table scan operator icon Table Scan
Table spool operator icon Table Spool
Table update operator icon Table Update
Table-valued function operator icon Table-valued Function
Top operator icon Top
Extended operator (UDX) icon UDX
Update (Database Engine) operator icon Update

The following icons displayed in the graphical execution plan represent the Cursor Logical and Physical Showplan Operators used by SQL Server to execute statements.

The following icons displayed in the graphical execution plan represent the Parallelism Showplan Operator physical operators used by SQL Server to execute statements.

Icon     Parallelism physical operator
Distribute streams parallelism operator icon Distribute Streams
Repartition streams parallelism operator icon Repartition Streams
Gather streams parallelism operator icon Gather Streams

The following icons displayed in the graphical execution plan represent the Transact-SQL language elements used by SQL Server.

Setting SQL Server Max Memory for VMware


SQL Server can be installed and used with VMware as a virtual server. There are some things that you will cause you  to adjust your thinking with running your SQL Server on VMware, and that includes setting memory. SQL Server has two settings that help govern how much memory it will use to cache data: min server memory and max server memory.  Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server.

The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB. By default, SQL Server can change its memory requirements dynamically based on available system resources. This can all change when using VMware.

With group of virtual servers running on one or more hosts, VMware does a great job of sharing memory between different virtual machines, but to do that  it might have to steal memory from one virtual server to take care of another virtual server.  Just because you configure a virtual server with 32GB of memory doesn’t mean the memory will always be available.  If a host crashes or there is a memory hardware issue VMware might remove some memory from some guests temporarily.  If your VMware hardware is configured wrong you might not have enough memory to begin with, and the memory might not be ever available, so VMware might already be doing this today.

To manage this in VMware, you can set a reservation for any virtual server memory.  SQL Server starts at near-zero memory used, and then gradually caches more and more data as queries request it.  Unlike most applications SQL Server’s memory needs won’t go back down, so you want to assign the proper amount of memory.  You need to make sure that SQL Server gets all the memory required to operate efficiently.

When you configure new virtual servers, you need to come up with three numbers:

  • The guest’s memory – this is the amount of memory the virtual server thinks it has when it starts up.  In this example we’re building a virtual machine with 32GB of memory.
  • SQL Server’s max memory – Best practice is to set this value at the highest value possible, after you leave 4GB of memory (or 10%) for the OS, whichever is greater.  In this example, we’d set SQL Server to a max memory at 28GB, which would leave the minimum of 4GB free for the OS.
  • The VMware reservation – the lowest amount of memory the guest will keep at all times.  In a perfect world this is 100% of the guest’s memory, but that’s not always practical. If there is a hardware issue you would rather boot up all virtual servers with less than ideal memory than not be able to boot them up at all.  For SQL Server, you generally set these reservations at 75% of the guest memory – in this example, 24GB.

So let’s assume we now have a disaster and VMware’s balloon driver fires up and now claims 25% of the memory we have assigned to our virtual server, leaving just 24GB total for the guest.  This means VMware has taken some of the memory normally available to our virtual server instance and now it has just 28GB of memory (which is also our max memory setting). This is when things start swapping to disk and performance starts degrading.

That’s where SQL Server’s min memory setting is useful. You have to set the min memory in a way that accommodates your VMware reservation.  If your reservation setting is only 24GB, that means that a hardware issue could cause VMware to steal 8GB of SQL Server’s memory at any time.  If you still want to reserve 4GB of memory (or 10%) for the OS, that means your min memory setting should be 20GB.

The max memory number doesn’t need to change but we need to pay more attention to our min server memory number.  It’s acceptable to set min memory even lower as long as reduced performance is acceptable.  It is often times more acceptable for your server to have poor performance than not to run at all.

You can find additional information on these setting at Microsoft. You can also get more information at Brent Ozar and VMware.