Feeds:
Posts
Comments

Sometimes, in our environment, we have multiple instances of Sql Server running on the same machine. In default configuration, when named instances come up, they listen on some port number. This port number changes every time they restart.Check out the listening port in two ways:

  • Sql Server Error Logs
  • SCM >> TCP/IP properties window

See below.

Dynamic Configuration : Listening Port

Dynamic Configuration : Listening Port

Sql Browser to rescue. When you connect to a named instance without specifying port number in the connection string, the connection is still successful because SQL Browser running on the machine takes that incoming request and re-directs it to the named instance through appropriate port number.

Solution

If you want to make sure the named instances are listening on static port, that could be done through Sql Server Configuration Manager. Sql Server Configuration Manager >> Sql Server Network Configuration >> Protocols for <instance> >> Right Click on TCP/IP >>

Sql Instance Listening Port Properties

Sql Instance Listening Port Properties

 

In properties window go to “IP Addresses” tab, and scroll to the bottom (like the image below), and enter a port number in the “TCP Port” section. Make sure the “TCP Dynamic Ports” is empty.

Sql Instanc Set Static Port

Sql Instanc Set Static Port

Now, when you restart the instance, Sql Server will come back up while listening to this static port. See Sql Server Error Logs and TCP/IP properties window below.

Sql Instance Listening on Static Port

Sql Instance Listening on Static Port

To connect to this named instance listening on static port, we mention port in the connection string like the image below:
Connect To Static Port Named Instance

Connect To Static Port Named Instance

Hope this helps,
_Sqltimes

Quick one today:

When I restore databases from one instance to a different instance, i.e. from QA environment to Integration environment or to UAT, this issue of orphan users occurs.

Login are instance level principals and Users are database level principals. Each user account at the database level is mapped to a login at instance level. As part of this mapping, sid (security ID) of the login is mapped to

--
-- Gather details of login and users
--
SELECT uid, name, sid
FROM sys.sysusers
WHERE name = 'Copient_Logix'
GO

SELECT name, sid
FROM master.sys.syslogins
WHERE name = 'Copient_Logix'
GO
Login and User SID mapping

Login and User SID mapping

 

So, when you restore a database from one instance to another instance, this sid mapping will be incorrect; Resulting in orphan users at the database level. To correct this, we could take these steps.

Step 1:

Get a list of users that are orphans.

--
-- Gather list of orphan users
--
EXEC sp_change_users_login 'Report'
GO

Step 2:

--
-- Fix the orphan user
--
EXEC sp_change_users_login 'Auto_Fix', 'user'
GO

 

Fix_orphan_users

 

Hope this helps,
_Sqltimes

Quick one today:

Recently, during troubleshooting an issue with one of our customers, some patterns emerged that indicated the possibility of resource contention for their repeated timeout issues. Upon further digging in, the CPU usage was abnormal. The database server was on a Virtual Machine with 8 processors allocated. The CPU usage was only on one processor. The rest were idle. 0% usage.

As part of that experience some lessons were gathered out of which today’s post emerges on CPU Affinity mask.

CPU Affinity Mask:

Sql Server uses all CPUs available through the Operating System. It creates schedulers on all the CPUs to make the best utilization of the resources for any given workload. Operating Systems, as part of its multitasking, it needs to switch process threads from processor to another. This is usually not detrimental for normal operations; But resource intensive operations like Sql Server this undercuts performance. Such Context Switching is not always a welcome scenario. To minimize we could configure the processors in a way that all the Sql Server load will be directed to a pre-selected group of processors. This is achieved using CPU Affinity Mask.

As you imagine, this is a HEX value. But we convert the HEX value into integer and configure Sql Server to lean towards (affinitize) a group of CPUs.

Let’s imagine a machine with 16 processors; When you assign all the processors to Sql Server and allow it to optimally utilize all the resources the settings look like this:

CPU Affinity Mask (default)

CPU Affinity Mask (default)

The options on the top convey that we are enabling Sql Server to fully manage the resource allocation with all the CPUs.

  • Automatically set processor affinity mask for all processors
  • Automatically set I/O affinity mask for all processors

