PowerShell: How to Find Logical and Physical Processors on SQL Server?

Working on PowerShell scripts have been an interesting journey. I have written a couple of posts before on using WMI events. Was pondering on this thought and this blog is inspired by combining using of WMI with PowerShell. So what are we trying to solve?

When SQL Server changed its whole licensing process from processor based licensing to core based licensing, a lot of us were confused to how this has to be calculated. Recently, one of my DBA friends asked how can I find the number of logical processors (cores) on my SQL Server machine? Now this question got me thinking and the first place I always go to search is SQLAuthority – trust me on this. Once figured out there isn’t much on this topic, I thought to write a simple script which I can refer in the future.

$Computer = 'localhost'
$ErrorActionPreference = 'SilentlyContinue'
$ProcessorConfig = Get-WmiObject -class Win32_Processor -computername $Computer -namespace root\CIMV2 | Select PSComputerName, Name, NumberOfCores, NumberOfLogicalProcessors
write-Debug $Error.Count
If ($Error.Count -gt 0)
$ProcessorConfig = New-Object psobject
$ProcessorConfig | Add-Member -type NoteProperty -name ComputerName ("$Computer-failed to connect")
$ProcessorConfig | Add-Member -type NoteProperty -name Name -value 'Unable to get ProcessorInfo'
$ProcessorConfig | Add-Member -type NoteProperty -name NumberOfCores -value $null
$ProcessorConfig | Add-Member -type NoteProperty -name NumberOfLogicalProcessors -value $null
Write-Debug "Failed to connect to $Computer"
$ErrorActionPreference = 'Continue'
$ProcessorConfig | FT * -AutoSize

When you run this above script inside PowerShell, we will get to see an output like below:

logical physical processor 01 PowerShell: How to Find Logical and Physical Processors on SQL Server?

As you can see I have a 4 core processor and have enabled HT (Hyper-Threading) which makes the number of Logical Processor to 8. I personally felt this was a quick way to learn PowerShell when you have a typical problem in hand.

If you have read this far, please let me know how you have used WMI or PowerShell to solve interesting problems. Let me know over the comments and I will surely try to publish a few more like these in the future too based on your inputs.

