MultiUsers Question

We had a debate on designing a database .The use case is;

an application that we will rent to other companies from our own datacenter on the web.They will all use the same database schema.We found 3 alternatives for our solution.But we are not sure which one is the best.

We are expecting 20.000 insurance agencies to use the database composed of 33 tables.And they will all have their unique data but sometimes for reporting we might union their data.

ALTERNATIVES

1.Make 20.000 database instances.Every agency will have their own database instance.

2.Make one database instance , put a database ownerID(int) field to every table.And use appropiate indexes.*or table partioning

3.Make one database instance , and create 33 tables for every database owner.So total number of tables in the datase equals to numberofUsers X 33

Did anyone tested this situation?

*an article I read on the web said partining is allowed to 1000 per table and it is slow in SQL 2005.(I am confused)

http://www.sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk

In common, I am a fan of putting all the data together in one database. based on the information that you provided it is hard to decide wheter you need separated database / instances or not.

There are several things which you should consider:

-Organisational structure (You could also split the database up in regions / geographical or country based)
-Maintainance (Due to different timezones / work loads on the databases you might have different maintainance windows to do something)
-Load balancing
-Scaling
-Availability
-Estimated workload

etc.

I think that we both agree that you wouldn′t put 20000 dbs on on server, or create thousends of instances (the limit is 25 for one computer)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

MultiUsers Question

We had a debate on designing a database .The use case is;

an application that we will rent to other companies from our own datacenter on theweb.They will all use thesame database schema.We found 3 alternatives for our solution.But we are not sure which one is the best.

We are expecting 20.000 insurance agencies to use the database composed of 33 tables.And they will all have their unique data but sometimes for reporting we might union their data.

ALTERNATIVES

1.Make 20.000 database instances.Every agency will have their own database instance.

2.Make one database instance , put a database ownerID(int) field to every table.And use appropiate indexes.*or table partioning

3.Make one database instance , and create 33 tables for every database owner.So total number of tables in the datase equals to numberofUsers X 33

Did anyone tested this situation?

*an article I read on the web said partining is allowed to 1000 per table and it is slow in SQL 2005.(I am confused)

http://www.sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk

I would choose #1 possibly if it would be important for backup purposes to keep the data "separate". Cross reporting would be difficult, but not totally impossible -- if you can work from a "snapshot". It would be easiest to run a process to pull the data you need from all the agencies into a singular table structure just for the report(s), that has the added OwnerID column. You could do this either as some nightly process, or place a trigger on each of the tables that need to be reported on to replicate the changes to the common table. It would be a completely redundant dataset, so the backup of it isn't all that important, other than the table structure, and any special views/stored procedures/extra tables you may have in there.

