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
>