Performance Recommendations for SQL Server - Paging Related

I just became in charge of a server running SQL Server 2000 Enterprise on a
Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB RAM
of the 32 GB RAM in use. The database on the server is 120GB. Approximately
60GB is actual data, 49GB of indexes and the rest is empty space. For
reference, this database is for our ClarifyCRM application.
I am starting to do performance monitoring and I am seeing a high amount of
system paging - 1,000-2000 or more pages/sec.
Even though RAM utilization is low with plenty to spare, I see that the
paging file on the server is only 2GB in size (40.8 MB of it is actually in
use). Other than increasing the size of the paging file (which I will be
doing soon) while is extremely small, are there any other recommendations to
decrease the amount of system paging that is occurring?What are the memory settings set to, especially the MAX Memory one? It
sounds odd that only 17.8 out of 32GB is being used. I assume this is 32 bit
so with AWE the memory should not be dynamic and I would expect it to be
much higher. What other apps are running on this server?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
of the actual memory you can get at.
Tony.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
> What are the memory settings set to, especially the MAX Memory one? It
> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
> bit so with AWE the memory should not be dynamic and I would expect it to
> be much higher. What other apps are running on this server?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
>> RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other recommendations
>> to
>> decrease the amount of system paging that is occurring?
>|||You may not actually have a problem.
Microsoft has an excellent (and short) article on paging and monitorint it,
I suggect you spend a few minutes reading it.
http://support.microsoft.com/kb/555223
Good luck,
Jay
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||Yes Tony probably hit the nail on the head here. You are using SQL2000 so
you most likely have SP4 without the Hotfix and it will only use approx half
the memory.
http://support.microsoft.com/kb/899761
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>of the actual memory you can get at.
> Tony.
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson
> [Ramblings from the field from a SQL consultant]
> http://sqlserverfaq.com
> [UK SQL User Community]
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it to
>> be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>|||Thats the one.
Thanks Andrew - I was being lazy, well, time constrained :(
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OhLBUbbDIHA.5976@.TK2MSFTNGP02.phx.gbl...
> Yes Tony probably hit the nail on the head here. You are using SQL2000 so
> you most likely have SP4 without the Hotfix and it will only use approx
> half the memory.
> http://support.microsoft.com/kb/899761
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
> news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>>of the actual memory you can get at.
>> Tony.
>> --
>> Tony Rogerson, SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson
>> [Ramblings from the field from a SQL consultant]
>> http://sqlserverfaq.com
>> [UK SQL User Community]
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it
>> to be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise
>>on a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high
>> amount of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is
>> actually in
>> use). Other than increasing the size of the paging file (which I will
>> be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>>
>

performance problems with SQL 2K SP3

I am having big performance problems afters applying SP3 for SQL Server 2000 Enterprise Edition.
We have a large query generated by Crystal Reports involving about 20 tables and views with joins and outer joins.
The largest table have 100000 rows and is 20 Mb large.
If I run this query on SQL server without service pack, it takes less than 1 second, with SP3 it takes more than 2 minutes!
I found a workaround, copying all data selectes by the larger view into a table and using the table instead of the view in the query.
I looked at execution plans and it seems an optimizer bug of SP3.
Someone could help me, please?Have you checked SP3a readme for any fix.|||Have you verified you applied SP3a NOT SP3?

SP3 had an Slow ODBC bug in the client part of it.

Tim S

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
.See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
..
See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.

The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)

Any idea what could be the issue here? The server
is not really heavily taxed.

The tables are small. They have very few rows.

VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364

The problem Query:

select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))

I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.

TIA,
Jack
...[posted and mailed, please reply in news]

Jack A (InformixMail@.yahoo.com) writes:
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)

DBCC PINTABLE is a command that very rarely is useful. If you have a
situation that you have a table that is referred to rearely, but
when it is referred to, you want the answers directly. Then you
have a case. Since these tables are referred to at log in and small,
I would assume that they are in memory anyway.

I could think of a possible rewrites of the query, but since this appears
to come from a third-party app, you don't seem to have any use for
that.

Without having the full information about the tables it is difficult
to say, but if it is correct that VGNCCB_ROLE_JT does not have a
clustered index, I think it is time to add one, and that would be
on (ROLE_ID). That could make the two indexes on USER_NAME and GROUP_ID
covering for the query, and could save you some bookmark lookups.

Another idea is to build an indexed view, and hope that SQL Server
will find the indexed view when looking for a query plan. But I am
not sure this is possible. And in any case, you need to have Enterprise
Edition for this to work.

I would encourage you to post the complete CREATE TABLE and CREATE INDEX
scripts for the tables. That makes it a little easier to guess.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK , Here goes with the table structure. BTW I've run DBCC reindex.

TABLE: VGNCCB_ROLE
PK__VGNCCB_ROLE__1FA39FB9clustered, unique, primary key located on
PRIMARY -- ROLE_ID