This approach also makes it a lot easier to insure that a mistake isn't made whereby another agencies data isn't presented to a different agency. If you have a DBA (Which I'm guessing you do not, or you are the DBA), then they should be able to provide you with views and stored procedures that the application programmers are allowed to use to access the data and hence shouldn't be able to get themselves into trouble.

This type of setup is a bit more complicated to set up, but, it does effectively partition the tables for cross-agency reports.

4. Make one database instance, put a database ownerID(int) field in one (or more) of the tables. ID's are global (across agencies), so that a ownerID is redundant in tables that use an ID (or GUID) as it's primary key that is created in a different table.

|||hmm I am sorry but I did not understand how your 4 th approach differs from the 2 nd approach?|||

First 20,000 insurance agencies is not many, if you take them to be users, it means you have 20,000 users. In the US insurance is divided into Property and Casuality for home and cars policies and Life and Health. So just put them into the type of policy sold or by location and it becomes just 20,000 users. I have told you before you need a DDL(data definition language) expert.

If you don't have it installed check the Pubs sample database for ideas if you decide to use location to separate them because car insurance is sold by state drivers license. Hope this helps.

|||

The 4th approach differs from the 2nd in that if you have say these two tables:

Users->UserID,Name, etc.
Policies->PolicyID(IDENTITY),OwnerID(a UserID),SalesmanID(a UserID),PolicyTypeID, etc.
PolicyRider->PolicyID,RiderTypeID,etc.

In approach 2, the tables would look like this:

Users->AgencyID,UserID,Name, etc.
Policies->AgencyID,PolicyID,OwnerID,SalesmanID,PolicyTypeID,etc
PolicyRider->AgencyID,PolicyID,RiderTypeID,etc.

Where in approach 4, the tables would look like this if you allow any user to belong to multiple Agencies:

Users->UserID,Name,etc
UserAgencies->UserID,AgencyID
Policies->AgencyID,PolicyID, ...
PolicyRider->PolicyID,RiderTypeID,etc (NOTE No AgencyID, because the PolicyRider record has a reference to the Policies table entry which already contains a AgencyID)

Or like this, if no Users can be in multiple agencies:

Users->AgencyID,UserID, ...
Policies->PolicyID, ... (NOTE No AgencyID because each policy is owned by a user, and each user can only be in a single agency, this policy must belong to the agency the user is in)
PolicyRider->PolicyRider->PolicyID,RiderTypeID,etc (NOTE No AgencyID, because the PolicyRider record has a reference to the Policies table entry which has a reference to a Users table entry which contains a AgencyID)

You may want to attach the AgencyID to the Policy instead of the User, in these cases, but the idea is that so long as the "parent" of any relationship already has an AgencyID, the children do not, since you can imply their AgencyID by referencing their (singular) parent's AgencyID.

|||

if no Users can be in multiple agencies:

Users->AgencyID,UserID, ...
Policies->PolicyID, ... (NOTE No AgencyID because each policy is owned by a user, and each user can only be in a single agency, this policy must belong to the agency the user is in)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I have read for 4 times.But did not understand how will I know that a record is owned by that AGENCY or by that USER.

maybe it is too late and I am really tired...is there anyuserID on the Policies table?

PS:I really do wanna implement a simple accountability pattern for the user system but performant.So the users can be in agencies but I will define that by simple attributeTypes in database not using the relations...

|||Sorry, yes. I was assuming that the Policies table would also have a OwnerID field (that's a UserID) or an AgentID (also a UserID), or some other field that you would use that to tie the record back to a particular user which is then tied to an AgencyID.

Multi-select problem with Excel as front-end

Hello,

I am trying to find a solution to a very elusive problem.

I have reviewed carefully every aspects of my cube as well as my test case and made only little progress.

When using Excel to connect to an AS2005 SP2 cube, if I try to select multiple items in a page selector, I get the grand-total of the cube instead of the total for my sub-selection. What I do not get is that it works for dim attributes with few members and it doe snot with dim attributes with more members.

The most frustrating part is that the bug exist ONLY in Excel 2003 (SP2).

I try the exact same cube in its XL03 and its Xl07 version, for the exact same view and setting I see that Excel is generating quite a different query in 03 and 07.

I have no control over the way Excel create the query. I tried to change things in the cube to coax XL03 to generate a query that works and I cannot get it right.

I cannot use XL2007 in production due to corporate policies.

Is there any way we could get a service pack for XL03, or any way to define the cube to force XL03 to generate correct query?

I cannot reproduce this behavior in the AdventureWorks sample cube, probably because it has not enough data.

Thanks for your kind attention,

Philippe

XL 2007 Correct query is as follow:

Code Snippet

SELECT NON EMPTY
Hierarchize(AddCalculatedMembers({DrilldownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM (SELECT ({[PTI3 CD].[All].[PAA],[PTI3 CD].[All].[PAB]})
ON COLUMNS FROM [ST_Crawl])
WHERE ([Data Switch].[Data Switch].&[On],[Exclusions].[Exclusions].&[Enabled],
[Quarters RSD].[Quarter 00].&[200703],
[Products].[All],[Measures].[Crawl RSD]) CELL PROPERTIES VALUE

XL 2003 query not working is as follow:

Code Snippet

WITH MEMBER
[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'
SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]
WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],
[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],
[PTI3 CD].[XL_QZX], [Products].[All])

Hi Philippe,

Not a solution, but maybe another data point - does this modification to the XL 2003 query still not work right?

Code Snippet

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers(

{DrillDownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [ST_Crawl]
WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],
[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],
{[PTI3 CD].[All].[PAB], [PTI3 CD].[All].[PAA]}, [Products].[All])

|||

I tried your suggestion and get the same issue, it returns the total.

If I try to query the immediate parent level then I get correct result with just one curious clue, the All weeks item would be "Error" but the data I need is just OK in that case as shown bellow. So in XL03 for level 3 it is wrong and for level 2 it is right while the same is right in Xl07 for both level 2 and 3. Very out of my limited undestanding.

This query for level 2 returns correct result. I do not see much difference except for the order of the last segment of the query. i tried all type of things like changing the dimension and hierarchies in every possible way and still no go with XL03.

i took great care of cleaning the underlying data up to perfection in that particular cube however this problem is a problem I am experiencing in many cubes and Despite creating logical keys, set the Cardinality and the RelationshipType as well as getting a rid of all star schema type of dimension, I still hit the wall. Note that level 3 (PTI3) has 217 members and that any level bellow that with more members will fail with the same issue.

I also tried to change the visual total property on the server side and the 2 options available on the Excel side, no luck.

Not that these cubes use calculated members, however even if I try a pure measure it still bug. So what could I do? Create a cube with no calcualte measures and same dimension structure? But still XL07 works while XL03 does not.

Should I post the cube code?

Code Snippet

WITH MEMBER

[PTI2 CD].[XL_QZX] AS 'Aggregate ( { [PTI2 CD].[All].[PB] , [PTI2 CD].[All].[PA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],

[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],

[PTI3 CD].[All], [PTI2 CD].[XL_QZX], [Products].[All])

|||"Note that level 3 (PTI3) has 217 members and that any level bellow that with more members will fail with the same issue" - but I thought that PTl3 is a different dimension than PTl2. Could you explain how these "levels" are related, and their structures? As far as the cube script, you could post the sections that relate to the query. For example, if [Crawl RSD] is a calculated measure - and any scoped calculations which apply as well.|||

Guys,

I cannot post the dimension code, too big, however I am getting closer to understand this Excel 2003 problem with cubes.

Here is a query that works very well.

Works

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Data Switch].[Data Switch].&[On], [Pti2 CD Desc].[All],

[Quarters RSD].[Quarter 00].[All], [PTI3 CD].[XL_QZX])

