Can I get Total CPU usage or Memory use by TSQL

Hi
I dont have direct access to Server Terminal.
Can I get Server CPU Utilization and total memory use and memory use
by sql server using TSQL?
Regards
Amish Shah
Hi
It is not clear what you are looking for hear. You may want to look at the
set options:
SET SET STATISTICS IO ON
SET SET STATISTICS TIME ON
or look at SQL Profiler.
John
"amish" wrote:

> Hi
> I dont have direct access to Server Terminal.
> Can I get Server CPU Utilization and total memory use and memory use
> by sql server using TSQL?
> Regards
> Amish Shah
>
|||Actually I want to know total cpu utilization and memory utilization of
server and how much sql server consuming in this both. I can connect to
the server only by sql server. I dont have windows user/password.
So, is there any way i can get this information by tsql?
Thanks
AM
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi
You may want to look at @.@.CPU_BUSY, @.@.IDLE, @.@.TIMETICKS or sp_monitor
You may also want to check out DBCC RESOURCE at
http://www.transactsql.com/html/DBCC%20RESOURCE.html
and other dbcc commands such as DBCC PERFMON, DBCC PROCCACHE, DBCC BUFFER
although I am not sure where to find total memory used.
Also look at sp_configure to find out the maximum configured value.
John
"AM" wrote:

> Actually I want to know total cpu utilization and memory utilization of
> server and how much sql server consuming in this both. I can connect to
> the server only by sql server. I dont have windows user/password.
> So, is there any way i can get this information by tsql?
> Thanks
> AM
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Some times CPU Utilization of my server goes upto 80% to 100 %. I want
to get this detail using TSQL.
I also want to know how much % sql server taking of over all cpu
utilization.
I want to do all this using SQL Server.
Thanks
AM
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi
This is really the job of performance monitor, and this will give you a
pro-active method of monitoring it. I suggest you get the system admin to set
these up. If you are using MOM or similar you can do it that way.
John
"AM" wrote:

> Some times CPU Utilization of my server goes upto 80% to 100 %. I want
> to get this detail using TSQL.
> I also want to know how much % sql server taking of over all cpu
> utilization.
> I want to do all this using SQL Server.
>
> Thanks
> AM
> *** Sent via Developersdex http://www.codecomments.com ***
>

Can I get Total CPU usage or Memory use by TSQL

Hi
I dont have direct access to Server Terminal.
Can I get Server CPU Utilization and total memory use and memory use
by sql server using TSQL?
Regards
Amish ShahHi
It is not clear what you are looking for hear. You may want to look at the
set options:
SET SET STATISTICS IO ON
SET SET STATISTICS TIME ON
or look at SQL Profiler.
John
"amish" wrote:
> Hi
> I dont have direct access to Server Terminal.
> Can I get Server CPU Utilization and total memory use and memory use
> by sql server using TSQL?
> Regards
> Amish Shah
>|||Hi
You may want to look at @.@.CPU_BUSY, @.@.IDLE, @.@.TIMETICKS or sp_monitor
You may also want to check out DBCC RESOURCE at
http://www.transactsql.com/html/DBCC%20RESOURCE.html
and other dbcc commands such as DBCC PERFMON, DBCC PROCCACHE, DBCC BUFFER
although I am not sure where to find total memory used.
Also look at sp_configure to find out the maximum configured value.
John
"AM" wrote:
> Actually I want to know total cpu utilization and memory utilization of
> server and how much sql server consuming in this both. I can connect to
> the server only by sql server. I dont have windows user/password.
> So, is there any way i can get this information by tsql?
> Thanks
> AM
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Hi
This is really the job of performance monitor, and this will give you a
pro-active method of monitoring it. I suggest you get the system admin to set
these up. If you are using MOM or similar you can do it that way.
John
"AM" wrote:
> Some times CPU Utilization of my server goes upto 80% to 100 %. I want
> to get this detail using TSQL.
> I also want to know how much % sql server taking of over all cpu
> utilization.
> I want to do all this using SQL Server.
>
> Thanks
> AM
> *** Sent via Developersdex http://www.developersdex.com ***
>

Can I get Total CPU usage or Memory use by TSQL

Hi
I dont have direct access to Server Terminal.
Can I get Server CPU Utilization and total memory use and memory use
by sql server using TSQL?
Regards
Amish ShahHi
It is not clear what you are looking for hear. You may want to look at the
set options:
SET SET STATISTICS IO ON
SET SET STATISTICS TIME ON
or look at SQL Profiler.
John
"amish" wrote:

> Hi
> I dont have direct access to Server Terminal.
> Can I get Server CPU Utilization and total memory use and memory use
> by sql server using TSQL?
> Regards
> Amish Shah
>|||Actually I want to know total cpu utilization and memory utilization of
server and how much sql server consuming in this both. I can connect to
the server only by sql server. I dont have windows user/password.
So, is there any way i can get this information by tsql?
Thanks
AM
*** Sent via Developersdex http://www.codecomments.com ***|||Hi
You may want to look at @.@.CPU_BUSY, @.@.IDLE, @.@.TIMETICKS or sp_monitor
You may also want to check out DBCC RESOURCE at
http://www.transactsql.com/html/DBCC%20RESOURCE.html
and other dbcc commands such as DBCC PERFMON, DBCC PROCCACHE, DBCC BUFFER
although I am not sure where to find total memory used.
Also look at sp_configure to find out the maximum configured value.
John
"AM" wrote:

