Database Hammer & Stress testing

Hello all
I am trying to stress test an SQL2000 server running on Windows 2003.
I am using the reskit utility database hammer.
The utility creates a table with 10,000,000 rows in it fine but when I try
to use the query/update part of the tool(procmaster.exe), it fails and I get
an error: 'Problem encountered during processing -2147217913'.
There is plenty of space in my log and data files etc.
Does anybody know what might be going on?
Thanks in advance.
Jane
PS I have also left a copy of this post in the sqlserver tools group.
Hi
Sounds something like:
http://support.microsoft.com/default...b;en-us;265166
You may want to run profiler to see where it is having problems.
John
"Jane Reynolds" <nospam@.please.com> wrote in message
news:%23i9J6wU1EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello all
> I am trying to stress test an SQL2000 server running on Windows 2003.
> I am using the reskit utility database hammer.
> The utility creates a table with 10,000,000 rows in it fine but when I try
> to use the query/update part of the tool(procmaster.exe), it fails and I
> get an error: 'Problem encountered during processing -2147217913'.
> There is plenty of space in my log and data files etc.
> Does anybody know what might be going on?
> Thanks in advance.
> Jane
> PS I have also left a copy of this post in the sqlserver tools group.
>
|||Thanks
It looks like it is a code problem iin the utility then. I have done some
more research and found that there is a bug in the code. I don't know VB
though to try to edit it so will have to think of something else.
Jane
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23ZiKizV1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi
> Sounds something like:
> http://support.microsoft.com/default...b;en-us;265166
> You may want to run profiler to see where it is having problems.
> John
> "Jane Reynolds" <nospam@.please.com> wrote in message
> news:%23i9J6wU1EHA.2676@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
The article does not relate to a SQL Server back end, therefore if you did
profile this you can get the exact statement that fails and see what error
message Query Analyser would give you. If it is a datatype problem then you
may want to remove the column from the test or possibly install a different
version of MDAC.
John
"Jane Reynolds" <nospam@.please.com> wrote in message
news:OkyHRvY1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks
> It looks like it is a code problem iin the utility then. I have done some
> more research and found that there is a bug in the code. I don't know VB
> though to try to edit it so will have to think of something else.
> Jane
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23ZiKizV1EHA.1404@.TK2MSFTNGP11.phx.gbl...
>
sql

Database Hammer & Stress testing

Hello all
I am trying to stress test an SQL2000 server running on Windows 2003.
I am using the reskit utility database hammer.
The utility creates a table with 10,000,000 rows in it fine but when I try
to use the query/update part of the tool(procmaster.exe), it fails and I get
an error: 'Problem encountered during processing -2147217913'.
There is plenty of space in my log and data files etc.
Does anybody know what might be going on?
Thanks in advance.
Jane
PS I have also left a copy of this post in the sqlserver tools group.Hi
Sounds something like:
http://support.microsoft.com/defaul...kb;en-us;265166
You may want to run profiler to see where it is having problems.
John
"Jane Reynolds" <nospam@.please.com> wrote in message
news:%23i9J6wU1EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello all
> I am trying to stress test an SQL2000 server running on Windows 2003.
> I am using the reskit utility database hammer.
> The utility creates a table with 10,000,000 rows in it fine but when I try
> to use the query/update part of the tool(procmaster.exe), it fails and I
> get an error: 'Problem encountered during processing -2147217913'.
> There is plenty of space in my log and data files etc.
> Does anybody know what might be going on?
> Thanks in advance.
> Jane
> PS I have also left a copy of this post in the sqlserver tools group.
>|||Thanks
It looks like it is a code problem iin the utility then. I have done some
more research and found that there is a bug in the code. I don't know VB
though to try to edit it so will have to think of something else.
Jane
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23ZiKizV1EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Hi
> Sounds something like:
> http://support.microsoft.com/defaul...kb;en-us;265166
> You may want to run profiler to see where it is having problems.
> John
> "Jane Reynolds" <nospam@.please.com> wrote in message
> news:%23i9J6wU1EHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||Hi
The article does not relate to a SQL Server back end, therefore if you did
profile this you can get the exact statement that fails and see what error
message Query Analyser would give you. If it is a datatype problem then you
may want to remove the column from the test or possibly install a different
version of MDAC.
John
"Jane Reynolds" <nospam@.please.com> wrote in message
news:OkyHRvY1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks
> It looks like it is a code problem iin the utility then. I have done some
> more research and found that there is a bug in the code. I don't know VB
> though to try to edit it so will have to think of something else.
> Jane
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23ZiKizV1EHA.1404@.TK2MSFTNGP11.phx.gbl...
>