Here a query which does not work, it returns the grand total. The difference is in red.

Does not work

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Data Switch].[Data Switch].&[On], [Pti2 CD Desc].[All],

[Quarters RSD].[Quarter 00].[All],

[Products Hierarchy].[All], [PTI3 CD].[XL_QZX])

The way this section of the dimension is build is as follow.

1 Hierarchy called Products Hierachy with Business, PTI2 CD Desc, PTI3 CD, Product ID

- Product ID is the dimension key, it has an attribute relationship ROOT PART ID rigid, many

Other dimension attributes are

- ROOT PART ID attribute relationship PTI3 CD rigid, many

- PTI3 CD attribute relationship PTI2 CD Desc, rigid, many

- PTI2 cd Desc Attribute relationship Business, rigid, many

- Business

The problem is that if I have both the hierachy and the attributes, excel (2003 only) will include conflictual limits in its query. If I remove the hierarchy it work.

I can use the hierachy when the PTI3 attribute is in the excel however to use PTI3 I must remove the hierarchy...

Users likes both direct access to any attribute and hierarchy browsing, I must educate them not to have both at the same time.

I tried to rebuild the cube from scratch with no calculated measures, no go. I spotted another cube where I have the exact same dimension structure and where it works. I will investigate this to see if there is a difference in the data or structure.

Any idea?|||

One more clue.

If i remove the time dimension from the view, Sub-Select works when both the attribute and the hierarchy are in the cube.

See this

Code Snippet

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All Products].[PAB] ,

[PTI3 CD].[All Products].[PAA] } )'

SELECT FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Exclusions].[All Products].[Enabled],

[Data Switch].[Data Switch].&[On], [Products Hierarchy].[All Products],

[Pti2 CD Desc].[All Products],

