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 recordset open ?

Hello,
I use VisualBasic 6, MSDE and ADO2.8.
I seek to know how many recordsets are open at the same time.
How much CPU (or memory) use they?
My goal is to know if all my recordset is well closed.
Thank you.
hi Sylvain,
Sylvain Aufrre wrote:
> Hello,
> I use VisualBasic 6, MSDE and ADO2.8.
> I seek to know how many recordsets are open at the same time.
> How much CPU (or memory) use they?
> My goal is to know if all my recordset is well closed.
> Thank you.
I do not know counters for such questions...
you can see active connections to the SQL Server instance, but not
recordsets, as they are part of the ADO design (better, the ADO counterpart
of OLE DB Rowsets)
and I do not think you should inspect for memory allocation/requirements in
order to check your clean-up code... you just have to review it and verify
you properly close and release objects as it should be
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you Andrea,
Ok, there is nothing to see open recordsets.
But, which tools allows seeing the application activity on MSDE data base ?
Which tables is opened? read? written? Who? When?
My goal is to know if my development team correctly releases the data base
objects.
I seek a "systematic" method to check that.
Thank-you to reply to these newbies questions !
"Andrea Montanari" wrote:

> hi Sylvain,
> Sylvain Aufrère wrote:
> I do not know counters for such questions...
> you can see active connections to the SQL Server instance, but not
> recordsets, as they are part of the ADO design (better, the ADO counterpart
> of OLE DB Rowsets)
> and I do not think you should inspect for memory allocation/requirements in
> order to check your clean-up code... you just have to review it and verify
> you properly close and release objects as it should be
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Sylvain,
Sylvain Aufrre wrote:
> Thank you Andrea,
> Ok, there is nothing to see open recordsets.
> But, which tools allows seeing the application activity on MSDE data
> base ? Which tables is opened? read? written? Who? When?
you can perhaps monitor active connections both via Enterprise Manager or by
executing sp_who filtering out for you required database...
actually read write operations depend on user's activity... if your user
form is simple waiting for user input you'll see no db activity at all...
tables are not opened... they are read and eventually written... they can
even bre read from cache as SQL Server try to keep them alive on cache once
read... writing conditions depends on database recovery model too, as dirty
pages can be flushed to the transaction log and stay there untill a backup
log or, with simple recovery model, be flushed to data pages at recurring
checkpoints...

> My goal is to know if my development team correctly releases the data
> base objects.
> I seek a "systematic" method to check that.
you can perhaps use the SQL Server Profiler running traces, and/or ad a
specific SQL Server counter on the System Monitor...
have a look at
http://msdn.microsoft.com/library/de..._perf_76cm.asp
for further info... and all counters are successively exploded..
but I do think the best check is code review...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

How many logical volumes for large database?

