Distinguishing Processors in Server Properties

I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
It has 4 dual core processors (with hyperthreading) and I would like to
scale it back to 2 dual cores. When I look at the processors in server
properties, it lists:
CPU0
CPU1
etc...
My assumption is that 0-3 are all from the first CPU (1 * dual core *
hyper thread). But I do not want to assume, I want to know which
processors I am picking for use and I can not find any documentation on
how those names map. Does anybody know?
PT
The more I think about it, I am thinking SQL 2005 does not know the
details of the physical processors, and is just told by the OS that
there are X logical processors. Anyhow, I would still like to know.
|||You are correct. SQL sees logical processors only. Plus, there aren't ane
"real" and "virtual" processors. They are all virtual. You have to turn
off hyperthreading at the BIOS level to get "real" processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163116200.511102.129730@.k70g2000cwa.googlegr oups.com...
> The more I think about it, I am thinking SQL 2005 does not know the
> details of the physical processors, and is just told by the OS that
> there are X logical processors. Anyhow, I would still like to know.
>
|||Paul
Check out this link - it provides an decent explanation
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
Cheers
Bil
"Geoff N. Hiten" wrote:

> You are correct. SQL sees logical processors only. Plus, there aren't ane
> "real" and "virtual" processors. They are all virtual. You have to turn
> off hyperthreading at the BIOS level to get "real" processors.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul T." <weluvpaul@.hotmail.com> wrote in message
> news:1163116200.511102.129730@.k70g2000cwa.googlegr oups.com...
>
>
|||You shouldn't worry about whch core maps to which socket. Each core is a real
processor, and there is not much you can or should do with that mapping. So
let's just call a core a physical processor. However, there is some
interesting issue with mapping a physical processor to a hyperthreaded (or
logical) one.
Ideally, you should be able to tell because BIOS assigns the first 'logical'
processor for each physical one before looping back to assign the second one
after it has gone through all the physical processors. This is all good.
However, Perfmon lists and numbers the logical processors differently. So you
could end up seeing CPU consumption on each alternate processor in Perfmon.
Microsoft has a little utility called htdump.exe that is distributed as part
of MPS Reports. This utility gives you the physical-to-logical mapping as
reported by Perfmon. Now, this was something we encountered about a year ago.
Things may have changed since then.
Linchi
"Paul T." wrote:

> I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>

Distinguishing Processors in Server Properties

I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
It has 4 dual core processors (with hyperthreading) and I would like to
scale it back to 2 dual cores. When I look at the processors in server
properties, it lists:
CPU0
CPU1
etc...
My assumption is that 0-3 are all from the first CPU (1 * dual core *
hyper thread). But I do not want to assume, I want to know which
processors I am picking for use and I can not find any documentation on
how those names map. Does anybody know?
PTThe more I think about it, I am thinking SQL 2005 does not know the
details of the physical processors, and is just told by the OS that
there are X logical processors. Anyhow, I would still like to know.|||You are correct. SQL sees logical processors only. Plus, there aren't ane
"real" and "virtual" processors. They are all virtual. You have to turn
off hyperthreading at the BIOS level to get "real" processors.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163116200.511102.129730@.k70g2000cwa.googlegroups.com...
> The more I think about it, I am thinking SQL 2005 does not know the
> details of the physical processors, and is just told by the OS that
> there are X logical processors. Anyhow, I would still like to know.
>|||Paul
Check out this link - it provides an decent explanation
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
--
Cheers
Bil
"Geoff N. Hiten" wrote:
> You are correct. SQL sees logical processors only. Plus, there aren't ane
> "real" and "virtual" processors. They are all virtual. You have to turn
> off hyperthreading at the BIOS level to get "real" processors.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul T." <weluvpaul@.hotmail.com> wrote in message
> news:1163116200.511102.129730@.k70g2000cwa.googlegroups.com...
> > The more I think about it, I am thinking SQL 2005 does not know the
> > details of the physical processors, and is just told by the OS that
> > there are X logical processors. Anyhow, I would still like to know.
> >
>
>|||Inter recommends that HT is implemented such as:
They you have two physical processors. This will surface as 4 processors to the OS. So, the
processors are presented to the OS should be:
CPU0 - Phy CPU0
CPU1 - Phy CPU1
CPU2 - Phy CPU0
CPU3 - Phy CPU1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163112570.239503.214220@.i42g2000cwa.googlegroups.com...
>I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>|||You shouldn't worry about whch core maps to which socket. Each core is a real
processor, and there is not much you can or should do with that mapping. So
let's just call a core a physical processor. However, there is some
interesting issue with mapping a physical processor to a hyperthreaded (or
logical) one.
Ideally, you should be able to tell because BIOS assigns the first 'logical'
processor for each physical one before looping back to assign the second one
after it has gone through all the physical processors. This is all good.
However, Perfmon lists and numbers the logical processors differently. So you
could end up seeing CPU consumption on each alternate processor in Perfmon.
Microsoft has a little utility called htdump.exe that is distributed as part
of MPS Reports. This utility gives you the physical-to-logical mapping as
reported by Perfmon. Now, this was something we encountered about a year ago.
Things may have changed since then.
Linchi
"Paul T." wrote:
> I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>sql