> Actually I want to know total cpu utilization and memory utilization of
> server and how much sql server consuming in this both. I can connect to
> the server only by sql server. I dont have windows user/password.
> So, is there any way i can get this information by tsql?
> Thanks
> AM
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Some times CPU Utilization of my server goes upto 80% to 100 %. I want
to get this detail using TSQL.
I also want to know how much % sql server taking of over all cpu
utilization.
I want to do all this using SQL Server.
Thanks
AM
*** Sent via Developersdex http://www.codecomments.com ***|||Hi
This is really the job of performance monitor, and this will give you a
pro-active method of monitoring it. I suggest you get the system admin to se
t
these up. If you are using MOM or similar you can do it that way.
John
"AM" wrote:

> Some times CPU Utilization of my server goes upto 80% to 100 %. I want
> to get this detail using TSQL.
> I also want to know how much % sql server taking of over all cpu
> utilization.
> I want to do all this using SQL Server.
>
> Thanks
> AM
> *** Sent via Developersdex http://www.codecomments.com ***
>sql

can i add more files to the filegroup?

hi all,
2 CPU , 1G memory , the storage device is Disk Array
(RAID5)
The PRIMARY filegroup contains one datafile , i want to
add more files to the PRIMARY and rebuild the index in
another filegroup FGINDX (contains more files) to get
better performance
Can i need to modify the database to get better
management and better performance '
Thanks in advance.Rainbow
You can have more than one datafile in the same filegroup.
What you can not do is have one data file in more than one
filegroup.
Be aware you can only place non-clustered indexes in a
seperate filegroup.
Hope this helps
John|||i wanna better performance , expand the data to more
file '
>--Original Message--
>Rainbow
>You can have more than one datafile in the same
filegroup.
>What you can not do is have one data file in more than
one
>filegroup.
>Be aware you can only place non-clustered indexes in a
>seperate filegroup.
>Hope this helps
>John
>.
>|||Yes you may add more data files to a filegroup.
If you wish existing data to be spread across the new files, you must re-add
the data ( perhaps dropping/re-creating the clust index).
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rainbow" <genghongjie@.163.com> wrote in message
news:061701c38e03$4c9a42d0$a401280a@.phx.gbl...
> i wanna better performance , expand the data to more
> file '
> >--Original Message--
> >Rainbow
> >
> >You can have more than one datafile in the same
> filegroup.
> >What you can not do is have one data file in more than
> one
> >filegroup.
> >
> >Be aware you can only place non-clustered indexes in a
> >seperate filegroup.
> >
> >Hope this helps
> >
> >John
> >.
> >

Can 2K5 Cluster can have 2 instances on each node?