TABLE: VGNCCB_ROLE_JT
index_nameindex_descriptionindex_keys
PK__VGNCCB_ROLE_JT__218BE82Bclustered, unique, primary key located on
PRIMARY- ID
VGNCCB_ROLE_JT_INDEX1nonclustered located on PRIMARY- USER_NAME
VGNCCB_ROLE_JT_INDEX2nonclustered located on PRIMARY- GROUP_ID

TABLE: VGNCCB_GROUP_USER_JT
index_nameindex_descriptionindex_keys
PK__VGNCCB_GROUP_USE__1DBB5747clustered, unique, primary key located
on PRIMARY- ID
VGNCCB_GROUP_USER_JT_INDEX1nonclustered located on PRIMARY -GROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2nonclustered located on PRIMARY-
USER_NAME|||Jack A (InformixMail@.yahoo.com) writes:
> OK , Here goes with the table structure. BTW I've run DBCC reindex.

Thanks, but I explicitly asked for CREATE TABLE and CREATE INDEX statements.
That could permit me see if it is possible to build an indexed view.

Also, in VGNCCB_ROLE_JT, I can't even see that there is a ROLE_ID
column.

You can script tables and indexes in Enterprise Manager or Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
.See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>

Performance problems

Hi,
I have two questions:
1. I have an SQL server with over 400 databases. I takes
quite some time to load the databases in Enterprise
manager. Does anyone have any suggestions on how I can
speed up this process.
2. I also have one database that is 2 GB in size and it
takes some time to run queries from the database. Can
anyone recommend how I can speed up the database - for
example reindex it.
Thanks for your help.
Regards,
Matthew ReedFor 2. - need more info. What type of indexes are existing on the table?
What are the major types of queries ran from it? Any locking/blocking
issues? Percentage of inserts/updates/deletes versus queries, etc. For
straight index info you might try the Inside SQL Server 2000 book.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Matthew Reed" <anonymous@.discussions.microsoft.com> wrote in message
news:088a01c3d3a1$0c6d4930$a601280a@.phx.gbl...
> Hi,
> I have two questions:
> 1. I have an SQL server with over 400 databases. I takes
> quite some time to load the databases in Enterprise
> manager. Does anyone have any suggestions on how I can
> speed up this process.
> 2. I also have one database that is 2 GB in size and it
> takes some time to run queries from the database. Can
> anyone recommend how I can speed up the database - for
> example reindex it.
> Thanks for your help.
> Regards,
> Matthew Reedsql

Performance on Windows 2003 Standard w/ SQL SERVER 2000 Enterprise

I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matter
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and not
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, I read the support article before, but because my installation is on a
windows 2003 server. The article indicates that it already supports up to 4
GB for Windows 2003 server and only need to modify the boot.ini to use more
than 4GB. Am I wrong in reading it that way. Thanks.
"Tracy McKibben" wrote:
> WD wrote:
> > I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> > with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> > configuration is set to use all available resource on the box. But no matter
> > what we throw at it. It's using only 25% of CPU resource and the memory
> > usuage is never over 1.7G. The installation is definately Enterprise and not
> > standard. What am I missing?
> >
> > Thanks!
> The CPU utilization may mean nothing. Did you set the BOOT.INI switches
> for the additional memory?
> http://support.microsoft.com/kb/274750/
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Performance on Windows 2003 Standard w/ SQL SERVER 2000 Enterprise

I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard b
ox
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matt
er
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and n
ot
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Performance Monitors Start/Stop 7AM

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_logman.mspx
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx
--
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>

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 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...
>
>
|||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:
[vbcol=seagreen]
> 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:
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:

Performance monitor How to change server

I'm using SQL Server 2000.
On my desktop is Win XP Pro
I have Enterprise manager, Performance Monitor and SQL profiler
installed.
Each time I start performance monitor, the server monitored is the
local server.
How do I change the server I want to monitor? I've searched all the
menus, did right clicks, etc. and I can't change the server.
Thanks,
DWUnderneth the graph there is a histogram showing what you
are watching, it has the menu Color, Scale, Counter,
Instance ect.
Right Click on it and select 'Add Counter'
After that its fairly intuitive ;)
Peter
peternolan67REMOVETHIS@.hotmail.com
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill
>--Original Message--
>I'm using SQL Server 2000.
>On my desktop is Win XP Pro
>I have Enterprise manager, Performance Monitor and SQL
profiler
>installed.
>Each time I start performance monitor, the server
monitored is the
>local server.
>How do I change the server I want to monitor? I've
searched all the
>menus, did right clicks, etc. and I can't change the
server.
>Thanks,
>DW
>.
>

Performance Monitor Counters Missing

All,
I am running SQL Server 2000 Enterprise Edition (SP3a +
MS03-031 patch) on Windows 2000 Advanced Server (SP3) in a
cluster using Microsoft clustering.
I am missing the Performance Monitor Counters for SQL
Server on the active node. I am not sure if the counters
were ever there. The other node was active when I did perf
monitoring before and they were present on that node then.
Any ideas?Hi,
The following article may explain the problem
812915 FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error
Message
http://support.microsoft.com/?id=812915
Regards
Clive Challinor [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.