CPU/Core Prio. on SQL Server Express Instance?

Hi,
I am currently using a single SQL Server Express Instance on a Core 2 Duo
box.
I know that the Express version is limited to use 1 CPU/Core.
I now want to setup a second SQL Server Express instance for unrelated
purposes.
Is it possible to give one instance Core 0 prio, and the Second the Core 1
prio, or do they ALWAYS (MUST/ONLY) use Core 0?
Also, if I don't set it specifically will Windows manage it properly?
I guess my main question in detail is:
Is the Express version build in such a way that it will always use the first
core/cpu regardless how many there are? For example, if I have 3 instances
running, would they all fight for cycles on the first core/cpu?
If not, do I have to do/set something to make it work properly?
Thanks for any Info. on this!
Regards,
FrankExpress will only create one scheduler. A scheduler is by default not bound to a particular core, it
can "float" between the cores. However, there is no guarantee that your two express instances will
always be on different cores.
However, you can use sp_configure and the "affinity mask" option to specify that instance it is tied
to core 0 and instance 2 is tied to core 1. This way you've configured the two instances to use
different cores.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Osterberg" <raven7370@.yahoo.com> wrote in message
news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am currently using a single SQL Server Express Instance on a Core 2 Duo box.
> I know that the Express version is limited to use 1 CPU/Core.
> I now want to setup a second SQL Server Express instance for unrelated purposes.
> Is it possible to give one instance Core 0 prio, and the Second the Core 1 prio, or do they ALWAYS
> (MUST/ONLY) use Core 0?
> Also, if I don't set it specifically will Windows manage it properly?
> I guess my main question in detail is:
> Is the Express version build in such a way that it will always use the first core/cpu regardless
> how many there are? For example, if I have 3 instances running, would they all fight for cycles on
> the first core/cpu?
> If not, do I have to do/set something to make it work properly?
> Thanks for any Info. on this!
> Regards,
> Frank
>|||> Also, if I don't set it specifically will Windows manage it properly?
To add on to Tibor's response, the OS can schedule work on any available
core when the SQL Server affinity mask is not set. You do not need to do
anything special to make this happen.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Frank Osterberg" <raven7370@.yahoo.com> wrote in message
news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am currently using a single SQL Server Express Instance on a Core 2 Duo
> box.
> I know that the Express version is limited to use 1 CPU/Core.
> I now want to setup a second SQL Server Express instance for unrelated
> purposes.
> Is it possible to give one instance Core 0 prio, and the Second the Core 1
> prio, or do they ALWAYS (MUST/ONLY) use Core 0?
> Also, if I don't set it specifically will Windows manage it properly?
> I guess my main question in detail is:
> Is the Express version build in such a way that it will always use the
> first core/cpu regardless how many there are? For example, if I have 3
> instances running, would they all fight for cycles on the first core/cpu?
> If not, do I have to do/set something to make it work properly?
> Thanks for any Info. on this!
> Regards,
> Frank
>|||Great! Thank you very much!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eI1NqL7dIHA.4728@.TK2MSFTNGP03.phx.gbl...
> Express will only create one scheduler. A scheduler is by default not
> bound to a particular core, it can "float" between the cores. However,
> there is no guarantee that your two express instances will always be on
> different cores.
> However, you can use sp_configure and the "affinity mask" option to
> specify that instance it is tied to core 0 and instance 2 is tied to core
> 1. This way you've configured the two instances to use different cores.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Frank Osterberg" <raven7370@.yahoo.com> wrote in message
> news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I am currently using a single SQL Server Express Instance on a Core 2 Duo
>> box.
>> I know that the Express version is limited to use 1 CPU/Core.
>> I now want to setup a second SQL Server Express instance for unrelated
>> purposes.
>> Is it possible to give one instance Core 0 prio, and the Second the Core
>> 1 prio, or do they ALWAYS (MUST/ONLY) use Core 0?
>> Also, if I don't set it specifically will Windows manage it properly?
>> I guess my main question in detail is:
>> Is the Express version build in such a way that it will always use the
>> first core/cpu regardless how many there are? For example, if I have 3
>> instances running, would they all fight for cycles on the first core/cpu?
>> If not, do I have to do/set something to make it work properly?
>> Thanks for any Info. on this!
>> Regards,
>> Frank
>|||Ok, i was hoping it would be like that. Thank you!!!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:D1D2B4EC-1D77-4D1E-941C-9E2E0F985F8C@.microsoft.com...
>> Also, if I don't set it specifically will Windows manage it properly?
> To add on to Tibor's response, the OS can schedule work on any available
> core when the SQL Server affinity mask is not set. You do not need to do
> anything special to make this happen.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Frank Osterberg" <raven7370@.yahoo.com> wrote in message
> news:uVFYNK6dIHA.748@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I am currently using a single SQL Server Express Instance on a Core 2 Duo
>> box.
>> I know that the Express version is limited to use 1 CPU/Core.
>> I now want to setup a second SQL Server Express instance for unrelated
>> purposes.
>> Is it possible to give one instance Core 0 prio, and the Second the Core
>> 1 prio, or do they ALWAYS (MUST/ONLY) use Core 0?
>> Also, if I don't set it specifically will Windows manage it properly?
>> I guess my main question in detail is:
>> Is the Express version build in such a way that it will always use the
>> first core/cpu regardless how many there are? For example, if I have 3
>> instances running, would they all fight for cycles on the first core/cpu?
>> If not, do I have to do/set something to make it work properly?
>> Thanks for any Info. on this!
>> Regards,
>> Frank
>