Distinguishing Processors in Server Properties

I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
It has 4 dual core processors (with hyperthreading) and I would like to
scale it back to 2 dual cores. When I look at the processors in server
properties, it lists:
CPU0
CPU1
etc...
My assumption is that 0-3 are all from the first CPU (1 * dual core *
hyper thread). But I do not want to assume, I want to know which
processors I am picking for use and I can not find any documentation on
how those names map. Does anybody know?
PTThe more I think about it, I am thinking SQL 2005 does not know the
details of the physical processors, and is just told by the OS that
there are X logical processors. Anyhow, I would still like to know.|||You are correct. SQL sees logical processors only. Plus, there aren't ane
"real" and "virtual" processors. They are all virtual. You have to turn
off hyperthreading at the BIOS level to get "real" processors.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163116200.511102.129730@.k70g2000cwa.googlegroups.com...
> The more I think about it, I am thinking SQL 2005 does not know the
> details of the physical processors, and is just told by the OS that
> there are X logical processors. Anyhow, I would still like to know.
>|||Paul
Check out this link - it provides an decent explanation
http://rentacoder.com/CS/blogs/real.../04/28/477.aspx
Cheers
Bil
"Geoff N. Hiten" wrote:

> You are correct. SQL sees logical processors only. Plus, there aren't an
e
> "real" and "virtual" processors. They are all virtual. You have to turn
> off hyperthreading at the BIOS level to get "real" processors.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul T." <weluvpaul@.hotmail.com> wrote in message
> news:1163116200.511102.129730@.k70g2000cwa.googlegroups.com...
>
>|||Inter recommends that HT is implemented such as:
They you have two physical processors. This will surface as 4 processors to
the OS. So, the
processors are presented to the OS should be:
CPU0 - Phy CPU0
CPU1 - Phy CPU1
CPU2 - Phy CPU0
CPU3 - Phy CPU1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1163112570.239503.214220@.i42g2000cwa.googlegroups.com...
>I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>|||You shouldn't worry about whch core maps to which socket. Each core is a rea
l
processor, and there is not much you can or should do with that mapping. So
let's just call a core a physical processor. However, there is some
interesting issue with mapping a physical processor to a hyperthreaded (or
logical) one.
Ideally, you should be able to tell because BIOS assigns the first 'logical'
processor for each physical one before looping back to assign the second one
after it has gone through all the physical processors. This is all good.
However, Perfmon lists and numbers the logical processors differently. So yo
u
could end up seeing CPU consumption on each alternate processor in Perfmon.
Microsoft has a little utility called htdump.exe that is distributed as part
of MPS Reports. This utility gives you the physical-to-logical mapping as
reported by Perfmon. Now, this was something we encountered about a year ago
.
Things may have changed since then.
Linchi
"Paul T." wrote:

> I have a SQL 2005 64 bit Standard box running on Windows Server 2003.
> It has 4 dual core processors (with hyperthreading) and I would like to
> scale it back to 2 dual cores. When I look at the processors in server
> properties, it lists:
> CPU0
> CPU1
> etc...
> My assumption is that 0-3 are all from the first CPU (1 * dual core *
> hyper thread). But I do not want to assume, I want to know which
> processors I am picking for use and I can not find any documentation on
> how those names map. Does anybody know?
> PT
>

Distinct Count Problem

I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message.

Can anyone help with this? Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key).

David

Both these approaches should work, the MDX Calc would not perform as well as adding the product class attribute to the fact table. Can you tell us exact what errors you were getting.

With the fact table approach I think you will need to create a second measure group (based on the same fact table) as you can only have one distinct count measure in each measure group.

|||

Darren,

In order to create a second fact group based on the same table, would I need to create an alias table in the dsv first. This approach seems like it would be more resource intensive since I'm technically adding another 700,000 records with the "new" fact table.

David

|||

Yes, that's right you would have to scan through the fact table again, but each distinct count measure you create means another scan through the fact table. It depends how often you are processing your cube as this sort of volume should only take minutes. Technically you don't need to create an alias table, but I think it is the only way to do this through the UI, through an XMLA script you can create multiple measure groups off the one dsv object.

The alternative is to use the DistinctCount() function in a calculated measure, you would have to test it and see, but it can be quite slow depending on the size of your queries.