Showing posts with label single. Show all posts
Showing posts with label single. Show all posts

Thursday, March 29, 2012

How output all Stored Procedures to a table or file

I wish to ultimately have the content of all stored procedures related to a database in a single ASCII file for review. Is that doable? If so, how?
Thanks,
PeterRight-click your database in Enterprise Manager, select All tasks, Generate Sql script, there you can choose All stored procedures and other objects/options. mojza|||Fantastic Mojza - thanks for the help!

Peter|||Along this same line, this is what we do in order to generate script files that we then place into SourceSafe for version control. I am just learning that end of the department's process, but it seems to me I see stored procs at the system level for version control stuff, but need to look farther into that to see what other stuff we might use.

The script-to-VSS solution seems cheesy to me, but without taking this to be a "How do I do it" question, I'd just like to ask, is there an easier way? I can go look in BOL, but just saw the thread and was reminded of our local cheese factory, so thought I'd ask for quick-n-dirty impressions.|||Use VBA with SQL-DMO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_2yi7.asp), possibly within DTS (http://msdn.microsoft.com/library/en-us/dtssql/dts_basic_5zg3.asp)?

-PatP

Monday, March 26, 2012

How much memory is allotted to a single user connection?

Hi, everybody!
I wonder how much memory space is allotted to a single user connection.
I know MS SQL Server 6.5 allots 37KB of memory space to a single connection.
And I heard that MS SQL 2000 allots 500KB of memory space to a single
connection.
Is it true?
Thanks in advance.Where did u get this stat from ... Will be interested to see the same ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>|||When in doubt, always first check the BOL. Under the section "Memory used by
SQL Server 2000 Objects Specifications", the BOL indicates that:
The memory used by a use connection uses 12 KB + (3 * Network Packet Size),
where Network Packet Size is controlled by the 'network packet size'
configuration option. The default is 4KB.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>

How much memory can SQL Enterprise use?

Hi,
I've heard that SQL Enterprise can use up to 4gigs of RAM. Windows 2000
Server can address up to 4gigs of RAM in a single process, but since 2gigs
of that address space is "reserved" -- this means that SQL Server Enterprise
can really only use a maximum 1.7 to 2 gigs of RAM in the SQL Server process
when running on Win2000... My question is: If I install Windows 2003 and
stick SQL Enterprise on that machine, how much memory will SQL be able to
use (in a given process). MSFT's site says that Win2003 can make use of 4
gigs of memory, but will I run into the same problem that Win2000 has in so
far as the first 2gigs of the process' virtual memory space is "reserved"?
Thanks.
DavidSQL Server under W2K Advanced server can use up to 8 GB of
RAM. Under W2K Datacenter it is 32 GB. With W2K3
Enterprise Edition it's 32 GB for 32-bit, 64-bit is 64 GB.
(And so on ...)
To use the advanced memory under 32-bit, you need to use
the /PAE switch and turn AWE on in SQL Server. However,
the memory is not dynamic - it is a fixed static amount.
Under 64-bit, all memory is dynamic.
If you need > 2 GB for SQL but not more then 3 GB, you do
not have to enable /PAE or AWE, but just set /3GB in the
OS, and SQL Server can use up to 3 GB with no problem.
These are the ways you get SQL Server to recognize more
than 2 GB of memory.
>--Original Message--
>Hi,
> I've heard that SQL Enterprise can use up to 4gigs of
RAM. Windows 2000
>Server can address up to 4gigs of RAM in a single
process, but since 2gigs
>of that address space is "reserved" -- this means that
SQL Server Enterprise
>can really only use a maximum 1.7 to 2 gigs of RAM in the
SQL Server process
>when running on Win2000... My question is: If I install
Windows 2003 and
>stick SQL Enterprise on that machine, how much memory
will SQL be able to
>use (in a given process). MSFT's site says that Win2003
can make use of 4
>gigs of memory, but will I run into the same problem that
Win2000 has in so
>far as the first 2gigs of the process' virtual memory
space is "reserved"?
>Thanks.
>David
>
>.
>|||Thanks for the information. I've done a bit of research regarding the
PAE and 3GB switches that you've mentioned and I have a few followup
questions/comments:
- First of all, it appears that these switches aren't available in the
standard version of W2K Server. The Advanced version (or greater) is
required. Is there a smooth upgrade path from standard W2K Server to
Advanced? In other words, can I just install advanced on top of my current
W2K Server, or must I format the drive and do a clean installed of Advanced?
- /PAE is an OS-specific switch, not SQL-specific. Setting this switch
means that the OS can address more than 4 gigs of physical memory. Is this
correct?
- /3G is an OS-specific switch. Setting this switch means that a given
process can access a maximum of 3 gigs of memory as opposed to the standard
2 gigs of memory. Could you please elaborate on this switch? It seems
partially redundant to /PAE. If I've switched on /PAE, why would I want /3G?
- AWE isn't a switch, but an API that can be used by individual
applications such as SQL Server. SQL Server uses AWE to address memory
beyond 4gigs. The memory is reserved for exclusive access by the AWE client
(in my case, SQL Server). Is this correct?
So for a machine that has 6 gigs of RAM:
- I *do* need the /PAE switch because the OS needs to address beyond 4
gigs of RAM.
- I *do* need SQL Server to run in AWE mode because it needs to access
to more than 4 gigs of RAM.
- Do I need /3gb?
Thanks for the clarification...
David
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:09c201c3a9a8$97307100$a301280a@.phx.gbl...
> SQL Server under W2K Advanced server can use up to 8 GB of
> RAM. Under W2K Datacenter it is 32 GB. With W2K3
> Enterprise Edition it's 32 GB for 32-bit, 64-bit is 64 GB.
> (And so on ...)
> To use the advanced memory under 32-bit, you need to use
> the /PAE switch and turn AWE on in SQL Server. However,
> the memory is not dynamic - it is a fixed static amount.
> Under 64-bit, all memory is dynamic.
> If you need > 2 GB for SQL but not more then 3 GB, you do
> not have to enable /PAE or AWE, but just set /3GB in the
> OS, and SQL Server can use up to 3 GB with no problem.
> These are the ways you get SQL Server to recognize more
> than 2 GB of memory.
> >--Original Message--
> >Hi,
> >
> > I've heard that SQL Enterprise can use up to 4gigs of
> RAM. Windows 2000
> >Server can address up to 4gigs of RAM in a single
> process, but since 2gigs
> >of that address space is "reserved" -- this means that
> SQL Server Enterprise
> >can really only use a maximum 1.7 to 2 gigs of RAM in the
> SQL Server process
> >when running on Win2000... My question is: If I install
> Windows 2003 and
> >stick SQL Enterprise on that machine, how much memory
> will SQL be able to
> >use (in a given process). MSFT's site says that Win2003
> can make use of 4
> >gigs of memory, but will I run into the same problem that
> Win2000 has in so
> >far as the first 2gigs of the process' virtual memory
> space is "reserved"?
> >Thanks.
> >
> >David
> >
> >
> >.
> >

Friday, March 23, 2012

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 standard licenses to buy?

We are planning to install 2005 standard Edition - single processor license
of SQL server 2005.
But the win2000 server has TWO processors
- one processor for sql2005, binds the sql2005 to CUP0 only.
- one processor for sybase asa 9.0 db, bind it to CUP1 only
The question is should we purchase ONE MORE processor license even sql2005
utilities ONE processor only'Licensing questions are best answered directly by Microsoft. From the SQL
Server 2005 Licensing FAQ:
<Excerpt href="http://links.10026.com/?link=http://www.microsoft.com/sql/howtobuy/faq.mspx">
Q. Do I have to acquire licenses for all of the processors in a server?
A. You have to acquire licenses only for processors that are accessible to
any copy of the operating system upon which SQL Server 2005 is installed. In
a virtualized environment, processor licenses are required for every
processor that is accessed by a virtual machine. Please see the
virtualization licensing brief and virtualization white paper for more
information on how to license under a virtualized environment.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:ORN3W1aPGHA.2696@.TK2MSFTNGP14.phx.gbl...
> We are planning to install 2005 standard Edition - single processor
> license of SQL server 2005.
> But the win2000 server has TWO processors
> - one processor for sql2005, binds the sql2005 to CUP0 only.
> - one processor for sybase asa 9.0 db, bind it to CUP1 only
> The question is should we purchase ONE MORE processor license even sql2005
> utilities ONE processor only'
>

How many standard licenses to buy?

We are planning to install 2005 standard Edition - single processor license
of SQL server 2005.
But the win2000 server has TWO processors
- one processor for sql2005, binds the sql2005 to CUP0 only.
- one processor for sybase asa 9.0 db, bind it to CUP1 only
The question is should we purchase ONE MORE processor license even sql2005
utilities ONE processor only?
Licensing questions are best answered directly by Microsoft. From the SQL
Server 2005 Licensing FAQ:
<Excerpt href="http://links.10026.com/?link=http://www.microsoft.com/sql/howtobuy/faq.mspx">
Q. Do I have to acquire licenses for all of the processors in a server?
A. You have to acquire licenses only for processors that are accessible to
any copy of the operating system upon which SQL Server 2005 is installed. In
a virtualized environment, processor licenses are required for every
processor that is accessed by a virtual machine. Please see the
virtualization licensing brief and virtualization white paper for more
information on how to license under a virtualized environment.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:ORN3W1aPGHA.2696@.TK2MSFTNGP14.phx.gbl...
> We are planning to install 2005 standard Edition - single processor
> license of SQL server 2005.
> But the win2000 server has TWO processors
> - one processor for sql2005, binds the sql2005 to CUP0 only.
> - one processor for sybase asa 9.0 db, bind it to CUP1 only
> The question is should we purchase ONE MORE processor license even sql2005
> utilities ONE processor only?
>
sql

How many standard licenses to buy?

We are planning to install 2005 standard Edition - single processor license
of SQL server 2005.
But the win2000 server has TWO processors
- one processor for sql2005, binds the sql2005 to CUP0 only.
- one processor for sybase asa 9.0 db, bind it to CUP1 only
The question is should we purchase ONE MORE processor license even sql2005
utilities ONE processor only'Licensing questions are best answered directly by Microsoft. From the SQL
Server 2005 Licensing FAQ:
<Excerpt href="http://links.10026.com/?link=">http://www.microsoft.com/sql/howtobuy/faq.mspx">
Q. Do I have to acquire licenses for all of the processors in a server?
A. You have to acquire licenses only for processors that are accessible to
any copy of the operating system upon which SQL Server 2005 is installed. In
a virtualized environment, processor licenses are required for every
processor that is accessed by a virtual machine. Please see the
virtualization licensing brief and virtualization white paper for more
information on how to license under a virtualized environment.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:ORN3W1aPGHA.2696@.TK2MSFTNGP14.phx.gbl...
> We are planning to install 2005 standard Edition - single processor
> license of SQL server 2005.
> But the win2000 server has TWO processors
> - one processor for sql2005, binds the sql2005 to CUP0 only.
> - one processor for sybase asa 9.0 db, bind it to CUP1 only
> The question is should we purchase ONE MORE processor license even sql2005
> utilities ONE processor only'
>

Wednesday, March 21, 2012

How many records in a table?

Hi! Stupid question as this also depends on hardware but is there a limit on
how many rows I can have in a single MSSQL 2000 table? Table consists of six
int columns and has three indexes. Is like 10 Million or 100 Million
considered to be 'normal' or unrealistic?

Thanks very much for your thoughts!

MartinMartin,

It's actually limited ONLY by hardware (disk space). I wouldn't say that 10
or 100 million are anywhere near "unrealistic". Maybe 1 trillion would be
more like it!

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:cit9ft$m5b$1@.hercules.btinternet.com...
> Hi! Stupid question as this also depends on hardware but is there a limit
on
> how many rows I can have in a single MSSQL 2000 table? Table consists of
six
> int columns and has three indexes. Is like 10 Million or 100 Million
> considered to be 'normal' or unrealistic?
> Thanks very much for your thoughts!
> Martin|||Thanks!

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:4152361a$0$2656$61fed72c@.news.rcn.com...
> Martin,
> It's actually limited ONLY by hardware (disk space). I wouldn't say that
> 10
> or 100 million are anywhere near "unrealistic". Maybe 1 trillion would be
> more like it!

Monday, March 12, 2012

How long does it take to drop a FT index?

Yesterday I created a full text index on a single field in my database with
365000 rows in order to do some performance testing to see if moving to a
full text index would provide significant improvement over LIKE clauses for
looking up words in titles of products. I now need to create indexes on a
couple more rows, and so I need to drop the existing index and create a new
one. However, it's now 3 hours later and iSQL is still running my drop
statement. How long does it normally take to drop a full text index of 98045
unique words with 364704 items, totalling 13MB? If I right click on the
catalog in SQL Ent Manager and choose Properties it shows the catalog as
idle. No errors are being returned by iSQL, and there is nothing in either
the W2K Event Log or the SQL log to indicate there is a problem. Please help
:|
Dan
This should happen well within a minute. run sp_lock to see if there is any
locking.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:emlT0SGrEHA.868@.TK2MSFTNGP10.phx.gbl...
> Yesterday I created a full text index on a single field in my database
with
> 365000 rows in order to do some performance testing to see if moving to a
> full text index would provide significant improvement over LIKE clauses
for
> looking up words in titles of products. I now need to create indexes on a
> couple more rows, and so I need to drop the existing index and create a
new
> one. However, it's now 3 hours later and iSQL is still running my drop
> statement. How long does it normally take to drop a full text index of
98045
> unique words with 364704 items, totalling 13MB? If I right click on the
> catalog in SQL Ent Manager and choose Properties it shows the catalog as
> idle. No errors are being returned by iSQL, and there is nothing in either
> the W2K Event Log or the SQL log to indicate there is a problem. Please
help
> :|
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eX8NLVGrEHA.896@.TK2MSFTNGP12.phx.gbl...
> This should happen well within a minute. run sp_lock to see if there is
any
> locking.
I knew I'd forgotten to do something :|
Thanks for the help. I found a stuck connection from a process that ran a
9:26 last night that for reason hadn't cleared - the process ended after
only a few seconds, yet SQL Server was maintaining the lock. In the end I
had to restart the SQL Server service to get rid of it, then the index
dropped in 5 seconds.
Dan
|||You have to be very careful with locking. SQL FTS does apply row level
locking while doing populations which can aggravate deadlocks and
maintenance operations like the one you have discovered.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:eSpySqGrEHA.452@.TK2MSFTNGP09.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eX8NLVGrEHA.896@.TK2MSFTNGP12.phx.gbl...
> any
> I knew I'd forgotten to do something :|
> Thanks for the help. I found a stuck connection from a process that ran a
> 9:26 last night that for reason hadn't cleared - the process ended after
> only a few seconds, yet SQL Server was maintaining the lock. In the end I
> had to restart the SQL Server service to get rid of it, then the index
> dropped in 5 seconds.
> Dan
>