Reference: Pinal Dave (http://blog.sqlauthority.com)


The technique used in this blog is explained in earlier blog SQL SERVER – Creating Dataset Using VALUES Clause Without Creating Table. To read more about Collation and Unicode refer to MSDN. This topic in general is very deep and complicated sometimes that you get to learn something every single time.

While playing with SSIS package, I was getting unique constraint error while data were getting inserted in SQL Server. Later I did further digging and found that it was due to German characters. Here is a simplified version of the problem. Will you be able to crack the puzzle?

You can use the SQL Server Management studio and run below three queries:

columny COLLATE SQL_Latin1_General_CP1_CI_AS AS 'First'
FROM (VALUES ('ß'),('SS') ) tablex(columny) ;
SELECT DISTINCT columny COLLATE Latin1_General_CI_AS AS 'Second'
FROM (VALUES ('ß'),('SS') ) tablex(columny) ;
FROM (VALUES (N'ß'),(N'SS') ) tablex(columny) ;

If you are not having access to SQL Server handy – here is a sample output for them:

ss puzzle 01 SQL SERVER   Puzzle   DISTINCT Values in NVARCHAR

Do you know the reason why ‘ß’ and ‘SS’ are same in nvarchar? Please comment and let me know. I will share surprise learning voucher with you.

Hope we will learn something together.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Enhancing Reference Data in Master Data Services – Notes from the Field #089

[Note from Pinal]: This is a 89th episode of Notes from the Field series. Master Data Services is one of the most important, but a very little explored feature of SQL Server. If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains why one should add referencing data to Master Data services to our enterprise. Read the experience of Reeves in his own words.

Reeves Smith SQL SERVER   Enhancing Reference Data in Master Data Services   Notes from the Field #089

In a previous post, I talked about adding reference data to Master Data Services and how it can enhance your Enterprise Data Warehouse. Through the Master Data Services add-in for Excel, I added reference data into a Master Data Services model. This model was created with data types that were inferred from the given values that were imported into the model. All of the data types were of basic types like strings or numeric.

In this post, I’m going to update the data model we created in the previous posts and add domain values to the column’s data types. Domain values are a way to restrict a column’s values to a defined list. This also provides a drop down for the column that restricts any free form text.

The steps are:

  • Create two entities that will be used for the domain values
  • Update the Customers entity to use the newly created entity

All of these step will be performed within Excel using the Master Data Services add-in for Excel.

Using the Demo model, created in the last post, we will update the MaritalStatus column with domain values.

Step 1 – Create Additional Entities

We will start with the MaritalStatus column of the Customers entity and create an additional entity called MaritalStatus. This entity will be used for the domain values in the MaritalStatus column of the Customers entity.

  1. Create an Excel worksheet with the reference data for MaritalStatus and provide a header for each of the columns in the data set. In this example we use Code and Name.

n 89 1 SQL SERVER   Enhancing Reference Data in Master Data Services   Notes from the Field #089

Figure 1 – MaritalStatus Excel worksheet data

  1. Select the data including the headers and click the Create Entity button from the Master Data tab in Excel.
  2. Enter the data for the following items:
    1. My data has headers: Checked
    2. Model: Demo
    3. Version: VERSION_1
    4. New entity name: MaritalStatus
    5. Code: Code
    6. Name (optional): Name

n 89 2 SQL SERVER   Enhancing Reference Data in Master Data Services   Notes from the Field #089

Figure 2 – Create Entity Dialog

  1. Click OK.

Step 2 – Update the Customers Entity

  1. Connect to the Customers Entity.

If you need help connecting to the Customer entity follow the below steps:

  1. From the Master Data tab click the Connect button and connect to the MDS instance with the Demo model.

n 89 3 SQL SERVER   Enhancing Reference Data in Master Data Services   Notes from the Field #089

Figure 3 – Connecting to MDS instance

  1. Select the Demo model and double click on the Customers Entity.

n 89 4 SQL SERVER   Enhancing Reference Data in Master Data Services   Notes from the Field #089

Figure 4 – The Master Data Explorer dialog

  1. Select the any cell in the MaritalStatus column of the Customer Entity.
  2. Click the Attribute Properties button.
  3. Change the Attribute type to Constrained list (Domain-based) and select the MartialStatus entity for Populate the Attribute with values from:

n 89 5 SQL SERVER   Enhancing Reference Data in Master Data Services   Notes from the Field #089

Figure 5 – Attribute Properties Dialog

Notice that the column has a different look with a constrained list and if a cell is selected, a drop down with the available values is now present. This keeps the users from adding values outside of the list.

n 89 6 SQL SERVER   Enhancing Reference Data in Master Data Services   Notes from the Field #089

Figure 6 – MaritalStatus as a Constrained List

Creating Additional Entities

Adding domain values to the other columns would require the same process.

Note: In the current dataset Education and Occupation does not have code values. One method to solve this creates an entity where the code and name contain the same values, see Figure 7.

n 89 7 SQL SERVER   Enhancing Reference Data in Master Data Services   Notes from the Field #089

Figure 7 – Education Entity


In the previous articles on Master Data Services we added reference data to a Master Data Service model. This data can be maintained through the web interface and Excel add-in by the subject matter experts (SME). By creating a robust model the SMEs are better able to create and maintain quality data.

Hope this helps.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Process ID X was killed by hostname ABC, host process ID Y

Errorlogs are an awesome place to learn something new every time. It springs up with some new errors which you might have not seen ever. I am a big believer of the fact that one needs to monitor and take a look at the logs from time to time. It is the best way to mitigate problems before they occur. Recently I noticed the below error in SQL Server ERRORLOG. I wanted to know the exact meaning of each and every placeholder in error message. Since I found it, I am sharing with you.

Error number: 18100

SELECT TEXT, severity, is_event_logged
FROM  sys.messages
WHERE message_id = 18100
AND   language_id = 1033

kill 01 SQL SERVER   Process ID X was killed by hostname ABC, host process ID Y

Process ID %d was killed by hostname %.*ls, host process ID %d.
As per my understanding %d stands for number and %ls stands for string.

Whenever a KILL command is issued in SQL Server, it is logged into SQL Server Errorlog.

  1. First placeholder would be the SPID which was KILL’ed.
  2. Second placeholder is the machine name from where the KILL command was issued. Note that host name can be obfuscated and not guaranteed to be exact name of the machine. While making connection, we can pass our own host name.
  3. Third placeholder is the client Process ID which has issued the kill command. This can be seen via Task Manager.

If we look at Errorlog, we can also find the SPID which has issued the kill command.

2015-07-14 05:00:00.290 spid52       Process ID 51 was killed by hostname BIGPINAL, host process ID 10044.
Killed by : SPID52
Killed : 51
Host Name – BIGPINAL
PID in Task Manager – 10044

kill 02 SQL SERVER   Process ID X was killed by hostname ABC, host process ID Y

I have seen few DBA putting schedule job to monitor blocking and kill it. In that case you would see Process ID from SQL Agent. One of the interesting piece which I was not able to answer was the question asked by a blog reader on http://blog.sqlauthority.com/2007/06/26/sql-server-explanation-and-example-four-part-name/

I am getting messages in errorlog as Process ID 400 was killed by hostname , host process ID 0.
there is no hostname , and process ID 0, when i checked for process id in task manager , 0 is for system idle process.

This made me think and I spoke to few local SQL Server Experts and they asked me the exact message in ERRORLOG and I got below as reply

2015-07-01 01:22:45.600 spid21s      Process ID 51 was killed by hostname , host process ID 0.

This means that system SPID 21s has killed the process 51 and since this is a system SPID, we would not see host name and host process.

kill 03 SQL SERVER   Process ID X was killed by hostname ABC, host process ID Y

Can you think of a way to reproduce this behavior and get similar message in SQL ERRORLOG where hostname is empty and host process ID is zero? If you can write the same over comments, it would be of great help for others too.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Querying Performance Counters from SQL Server

Troubleshooting SQL Server is something almost every single DBA has to go through in their lifespan. It sounds simple and often we are clueless on where to start. Here is a scenario, you have been told that SQL Server is slow or unresponsive. Now you need to troubleshoot and figure out what is happening. Sounds simple right? What would you do in this situation?

When I asked this scenario to one of my DBA friend, their answer was simple. “Pinal, the first thing is to log onto the server and open Task Manager. Next I will see the basic parameters of how the CPU, Memory and IO are doing. Based on what I see at a macro level; we will troubleshoot accordingly.”

This seemed to be a simple yet a logical way to see things. On further query, they did mention about using PerfMon counters as an ideal way to capture some of the current problems running inside SQL Server. Capture the numbers and then troubleshoot later with other tools like Extended Events, Profiler, Activity Monitor etc. This conversation interested me big time because I wanted to see how people query performance counters.

The basic thing is to initialize Perfmon.exe and look at the various counters. We can also create a Data Collection template and go ahead with using the same. I am sure most of you are aware about doing the same.

Let me take an unconventional approach to a simple requirement to query Memory Grants pending on a SQL Server box using other techniques.

SQL to Query Performance Counter

In the below query, we use two methods. One to query to the Performance Counters and other via the DMV.

-- Get memory grants pending (perfmon counter)
SELECT cntr_value AS memory_grants_pending
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Memory%Grants%Pending%';
-- Get memory grants pending (DMV)
SELECT COUNT(*) AS memory_grants_pending
FROM sys.dm_exec_query_memory_grants
WHERE wait_time_ms IS NOT NULL;

This metric defines the total amount of memory grants that are waiting for workspace memory, and if this value is consistently above zero it could be a sign of memory pressure, and it could be negatively impacting the querying process. It is recommended that reevaluation of memory allocations and/or available physical memory is taken into account in order to relieve memory pressure for the current SQL Server instance where this query is being run.

If there are memory related errors, then we are likely to see 8645 errors in our SQL Server Errorlog. How to troubleshoot SQL Server error 8645 is explained in the KB.

Query Counter Using PowerShell

As we have done in various blog posts, we can also use PowerShell to query counters too. A simple script I wrote to query looks like below:

Get-Counter -Counter "\SQLServer:Memory Manager\Memory Grants Pending"| SELECT-Object @{Name = "Memory Grants Pending" Expression = {$_.CounterSamples.CookedValue}}

Please let me know if you have every used any of these techniques in your environment to automate querying values from Performance Counters. How do you use these counter values to proactively mitigate problems in your environments? What are some of the techniques that you have been using?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Change Server Name?

In recent past I have renamed my VM and wanted to make sure that everything is fine. This renaming process is also needed for changing the server names called inside of SQL Server too. So I searched to know various places from where we can get host name and SQL Server name. Finally, I formed this query which can gather same details from various sources:

SELECT  HOST_NAME() AS 'host_name()',
@@servername AS 'ServerName\InstanceName',
SERVERPROPERTY('servername') AS 'ServerName',
SERVERPROPERTY('machinename') AS 'Windows_Name',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
SERVERPROPERTY('instanceName') AS 'InstanceName',
SERVERPROPERTY('IsClustered') AS 'IsClustered'

Most of these server properties have been there for a while and sometimes I think it requires a refresher like this to remember them again. When we run this query on the non-clustered machine, we should see only two distinguished names. Machine name and Instance name. If you have default instance, then Instance Name would be NULL in the query.

ren sql 01 SQL SERVER – How to Change Server Name?

If there was a rename of host, then you would see host_name would be different and that’s not something which should exist.

Note that Instance Name rename is unsupported. In named instance the server name is in format of ServerName\InstanceName. So, you can change it to NewName\InstanceName but NOT to ServerName\ NewInstanceName. I am sure this is a fantastic trivia to know.

If you find any mismatch, then you need to follow below steps:

  1. execute below to drop the current server name

EXEC sp_DROPSERVER 'oldservername'

  1. Execute below to add new server name. Make sure local is specified.

EXEC sp_ADDSERVER 'newservername', 'local'

  1. Restart SQL Services.
  2. Verify the new name using:
    2. SELECT * FROM sys.servers WHERE server_id = 0

I must point out that you should not perform rename if you are using:

  1. SQL Server is clustered.
  2. Using replication.
  3. Reporting Service is installed.

I would like to know if anyone has ever tried doing this in your environment? What is the typical reason for you doing the same? I hope this helps incase you get into a tangle like this. Let me know your thoughts via comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #028 – T-SQL Script to Detect SQL Server Version and Property

Here is a very basic question I just came across the other day. I was interviewing nearly 20 candidates and I found that only 5 of them could write the query (with the help of the internet).

Question: How to determine the version and properties of SQL Server with the help of T-SQL?

Answer: Here is the simple script which can list version of SQL Server:

SELECT SERVERPROPERTY('productversion') ProductVersion,
SERVERPROPERTY ('productlevel') ProductLevel,
SERVERPROPERTY ('edition') Edition

sqlversion Interview Question of the Week #028   T SQL Script to Detect SQL Server Version and Property

Reference: Pinal Dave (http://blog.sqlauthority.com)

POSTGRESQL – How to Create Function? – How to Declare Local Variable

I have written few courses on PostgreSQL on Pluralsight. You can watch list of all the courses over here.

Recently, after watching my courses one of the user who is familiar with SQL Server but just beginning with PostgreSQL asked me very interesting questions. Let us here the question in his own words.

“How to declare local variables in PostgreSQL? It is very easy to declare local variables in SQL Server but it seems not possible in PostgreSQL. Any suggestions?”

I loved this question as in PostgreSQL, there is no notion of much procedural language. In the recent version, it is getting better with some of the support, but overall, PostgreSQL is not easy to deal with when there are set of codes and one has to execute them in succession.

Let us see a simple example of SQL Server code:

SELECT @Int1 Col1;

sqldeclare POSTGRESQL   How to Create Function?   How to Declare Local Variable

Now let us see the same example in PostgreSQL.

IN Int1 integer,
OUT p_film_count integer)

SELECT DisplayFunction(1);

sqldeclare1 POSTGRESQL   How to Create Function?   How to Declare Local Variable

You can clearly see how PostgreSQL requires the user to do more work to accomplish simple task. I like PostgreSQL very much and I hope that it comes out with more support to procedural languages.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Puzzle – Working with functions to Concatenate columns

Every now and then I get pulled into coding review sessions and those are some of the fun times I every have working with T-SQL of any sorts. This blog is inspired by one such time where I saw at a customer location a code that was lengthy and in one location I was seeing close to 15 fields which were concatenated to form a single column as part of output.

I generally don’t get into the reasoning of it, but what was interesting is they were using ISNULL in front of every column because they were not sure how whole column values to be returned as NULL. To give you an idea, here is a mini-repro to the same problem.

emp_name NVARCHAR(200) NOT NULL,
emp_middlename NVARCHAR(200) NULL,
emp_lastname NVARCHAR(200) NOT NULL
INSERT INTO #temp VALUES( 'SQL', NULL, 'Authority' );
INSERT INTO #temp VALUES( 'SQL', 'Server', 'Authority' );
SELECT emp_name + emp_middlename + emp_lastname AS Result
FROM #temp;

Now, you can see the output by default will look like:

concatenate quiz 01 SQL SERVER   Puzzle   Working with functions to Concatenate columns

As you can see, the problem is getting the first value as NULL. Customers sometimes don’t want this behavior.

Question for the Day

What would you do in this situation? Would you add an ISNULL in front of every column because you are not sure which of these might be NULL? Can you think of a solution without using the ISNULL function?

Give me your solution over comments. It is very easy – trust me. In my example the customer was also trying to use various Datatypes concatenating into a single column.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Using Project Connections in SSIS – Notes from the Field #088

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Using Project Connections in SSIS.

Tim Mitchell SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088Linchpin People are database coaches and wellness experts for a data driven world. In this 88th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to use raw files in SSIS.

In SQL Server Integration Services, connection managers are used as gateways for most any external read and write operation. Connection managers are type- and format-specific, and in the case of relational database connection managers, they are usually specific to vendor (Oracle, DB2, etc.) as well.

In most use cases, the same connection will be used across multiple packages in the same project. In pre-2012 versions of SSIS, each package would have its own connection manager for every connection used in that package. Creating and maintaining all those connection managers could be time-consuming as the number of packages grows. In SQL Server 2012, Microsoft added project connections to SSIS, allowing for the creation of connections that were accessible across all packages in a project. Instead of having to create a copy of each connection manager in every package, developers can now simply create the connection at the project level. Project connections will automatically show up in the connection manager tray for all packages in that project.

n 88 1 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

As shown, any project connection automatically has the designation (project) prepended to the name to clearly indicate that it is a project connection. Those without this designation are package connections, and are only accessible from within that package.

Project connections will also appear in the Solution Explorer window, under the Connection Managers tab.

n 88 2 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

You can create a new project connection by right-clicking on the Connection Managers node shown above, and walking through the steps to build the connection. Similarly, you can edit or delete an existing project-level connection manager from this same window.

You can also promote an existing package connection to a project connection by right-clicking on the package connection and selecting Convert to Project Connection.

n 88 3 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

Coincidentally, you can also convert back to a package connection through a similar process. Right-clicking on a project connection will expose an option to Convert to Package Connection. However, you have to be careful when choosing this option. If you convert a project connection to a package connection, that connection will then be visible only in the package in which you are currently working. If you have used the connection in any other packages in that project, those operations will fail because the connection is no longer visible at the project level. You will get a warning message when you attempt to convert a project connection to a package connection.

n 88 4 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

Finally, if you are using project connections, you can still use dynamic properties such as expressions and parameters. Do be aware that, if you use parameters to configure a project connection, you must use project parameters rather than package parameters. The latter is not accessible beyond the scope of a single package, and therefore would not always be accessible for project connections. Fortunately, the UI for the expression builder limits you to only project parameters when configuring project connections.

n 88 5 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

In conclusion, the project connection in SSIS is an excellent tool for configuring connections at the project level to minimize the extra work required for sharing connections across multiple packages in a project.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)