[PTI3 CD].[XL_QZX])

sql

Multi-select problem with Excel as front-end

Hello,

I am trying to find a solution to a very elusive problem.

I have reviewed carefully every aspects of my cube as well as my test case and made only little progress.

When using Excel to connect to an AS2005 SP2 cube, if I try to select multiple items in a page selector, I get the grand-total of the cube instead of the total for my sub-selection. What I do not get is that it works for dim attributes with few members and it doe snot with dim attributes with more members.

The most frustrating part is that the bug exist ONLY in Excel 2003 (SP2).

I try the exact same cube in its XL03 and its Xl07 version, for the exact same view and setting I see that Excel is generating quite a different query in 03 and 07.

I have no control over the way Excel create the query. I tried to change things in the cube to coax XL03 to generate a query that works and I cannot get it right.

I cannot use XL2007 in production due to corporate policies.

Is there any way we could get a service pack for XL03, or any way to define the cube to force XL03 to generate correct query?

I cannot reproduce this behavior in the AdventureWorks sample cube, probably because it has not enough data.

Thanks for your kind attention,

Philippe

XL 2007 Correct query is as follow:

Code Snippet

SELECT NON EMPTY
Hierarchize(AddCalculatedMembers({DrilldownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM (SELECT ({[PTI3 CD].[All].[PAA],[PTI3 CD].[All].[PAB]})
ON COLUMNS FROM [ST_Crawl])
WHERE ([Data Switch].[Data Switch].&[On],[Exclusions].[Exclusions].&[Enabled],
[Quarters RSD].[Quarter 00].&[200703],
[Products].[All],[Measures].[Crawl RSD]) CELL PROPERTIES VALUE

XL 2003 query not working is as follow:

Code Snippet

WITH MEMBER
[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'
SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]
WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],
[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],
[PTI3 CD].[XL_QZX], [Products].[All])

Hi Philippe,

Not a solution, but maybe another data point - does this modification to the XL 2003 query still not work right?

Code Snippet

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers(

{DrillDownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [ST_Crawl]
WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],
[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],
{[PTI3 CD].[All].[PAB], [PTI3 CD].[All].[PAA]}, [Products].[All])

|||

I tried your suggestion and get the same issue, it returns the total.

If I try to query the immediate parent level then I get correct result with just one curious clue, the All weeks item would be "Error" but the data I need is just OK in that case as shown bellow. So in XL03 for level 3 it is wrong and for level 2 it is right while the same is right in Xl07 for both level 2 and 3. Very out of my limited undestanding.

This query for level 2 returns correct result. I do not see much difference except for the order of the last segment of the query. i tried all type of things like changing the dimension and hierarchies in every possible way and still no go with XL03.

i took great care of cleaning the underlying data up to perfection in that particular cube however this problem is a problem I am experiencing in many cubes and Despite creating logical keys, set the Cardinality and the RelationshipType as well as getting a rid of all star schema type of dimension, I still hit the wall. Note that level 3 (PTI3) has 217 members and that any level bellow that with more members will fail with the same issue.

I also tried to change the visual total property on the server side and the 2 options available on the Excel side, no luck.

Not that these cubes use calculated members, however even if I try a pure measure it still bug. So what could I do? Create a cube with no calcualte measures and same dimension structure? But still XL07 works while XL03 does not.

Should I post the cube code?

Code Snippet

WITH MEMBER

[PTI2 CD].[XL_QZX] AS 'Aggregate ( { [PTI2 CD].[All].[PB] , [PTI2 CD].[All].[PA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],

[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],

[PTI3 CD].[All], [PTI2 CD].[XL_QZX], [Products].[All])

|||"Note that level 3 (PTI3) has 217 members and that any level bellow that with more members will fail with the same issue" - but I thought that PTl3 is a different dimension than PTl2. Could you explain how these "levels" are related, and their structures? As far as the cube script, you could post the sections that relate to the query. For example, if [Crawl RSD] is a calculated measure - and any scoped calculations which apply as well.|||

Guys,

I cannot post the dimension code, too big, however I am getting closer to understand this Excel 2003 problem with cubes.