database design question

Hello all

I have been asked to create a database that has a users table, news table and a groups table. I'm new to this so I'm having a bit of difficulty mapping out the database design, and was hoping someone here could offer some input.

The idea is that the users will add their own news stories, and assign the stories to certain groups that HAVE been set (default groups) for them already like friends/work/public. But the users can ALSO create extra groups if they need to. So my problem is figuring out the best structure for the group's table.

What I have come up with is this...

Users Table
===========

UserID UserName
1 John
2 Mike
3 Sarah

Groups Table
============

GroupID Group Type UserID
1 Public 1
2 Work 1
3 Friends 1
4 Public 2
5 Work 2
6 Friends 2
7 Public 3
8 Work 3
9 Friends 3
10 FAMILY 2

(you'll notice that mike created his new group called family. The other groups will be automatically created when a new user is added to the system. Their new USERID will be assigned to a public, work and family groupID)

News Table
==========

NewsID UserID GroupID NewsStory
1 3 8 Blah, blah news...
2 3 3 Blah, blah news...
3 2 2 Blah, blah news...
4 1 2 Blah, blah news...

(Each user will be able to assign his/her news story to a certain group)

So my concern is the group's table. There's a lot of data repeating itself, but I can't think of a good way to store newly added groups by users. Is there a better way of doing this?

Thanks for your help!

Honestly, That is the best way to design it. That is the structure of how it should be done 'by the book.' I am pretty sure there is no other way.

It seems like allot of dat is repeated but you are doing it better than most becuase you are storing ID's rather than actually storing the persons name. It saves allot of space. Keep it up and you shall be a pretty good DBA anytime soon.

|||

Hi

The users table and the News table looks fine -- but the Groups table structure, as you rightly observe, does not look very robust. In database design lingo it is not normalized. You would face insert, update and delete anomalies.

It is best to have just one row for each group in the groups table. Hence, you may want to have just one row each for Public, Work and Friends.

Now, you say that users can create extra groups. In this instance, Mike, for example has created the group FAMILY. Is the FAMILY group available only to Mike? Can Sarah, for example, create a FAMILY group as well? I would recommend that the latter not be the case. There should be only one group FAMILY (or better, Family) and the 'Created User' could be Mike. The row created by Mike will be available for use by all other users (think about this -- whether it matches your requirements) Thus your table design would be

GroupID, GroupDescription, CreatedUser
1 Public
2 Work
3 Friends
4 Family 2

NOTE -- The CreatedUser column could be left null (by convention) for seeded groups (default groups).

Hope this helps and do let me know your views on the suggestion. It may not be the best possible solution for your circumstances.

Good Luck!

Fouwaaz

|||

Hi,

Your structure is good. Don't change anything. You can create T-SQL script for creation default groups, so you don't need to do it manually.

|||

Hello all.

Thank you all for your input! I'm going to leave it as is and see how it handles.

Thanks again!

|||

...Sorry sql is not one of my strong points either

If I wanted to select all storys by a user I would user...

SELECT
N.[NewsStory]
FROM
[NewsTable] AS N
INNER JOIN [UsersTable] AS U ON U.[User_ID] = N.[UserID]
WHERE
N.[UserID] = 'number'

but how would I go about selecting only storys for a certain group as well?

What I mean is, how would I select a users newsstorys for say friends only?

thanks again

|||

just do another inner join like you have it. Are you using Visual Studio for this? they have a query builder built in to the program that would allow you to build awesome queries.

database design - keys

Hello All
I am designing a database, or rather redesigning a very old database and
have a question regarding setting up key fields. The old database has a
table called Equipment with two fields:
Equipment Code - text 8
Equipment Description - text 50
In the new design I will have a table called Manufacturers that will have an
Equipment Code field, which will link to the Equipment table to get the
description. My question is, should I make a new Integer key field called
say EquipID which is what would get stored in the Manufacturers table or
should I simply use the Equipment Code field as the key? What are the
advantages/disadvantages to each method?
I have several other tables with a similar situation where the 'Code' is
unique but there are more fields in these tables.
Thanks,
GerryThis is a subject that has spawned many heated debates. Here's my take on
it:
In your case, just use the codes that exist.
In general, if your data has a natural key, use it. If the natural key is a
composite key of sufficient length/complexity (a totally subjective
determination) there might be a good reason to use a surrogate. However,
you MUST also enforce the uniqueness of the natural key! I am not against
the use of surrogate keys at all, but they should be used only after much
careful thought and consideration. Surrogate keys tend, in the hands of the
inexperienced, to lend a false sense of security ("Of course I don't have
any duplicates, my surrogate key assures that!")
Since you have a simple natural key, there is really no reason not to use
it. Adding a surrogate key just creates more data...
"News" <gerrydyck@.shaw.ca> wrote in message
news:Q7cSb.328534$X%5.134270@.pd7tw2no...

quote:

---

> Hello All
> I am designing a database, or rather redesigning a very old database and
> have a question regarding setting up key fields. The old database has a
> table called Equipment with two fields:
> Equipment Code - text 8
> Equipment Description - text 50
> In the new design I will have a table called Manufacturers that will have

---

an

quote:

---

> Equipment Code field, which will link to the Equipment table to get the
> description. My question is, should I make a new Integer key field called
> say EquipID which is what would get stored in the Manufacturers table or
> should I simply use the Equipment Code field as the key? What are the
> advantages/disadvantages to each method?
> I have several other tables with a similar situation where the 'Code' is
> unique but there are more fields in these tables.
> Thanks,
> Gerry
>

---

|||There are no general rules or norms which recommend a specific datatype for
a key.
The considerations to select a good key are often misunderstood. They
include stability (column values rarely change), simplicity (so that
relational operations can be effective), familiarity (meaningful or commonly
understood by the user) and irreducibility (no proper subset of key column
be another key). A good design can tradeoff certain characteristics in favor
of others to tackle specific issues with regard to key selection.
In a precisely modeled system, a key is chosen only based on the rules
defined at the business model & key selection involves only logical
considerations.
However, the implementation of databases using popular SQL DBMSs, generally
favors the usage of narrow keys for query efficiency, due to their smaller
size at the physical level. This may often fall under the criteria of
simplicity (mentioned above), but shuffling keys just for performance sake
is not always a good idea.
Anith|||Thanks Don. This is my first SQL database and sometimes with new programs I
tend to overthink a solution. For this one, I will be sticking with a
natural key.
Gerry
"Don Peterson" <no1@.nunya.com> wrote in message
news:uVsFYAq5DHA.2556@.TK2MSFTNGP09.phx.gbl...

quote:

---

> This is a subject that has spawned many heated debates. Here's my take on
> it:
> In your case, just use the codes that exist.
> In general, if your data has a natural key, use it. If the natural key is

---

a

quote:

---

> composite key of sufficient length/complexity (a totally subjective
> determination) there might be a good reason to use a surrogate. However,
> you MUST also enforce the uniqueness of the natural key! I am not against
> the use of surrogate keys at all, but they should be used only after much
> careful thought and consideration. Surrogate keys tend, in the hands of

---

the

quote:

---

> inexperienced, to lend a false sense of security ("Of course I don't have
> any duplicates, my surrogate key assures that!")
> Since you have a simple natural key, there is really no reason not to use
> it. Adding a surrogate key just creates more data...
> "News" <gerrydyck@.shaw.ca> wrote in message
> news:Q7cSb.328534$X%5.134270@.pd7tw2no...
have[QUOTE]
> an
called[QUOTE]
>

---

|||Thanks for your input Anith.
Gerry
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ex9OK3q5DHA.1596@.TK2MSFTNGP10.phx.gbl...

quote:

---

> There are no general rules or norms which recommend a specific datatype

---

for

quote:

---

> a key.
> The considerations to select a good key are often misunderstood. They
> include stability (column values rarely change), simplicity (so that
> relational operations can be effective), familiarity (meaningful or

---

commonly

quote:

---

> understood by the user) and irreducibility (no proper subset of key column
> be another key). A good design can tradeoff certain characteristics in

---

favor

quote:

---

> of others to tackle specific issues with regard to key selection.
> In a precisely modeled system, a key is chosen only based on the rules
> defined at the business model & key selection involves only logical
> considerations.
> However, the implementation of databases using popular SQL DBMSs,

---

generally

quote:

---

> favors the usage of narrow keys for query efficiency, due to their smaller
> size at the physical level. This may often fall under the criteria of
> simplicity (mentioned above), but shuffling keys just for performance sake
> is not always a good idea.
> --
> Anith
>

---