How many users will a single CPU handle? (relates to licensing)

To figure out which licensing option for SQL to recommend to our customers,
we need to know how many processors we need. On MSDN I ran through a couple
of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
40K if you go with the Processor license (versus Server with User CALs). If i
could figure out how many users require one CPU, this would help me. You know
of any tools or capacity planning logic to help me?
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:363399
In article <C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com>, Emma
Nelson <EmmaNelson@.discussions.microsoft.com> wrote:

> To figure out which licensing option for SQL to recommend to our customers,
> we need to know how many processors we need. On MSDN I ran through a couple
> of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> 40K if you go with the Processor license (versus Server with User CALs). If i
> could figure out how many users require one CPU, this would help me. You know
> of any tools or capacity planning logic to help me?
This is what I used:
http://www.microsoft.com/sql/howtobuy/faq.asp
Vessel
|||"Emma Nelson" <EmmaNelson@.discussions.microsoft.com> wrote in message
news:C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com...
> To figure out which licensing option for SQL to recommend to our
customers,
> we need to know how many processors we need. On MSDN I ran through a
couple
> of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> 40K if you go with the Processor license (versus Server with User CALs).
Note, that's if you require Enterprise Server. Standard Server is only
$5K/cpu last I checked.

> If i
> could figure out how many users require one CPU, this would help me. You
know
> of any tools or capacity planning logic to help me?
Well, sounds like you've already done the capacity planning. Not sure what
you're looking for differently.
Perhaps there's tuning in your app you can do to be less CPU intensive?
sql

How many users will a single CPU handle? (relates to licensing)

To figure out which licensing option for SQL to recommend to our customers,
we need to know how many processors we need. On MSDN I ran through a couple
of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
40K if you go with the Processor license (versus Server with User CALs). If i
could figure out how many users require one CPU, this would help me. You know
of any tools or capacity planning logic to help me?In article <C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com>, Emma
Nelson <EmmaNelson@.discussions.microsoft.com> wrote:
> To figure out which licensing option for SQL to recommend to our customers,
> we need to know how many processors we need. On MSDN I ran through a couple
> of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> 40K if you go with the Processor license (versus Server with User CALs). If i
> could figure out how many users require one CPU, this would help me. You know
> of any tools or capacity planning logic to help me?
This is what I used:
http://www.microsoft.com/sql/howtobuy/faq.asp
--
Vessel|||I have read through this Web site and called MS Sales reps. Question still
remains... thanks for responding though.
"Vessel" wrote:
> In article <C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com>, Emma
> Nelson <EmmaNelson@.discussions.microsoft.com> wrote:
> > To figure out which licensing option for SQL to recommend to our customers,
> > we need to know how many processors we need. On MSDN I ran through a couple
> > of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> > 40K if you go with the Processor license (versus Server with User CALs). If i
> > could figure out how many users require one CPU, this would help me. You know
> > of any tools or capacity planning logic to help me?
> This is what I used:
> http://www.microsoft.com/sql/howtobuy/faq.asp
> --
> Vessel
>|||"Emma Nelson" <EmmaNelson@.discussions.microsoft.com> wrote in message
news:C05A85E8-DF34-4EA0-82AD-7E415DF3C79C@.microsoft.com...
> To figure out which licensing option for SQL to recommend to our
customers,
> we need to know how many processors we need. On MSDN I ran through a
couple
> of capacity planning tools, but it keeps saying I need 2 CPUs, which costs
> 40K if you go with the Processor license (versus Server with User CALs).
Note, that's if you require Enterprise Server. Standard Server is only
$5K/cpu last I checked.
> If i
> could figure out how many users require one CPU, this would help me. You
know
> of any tools or capacity planning logic to help me?
Well, sounds like you've already done the capacity planning. Not sure what
you're looking for differently.
Perhaps there's tuning in your app you can do to be less CPU intensive?

How many open connections does SQL server 2005 express allow to .mdf file

Hi Guys, how many connections does sql 2005 express allow to .mdf file. I stumpled on info on a microsoft site (actually a forum post on msdn) that sql server express allows only a single connection to an .mdf file.

Iam intrested in this because i developed reporting services reports in BIS and iam having problems. If i run a report from report manager, i have to wait for close to 20 minutes before i can be able to run my asp.net application agian. Else if i insert data or retrieve data from my asp.net application, then i have to wait again for 20 minutes or more before i can be able to run my reports else i will get an error that a connection can not be made to the database or that the database is being used by another process.

