Performance Question Using Views

When creating views, do you follow the same rule to select as little as
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create
a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense."Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David|||Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:

> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't b
e
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
>
>|||That's good to know...As I replied to Uri, I'm still wondering whether to us
e
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks
.
"David Browne" wrote:

> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the joi
n
> can't add or remove rows from the result.
> David
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
David

Performance Question Using Views

When creating views, do you follow the same rule to select as little as
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense.Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:
> Mike
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> > When creating views, do you follow the same rule to select as little as
> > possible? For instance, is it bad practice to create a view that selects
> > everything from one of my tables and certain columns from another table,
> > and
> > when executing a stored procedure against the view to limit that to only
> > the
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
>
>|||That's good to know...As I replied to Uri, I'm still wondering whether to use
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks.
"David Browne" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> > When creating views, do you follow the same rule to select as little as
> > possible? For instance, is it bad practice to create a view that selects
> > everything from one of my tables and certain columns from another table,
> > and
> > when executing a stored procedure against the view to limit that to only
> > the
> > columns I need at the time. So if I had a view that included 20 fields
> > from
> > one table and 10 fields from another table, is that slower than if I
> > create a
> > view that only shows the actual fields I'll need for the particular lookup
> > I'll be doing? Hope this makes sense.
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the join
> can't add or remove rows from the result.
> David
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>> columns I need at the time. So if I had a view that included 20 fields
>> from
>> one table and 10 fields from another table, is that slower than if I
>> create a
>> view that only shows the actual fields I'll need for the particular
>> lookup
>> I'll be doing? Hope this makes sense.
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
>> Mike
>> columns I need at the time. So if I had a view that included 20 fields
>> from
>> one table and 10 fields from another table, is that slower than if I
>> create a
>> view that only shows the actual fields I'll need for the particular
>> lookup
>> I'll be doing? Hope this makes sense.
>>
>> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
>> be ably to use indexes in that case I mean COVERING indexs and as result
>> you will get a bad performance.
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Davidsql

Performance Question Using Views

When creating views, do you follow the same rule to select as little as
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense.
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David
|||Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:

> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
>
>
|||That's good to know...As I replied to Uri, I'm still wondering whether to use
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks.
"David Browne" wrote:

> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the join
> can't add or remove rows from the result.
> David
>
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
David

Performance problems after updating to SQL Server 2005

We have updated to SQL Server 2005, let’s say, in a hurry without thinking or testing. Databases were attached to the new instance of SQL Server 2005. It looked great when I tested it alone but then a new day come and as all users logged into the system we had got a big problem. The response times are very long and users receive time out errors all the time.

A little background:

The instance of SQL Server 2005 is installed on the same server as 2000 was installed on. 2000 has been uninstalled. It is a Xenon 3.2 GHz with 2GB RAM and SCSI raid. Data and logs are on different spins.

Application is an old ASP code and some parts are not optimized at all. But it worked fine on SQL Server 2000.

What could be the problem?

I really don’t want to downgrade to SQL Server 2000.

Hi Stolin,

Just wondering if you ran UPDATE STATS after the upgrade.

If not, This is the first thing I would do. UPDATE STATS with FULL SCAN.

Good luck and let us know how u get on

Jag

|||

Thank You,

That’s exactly what I did right after I’d posted the message. I also rebuild the indexes. Let’s hope that’s enough.

Performance Optimizing

I am trying to monitor performancce of a database using PerfMon, but the
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?Do you have more than 99 DBs on your SQL instance (including master,
model & tempdb)? SQL used to have a problem keeping perfmon stats for
dbids > 99. I don't know if Microsoft have fixed that issue with SQL
2005 or not. If that's what's happening with you then in perfmon you'll
see DBs whose dbid() <= 99 but not those DBs over that.
*mike hodgson*
http://sqlnerd.blogspot.com
Paul wrote:

>I am trying to monitor performancce of a database using PerfMon, but the
>database instance does not show in PerfMon. I have noticed that some
>databases show up in the list and some don't. These database are all active
>and running. Why is this?
>

Performance Optimizing