CPU Utiliization reaches 100% when using ExcelPivot Table to browse AS 2005 Cube

CPU Utiliization reaches 100% when using ExcelPivot Table to browse AS 2005 Cube. Only one user is using the cube. Its a single CPU with SQL Server running on the same box. Is there any configuration parameters I should be changing ?

Thanks

SelvaHi Selva,

This is nothing to do with your question. But I was wondering if you have been able to drill down hierarchy levels from your cube in your excel pivot table? Because I can't. Have you tried that. Also, I will say, that I had similar issue browsing a cube from the pivot table, but I had to filter my pivot table, so I wouldn't be pulling so many rows (3 million). Filter it down and see how it responds.

Couple of questions = )

1.
Does anyone know the max number of Databases a single MSSQL 2000 enterprise machine can handle ??
2.
Does anyone have an idea on how you could use the DTS in MSSQL server to kick a script off on a UNIX machine ??
Chris1. 32,767
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp

Counting Rows

I want to define a measure which is count of rows in a fact table satisfying certain criteria. I just need a single value and hence this measure need not be additive from any dimension.

There are 2 possible scenarios -

(1) The criteria involves columns from the fact table itself (Example: [Fact Table].[Transaction Type] = 1 or 2 or 3)

(2) The criteria involves columns from dimension tables (Example: [Dimension Table].[Transaction Category] = 'D')

I am novice to SS BI and I have tried-out various solutions involving MDX Queries, Calculated Member, Named Set etc. but not successful. Kindly suggest me, how to achieve this.

Thanks & Regards.

Is my question so naive?|||

Maybe the best solution (for performance and smart use) should be to create an additive measure.

Try it in this way:

1) Create a Named Calculation in your fact table in your data source view

2) In the Expression write a CASE to test your condition to have 1=True and Null=False, something like this:

CASE
WHEN ([Transaction Type] = 1) THEN 1
WHEN ([Transaction Type] = 2) THEN 1
WHEN ([Transaction Type] = 3) THEN 1

WHEN ([Transaction Category] = 'D') THEN 1
ELSE NULL
END

3) In your cube create an additive measure based on your Named Calculation

Now you should use directly your measure in every MDX query.

Let me know if it work fine.

Francesco

|||Another approach is to create a row count measure and create a dimension based on Transation Type in addition to the with the dimension containing Transaction Category. You can then filter the count on Transaction Type and Transaction Category at run-time. Or, if you never wanted to see a certain type of transaction, you could put in a named query in your DSV or a query binding in your partition that filters out those rows.

Counting Rows

I want to define a measure which is count of rows in a fact table satisfying certain criteria. I just need a single value and hence this measure need not be additive from any dimension.

There are 2 possible scenarios -

(1) The criteria involves columns from the fact table itself (Example: [Fact Table].[Transaction Type] = 1 or 2 or 3)

(2) The criteria involves columns from dimension tables (Example: [Dimension Table].[Transaction Category] = 'D')

I am novice to SS BI and I have tried-out various solutions involving MDX Queries, Calculated Member, Named Set etc. but not successful. Kindly suggest me, how to achieve this.

Thanks & Regards.

Is my question so naive?|||

Maybe the best solution (for performance and smart use) should be to create an additive measure.

Try it in this way:

1) Create a Named Calculation in your fact table in your data source view

2) In the Expression write a CASE to test your condition to have 1=True and Null=False, something like this:

CASE
WHEN ([Transaction Type] = 1) THEN 1
WHEN ([Transaction Type] = 2) THEN 1
WHEN ([Transaction Type] = 3) THEN 1

WHEN ([Transaction Category] = 'D') THEN 1
ELSE NULL
END

3) In your cube create an additive measure based on your Named Calculation

Now you should use directly your measure in every MDX query.

Let me know if it work fine.

Francesco

|||Another approach is to create a row count measure and create a dimension based on Transation Type in addition to the with the dimension containing Transaction Category. You can then filter the count on Transaction Type and Transaction Category at run-time. Or, if you never wanted to see a certain type of transaction, you could put in a named query in your DSV or a query binding in your partition that filters out those rows.