Performance Tuning UPDATE Statement

Below is a simple UPDATE that I have to perform on a table that has
about 2.5 million rows (about 4 million in production) This query
runs for an enourmous amount of time (over 1 hour). Both the
ChangerRoleID and the ChangerID are indexed (not unique). Is there
any way to performance tune this?

Controlling the physical drive of the log file isn't possible at our
client sites (we don't have control) and the recovery model needs to
be set to "Full".

UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
ChangerRoleID IS NULL

Any Help would be greatly appreciated!On 4 Aug 2004 08:27:50 -0700, MAS wrote:

>Below is a simple UPDATE that I have to perform on a table that has
>about 2.5 million rows (about 4 million in production) This query
>runs for an enourmous amount of time (over 1 hour). Both the
>ChangerRoleID and the ChangerID are indexed (not unique). Is there
>any way to performance tune this?
>Controlling the physical drive of the log file isn't possible at our
>client sites (we don't have control) and the recovery model needs to
>be set to "Full".
>UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
>ChangerRoleID IS NULL
>Any Help would be greatly appreciated!

Hi MAS,

If you remove the non-unique index on ChangerRoleID before doing the
update and recreate it afterwards, you'll probably save some time. The
index could have been useful if only a few of all rows match the IS NULL
condition, but with over aan hour execution time, I think there are so
many matches that a full table scan will be quicker. Removing the index
before doing the update saves SQL Server the extra work of constantly
having to update the index to keep it in sync with the data. Of course,
this might affect other queries that execute during the update and would
have benefited from this index. The index on ChangerID will neither be
used nor cause extra work for this update.

Check if there's a trigger that gets fired by the update. If you can
safely disable that trigger during the update process, do so. Same for
constraints: are there any CHECK or REFERENCES (foreign key) constraints
defined for ChangerRoleID? If so, disable constraint checking (again, only
if it is safe, i.e. you have to be sure that this update won't cause
violation of the constraint *and* that no other person accessing the
database during the time constraint checking is disabled will be able to
cause violations of the constraint).

You state that the recovery model needs to be full; from that I conclude
that you can't lock other users out of the database during the update. Can
you at least take measures to prevent other users from using (updating,
but preferably reading as well) the CLIENTSHISTORY table?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

MAS (mas32677@.hotmail.com) writes:
> Below is a simple UPDATE that I have to perform on a table that has
> about 2.5 million rows (about 4 million in production) This query
> runs for an enourmous amount of time (over 1 hour). Both the
> ChangerRoleID and the ChangerID are indexed (not unique). Is there
> any way to performance tune this?
> Controlling the physical drive of the log file isn't possible at our
> client sites (we don't have control) and the recovery model needs to
> be set to "Full".
> UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
> ChangerRoleID IS NULL
> Any Help would be greatly appreciated!

To add to what Hugo said, if that index on ChangerRoleID is clustered,
and many rows have a NULL value, then you are in for a problem.

It may help to do it batches:

DECLARE @.batch_size int, @.rowc int
SELECT @.batch_size = 50000
SELECT @.rowc = @.batch_size
SET ROWCOUNT @.batch_size
WHILE @.rowc = @.batch_size
BEGIN
UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID
WHERE ChangerRoleID IS NULL
AND ChangerID IS NOT NULL
SELECT @.rowc = @.@.rowcount
END
SET ROWCOUNT 0

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Performance questions

We have a production database that sits on a 4 proc server with 4 GB of memory and SAN disk storage via fiber. There are some stored procedures that run and they take approximately 10 minutes to run. A developer has SQL Server installed on his local pc that has 1 2.5 GHz processor and 2 GB of memory and the stored procedures run in approximately 2 minutes. I have updated statistics and rebuilt indexes to no avail. He is questioning why it runs so much faster on his smaller pc compared to the production environment. I have monitored CPU, Memory, and Disk Queue Length and none of these performance counters look concerning to me while the stored procedures are running.

Can anyone out there give me some input on what I could check to figure out why we are experiencing this performance difference?

Thanks,

Corey

Do both databases have the same volume of data? You did not mention what speed and type of processors were in the server. If it is an older server, with say, 1GHz Pentium 3 based Xeon CPU's, you could easily see much slower query performance than on a developer's workstation, with a single faster CPU.

You should also look at the graphical execution plan for the query on the server, and see whether or not the plan is being parallelized or not. BTW, what version of SQL Server are you running?

|||

The data is the same between the two environments. His pc has a 2.5 GHz Intel processor and our server has 4 2.7 GHz Intel processors. We are running SQL Server 2000 SP3a. I will try to look at the queries but their are hundreds. Should the query plan be the same since I ran update statistics, rebuild indexes, and then the process so that I was comparing as each as close as possible.

|||

Just pick two or three commonly executed queries and run them in Query Analyzer with the graphical execution plan turned on and with SET STATISTICS IO on (just run that statement before you run the queries).

Do you see similar query plans and I/O statistics between the developer's workstation and your server?

Are we comparing a developer's workstation with no load with a production server with a full-work load?

If so, I would start looking at the production server to see if you see signs of CPU pressure, memory pressure, I/O bottlenecks, etc.

|||

I will try to get a couple of queries and compare them as suggested.

The developer's workstation has no load and the production server does have other work going on but this server does have plenty of resources left and it is a much larger box than the developer's workstation. I will go ahead and grab some perfmon counters to verify this too.

sql

performance Problems

I am setting counters for performance monitor and have
about 12 different counters. I have 13 production
servers. How can i create a .msc file and use the same
file for all the servers. Now I think i need to create
each on the server manually.
is there any better way of doing this.
Thanks
AjaOne tool you may want to review is PSSDIAG. It can capture PerfMon, Profiler
and blocking traces.
PSSDIAG data collection utility
http://support.microsoft.com/default.aspx?scid=kb;en-us;830232
Internal SQL Server Diagnostics Tools, Part 1: PSSDiag
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_12072004.asp
Adrian
"aja" <anonymous@.discussions.microsoft.com> wrote in message
news:08cc01c52e64$3c526fd0$a501280a@.phx.gbl...
>I am setting counters for performance monitor and have
> about 12 different counters. I have 13 production
> servers. How can i create a .msc file and use the same
> file for all the servers. Now I think i need to create
> each on the server manually.
> is there any better way of doing this.
> Thanks
> Aja

Performance Problem

I was getting the performance monitor objects for SQL server for my producti
on server and I was monitoring them perfectly. But after I've restarted my S
QL Server Service I don't know I'm not getting performance monitor objects f
or SQL server in my perform
ance monitor.
I need to monitor again. Can someone help me in this regard
Regards,
SunilHi,
Verify that MSSQL Server service is started. If it is not started then start
the service and wait for a 10 minutes and then try opening the performance
monitor counters.
Note:
I just tried before doing this post.
Thanks
Hari
MCDBA
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:062BFE35-989F-49CD-9BC3-4FBE9F36A9FC@.microsoft.com...
> I was getting the performance monitor objects for SQL server for my
production server and I was monitoring them perfectly. But after I've
restarted my SQL Server Service I don't know I'm not getting performance
monitor objects for SQL server in my performance monitor.
>
> I need to monitor again. Can someone help me in this regard
>
> Regards,
> Sunil

Performance Problem

I was getting the performance monitor objects for SQL server for my production server and I was monitoring them perfectly. But after I've restarted my SQL Server Service I don't know I'm not getting performance monitor objects for SQL server in my perform
ance monitor.
I need to monitor again. Can someone help me in this regard
Regards,
Sunil
Hi,
Verify that MSSQL Server service is started. If it is not started then start
the service and wait for a 10 minutes and then try opening the performance
monitor counters.
Note:
I just tried before doing this post.
Thanks
Hari
MCDBA
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:062BFE35-989F-49CD-9BC3-4FBE9F36A9FC@.microsoft.com...
> I was getting the performance monitor objects for SQL server for my
production server and I was monitoring them perfectly. But after I've
restarted my SQL Server Service I don't know I'm not getting performance
monitor objects for SQL server in my performance monitor.
>
> I need to monitor again. Can someone help me in this regard
>
> Regards,
> Sunil

Performance Problem

I was getting the performance monitor objects for SQL server for my production server and I was monitoring them perfectly. But after I've restarted my SQL Server Service I don't know I'm not getting performance monitor objects for SQL server in my performance monitor
I need to monitor again. Can someone help me in this regar
Regards
SunilHi,
Verify that MSSQL Server service is started. If it is not started then start
the service and wait for a 10 minutes and then try opening the performance
monitor counters.
Note:
I just tried before doing this post.
Thanks
Hari
MCDBA
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:062BFE35-989F-49CD-9BC3-4FBE9F36A9FC@.microsoft.com...
> I was getting the performance monitor objects for SQL server for my
production server and I was monitoring them perfectly. But after I've
restarted my SQL Server Service I don't know I'm not getting performance
monitor objects for SQL server in my performance monitor.
>
> I need to monitor again. Can someone help me in this regard
>
> Regards,
> Sunil

performance monitoring

sql2k
Howdy kids. I need to start monitoring performance on our
Production SQL box. My boss is interested in query wait
times and transactions per second mainly. The problem is
I cant figure out how to come up with that info. What do
some of you look @. for performance monitoring? We arent
having any problems but wanting to take a more proactive
approach. Where do I start? What do I monitor in either
Profiler or Perfromance Monitor? Ive always been more
from the "if it aint broke" school of thought so Im at a
real loss here? Articles/ white papers would be
especially useful.Hi Chris,
I wrote a white paper exactly on this topic. There's also a Powerpoint
presentation with audio available on the website. It's free, but you might
have to register to get it.
Check out this URL:
http://www.quest.com/content/list.asp?ContentTypeID=1&Format=table&nav=%2Fsolutions%2Fnavigation%2Exml&cat=34
There's also some really good freeware there for SQL Server users.
Spotlight for SQL Server is waaay better than any other diagnostic tool out
there.
Best regards,
-Kevin (SQL Server MVP)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:3b5501c4aa6a$b50e8720$a401280a@.phx.gbl...
> sql2k
> Howdy kids. I need to start monitoring performance on our
> Production SQL box. My boss is interested in query wait
> times and transactions per second mainly. The problem is
> I cant figure out how to come up with that info. What do
> some of you look @. for performance monitoring? We arent
> having any problems but wanting to take a more proactive
> approach. Where do I start? What do I monitor in either
> Profiler or Perfromance Monitor? Ive always been more
> from the "if it aint broke" school of thought so Im at a
> real loss here? Articles/ white papers would be
> especially useful.

Performance Monitoring

Hi all,
I'm in the process of monitoring a production SQL Server for the very first
time using perfmon - so I'm very green in this area. I was seeing some
heavy CPU spikes and I've tracked them down to a few large reporting queries
using Profiler with CPU numbers like 126766 and Reads like 20473871. These
numbers seem extremely high compared to the other numbers for comon procs
and statements (% Processor Avg for both procs about 5% when these queries
are not run)
Now I'm trying to understand the memory utilization. The server contains
2GB of memory. Task Manager shows Total Physical Memory at 2096556, Memory
Usage at 1973076 and SQL Server memory usage at 1695460. I set up the
counters in perfmon and here are the values:
Counter Average Scale
Activity
Target Server Memory (KB) 1677928 .00001
Constant
Total Server Memory (KB) 1677928 .00001
Constant
Available Bytes (KB) 146315 .0001
Constant
Pages/Sec .495 1
Constant
Page Faults/Sec 60 1
Spikes
SQL Server is running on a dedicated machine with dynamic memory enabled 0
Min 2047MB Max. To me it looks as though SQL Server has maxed out the
available memory. I haven't gotten any performance calls where the system
slows down except when those rouge queries are run. I've spoken with the
user running the queries are we're looking at running the reporting queries
during non-peak hours to reduce the performance impact. The functionality
of the database on this server is in the process of being moved to a product
called Maximo that will be placed on a different SQL Server sometime in the
near future.
Based on the numbers provided can someone help me understand the memory
usage and possibly make some suggestions/recommendations?
Thanks!
JerryJerry,
I could spew a bunch of info, but it might be more helpful if you hit the
SQL-Server-Performance site and read all the links related to Performance
Monitor.
(Give a man a fish, feed him for a day. Teach him to fish and feed him for
life)
http://www.sql-server-performance.com/
you'll see all the links towards the bottom of the homepage here.
You spend an hour or two here, and you'll be good to go...
if you have further questions, feel free to post back
Cheers
Greg Jackson
PDX, Oregon|||Jerry,
The counters seem perfectly normal. SQL Server will use all available
memory (minus a little for the OS) if there are no other apps on the same
machine requesting memory. The Pagess/sec and Page Faults also indicate
there is very little paging going on which is what SQL Server likes. Your
best bet is to tune those queries so they don't do so many reads and they
won't affect everyone as much.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OePhBlUYFHA.4032@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm in the process of monitoring a production SQL Server for the very
> first time using perfmon - so I'm very green in this area. I was seeing
> some heavy CPU spikes and I've tracked them down to a few large reporting
> queries using Profiler with CPU numbers like 126766 and Reads like
> 20473871. These numbers seem extremely high compared to the other numbers
> for comon procs and statements (% Processor Avg for both procs about 5%
> when these queries are not run)
> Now I'm trying to understand the memory utilization. The server contains
> 2GB of memory. Task Manager shows Total Physical Memory at 2096556,
> Memory Usage at 1973076 and SQL Server memory usage at 1695460. I set up
> the counters in perfmon and here are the values:
> Counter Average
> Scale Activity
> Target Server Memory (KB) 1677928 .00001 Constant
> Total Server Memory (KB) 1677928 .00001 Constant
> Available Bytes (KB) 146315 .0001
> Constant
> Pages/Sec .495 1
> Constant
> Page Faults/Sec 60 1
> Spikes
> SQL Server is running on a dedicated machine with dynamic memory enabled 0
> Min 2047MB Max. To me it looks as though SQL Server has maxed out the
> available memory. I haven't gotten any performance calls where the system
> slows down except when those rouge queries are run. I've spoken with the
> user running the queries are we're looking at running the reporting
> queries during non-peak hours to reduce the performance impact. The
> functionality of the database on this server is in the process of being
> moved to a product called Maximo that will be placed on a different SQL
> Server sometime in the near future.
> Based on the numbers provided can someone help me understand the memory
> usage and possibly make some suggestions/recommendations?
> Thanks!
> Jerry
>|||Awesome! Thanks Andrew.
Yea...96% CacheHit Ratio so that's good.
Working on those problematic queries now.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkwwybYYFHA.1148@.tk2msftngp13.phx.gbl...
> Jerry,
> The counters seem perfectly normal. SQL Server will use all available
> memory (minus a little for the OS) if there are no other apps on the same
> machine requesting memory. The Pagess/sec and Page Faults also indicate
> there is very little paging going on which is what SQL Server likes. Your
> best bet is to tune those queries so they don't do so many reads and they
> won't affect everyone as much.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OePhBlUYFHA.4032@.tk2msftngp13.phx.gbl...
> > Hi all,
> >
> > I'm in the process of monitoring a production SQL Server for the very
> > first time using perfmon - so I'm very green in this area. I was seeing
> > some heavy CPU spikes and I've tracked them down to a few large
reporting
> > queries using Profiler with CPU numbers like 126766 and Reads like
> > 20473871. These numbers seem extremely high compared to the other
numbers
> > for comon procs and statements (% Processor Avg for both procs about 5%
> > when these queries are not run)
> >
> > Now I'm trying to understand the memory utilization. The server
contains
> > 2GB of memory. Task Manager shows Total Physical Memory at 2096556,
> > Memory Usage at 1973076 and SQL Server memory usage at 1695460. I set
up
> > the counters in perfmon and here are the values:
> >
> > Counter Average
> > Scale Activity
> > Target Server Memory (KB) 1677928 .00001 Constant
> > Total Server Memory (KB) 1677928 .00001 Constant
> > Available Bytes (KB) 146315 .0001
> > Constant
> > Pages/Sec .495
1
> > Constant
> > Page Faults/Sec 60 1
> > Spikes
> >
> > SQL Server is running on a dedicated machine with dynamic memory enabled
0
> > Min 2047MB Max. To me it looks as though SQL Server has maxed out the
> > available memory. I haven't gotten any performance calls where the
system
> > slows down except when those rouge queries are run. I've spoken with
the
> > user running the queries are we're looking at running the reporting
> > queries during non-peak hours to reduce the performance impact. The
> > functionality of the database on this server is in the process of being
> > moved to a product called Maximo that will be placed on a different SQL
> > Server sometime in the near future.
> >
> > Based on the numbers provided can someone help me understand the memory
> > usage and possibly make some suggestions/recommendations?
> >
> > Thanks!
> >
> > Jerry
> >
> >
>|||Actually 96% is OK but not great. Great would be 99% or greater<g>. In
your case it is probably those large queries that are pulling data from disk
that is dropping it down. Once you attack them you should see it get closer
to 99%.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jerrysp69@.hotmail.com> wrote in message
news:%23lPC02YYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Awesome! Thanks Andrew.
> Yea...96% CacheHit Ratio so that's good.
> Working on those problematic queries now.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkwwybYYFHA.1148@.tk2msftngp13.phx.gbl...
>> Jerry,
>> The counters seem perfectly normal. SQL Server will use all available
>> memory (minus a little for the OS) if there are no other apps on the same
>> machine requesting memory. The Pagess/sec and Page Faults also indicate
>> there is very little paging going on which is what SQL Server likes.
>> Your
>> best bet is to tune those queries so they don't do so many reads and they
>> won't affect everyone as much.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OePhBlUYFHA.4032@.tk2msftngp13.phx.gbl...
>> > Hi all,
>> >
>> > I'm in the process of monitoring a production SQL Server for the very
>> > first time using perfmon - so I'm very green in this area. I was
>> > seeing
>> > some heavy CPU spikes and I've tracked them down to a few large
> reporting
>> > queries using Profiler with CPU numbers like 126766 and Reads like
>> > 20473871. These numbers seem extremely high compared to the other
> numbers
>> > for comon procs and statements (% Processor Avg for both procs about 5%
>> > when these queries are not run)
>> >
>> > Now I'm trying to understand the memory utilization. The server
> contains
>> > 2GB of memory. Task Manager shows Total Physical Memory at 2096556,
>> > Memory Usage at 1973076 and SQL Server memory usage at 1695460. I set
> up
>> > the counters in perfmon and here are the values:
>> >
>> > Counter Average
>> > Scale Activity
>> > Target Server Memory (KB) 1677928 .00001 Constant
>> > Total Server Memory (KB) 1677928 .00001
>> > Constant
>> > Available Bytes (KB) 146315 .0001
>> > Constant
>> > Pages/Sec .495
> 1
>> > Constant
>> > Page Faults/Sec 60
>> > 1
>> > Spikes
>> >
>> > SQL Server is running on a dedicated machine with dynamic memory
>> > enabled
> 0
>> > Min 2047MB Max. To me it looks as though SQL Server has maxed out the
>> > available memory. I haven't gotten any performance calls where the
> system
>> > slows down except when those rouge queries are run. I've spoken with
> the
>> > user running the queries are we're looking at running the reporting
>> > queries during non-peak hours to reduce the performance impact. The
>> > functionality of the database on this server is in the process of being
>> > moved to a product called Maximo that will be placed on a different SQL
>> > Server sometime in the near future.
>> >
>> > Based on the numbers provided can someone help me understand the memory
>> > usage and possibly make some suggestions/recommendations?
>> >
>> > Thanks!
>> >
>> > Jerry
>> >
>> >
>>
>

Performance Monitoring

Hi all,
I'm in the process of monitoring a production SQL Server for the very first
time using perfmon - so I'm very green in this area. I was seeing some
heavy CPU spikes and I've tracked them down to a few large reporting queries
using Profiler with CPU numbers like 126766 and Reads like 20473871. These
numbers seem extremely high compared to the other numbers for comon procs
and statements (% Processor Avg for both procs about 5% when these queries
are not run)
Now I'm trying to understand the memory utilization. The server contains
2GB of memory. Task Manager shows Total Physical Memory at 2096556, Memory
Usage at 1973076 and SQL Server memory usage at 1695460. I set up the
counters in perfmon and here are the values:
Counter Average Scale
Activity
Target Server Memory (KB) 1677928 .00001
Constant
Total Server Memory (KB) 1677928 .00001
Constant
Available Bytes (KB) 146315 .0001
Constant
Pages/Sec .495 1
Constant
Page Faults/Sec 60 1
Spikes
SQL Server is running on a dedicated machine with dynamic memory enabled 0
Min 2047MB Max. To me it looks as though SQL Server has maxed out the
available memory. I haven't gotten any performance calls where the system
slows down except when those rouge queries are run. I've spoken with the
user running the queries are we're looking at running the reporting queries
during non-peak hours to reduce the performance impact. The functionality
of the database on this server is in the process of being moved to a product
called Maximo that will be placed on a different SQL Server sometime in the
near future.
Based on the numbers provided can someone help me understand the memory
usage and possibly make some suggestions/recommendations?
Thanks!
Jerry
Jerry,
I could spew a bunch of info, but it might be more helpful if you hit the
SQL-Server-Performance site and read all the links related to Performance
Monitor.
(Give a man a fish, feed him for a day. Teach him to fish and feed him for
life)
http://www.sql-server-performance.com/
you'll see all the links towards the bottom of the homepage here.
You spend an hour or two here, and you'll be good to go...
if you have further questions, feel free to post back
Cheers
Greg Jackson
PDX, Oregon
|||Jerry,
The counters seem perfectly normal. SQL Server will use all available
memory (minus a little for the OS) if there are no other apps on the same
machine requesting memory. The Pagess/sec and Page Faults also indicate
there is very little paging going on which is what SQL Server likes. Your
best bet is to tune those queries so they don't do so many reads and they
won't affect everyone as much.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OePhBlUYFHA.4032@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm in the process of monitoring a production SQL Server for the very
> first time using perfmon - so I'm very green in this area. I was seeing
> some heavy CPU spikes and I've tracked them down to a few large reporting
> queries using Profiler with CPU numbers like 126766 and Reads like
> 20473871. These numbers seem extremely high compared to the other numbers
> for comon procs and statements (% Processor Avg for both procs about 5%
> when these queries are not run)
> Now I'm trying to understand the memory utilization. The server contains
> 2GB of memory. Task Manager shows Total Physical Memory at 2096556,
> Memory Usage at 1973076 and SQL Server memory usage at 1695460. I set up
> the counters in perfmon and here are the values:
> Counter Average
> Scale Activity
> Target Server Memory (KB) 1677928 .00001 Constant
> Total Server Memory (KB) 1677928 .00001 Constant
> Available Bytes (KB) 146315 .0001
> Constant
> Pages/Sec .495 1
> Constant
> Page Faults/Sec 60 1
> Spikes
> SQL Server is running on a dedicated machine with dynamic memory enabled 0
> Min 2047MB Max. To me it looks as though SQL Server has maxed out the
> available memory. I haven't gotten any performance calls where the system
> slows down except when those rouge queries are run. I've spoken with the
> user running the queries are we're looking at running the reporting
> queries during non-peak hours to reduce the performance impact. The
> functionality of the database on this server is in the process of being
> moved to a product called Maximo that will be placed on a different SQL
> Server sometime in the near future.
> Based on the numbers provided can someone help me understand the memory
> usage and possibly make some suggestions/recommendations?
> Thanks!
> Jerry
>
|||Awesome! Thanks Andrew.
Yea...96% CacheHit Ratio so that's good.
Working on those problematic queries now.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkwwybYYFHA.1148@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Jerry,
> The counters seem perfectly normal. SQL Server will use all available
> memory (minus a little for the OS) if there are no other apps on the same
> machine requesting memory. The Pagess/sec and Page Faults also indicate
> there is very little paging going on which is what SQL Server likes. Your
> best bet is to tune those queries so they don't do so many reads and they
> won't affect everyone as much.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OePhBlUYFHA.4032@.tk2msftngp13.phx.gbl...
reporting[vbcol=seagreen]
numbers[vbcol=seagreen]
contains[vbcol=seagreen]
up[vbcol=seagreen]
1[vbcol=seagreen]
0[vbcol=seagreen]
system[vbcol=seagreen]
the
>
|||Actually 96% is OK but not great. Great would be 99% or greater<g>. In
your case it is probably those large queries that are pulling data from disk
that is dropping it down. Once you attack them you should see it get closer
to 99%.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jerrysp69@.hotmail.com> wrote in message
news:%23lPC02YYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Awesome! Thanks Andrew.
> Yea...96% CacheHit Ratio so that's good.
> Working on those problematic queries now.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkwwybYYFHA.1148@.tk2msftngp13.phx.gbl...
> reporting
> numbers
> contains
> up
> 1
> 0
> system
> the
>

Performance Monitoring

Hi all,
I'm in the process of monitoring a production SQL Server for the very first
time using perfmon - so I'm very green in this area. I was seeing some
heavy CPU spikes and I've tracked them down to a few large reporting queries
using Profiler with CPU numbers like 126766 and Reads like 20473871. These
numbers seem extremely high compared to the other numbers for comon procs
and statements (% Processor Avg for both procs about 5% when these queries
are not run)
Now I'm trying to understand the memory utilization. The server contains
2GB of memory. Task Manager shows Total Physical Memory at 2096556, Memory
Usage at 1973076 and SQL Server memory usage at 1695460. I set up the
counters in perfmon and here are the values:
Counter Average Scale
Activity
Target Server Memory (KB) 1677928 .00001
Constant
Total Server Memory (KB) 1677928 .00001
Constant
Available Bytes (KB) 146315 .0001
Constant
Pages/Sec .495 1
Constant
Page Faults/Sec 60 1
Spikes
SQL Server is running on a dedicated machine with dynamic memory enabled 0
Min 2047MB Max. To me it looks as though SQL Server has maxed out the
available memory. I haven't gotten any performance calls where the system
slows down except when those rouge queries are run. I've spoken with the
user running the queries are we're looking at running the reporting queries
during non-peak hours to reduce the performance impact. The functionality
of the database on this server is in the process of being moved to a product
called Maximo that will be placed on a different SQL Server sometime in the
near future.
Based on the numbers provided can someone help me understand the memory
usage and possibly make some suggestions/recommendations?
Thanks!
JerryJerry,
I could spew a bunch of info, but it might be more helpful if you hit the
SQL-Server-Performance site and read all the links related to Performance
Monitor.
(Give a man a fish, feed him for a day. Teach him to fish and feed him for
life)
http://www.sql-server-performance.com/
you'll see all the links towards the bottom of the homepage here.
You spend an hour or two here, and you'll be good to go...
if you have further questions, feel free to post back
Cheers
Greg Jackson
PDX, Oregon|||Jerry,
The counters seem perfectly normal. SQL Server will use all available
memory (minus a little for the OS) if there are no other apps on the same
machine requesting memory. The Pagess/sec and Page Faults also indicate
there is very little paging going on which is what SQL Server likes. Your
best bet is to tune those queries so they don't do so many reads and they
won't affect everyone as much.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OePhBlUYFHA.4032@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm in the process of monitoring a production SQL Server for the very
> first time using perfmon - so I'm very green in this area. I was seeing
> some heavy CPU spikes and I've tracked them down to a few large reporting
> queries using Profiler with CPU numbers like 126766 and Reads like
> 20473871. These numbers seem extremely high compared to the other numbers
> for comon procs and statements (% Processor Avg for both procs about 5%
> when these queries are not run)
> Now I'm trying to understand the memory utilization. The server contains
> 2GB of memory. Task Manager shows Total Physical Memory at 2096556,
> Memory Usage at 1973076 and SQL Server memory usage at 1695460. I set up
> the counters in perfmon and here are the values:
> Counter Average
> Scale Activity
> Target Server Memory (KB) 1677928 .00001 Constant
> Total Server Memory (KB) 1677928 .00001 Constant
> Available Bytes (KB) 146315 .0001
> Constant
> Pages/Sec .495 1
> Constant
> Page Faults/Sec 60 1
> Spikes
> SQL Server is running on a dedicated machine with dynamic memory enabled 0
> Min 2047MB Max. To me it looks as though SQL Server has maxed out the
> available memory. I haven't gotten any performance calls where the system
> slows down except when those rouge queries are run. I've spoken with the
> user running the queries are we're looking at running the reporting
> queries during non-peak hours to reduce the performance impact. The
> functionality of the database on this server is in the process of being
> moved to a product called Maximo that will be placed on a different SQL
> Server sometime in the near future.
> Based on the numbers provided can someone help me understand the memory
> usage and possibly make some suggestions/recommendations?
> Thanks!
> Jerry
>|||Awesome! Thanks Andrew.
Yea...96% CacheHit Ratio so that's good.
Working on those problematic queries now.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkwwybYYFHA.1148@.tk2msftngp13.phx.gbl...
> Jerry,
> The counters seem perfectly normal. SQL Server will use all available
> memory (minus a little for the OS) if there are no other apps on the same
> machine requesting memory. The Pagess/sec and Page Faults also indicate
> there is very little paging going on which is what SQL Server likes. Your
> best bet is to tune those queries so they don't do so many reads and they
> won't affect everyone as much.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OePhBlUYFHA.4032@.tk2msftngp13.phx.gbl...
reporting[vbcol=seagreen]
numbers[vbcol=seagreen]
contains[vbcol=seagreen]
up[vbcol=seagreen]
1[vbcol=seagreen]
0[vbcol=seagreen]
system[vbcol=seagreen]
the[vbcol=seagreen]
>|||Actually 96% is OK but not great. Great would be 99% or greater<g>. In
your case it is probably those large queries that are pulling data from disk
that is dropping it down. Once you attack them you should see it get closer
to 99%.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jerrysp69@.hotmail.com> wrote in message
news:%23lPC02YYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Awesome! Thanks Andrew.
> Yea...96% CacheHit Ratio so that's good.
> Working on those problematic queries now.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkwwybYYFHA.1148@.tk2msftngp13.phx.gbl...
> reporting
> numbers
> contains
> up
> 1
> 0
> system
> the
>