Here is a query that works very well.

Works

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Data Switch].[Data Switch].&[On], [Pti2 CD Desc].[All],

[Quarters RSD].[Quarter 00].[All], [PTI3 CD].[XL_QZX])

Here a query which does not work, it returns the grand total. The difference is in red.

Does not work

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Data Switch].[Data Switch].&[On], [Pti2 CD Desc].[All],

[Quarters RSD].[Quarter 00].[All],

[Products Hierarchy].[All], [PTI3 CD].[XL_QZX])

The way this section of the dimension is build is as follow.

1 Hierarchy called Products Hierachy with Business, PTI2 CD Desc, PTI3 CD, Product ID

- Product ID is the dimension key, it has an attribute relationship ROOT PART ID rigid, many

Other dimension attributes are

- ROOT PART ID attribute relationship PTI3 CD rigid, many

- PTI3 CD attribute relationship PTI2 CD Desc, rigid, many

- PTI2 cd Desc Attribute relationship Business, rigid, many

- Business

The problem is that if I have both the hierachy and the attributes, excel (2003 only) will include conflictual limits in its query. If I remove the hierarchy it work.

I can use the hierachy when the PTI3 attribute is in the excel however to use PTI3 I must remove the hierarchy...

Users likes both direct access to any attribute and hierarchy browsing, I must educate them not to have both at the same time.

I tried to rebuild the cube from scratch with no calculated measures, no go. I spotted another cube where I have the exact same dimension structure and where it works. I will investigate this to see if there is a difference in the data or structure.

Any idea?|||

One more clue.

If i remove the time dimension from the view, Sub-Select works when both the attribute and the hierarchy are in the cube.

See this

Code Snippet

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All Products].[PAB] ,

[PTI3 CD].[All Products].[PAA] } )'

SELECT FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Exclusions].[All Products].[Enabled],

[Data Switch].[Data Switch].&[On], [Products Hierarchy].[All Products],

[Pti2 CD Desc].[All Products],

[PTI3 CD].[XL_QZX])

Multiple tempdb files to no. of CPUs in SQL 2005

I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT

Multiple tempdb files to no. of CPUs in SQL 2005

I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT

Multiple tempdb files to no. of CPUs in SQL 2005

I know in SQL 2000, it was recommended to have the same number of tempdb
files as the number of CPUs.
Is it still the case for SQL 2005 ?
Hassan
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
I think it depends on what your application does againts sql server
"Hassan" <hassan@.hotmail.com> wrote in message
news:e2R59sQyHHA.1208@.TK2MSFTNGP05.phx.gbl...
>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
> Is it still the case for SQL 2005 ?
>
|||On Wed, 18 Jul 2007 00:14:10 -0700, "Hassan" <hassan@.hotmail.com>
wrote:

>I know in SQL 2000, it was recommended to have the same number of tempdb
>files as the number of CPUs.
>Is it still the case for SQL 2005 ?
According to Books On Line (BOL, also known as The Documentation) the
answer is Yes.
"Create as many files as needed to maximize disk bandwidth. Using
multiple files reduces tempdb storage contention and yields
significantly better scalability. However, do not create too many
files because this can reduce performance and increase management
overhead. As a general guideline, create one data file for each CPU on
the server (accounting for any affinity mask settings) and then adjust
the number of files up or down as necessary. Note that a dual-core CPU
is considered to be two CPUs."
Roy Harvey
Beacon Falls, CT

Multiple table or matrix under one grouping

Is it possible to have multiple tables or matrixes under one header grouping. I'm having a case where two tables need to be under one grouping (like "Sports vehicle) and under that "sports vehicle" I have two very different tables and on it goes for each grouping (next one like "Off road vehicle), etc.

Is there anyway to do this. I can make this work with one table using the table grouping.

Any suggestions or ideas?

Thanks.

Enkht,

Yes, It possible to have a Matrix Group = "Sports Vehicles" and the have type of "Off road", "Soup Up", "Etc" - It believe you can achieve your results my making Sport Vehicles your Group rows and "Type of" your Group Columns.

Ham