So in all cases, i have to wait for 20--30 minutes before a connection can be made to the .mdf file. i.e after running a report or after inserting or retrieving data from the database through my asp.net application.

Iam using sql sever 2005 express with advanced services and visual web developer on windows server 2003.

Any ideas or help.

Hi Nick,

Sql Express Allows multiple connection to a mdf file. The important thing to note down is are you using different connection string and the limit on sql express is set to 1. coz if u will change the connectionstring it will try to create a new connection and in case if the limit is set to 1. you need to wait till the earlier connection is close or inactive.

I have developed a asp.net site hosted on server with Sql express MDF file and i am able to open page from different browser at the same time and can get different response at the same time.

Hope this will help

Satya

|||

Thankssatya_tanwar,

Yes with my application here i can open different pages or the same page in very many windows and from very many computers but problems crop up when i try to run reports hosted on a reporting services report server from report manager. If the application is open and i try to run my reports i get an error that the database is being used by another process. Likewise if i run my reports before running the application, they run correctly but then iam unable to run the application, it will also tell me the database is closed. My work around so far is to wait for close to 30 minutes for the connection to open it self with out my intervation but this is undesirable for the users.

Then most importantly from your replyt, please give more light on the use of different connection strings. I have a connection string in my web.config file of my asp.net application. Then when designing sql server reporting services reports in business interlligence development studio, your prompted to provide a connection string to the database file your attaching and you either type or paste it manually or you click edit and browse to location of your database. If you browse to the database, the connection string is build for you and this is the approach i take. so i woould imagine that the connection strings will be the same in the web.config and for my report datasource because the location of the database is the same.

I had not thought about this connection string issue but iam going to verify if they match. In the mean time though, give me your take from what i have explained. I badly deadly need to deploy sql server reporting services.

sql

How good is your logic?

In relation to another post of mine:

http://forums.microsoft.com/msdn/ShowPost.aspx?postid=1658798&isthread=false&siteid=1

Seeing as I am well and truely stumped - I'm prepared to try look at it (with the help of you) in an entirely different light!

OK - scenario: Company with many 'cost centres', we want to know some details of staff turnover. The fiscal year starts 1 May and ends 31 April. Every fiscal year there is a count of staff 'on the books' to start that year. During that year people are employed and people leave employment. For every person they have an 'Employee Number', there various details, the cost centre they were assigned to, their starting date (date of employment) and their finishing date (date of employment termination).

Say we want to calculate staff turnover % as number of terminations divided by opening headcount. So this seems easy enough. The problem is finding the 'opening headcount' - well that's not true - it's a seemingly easy calculation - opening headcount = sum(starts) - sum(terminations) for what ever year. This can be done in excel quite quickly and easily. The problem lies in the fact that people join and people leave every year with a percentage of people remaining. All I have to work with for every record is the start date and finish date (for sakes of usability i've set - for the purposes of this cube - the finish date to 11/11/2222 if in the actual database the finish date is NULL - that is if an employment hasn't been terminated yet).

The way I have attacked it so far is according to the other thread (link above). I was hoping to simply use a sum(periodstodate)) for both starting dates and finishing dates and then subtract them. But I can't even get the darn sum(periodstodate)) to work properly!

So here I leave it to your wisdom! if you can shed some light - please do. If you want to know more - please ask!

Many thanks

Karl

You'll need to use the descendants function to calculate at the employee level. We had a similar problem calculating customer churn. I'll post the way we did it in the next few weeks (as I have to do this for employee churn as well).|||

Hmmm... Been reading up about descendants - still not sure how it would work - but I believe you would probably know better than I!

Have got the running sum to work now, so it now stands - how would I subtract a cube of column(cumulative start count), rows(FiscalStartDate) from cube column(cumulative finish count), rows(FiscalFinishDate)?

So I now have two calculated measures(members)

1: [Start Cumulative Count]

SUM(PERIODSTODATE([Start Date].[Fiscal].[(All)],

[Start Date].[Fiscal].CurrentMember),

[Measures].[Fact Employee Count])

And 2: [Finish Cumulative Count]

SUM(PERIODSTODATE([Finish Date].[Fiscal].[(All)],

[Finish Date].[Fiscal].CurrentMember),

[Measures].[Fact Employee Count])

Now you can't simply plonk a calculation of [Start Cumulative Count] - [Finish Cumulative Count], because what would you use on rows? The Start Date Hierarcy? Nope - because you would then have 'starting cumulative count' (correct) - 'Cumulative count of people who started and ended in that year' (incorrect) which = not the right answer!