I am trying to monitor performancce of a database using PerfMon, but the
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?This is a multi-part message in MIME format.
--060202050209050201010409
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Do you have more than 99 DBs on your SQL instance (including master,
model & tempdb)? SQL used to have a problem keeping perfmon stats for
dbids > 99. I don't know if Microsoft have fixed that issue with SQL
2005 or not. If that's what's happening with you then in perfmon you'll
see DBs whose dbid() <= 99 but not those DBs over that.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Paul wrote:
>I am trying to monitor performancce of a database using PerfMon, but the
>database instance does not show in PerfMon. I have noticed that some
>databases show up in the list and some don't. These database are all active
>and running. Why is this?
>
--060202050209050201010409
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Do you have more than 99 DBs on your SQL instance (including
master, model & tempdb)? SQL used to have a problem keeping
perfmon stats for dbids > 99. I don't know if Microsoft have fixed
that issue with SQL 2005 or not. If that's what's happening with you
then in perfmon you'll see DBs whose dbid() <= 99 but not those DBs
over that.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Paul wrote:
<blockquote cite="midFDAEF3DA-76A8-4A36-920B-36D6E7DCFB0D@.microsoft.com"
type="cite">
<pre wrap="">I am trying to monitor performancce of a database using PerfMon, but the
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?
</pre>
</blockquote>
</body>
</html>
--060202050209050201010409--

Performance of named instance

Hi,
I have an app that ran just fine with the default instance of MSDE. I
changed the connection string in the app to work with a named instance
instead, like this: (local)/MyNamedInstance.
Database performance slowed measurably as soon as I did that, particularly
with DDL commands, like:
CREATE DEFAULT BOOLEAN_DEFAULT AS ''F''
CREATE RULE BOOLEAN_RULE AS @.list IN (''T'', ''F'')
sp_addtype DOM_BOOLEAN, ''VARCHAR(1)'', ''NOT NULL''
sp_bindefault BOOLEAN_DEFAULT, DOM_BOOLEAN
sp_bindrule BOOLEAN_RULE, DOM_BOOLEAN
It literally takes 10-15 seconds to execute about 4 of these lines of code,
where they used to execute pretty much immediately.
Is there anything special I need to do with a named instance of MSDE to
obtain the same performance levels?
Thanks in advance.
-Eric Harmon
If the instances are on the same machine it is possible that the first
instance has taken the majority of the memory and is still using it. If the
first instance needs the memory it will not free it up for the second
instance.
I have never seen a case where the cause of a performance problem was a
default vs. a named instance, so you need to concentrate on the other
differences; resources available, differences in data, schema differences,
disk speed, etc.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Performance Monitoring â?" Interpretation