I/O tasks like Lazywriter, CHECKPOINT, Log Writer, etc are directly affected by the dedicated CPUs allocated for I/O activities.

NUMA Noode0 and NUMA Node 1 are the CPU groups (either Hardware or Software configuration) to enhance hardware resource consumption. Each NUMA node has multiple CPUs with dedicated memory.

The ‘Processor Affinity’ Check box allows you to select individual CPUs, from each NUMA nodes, to SQL Server. Since the check box is not checked, none of the CPUs (from that NUMA node) have their affinity set (not allocated primarily for Sql Server).

Now, if you want to select all the processors from NUMA Node 0 to Sql Server, select that Check box. If you want to accomplish the same with T-SQL scripts like this:


--
-- Since these are advanced options, we first need to set 'Show Advanced Options' & RECONFIGURE
--
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

--
--  Integer value 15 translates to 0x000F (which shows that the last 4 bits are set to 1. Similarly 240 translates to 0x00F0
--
EXEC sp_configure 'affinity mask', 15
EXEC sp_configure 'affinity I/O mask', 240
GO
RECONFIGURE
GO

--
--  Once completed, reset the Advanced Options
--
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO

Affinity Mask for each processor could be set using a 1 or 0 bit flag. 1 signifies the affinity being set. So each processor has an affinity mask bit flag that controls its affinity.

Let’s says you want to allocate all the 8 CPUs in NUMA Node 0 to Sql Server; First 4 with CPU affinity and last 4 for I/O affinity. This are the steps to follow:

Imagine setting 1 (bit) for each allocated CPU; Then convert that value into INTEGER.

In Binary the result looks like this 00001111; It translates to 0X0F in HEX. Which is 15 in integer. This is the value you want to set for ‘affinity mask’.

Now, let’s try the same logic for I/O affinity mask. Let’s set the last 4 bits to 1.

So, 11110000 in binary translates to 0xF0 in HEX and 240 in integer.

Putting it all together it becomes:

EXEC sp_configure 'affinity mask', 15
EXEC sp_configure 'affinity I/O mask', 240
GO
After setting CPU Affinity Mask

After setting CPU Affinity Mask

Since Following is the table from MSDN that shows other example:

These are affinity mask values for an 8-CPU system.

Decimal value Binary bit mask Allow SQL Server threads on processors
1 00000001 0
3 00000011 0 and 1
7 00000111 0, 1, and 2
15 00001111 0, 1, 2, and 3
31 00011111 0, 1, 2, 3, and 4
63 00111111 0, 1, 2, 3, 4, and 5
127 01111111 0, 1, 2, 3, 4, 5, and 6
255 11111111 0, 1, 2, 3, 4, 5, 6, and 7

Some more points:

  • One byte (8 bits) covers affinity mask for 8 CPUs. And two bytes for 16 CPUs, 3 bytes for 24; 4 for 32 CPUs.
  • For more than 32 processors, you want to use affinity64 for another 32 processors.
  • It is recommended not to use the same CPU for both CPU affinity and I/O affinity.
  • Changing CPU affinity mask does not require restart.
  • Any schedulers assigned to those CPUs will be shutdown once the current workload is completed. Any new workload, is assigned to new schedulers (or the assigned CPUs)

For more information read MSDN BoL article.

Hope this helps,
_Sqltimes

Quick one today:

Sometimes there is a need to convert data from integer to HEX and from HEX to integer. There are ways to accomplish this and today, this post covers one such ways.

--
--	Conversation samples
--
SELECT	  CONVERT(INT, 0x00F0)		AS [Convert_HEX_to_INT]
        , CONVERT(VARBINARY(8), 15)	AS [Convert_INT_to_HEX]
GO
Hope this helps,
_Sqltimes

A few days ago, there was an interesting requirement. We have a query that needs to bring data from a set of tables in a particular order that did not seem straight forward. After some head scratching and banging on the desk, with no avail, my colleague Dylan rescued the quickly dying hope.

Requirement

We have a query that returns necessary data set to be populated on the webpage. But the resultant data set needs to be broken into two logical groups. The first group, should only contain data where a particular column is NULL or empty. Second group has non-NULL data. First group is always displayed at the top of the screen followed by Group 2. But both the groups need to be ORDERed by a different column.

Let’s simplify it further. Entire data set, needs to be ORDERed BY two columns, let’s say, Col8. But all records will NULL value for Col2 needs to be on the top (still ordered by Col8).

So, it is not a simple ORDER BY Col2 ASC, Col8 ASC. As this would result in second group being ordered by Col2 first then Col8. This is incorrect. So we need a way to combine the data set into two groups, with NULL and without NULL; and then order them by Col9.

Let’s take sample data set.

--
-- Sample table to demonstrate
--
CREATE TABLE dbo.test1 (
    ID         INT          NOT NULL IDENTITY(1,1)
  , ExtID      VARCHAR(30)
  , Descrip    VARCHAR(50)
  , Name       VARCHAR(20)
)
GO

INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('143', 'Apple', 'Joe')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('0140', 'Adam', 'Jim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('123', 'Cucumber', 'Tim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('133', 'Banana', 'Kim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('170', NULL, 'Sim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('160', '', 'Rim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('150', NULL, 'Lin')
GO

Correct Order of Data

Correct Order of Data

Consider this query:

--
-- Beauty of ORDER BY clause
--
SELECT  ExtID
      , ISNULL(Descrip,'') AS Descrip
      , Name
FROM dbo.test1
ORDER BY
     CASE WHEN Descrip IS NULL OR Descrip = ''
              THEN NULLIF(Descrip,'')
          ELSE
              CAST(ExtID AS INT)
     END
   , CAST(ExtID AS INT)
GO

Usually, ORDER BY clause is one of the last clauses to be processed (in a SELECT statement). So, once a data set (internal virtual table) is created by FROM clause and WHERE clause; ORDER BY is applied on that internal virtual table to sort the dataset in correct format.

Using complicated ORDER BY statements you could order the data as you need. The above query is similar to having a new column added to SELECT clause with the same CASE statement logic as in ORDER BY clause. See below:

--
-- Beauty of ORDER BY clause
--
SELECT
     CASE WHEN Descrip IS NULL OR Descrip = ''
              THEN NULLIF(Descrip,'')
          ELSE
              CAST(ExtID AS INT)
     END AS [NewDescrip]
   , ExtID
   , ISNULL(Descrip,'') AS Descrip
   , Name
FROM dbo.test1
ORDER BY
   [NewDescrip]
 , CAST(ExtID AS INT)
GO

ORDER BY Clause with SELECT column

ORDER BY Clause with SELECT column

So, if you want to order a data set in a particular way, first try to add columns to SELECT clause with that logic (to make it easier to visualize), then use the same logic in ORDER BY clause.

This is very interesting !! Thanks Dylan. Not sure if I never know this feature or completely forgot it after used it before. Either way, good to learn something new again.

Hope this helps,
_Sqltimes

More and more database servers are being migrated into virtual machines. The momentum towards virtual machines is strong, with good reasons, and as a DBA we need to come up with ways to make sure that our database servers are correctly configured to fully utilize the capabilities of virtual environments. Database servers are usually the last ones to be converted into virtual machines for obvious reasons, but in general, not all database servers need to be physical machines.= ir even have a dedicated machine, per se.

A good percentage of the database servers we have are small (to medium) size that it would not make much different if we virtualized them. Though the migration effort has become simpler with better tools availalbe, the task of comeing up with appropriate database configurations to fully utilize the resources of virtual environment while maintainig the same performance (SLA) is a non-trivial task. More on this subject later, but today, the topic is on vCPUs.

What are vCPUs?

Hypervisor software take full control of the hardware on the host machine and allows system administrators to present them to the virtual machines as needed. This capability allows us to enhance hardware resource utilization and bypass some license agreement restrictions. For example, Windows 2003 Standard Edition is limited to 4 CPUs. So if you have a machine with 6 or 8 CPUs, then the Operating System will only see 4 CPUs. With VMWare there is a way to get around this, a.k.a. cpuid.coresPerSocket/sockets option.

In VMware, vCPUs appears to the operating system as a single core single socket CPU (over simplified). vCPUs are scheduled on the logical CPUs which are hardware execution contexts. So, when you assign 4 vCPUs to a virtual machine, the Operating System on the VM sees them as 4 CPU sockets with single cores each. Now with cpuid.coresPerSocket/sockets option, we could configure the 8 CPUs from host machine as 4 CPUs with 2 cores each (4*2=8 vCPUs) or 2 CPU sockets with 4 cores each (2*4=8 vCPUs).

Example 1:

If you want a virtual machine with 1 socket with 8 cores, then you use the following configuration:

  • vCPU = 8
  • cpuid.coresPerSocket/sockets = 8

Example 2:

If you want a virtual machine with 4 sockets with 2 cores each, then use the following setting:

  • vCPU = 8
  • cpuid.coresPerSocket/sockets = 2

This is an over simplified summary f the underlying details for easy consumption. For more detailed explanation, please refer to this article on VMWare.

Other considerations:

A physical machine with 4 sockets with 4 cores each is not same as a Virtual machine with 4 Sockets with 4 cores each (using above settings). A vCPU is close to a core, but not exactly a core. It is the logical execution context (thread) on a core. A core could have 1 or more logical CPUs (threads). This difference matters of a database server as they are very resource intensive machines.

When the database server is processing 3000 or more transactions per second, having available CPUs to process the requests is important. A shared CPU (logical CPU a.k.a. CPU thread) could result in CPU bottleneck; Might result in heavy SOS_SCHEDULER_YIELD waits. There are some techniques to over come these situations, but need close coordination with System Administrators. As a rule of thumb, small and medium database server (in terms of traffic, size, payload etc) usually make better candidates for virtual machines than VLDBs.

 

Hope this helps,

OUTPUT is an interesting clause added starting Sql Server 2005.

DELETE

Recently, in my previous post, we saw an example with some insight into the usage of OUTPUT clause with DELETE statement. In this post we’ll cover INSERT, UPDATE and MERGE.

UPDATE

In an UPDATE statement, both INSERTED.* and DELETED.* internal tables are available. They store the before and after snapshot of the affected records.

Alternatively, the resultant records could be stored in a temporary table for further processing.

--
-- Usage example of OUTPUT Clause with UPDATE statement
--
UPDATE SomeTable
SET Status = 69874
OUTPUT INSERTED.CustomerPK
     , INSERTED.AdjAmount
     , DELETED.AveragePoints
WHERE TargetID = 1656
AND Status = 0
GO

INSERT

In INSERT statement, when OUTPUT clause is used, only the INSERTED.* internal table is available. The result of this table could be sent directly back or could be stored in a temporary table or table variable for further processing.

--
-- Usage example of OUTPUT Clause with INSERT statement
--
INSERT INTO dbo.SomeTable (CustomerID, AdjAmt, LastUpdate)
OUTPUT INSERTED.CustomerID
     , INSERTED.AdjAmt
     , INSERTED.LastUpdate
INTO @MyTableVariable
VALUES (123, 300.0, GETDATE())

SELECT *
FROM @MyTableVariable
GO
INSERT Statement OUTPUT Clause

INSERT Statement OUTPUT Clause


 

MERGE

In MERGE statement also, we could use both INSERTED.* and DELETED.* internal tables to retrieve the snapshot of data set before and after the change. One unique aspect to this is the $action that indicates the action that resulted in the record. It says either INSERT or DELETE. Keep in mind, MERGE statement is added since Sql Server 2008.

--
-- Usage example of OUTPUT Clause with MERGE statement
--
MERGE SomeTarget AS TGT
USING (
        -- some source data set
      ) AS SRC
ON TGT.CustomerPK = SRC.CustomerPK

WHEN MATCHED THEN
    UPDATE SET TGT.WaitingAck= 0

WHEN NOT MATCHED THEN
    INSERT (CustomerPK, AdjAmount, LastUpdate)
    VALUES (123, 300.0, GETDATE())

OUPUT $action
    , INSERTED.CustomerPK
    , INSERTED.AdjAmount
    , INSERTED.LastUpdate
    , DELETED.CustomerPK
    , DELETED.AdjAmount
    , DELETED.LastUpdate
GO

Hope this helps,
_Sqltimes

Follow

Get every new post delivered to your Inbox.