Hi,
We have sql 2005 standard installed as cluster on win2k3 cluster with 8
GB memory on each node. We have one virtual sql server named instance on
first node. Can we create one more instance on the same node and
similarly 2 more instances on the second node. The cluster will be in
active\active mode. Our reason for considering this is that we will be
able to fail over one instance from the node 1 to node2 and node2 still
have the 2nd instance running. Is this possible?
Thanks
DeBe
DeBe's Profile: http://www.dbtalk.net/m115
View this thread: http://www.dbtalk.net/t297735
I think you have some misconceptions on how clustering works. The obsolete
naming of "Active/passive/Active..." is partially responsible for that.
Instances are installed to the cluster. You can have up to sixteen
instances on a cluster. SQL Server 2005 Standard Edition supports up to
two-node clusters. Nodes are the host computers for a cluster. An instance
can run on either node, but only on one node at a time. You cannot install
the same SQL instance to two nodes and have them both serviceing data
requests as if they were the same server. SQL Clustering is a failover
technology, not a scale-out technology. You can create two SQL instances on
a SQL 2005 two-node cluster and let one run normally on each node. During a
failure event one node can run both instances, with some performance
compromises of course.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"DeBe" <DeBe.25toh0@.no-mx.forums.yourdomain.com.au> wrote in message
news:DeBe.25toh0@.no-mx.forums.yourdomain.com.au...
> Hi,
> We have sql 2005 standard installed as cluster on win2k3 cluster with 8
> GB memory on each node. We have one virtual sql server named instance on
> first node. Can we create one more instance on the same node and
> similarly 2 more instances on the second node. The cluster will be in
> active\active mode. Our reason for considering this is that we will be
> able to fail over one instance from the node 1 to node2 and node2 still
> have the 2nd instance running. Is this possible?
> Thanks
>
> --
> DeBe
> DeBe's Profile: http://www.dbtalk.net/m115
> View this thread: http://www.dbtalk.net/t297735
>
|||I will try to make my problem more clear.
I am talking about 4 different instances and they have 4 different
databases
connecting to 4 very different application.
so
Node1-->InsA-->DBa
Node1-->InsB-->DBb
Node2-->InsC-->DBc
Node2-->InsD-->DBd
So I guess my question is is it possible for just InsA to fail over to
Node 2. So that node 2 is now running with 3 instances and node 1 is
running with 1 instance?
My second question would be about the memory. If out of 8 GB Ram on
each node if 1 GB is assigned to os and 3 GB is assigned to each
instance (assuming 2 instances) and if the third instance from the node
2 fails over how will it get the memory for itself?
Thanks
DeBe
DeBe's Profile: http://www.dbtalk.net/m115
View this thread: http://www.dbtalk.net/t297735
|||You can have up to sixteen instances per cluster. Once the instances are
installed, you can set the preferred node order for each instance. That
determines which node is the normal host for this instance. Each instance
is completely independent from the other instances. You can move each
instance independently between nodes without affecting any other instance.
Just as on a stand-alone system with multiple instances, it is the DBA's
responsibility to make sure there are enough resources for all instances to
function properly. Note that SQL will not rebalance memory amongst multiple
instances. I.E. It will not make one instance give up memory so another one
can have it. You must plan ahead for the situation where one node hosts
more than its normal set of instances.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"DeBe" <DeBe.25uu4z@.no-mx.forums.yourdomain.com.au> wrote in message
news:DeBe.25uu4z@.no-mx.forums.yourdomain.com.au...
> I will try to make my problem more clear.
> I am talking about 4 different instances and they have 4 different
> databases
> connecting to 4 very different application.
> so
> Node1-->InsA-->DBa
> Node1-->InsB-->DBb
> Node2-->InsC-->DBc
> Node2-->InsD-->DBd
> So I guess my question is is it possible for just InsA to fail over to
> Node 2. So that node 2 is now running with 3 instances and node 1 is
> running with 1 instance?
> My second question would be about the memory. If out of 8 GB Ram on
> each node if 1 GB is assigned to os and 3 GB is assigned to each
> instance (assuming 2 instances) and if the third instance from the node
> 2 fails over how will it get the memory for itself?
> Thanks
>
> --
> DeBe
> DeBe's Profile: http://www.dbtalk.net/m115
> View this thread: http://www.dbtalk.net/t297735
>
|||Just curious why you need so many instances. One instance can handle 4
DB's.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"DeBe" <DeBe.25uu4z@.no-mx.forums.yourdomain.com.au> wrote in message
news:DeBe.25uu4z@.no-mx.forums.yourdomain.com.au...
I will try to make my problem more clear.
I am talking about 4 different instances and they have 4 different
databases
connecting to 4 very different application.
so
Node1-->InsA-->DBa
Node1-->InsB-->DBb
Node2-->InsC-->DBc
Node2-->InsD-->DBd
So I guess my question is is it possible for just InsA to fail over to
Node 2. So that node 2 is now running with 3 instances and node 1 is
running with 1 instance?
My second question would be about the memory. If out of 8 GB Ram on
each node if 1 GB is assigned to os and 3 GB is assigned to each
instance (assuming 2 instances) and if the third instance from the node
2 fails over how will it get the memory for itself?
Thanks
DeBe
DeBe's Profile: http://www.dbtalk.net/m115
View this thread: http://www.dbtalk.net/t297735

Calrification on number of columns fetched to server memory

Hello,

I need a clarification on the mechanism of sql server fetches records
to memory (buffer)

Example: A Table ( Table1 ) having 10 Columns
(column1,column2,column3,column4 etc)

When SELECT column1,column2 FROM Table1 is executed

Whether all columns are trasnferred to the server memory from the disk

OR only column1 and column2 are read from disk to memory .

My assumption is that, entire page (8K) containg the rows with all the
colunmns are transferred to the memory and from the memory the select
columns are displayed .

Please confirm

Thanks

M A SrinivasMy assumption is that, entire page (8K) containg the rows with all the

Quote:

Originally Posted by

colunmns are transferred to the memory and from the memory the select
columns are displayed .

Correct. Keep in mind that non-clustered index pages can also be used and
are typically more dense than data pages. A composite index on column1 and
column2 will cover your query so the optimizer will probably choose to scan
that index rather than the entire table.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<masri999@.gmail.comwrote in message
news:1159952825.340114.188580@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Hello,
>
I need a clarification on the mechanism of sql server fetches records
to memory (buffer)
>
Example: A Table ( Table1 ) having 10 Columns
(column1,column2,column3,column4 etc)
>
When SELECT column1,column2 FROM Table1 is executed
>
Whether all columns are trasnferred to the server memory from the disk
>
OR only column1 and column2 are read from disk to memory .
>
My assumption is that, entire page (8K) containg the rows with all the
colunmns are transferred to the memory and from the memory the select
columns are displayed .
>
Please confirm
>
Thanks
>
M A Srinivas
>