Folks,
Objective: Is there enough resources on this server to create another
instance of SQL Server?
SQL Server 2000 Enterprise Edition SP3
Windows 2003 Enterprise Edition SP1 4GB Memory
The server currently has 1 instance.
SQL Server is dynamically managing memory
Total Server Memory: 2.606 GB
Available Mbytes: 380
Pages/Sec: .023
SQLSERV Working Set: 2.648 GB
Processor: 10%
Cache Hit Ratio: 99.8%
SQL Server Free Pages: 1,031 (1.611 MB)
No disk contention.
My interpretation is this:
This instance of SQL Server appears to be utilizing all the memory available
to it. If I scale this instance back via max server memory so that I have
available memory for the second instance, Iâ'm thinking that the good
performance Iâ'm realizing now on this server would be jeopardized.
The book I am referencing (SQL Server 2000 Performance Tuning â' Microsoft
Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
consistently below 5MB (in my case it is) that I am either running low on
physical memory or allocating too much to SQL Server. This has me confused.
How can I determine?
Iâ'd also like to know how SQL Server is able to use more than 2GB of
physical memory. Is there anything special (i.e. Boot.ini flag) that is
required?
Thanks in advance.
Scott H.Adding another instance will mean that you have to allocate memory between
the two instances and they may compete but it is hard to say without knowing
what the other instance needs for resources. Why not simply add the new db
to the existing instance? I would look at the Page Life Expectancy counter
to see how well the memory you have now is used. And you are obviously using
more than 2GB for this instance if you have 2.66GB of total memory. So you
must have already set the /3GB switch in the boot.ini file other wise you
would only be using 2GB or less. And why not add more memory? You have EE
for both SQL and Windows yet only have 4GB of memory. Seems counter
productive.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> Folks,
> Objective: Is there enough resources on this server to create another
> instance of SQL Server?
> SQL Server 2000 Enterprise Edition SP3
> Windows 2003 Enterprise Edition SP1 4GB Memory
> The server currently has 1 instance.
> SQL Server is dynamically managing memory
> Total Server Memory: 2.606 GB
> Available Mbytes: 380
> Pages/Sec: .023
> SQLSERV Working Set: 2.648 GB
> Processor: 10%
> Cache Hit Ratio: 99.8%
> SQL Server Free Pages: 1,031 (1.611 MB)
> No disk contention.
> My interpretation is this:
> This instance of SQL Server appears to be utilizing all the memory
> available
> to it. If I scale this instance back via max server memory so that I have
> available memory for the second instance, Iâ?Tm thinking that the good
> performance Iâ?Tm realizing now on this server would be jeopardized.
> The book I am referencing (SQL Server 2000 Performance Tuning â?"
> Microsoft
> Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> consistently below 5MB (in my case it is) that I am either running low on
> physical memory or allocating too much to SQL Server. This has me
> confused.
> How can I determine?
> Iâ?Td also like to know how SQL Server is able to use more than 2GB of
> physical memory. Is there anything special (i.e. Boot.ini flag) that is
> required?
> Thanks in advance.
> Scott H.
>|||Andrew,
The reason for the additional instance is that there will be 2 new databases
that will be internet facing. Currently this instance is not, and no plans on
opening up port 1433 through the firewall.
My recommendation is to add more memory. There is some reluctance due to
cost. The server is an older generation HP (HP DL380 G2). I'm being told that
the memory is not available.
So, the /3GB switch in the boot.ini file is still applicable on Windows 2003
EE and SQL Server 2000 EE? How can I find/view this parameter?
Based on the performance counters, would your recommendation be to add more
memory?
Thanks for your help.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:
> Adding another instance will mean that you have to allocate memory between
> the two instances and they may compete but it is hard to say without knowing
> what the other instance needs for resources. Why not simply add the new db
> to the existing instance? I would look at the Page Life Expectancy counter
> to see how well the memory you have now is used. And you are obviously using
> more than 2GB for this instance if you have 2.66GB of total memory. So you
> must have already set the /3GB switch in the boot.ini file other wise you
> would only be using 2GB or less. And why not add more memory? You have EE
> for both SQL and Windows yet only have 4GB of memory. Seems counter
> productive.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> > Folks,
> >
> > Objective: Is there enough resources on this server to create another
> > instance of SQL Server?
> >
> > SQL Server 2000 Enterprise Edition SP3
> > Windows 2003 Enterprise Edition SP1 4GB Memory
> >
> > The server currently has 1 instance.
> > SQL Server is dynamically managing memory
> > Total Server Memory: 2.606 GB
> > Available Mbytes: 380
> > Pages/Sec: .023
> > SQLSERV Working Set: 2.648 GB
> > Processor: 10%
> > Cache Hit Ratio: 99.8%
> > SQL Server Free Pages: 1,031 (1.611 MB)
> > No disk contention.
> >
> > My interpretation is this:
> >
> > This instance of SQL Server appears to be utilizing all the memory
> > available
> > to it. If I scale this instance back via max server memory so that I have
> > available memory for the second instance, Iâ?Tm thinking that the good
> > performance Iâ?Tm realizing now on this server would be jeopardized.
> >
> > The book I am referencing (SQL Server 2000 Performance Tuning â?"
> > Microsoft
> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> > consistently below 5MB (in my case it is) that I am either running low on
> > physical memory or allocating too much to SQL Server. This has me
> > confused.
> > How can I determine?
> >
> > Iâ?Td also like to know how SQL Server is able to use more than 2GB of
> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> > required?
> >
> > Thanks in advance.
> >
> > Scott H.
> >
>
>|||I should have investigated prior to asking. I found the boot.ini under C:\ -
it was hiddent. And yes, the 3GB/ switch is there.
--
Thanks,
Scott H.
"Scott H." wrote:
> Andrew,
> The reason for the additional instance is that there will be 2 new databases
> that will be internet facing. Currently this instance is not, and no plans on
> opening up port 1433 through the firewall.
> My recommendation is to add more memory. There is some reluctance due to
> cost. The server is an older generation HP (HP DL380 G2). I'm being told that
> the memory is not available.
> So, the /3GB switch in the boot.ini file is still applicable on Windows 2003
> EE and SQL Server 2000 EE? How can I find/view this parameter?
> Based on the performance counters, would your recommendation be to add more
> memory?
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
> > Adding another instance will mean that you have to allocate memory between
> > the two instances and they may compete but it is hard to say without knowing
> > what the other instance needs for resources. Why not simply add the new db
> > to the existing instance? I would look at the Page Life Expectancy counter
> > to see how well the memory you have now is used. And you are obviously using
> > more than 2GB for this instance if you have 2.66GB of total memory. So you
> > must have already set the /3GB switch in the boot.ini file other wise you
> > would only be using 2GB or less. And why not add more memory? You have EE
> > for both SQL and Windows yet only have 4GB of memory. Seems counter
> > productive.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> > news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> > > Folks,
> > >
> > > Objective: Is there enough resources on this server to create another
> > > instance of SQL Server?
> > >
> > > SQL Server 2000 Enterprise Edition SP3
> > > Windows 2003 Enterprise Edition SP1 4GB Memory
> > >
> > > The server currently has 1 instance.
> > > SQL Server is dynamically managing memory
> > > Total Server Memory: 2.606 GB
> > > Available Mbytes: 380
> > > Pages/Sec: .023
> > > SQLSERV Working Set: 2.648 GB
> > > Processor: 10%
> > > Cache Hit Ratio: 99.8%
> > > SQL Server Free Pages: 1,031 (1.611 MB)
> > > No disk contention.
> > >
> > > My interpretation is this:
> > >
> > > This instance of SQL Server appears to be utilizing all the memory
> > > available
> > > to it. If I scale this instance back via max server memory so that I have
> > > available memory for the second instance, Iâ?Tm thinking that the good
> > > performance Iâ?Tm realizing now on this server would be jeopardized.
> > >
> > > The book I am referencing (SQL Server 2000 Performance Tuning â?"
> > > Microsoft
> > > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> > > consistently below 5MB (in my case it is) that I am either running low on
> > > physical memory or allocating too much to SQL Server. This has me
> > > confused.
> > > How can I determine?
> > >
> > > Iâ?Td also like to know how SQL Server is able to use more than 2GB of
> > > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> > > required?
> > >
> > > Thanks in advance.
> > >
> > > Scott H.
> > >
> >
> >
> >|||Well like I said I would look at the Page Life Exptancy counter first and
see how high or low that is. You can't tell from a few counters like this
what is needed. We would really need a lot more info. The existing instance
is using all the available memory but that does not mean it actually needs
it. SQL Server will always use what is there if the db is large enough and
you ask for that much data over time. Having said that memory is one of the
cheapest and easiest ways to grow a SQL box.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
> Andrew,
> The reason for the additional instance is that there will be 2 new
> databases
> that will be internet facing. Currently this instance is not, and no plans
> on
> opening up port 1433 through the firewall.
> My recommendation is to add more memory. There is some reluctance due to
> cost. The server is an older generation HP (HP DL380 G2). I'm being told
> that
> the memory is not available.
> So, the /3GB switch in the boot.ini file is still applicable on Windows
> 2003
> EE and SQL Server 2000 EE? How can I find/view this parameter?
> Based on the performance counters, would your recommendation be to add
> more
> memory?
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> Adding another instance will mean that you have to allocate memory
>> between
>> the two instances and they may compete but it is hard to say without
>> knowing
>> what the other instance needs for resources. Why not simply add the new
>> db
>> to the existing instance? I would look at the Page Life Expectancy
>> counter
>> to see how well the memory you have now is used. And you are obviously
>> using
>> more than 2GB for this instance if you have 2.66GB of total memory. So
>> you
>> must have already set the /3GB switch in the boot.ini file other wise you
>> would only be using 2GB or less. And why not add more memory? You have EE
>> for both SQL and Windows yet only have 4GB of memory. Seems counter
>> productive.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
>> > Folks,
>> >
>> > Objective: Is there enough resources on this server to create another
>> > instance of SQL Server?
>> >
>> > SQL Server 2000 Enterprise Edition SP3
>> > Windows 2003 Enterprise Edition SP1 4GB Memory
>> >
>> > The server currently has 1 instance.
>> > SQL Server is dynamically managing memory
>> > Total Server Memory: 2.606 GB
>> > Available Mbytes: 380
>> > Pages/Sec: .023
>> > SQLSERV Working Set: 2.648 GB
>> > Processor: 10%
>> > Cache Hit Ratio: 99.8%
>> > SQL Server Free Pages: 1,031 (1.611 MB)
>> > No disk contention.
>> >
>> > My interpretation is this:
>> >
>> > This instance of SQL Server appears to be utilizing all the memory
>> > available
>> > to it. If I scale this instance back via max server memory so that I
>> > have
>> > available memory for the second instance, Iâ?Tm thinking that the good
>> > performance Iâ?Tm realizing now on this server would be jeopardized.
>> >
>> > The book I am referencing (SQL Server 2000 Performance Tuning â?"
>> > Microsoft
>> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
>> > consistently below 5MB (in my case it is) that I am either running low
>> > on
>> > physical memory or allocating too much to SQL Server. This has me
>> > confused.
>> > How can I determine?
>> >
>> > Iâ?Td also like to know how SQL Server is able to use more than 2GB of
>> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
>> > required?
>> >
>> > Thanks in advance.
>> >
>> > Scott H.
>> >
>>|||Thanks for your help Andrew.
Again, I'm taking my lead from SQL Server 2000 Performance Tuning book -
Microsoft Press. The counters I've included are what they suggest. I'd be
happy to read other performance tuning documents if you have any
recommendations.
I have added the suggested counter - page life expectancy. The first
interval returned 12 hours (after conversion)
I assumed SQL Server would release memory if it did not require it.
What I'm going to suggest, until the powers that be decide to spend some
money on memory, is that we run the instance with max server memory
configured to say 1.5GB for a few days. I'll compare the counters I'm
collecting now, with a few days of running with the memory scaled back.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:
> Well like I said I would look at the Page Life Exptancy counter first and
> see how high or low that is. You can't tell from a few counters like this
> what is needed. We would really need a lot more info. The existing instance
> is using all the available memory but that does not mean it actually needs
> it. SQL Server will always use what is there if the db is large enough and
> you ask for that much data over time. Having said that memory is one of the
> cheapest and easiest ways to grow a SQL box.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
> > Andrew,
> >
> > The reason for the additional instance is that there will be 2 new
> > databases
> > that will be internet facing. Currently this instance is not, and no plans
> > on
> > opening up port 1433 through the firewall.
> >
> > My recommendation is to add more memory. There is some reluctance due to
> > cost. The server is an older generation HP (HP DL380 G2). I'm being told
> > that
> > the memory is not available.
> >
> > So, the /3GB switch in the boot.ini file is still applicable on Windows
> > 2003
> > EE and SQL Server 2000 EE? How can I find/view this parameter?
> >
> > Based on the performance counters, would your recommendation be to add
> > more
> > memory?
> >
> > Thanks for your help.
> > --
> > Thanks,
> >
> > Scott H.
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Adding another instance will mean that you have to allocate memory
> >> between
> >> the two instances and they may compete but it is hard to say without
> >> knowing
> >> what the other instance needs for resources. Why not simply add the new
> >> db
> >> to the existing instance? I would look at the Page Life Expectancy
> >> counter
> >> to see how well the memory you have now is used. And you are obviously
> >> using
> >> more than 2GB for this instance if you have 2.66GB of total memory. So
> >> you
> >> must have already set the /3GB switch in the boot.ini file other wise you
> >> would only be using 2GB or less. And why not add more memory? You have EE
> >> for both SQL and Windows yet only have 4GB of memory. Seems counter
> >> productive.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> >> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
> >> > Folks,
> >> >
> >> > Objective: Is there enough resources on this server to create another
> >> > instance of SQL Server?
> >> >
> >> > SQL Server 2000 Enterprise Edition SP3
> >> > Windows 2003 Enterprise Edition SP1 4GB Memory
> >> >
> >> > The server currently has 1 instance.
> >> > SQL Server is dynamically managing memory
> >> > Total Server Memory: 2.606 GB
> >> > Available Mbytes: 380
> >> > Pages/Sec: .023
> >> > SQLSERV Working Set: 2.648 GB
> >> > Processor: 10%
> >> > Cache Hit Ratio: 99.8%
> >> > SQL Server Free Pages: 1,031 (1.611 MB)
> >> > No disk contention.
> >> >
> >> > My interpretation is this:
> >> >
> >> > This instance of SQL Server appears to be utilizing all the memory
> >> > available
> >> > to it. If I scale this instance back via max server memory so that I
> >> > have
> >> > available memory for the second instance, I�¢?Tm thinking that the good
> >> > performance I�¢?Tm realizing now on this server would be jeopardized.
> >> >
> >> > The book I am referencing (SQL Server 2000 Performance Tuning �¢?"
> >> > Microsoft
> >> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages is
> >> > consistently below 5MB (in my case it is) that I am either running low
> >> > on
> >> > physical memory or allocating too much to SQL Server. This has me
> >> > confused.
> >> > How can I determine?
> >> >
> >> > I�¢?Td also like to know how SQL Server is able to use more than 2GB of
> >> > physical memory. Is there anything special (i.e. Boot.ini flag) that is
> >> > required?
> >> >
> >> > Thanks in advance.
> >> >
> >> > Scott H.
> >> >
> >>
> >>
> >>
>
>|||SQL Server never gives back memory unless the OS specifically asks for it.
So if there is nothing else requiring the memory SQL Server will hold onto
it. IF your PLE counter reads 12 hours chances are you have more memory than
you can get away with. A PLE reading of around 300 seconds or more is
usually enough for decent performance. Over 1000 is considered really good.
I think your idea of dropping the MAX memory and see how it performs is a
good idea. Keep in mind that the Max memory setting is for the buffer pool
only and that on a 32 bit machine SQL Server may use up to ~384MB of
additional memory for the MemToLeave area.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:19D9D73E-0A5C-441D-BFDF-24DE162FD36E@.microsoft.com...
> Thanks for your help Andrew.
> Again, I'm taking my lead from SQL Server 2000 Performance Tuning book -
> Microsoft Press. The counters I've included are what they suggest. I'd be
> happy to read other performance tuning documents if you have any
> recommendations.
> I have added the suggested counter - page life expectancy. The first
> interval returned 12 hours (after conversion)
> I assumed SQL Server would release memory if it did not require it.
> What I'm going to suggest, until the powers that be decide to spend some
> money on memory, is that we run the instance with max server memory
> configured to say 1.5GB for a few days. I'll compare the counters I'm
> collecting now, with a few days of running with the memory scaled back.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> Well like I said I would look at the Page Life Exptancy counter first and
>> see how high or low that is. You can't tell from a few counters like
>> this
>> what is needed. We would really need a lot more info. The existing
>> instance
>> is using all the available memory but that does not mean it actually
>> needs
>> it. SQL Server will always use what is there if the db is large enough
>> and
>> you ask for that much data over time. Having said that memory is one of
>> the
>> cheapest and easiest ways to grow a SQL box.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:FFF35509-CC93-4E35-8AE2-C61F0A436B82@.microsoft.com...
>> > Andrew,
>> >
>> > The reason for the additional instance is that there will be 2 new
>> > databases
>> > that will be internet facing. Currently this instance is not, and no
>> > plans
>> > on
>> > opening up port 1433 through the firewall.
>> >
>> > My recommendation is to add more memory. There is some reluctance due
>> > to
>> > cost. The server is an older generation HP (HP DL380 G2). I'm being
>> > told
>> > that
>> > the memory is not available.
>> >
>> > So, the /3GB switch in the boot.ini file is still applicable on Windows
>> > 2003
>> > EE and SQL Server 2000 EE? How can I find/view this parameter?
>> >
>> > Based on the performance counters, would your recommendation be to add
>> > more
>> > memory?
>> >
>> > Thanks for your help.
>> > --
>> > Thanks,
>> >
>> > Scott H.
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Adding another instance will mean that you have to allocate memory
>> >> between
>> >> the two instances and they may compete but it is hard to say without
>> >> knowing
>> >> what the other instance needs for resources. Why not simply add the
>> >> new
>> >> db
>> >> to the existing instance? I would look at the Page Life Expectancy
>> >> counter
>> >> to see how well the memory you have now is used. And you are obviously
>> >> using
>> >> more than 2GB for this instance if you have 2.66GB of total memory. So
>> >> you
>> >> must have already set the /3GB switch in the boot.ini file other wise
>> >> you
>> >> would only be using 2GB or less. And why not add more memory? You have
>> >> EE
>> >> for both SQL and Windows yet only have 4GB of memory. Seems counter
>> >> productive.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> >> news:978AEB2B-21E0-48EA-B8A3-687B2A629476@.microsoft.com...
>> >> > Folks,
>> >> >
>> >> > Objective: Is there enough resources on this server to create
>> >> > another
>> >> > instance of SQL Server?
>> >> >
>> >> > SQL Server 2000 Enterprise Edition SP3
>> >> > Windows 2003 Enterprise Edition SP1 4GB Memory
>> >> >
>> >> > The server currently has 1 instance.
>> >> > SQL Server is dynamically managing memory
>> >> > Total Server Memory: 2.606 GB
>> >> > Available Mbytes: 380
>> >> > Pages/Sec: .023
>> >> > SQLSERV Working Set: 2.648 GB
>> >> > Processor: 10%
>> >> > Cache Hit Ratio: 99.8%
>> >> > SQL Server Free Pages: 1,031 (1.611 MB)
>> >> > No disk contention.
>> >> >
>> >> > My interpretation is this:
>> >> >
>> >> > This instance of SQL Server appears to be utilizing all the memory
>> >> > available
>> >> > to it. If I scale this instance back via max server memory so that I
>> >> > have
>> >> > available memory for the second instance, IÃf¢?Tm thinking that the
>> >> > good
>> >> > performance IÃf¢?Tm realizing now on this server would be
>> >> > jeopardized.
>> >> >
>> >> > The book I am referencing (SQL Server 2000 Performance Tuning Ãf¢?"
>> >> > Microsoft
>> >> > Press) also mentions that if SQL Server: Buffer Manager/Free Pages
>> >> > is
>> >> > consistently below 5MB (in my case it is) that I am either running
>> >> > low
>> >> > on
>> >> > physical memory or allocating too much to SQL Server. This has me
>> >> > confused.
>> >> > How can I determine?
>> >> >
>> >> > IÃf¢?Td also like to know how SQL Server is able to use more than
>> >> > 2GB of
>> >> > physical memory. Is there anything special (i.e. Boot.ini flag) that
>> >> > is
>> >> > required?
>> >> >
>> >> > Thanks in advance.
>> >> >
>> >> > Scott H.
>> >> >
>> >>
>> >>
>> >>
>>

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob

Performance Monitor Counters with Multiple Instances

I have a server that has 2 instances of SQL Server 2000. The first is the default instance and the second is a named instance. When I go to Performance Monitor to view counters, I can only find counters for the named instance. Is ther a way to view counters for both/all instances?

Thanks in advance for your help.

Bob

IS that default instance sql services are started?

What is the OS version? Service pack?
When some counters disapear, what counters are still there?
Do you start perfmon first (remotely or localy) and then SQL Server?
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy)
When counters disapear do you see any rows with select * from sysperfinfo? If you get no rows you should check the errorlog for the instances affected.

|||

Thanks for the response Satya:

IS that default instance sql services are started? >> Yes

What is the OS version? Service pack? >> Not Sure
When some counters disapear, what counters are still there? >> Counters don't "disappear" - they are never there
Do you start perfmon first (remotely or localy) and then SQL Server? >> SQL Server(s) are running first
Do you change the service account for the SQL Server instances while perfmon is running (remotely or localy) >> No
When counters disapear do you see any rows with select * from sysperfinfo? >> Again, counters don't "disappear" - yes I get 700+ rows

If you get no rows you should check the errorlog for the instances affected.

|||I believe still there is much information needed to assess, check the service pack level on SQL and operating system. BTW are there any clustered instances?|||

Thanks again Satya...

I am trying to get my Windows Admin to get the information on the operating system. The instances are not clustered. Was there a problem with performance counters for a given SP?

Bob