Any thoughts?

|||

Another approach is to explode your data out when you build your fact table.

There are a could of variations to this approach, but based on what I understand of your issue at the moment you could insert a record for the start even and another for the end, storing a count in separate columns.

EmployeeID StartDate EndDate

========== ========= ========

1 1 Jan 07 31 Mar 07

2 1 Apr 05 NULL

Could be inserted into a fact table as

EmployeeID TimeID StartCount EndCount

========== ======== ========== ========

1 20070101 1 NULL

1 20070331 NULL 1

2 20050401 1 NULL

Then for any time period you could take the sum of the starts and subtract the sum of the ends to get to a head count. You can also get the number of new people or terminations between any arbitrary date range.

|||

Now that is some great logic - I kept trying to think of a way to explode out the fact table! Thanks! I'll give that a shot! Will keep you posted.

Karl

|||Another variation that is probably more flexible is to have a single HeadCount measure and something like an EventTypeID where you could track starts, finishes and other events like promotions, transfers etc.|||

I had already done that... Although I'm not usining it at the moment. I still use both startcount and endcount and a TerminationID (which doesn't neccessarily mean terminated - it consists of current employee, left, dismissed and change of contract).

However, I've hit yet another wall........

I now want to work ou t the average length of service for those on hand at teh beginning of each year. This is easy to do for those that have departed - I simply added another column onto the fact table called LengthService which simply used the DATEDIFF(day,start_date, finish_date) when populating it, I then summed the column, and divided the measure by the end count measure and divided again by 365 (to give average length of service in years).

The on hand poses a more complex problem. I have to take into account teh datediff between a start date and the currentmember date, but then I also need to take into account people that have left prior to the year in question... I'm sitting here stewing on a way to do this with nothing of substance yet...

Any thoughts?

Again - many thanks for all the great ideas and help so far - it is mostly appreciated!

Karl

|||

Example with descendants:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Cancel Sub USD]

AS

sum(filter(

(Descendants( [Customer].[SEGMENT - PARENT CO].currentmember,

[PARENT CO] ,SELF )),

([Fiscal Calendar].[YEAR - QUARTER - PERIOD - DATE].currentmember, [Measures].[Parent Company Distinct Count]) <

([Fiscal Calendar].[YEAR - QUARTER - PERIOD - DATE].prevmember, [Measures].[Parent Company Distinct Count])) *

[Measures].[USD AMT] *[Fiscal Calendar].[YEAR - QUARTER - PERIOD - DATE].prevmember )

,

FORMAT_STRING = "Currency",

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[New Sub Cnt]

AS

count(filter(

Descendants( [Customer].[SEGMENT - PARENT CO].currentmember,

[PARENT CO] ,SELF ),

([Fiscal Calendar].[YEAR - QUARTER - PERIOD - DATE].currentmember, [Measures].[Parent Company Distinct Count]) >

([Fiscal Calendar].[YEAR - QUARTER - PERIOD - DATE].prevmember, [Measures].[Parent Company Distinct Count])))

,

FORMAT_STRING = "#,#",

VISIBLE = 1;

|||

Karl,

I think what I would do is to add the Start and End dates as attributes to the employee dimension. This way for a given employee you would be able to extract the corresponding dates. You would need to use the descendants function to make sure you always drill down to the individual employee so that you can extract the corresponding dates.

I think logic like the following should work for you. I have typed it in off the top of my head, so I have not tested it, but I have put comments through it so hopefully you can follow my logic.

Code Snippet

AVG(

-- Get a set of all the employees below the currently selected member

-- which will be ALL Employees if no other member in the hierarchy is selected

DESCENDANTS([Employee].[Employees].Currentmember,,LEAVES)

,DATEDIFF(

[Employee].[Employees].Currentmember.Properties("StartDate").Value

-- we need to check the date diff between the earliest of the selected date

-- or the end date, if a member higher in the date hierarchy is selected

-- (like year, month or quarter) is selected I am using descendants to get the

-- set of dates underneath that member and then using tail to get the last one.

-- so if a month is selected it will do a datediff to the last day of the month

, IIF([Employee].[Employees].Currentmember.Properties("EndDate").Value

< TAIL(DESCENDANTS([Time].[Calendar].CurrentMember,,LEAVES),1).Item(0).MemberValue

,[Employee].[Employees].Currentmember.Properties("EndDate").Value

,TAIL(DESCENDANTS([Time].[Calendar].CurrentMember,,LEAVES),1).Item(0).MemberValue

)

)

)