We are about to migrate a large database server (600+ GB data) onto new
hardware. It is currently on a 450 MHz quad CPU, 4 GB RAM box with local
SCSI storage, and will be moving to a new 3 GHz Quad CPU, 6 GB RAM box with
SAN attached storage. My question concerns how many logical volumes we
should have to support the various database components.
Currently, we have separate logical drives for the Data files, Indexes,
Logs, and TempDB. This was done for two reasons:
First, we had four available SCSI channels, so by creating four arrays each
on a different channel we increased disk I/O.
Second, we did it to reduce fragmentation. The database is used mainly for
running reports and analyses off of data from our main business application,
which is run on a VMS cluster. That system can't handle the performance hit
for all of the reporting, so once a week data is dumped from the mainframe
into the SQL Server. The rest of the week the SQL db is essentially
read-only. So the data volume is fairly static, growing on a weekly basis
but almost never shrinking. For performance reasons, the indexes are
dropped and recreated weekly, the logs are truncated and then grow, and of
course TempDB grows and shrinks as needed. By separating each component
onto a different logical drive, we should have less fragmentation.
Now that we are moving to a new architecture, the question has been raised
as to whether this is still the best setup, or if it just creates
unnecessary administrative overhead. Obviously we no longer get a
performance gain from multiple I/O channels, because all of the storage will
be accessed via the same fiber channel to the SAN.
As for the fragmentation issue, is it really that much of a problem? Would
it cause issues if we were to, say, combine data and indexes on the same
volume, or logs and TempDB?
The goal is to reduce administrative overhead. But since we have data
files, indexes, logs, and tempDB in any case, does it make a difference from
a management standpoint whether they are all on one volume or on four
separate ones? Would it be an issue either way from a backup/restore
standpoint?
Thanks,
Gary
Gary,
You can still benefit from multiple volumes on a SAN, specifically for data,
log, and tempdb. (Since it is essentially a read only db,
you apparently don't need a volume for backups.) Even with one HBA, having
independent sets of spindles for different types of IO (data, log, and
tempdb) usually provides some performance gain.
If the queries are active and make any use of temp tables, then you can
benefit from having tempdb on its own volume. The database log files will be
active when loading, so keeping them on their own volume should help with
load time.
I've seen many installations with that much or more data that don't use
separate volumes for indexes. If you never update the tables, and recreate
all the indexes weekly with each new load, you should not have problems with
defragmentation. But check anyway by using DBCC SHOWCONTIG.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Gary Dom" <domgATsutterhealthDOTorg@.no.spam> wrote in message
news:%230VstQAnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> We are about to migrate a large database server (600+ GB data) onto new
> hardware. It is currently on a 450 MHz quad CPU, 4 GB RAM box with local
> SCSI storage, and will be moving to a new 3 GHz Quad CPU, 6 GB RAM box
with
> SAN attached storage. My question concerns how many logical volumes we
> should have to support the various database components.
> Currently, we have separate logical drives for the Data files, Indexes,
> Logs, and TempDB. This was done for two reasons:
> First, we had four available SCSI channels, so by creating four arrays
each
> on a different channel we increased disk I/O.
> Second, we did it to reduce fragmentation. The database is used mainly
for
> running reports and analyses off of data from our main business
application,
> which is run on a VMS cluster. That system can't handle the performance
hit
> for all of the reporting, so once a week data is dumped from the mainframe
> into the SQL Server. The rest of the week the SQL db is essentially
> read-only. So the data volume is fairly static, growing on a weekly basis
> but almost never shrinking. For performance reasons, the indexes are
> dropped and recreated weekly, the logs are truncated and then grow, and of
> course TempDB grows and shrinks as needed. By separating each component
> onto a different logical drive, we should have less fragmentation.
> Now that we are moving to a new architecture, the question has been raised
> as to whether this is still the best setup, or if it just creates
> unnecessary administrative overhead. Obviously we no longer get a
> performance gain from multiple I/O channels, because all of the storage
will
> be accessed via the same fiber channel to the SAN.
> As for the fragmentation issue, is it really that much of a problem?
Would
> it cause issues if we were to, say, combine data and indexes on the same
> volume, or logs and TempDB?
> The goal is to reduce administrative overhead. But since we have data
> files, indexes, logs, and tempDB in any case, does it make a difference
from
> a management standpoint whether they are all on one volume or on four
> separate ones? Would it be an issue either way from a backup/restore
> standpoint?
> Thanks,
> Gary
>
>

How many logical volumes for large database?

We are about to migrate a large database server (600+ GB data) onto new
hardware. It is currently on a 450 MHz quad CPU, 4 GB RAM box with local
SCSI storage, and will be moving to a new 3 GHz Quad CPU, 6 GB RAM box with
SAN attached storage. My question concerns how many logical volumes we
should have to support the various database components.
Currently, we have separate logical drives for the Data files, Indexes,
Logs, and TempDB. This was done for two reasons:
First, we had four available SCSI channels, so by creating four arrays each
on a different channel we increased disk I/O.
Second, we did it to reduce fragmentation. The database is used mainly for
running reports and analyses off of data from our main business application,
which is run on a VMS cluster. That system can't handle the performance hit
for all of the reporting, so once a week data is dumped from the mainframe
into the SQL Server. The rest of the week the SQL db is essentially
read-only. So the data volume is fairly static, growing on a weekly basis
but almost never shrinking. For performance reasons, the indexes are
dropped and recreated weekly, the logs are truncated and then grow, and of
course TempDB grows and shrinks as needed. By separating each component
onto a different logical drive, we should have less fragmentation.
Now that we are moving to a new architecture, the question has been raised
as to whether this is still the best setup, or if it just creates
unnecessary administrative overhead. Obviously we no longer get a
performance gain from multiple I/O channels, because all of the storage will
be accessed via the same fiber channel to the SAN.
As for the fragmentation issue, is it really that much of a problem? Would
it cause issues if we were to, say, combine data and indexes on the same
volume, or logs and TempDB?
The goal is to reduce administrative overhead. But since we have data
files, indexes, logs, and tempDB in any case, does it make a difference from
a management standpoint whether they are all on one volume or on four
separate ones? Would it be an issue either way from a backup/restore
standpoint?
Thanks,
GaryGary,
You can still benefit from multiple volumes on a SAN, specifically for data,
log, and tempdb. (Since it is essentially a read only db,
you apparently don't need a volume for backups.) Even with one HBA, having
independent sets of spindles for different types of IO (data, log, and
tempdb) usually provides some performance gain.
If the queries are active and make any use of temp tables, then you can
benefit from having tempdb on its own volume. The database log files will be
active when loading, so keeping them on their own volume should help with
load time.
I've seen many installations with that much or more data that don't use
separate volumes for indexes. If you never update the tables, and recreate
all the indexes weekly with each new load, you should not have problems with
defragmentation. But check anyway by using DBCC SHOWCONTIG.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Gary Dom" <domgATsutterhealthDOTorg@.no.spam> wrote in message
news:%230VstQAnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> We are about to migrate a large database server (600+ GB data) onto new
> hardware. It is currently on a 450 MHz quad CPU, 4 GB RAM box with local
> SCSI storage, and will be moving to a new 3 GHz Quad CPU, 6 GB RAM box
with
> SAN attached storage. My question concerns how many logical volumes we
> should have to support the various database components.
> Currently, we have separate logical drives for the Data files, Indexes,
> Logs, and TempDB. This was done for two reasons:
> First, we had four available SCSI channels, so by creating four arrays
each
> on a different channel we increased disk I/O.
> Second, we did it to reduce fragmentation. The database is used mainly
for
> running reports and analyses off of data from our main business
application,
> which is run on a VMS cluster. That system can't handle the performance
hit
> for all of the reporting, so once a week data is dumped from the mainframe
> into the SQL Server. The rest of the week the SQL db is essentially
> read-only. So the data volume is fairly static, growing on a weekly basis
> but almost never shrinking. For performance reasons, the indexes are
> dropped and recreated weekly, the logs are truncated and then grow, and of
> course TempDB grows and shrinks as needed. By separating each component
> onto a different logical drive, we should have less fragmentation.
> Now that we are moving to a new architecture, the question has been raised
> as to whether this is still the best setup, or if it just creates
> unnecessary administrative overhead. Obviously we no longer get a
> performance gain from multiple I/O channels, because all of the storage
will
> be accessed via the same fiber channel to the SAN.
> As for the fragmentation issue, is it really that much of a problem?
Would
> it cause issues if we were to, say, combine data and indexes on the same
> volume, or logs and TempDB?
> The goal is to reduce administrative overhead. But since we have data
> files, indexes, logs, and tempDB in any case, does it make a difference
from
> a management standpoint whether they are all on one volume or on four
> separate ones? Would it be an issue either way from a backup/restore
> standpoint?
> Thanks,
> Gary
>
>sql