I am a new admin at our office and I am trying to get a handle on the SQL
systems running.
I followed a few online guides to performance monitoring and I have a few
concerns that I wanted to run past some experts.
This SQL server is a dual PIII 1.4 GHz with 2 GB of RAM.
So far I have monitored a few SQL specific counters along with the proc and
memory. Under normal load, everything has seemed good so far, but when we
run reports, the SQL server goes under a heavy load that appears to be
stressing the system. Under Task Manager, the procs spike a lot (not a
curtain though) and the server becomes sluggish. This is what I have
discovered so far from perfmon:
1) Under the processor queue length, the graph is spiking over 200, but it
lists a maximum of 49 on the chart. Either way, this seems to be pretty
high.
2) Total proc time is averaging at around 50%
3) The sqlserver Working Set is at 1.6 GB
4) Pages/Sec frequently exceeds 50 with an average at 20 and a maximum of
755
5) Available MBytes is at 10 - 16
6) Page File % usage is at 38%
7) Non-Paged Pool Memory was around 26 MB
During heavy load, I also lost some collection of data via perfmon at 5
second intervals. My interpretation from this is that SQL has sucked up all
available RAM and wants more. I have not rebooted to see how this effects
the server. The proc's seem to be overloaded under heavy load with the
queue length filing up, but an avg of 50% shouldn't need more processing.
Also I attribute the loss of data collection to the high queue length.
What are possible resolutions? Do I need to collect more data? I suspect
that SQL is not using the 1.6 GB assigned to it very effectively. Is it
possible that some of the applications we have running are somehow either
causing a memory leak, or locking the memory in SQL? If that is an issue,
how can I detect what memory inside SQL is locked/wasted?
Thank You for any assistance,
Kevin Hammonddatabase engines are essentially a disk cache, so it so
should be using all the memory it needs if it is
available, otherwise, it would have to go to disk for the
data,
if you are concerned as to whether SQL server really needs
all that memory, monitor disk read/sec, writes/sec, avg
disk queue len, avg sec/read and avg sec/write for the
physical disk containing the main data files (not the log)
then try reducing the amount of memory SQL Server can use,
if the disk counters rise, then SQL Server was making good
use of that memory
>--Original Message--
>I am a new admin at our office and I am trying to get a
handle on the SQL
>systems running.
>I followed a few online guides to performance monitoring
and I have a few
>concerns that I wanted to run past some experts.
>This SQL server is a dual PIII 1.4 GHz with 2 GB of RAM.
>So far I have monitored a few SQL specific counters along
with the proc and
>memory. Under normal load, everything has seemed good so
far, but when we
>run reports, the SQL server goes under a heavy load that
appears to be
>stressing the system. Under Task Manager, the procs
spike a lot (not a
>curtain though) and the server becomes sluggish. This is
what I have
>discovered so far from perfmon:
>1) Under the processor queue length, the graph is
spiking over 200, but it
>lists a maximum of 49 on the chart. Either way, this
seems to be pretty
>high.
>2) Total proc time is averaging at around 50%
>3) The sqlserver Working Set is at 1.6 GB
>4) Pages/Sec frequently exceeds 50 with an average at 20
and a maximum of
>755
>5) Available MBytes is at 10 - 16
>6) Page File % usage is at 38%
>7) Non-Paged Pool Memory was around 26 MB
>During heavy load, I also lost some collection of data
via perfmon at 5
>second intervals. My interpretation from this is that
SQL has sucked up all
>available RAM and wants more. I have not rebooted to see
how this effects
>the server. The proc's seem to be overloaded under heavy
load with the
>queue length filing up, but an avg of 50% shouldn't need
more processing.
>Also I attribute the loss of data collection to the high
queue length.
>What are possible resolutions? Do I need to collect more
data? I suspect
>that SQL is not using the 1.6 GB assigned to it very
effectively. Is it
>possible that some of the applications we have running
are somehow either
>causing a memory leak, or locking the memory in SQL? If
that is an issue,
>how can I detect what memory inside SQL is locked/wasted?
>Thank You for any assistance,
>Kevin Hammond
>
>.
>
Showing posts with label perfmon. Show all posts
Showing posts with label perfmon. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
Performance Optimizing
I am trying to monitor performancce of a database using PerfMon, but the
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?Do you have more than 99 DBs on your SQL instance (including master,
model & tempdb)? SQL used to have a problem keeping perfmon stats for
dbids > 99. I don't know if Microsoft have fixed that issue with SQL
2005 or not. If that's what's happening with you then in perfmon you'll
see DBs whose dbid() <= 99 but not those DBs over that.
*mike hodgson*
http://sqlnerd.blogspot.com
Paul wrote:
>I am trying to monitor performancce of a database using PerfMon, but the
>database instance does not show in PerfMon. I have noticed that some
>databases show up in the list and some don't. These database are all active
>and running. Why is this?
>
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?Do you have more than 99 DBs on your SQL instance (including master,
model & tempdb)? SQL used to have a problem keeping perfmon stats for
dbids > 99. I don't know if Microsoft have fixed that issue with SQL
2005 or not. If that's what's happening with you then in perfmon you'll
see DBs whose dbid() <= 99 but not those DBs over that.
*mike hodgson*
http://sqlnerd.blogspot.com
Paul wrote:
>I am trying to monitor performancce of a database using PerfMon, but the
>database instance does not show in PerfMon. I have noticed that some
>databases show up in the list and some don't. These database are all active
>and running. Why is this?
>
Performance Optimizing
I am trying to monitor performancce of a database using PerfMon, but the
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?This is a multi-part message in MIME format.
--060202050209050201010409
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Do you have more than 99 DBs on your SQL instance (including master,
model & tempdb)? SQL used to have a problem keeping perfmon stats for
dbids > 99. I don't know if Microsoft have fixed that issue with SQL
2005 or not. If that's what's happening with you then in perfmon you'll
see DBs whose dbid() <= 99 but not those DBs over that.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Paul wrote:
>I am trying to monitor performancce of a database using PerfMon, but the
>database instance does not show in PerfMon. I have noticed that some
>databases show up in the list and some don't. These database are all active
>and running. Why is this?
>
--060202050209050201010409
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Do you have more than 99 DBs on your SQL instance (including
master, model & tempdb)? SQL used to have a problem keeping
perfmon stats for dbids > 99. I don't know if Microsoft have fixed
that issue with SQL 2005 or not. If that's what's happening with you
then in perfmon you'll see DBs whose dbid() <= 99 but not those DBs
over that.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Paul wrote:
<blockquote cite="midFDAEF3DA-76A8-4A36-920B-36D6E7DCFB0D@.microsoft.com"
type="cite">
<pre wrap="">I am trying to monitor performancce of a database using PerfMon, but the
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?
</pre>
</blockquote>
</body>
</html>
--060202050209050201010409--
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?This is a multi-part message in MIME format.
--060202050209050201010409
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Do you have more than 99 DBs on your SQL instance (including master,
model & tempdb)? SQL used to have a problem keeping perfmon stats for
dbids > 99. I don't know if Microsoft have fixed that issue with SQL
2005 or not. If that's what's happening with you then in perfmon you'll
see DBs whose dbid() <= 99 but not those DBs over that.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Paul wrote:
>I am trying to monitor performancce of a database using PerfMon, but the
>database instance does not show in PerfMon. I have noticed that some
>databases show up in the list and some don't. These database are all active
>and running. Why is this?
>
--060202050209050201010409
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Do you have more than 99 DBs on your SQL instance (including
master, model & tempdb)? SQL used to have a problem keeping
perfmon stats for dbids > 99. I don't know if Microsoft have fixed
that issue with SQL 2005 or not. If that's what's happening with you
then in perfmon you'll see DBs whose dbid() <= 99 but not those DBs
over that.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Paul wrote:
<blockquote cite="midFDAEF3DA-76A8-4A36-920B-36D6E7DCFB0D@.microsoft.com"
type="cite">
<pre wrap="">I am trying to monitor performancce of a database using PerfMon, but the
database instance does not show in PerfMon. I have noticed that some
databases show up in the list and some don't. These database are all active
and running. Why is this?
</pre>
</blockquote>
</body>
</html>
--060202050209050201010409--
Saturday, February 25, 2012
Performance Object SQL Server
Bart
I'm nbot sure uinderstand you. Click Start --Run --perfmon.
On the sceen right click and Add counters. There is Perfomance Object
combobox where you can choose many counters for SQL Server as well
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23EaGga8PHHA.404@.TK2MSFTNGP02.phx.gbl...
> Hello
> We are running W2003 Server with SQL Server 2000. Now I want to monitor
> performance using the administrative tool Performance.
> According to the SQL Server 2000 System Administration (Chapter 14), there
> should be several SQL Server Performance Objects to track SQL server
> activity, but...
> I can't find them. What happend, where are they.
> Anyone?
> Thanks,
> Bart
>
Bart
http://support.microsoft.com/kb/227662
http://support.microsoft.com/kb/246328
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
> Some Extra information (Had a chat with our System Administrator).
> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another that
> came with our backup program (BACKUPEXEC) to store backup information. The
> last one I can see in the Performance Monitor. But that's not the one I
> would like to monitor. Any idea how to get the objects of the other SQL
> server into the performance monitor, or maybe how to swap them.
> thanks
> Bart
>
|||Bart
You are right, my mistake, sorry
For detailed information, please reference the following article:
330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
http://support.microsoft.com/?id=330088
SQL Server 2000 Performance Counters Do Not Appear in the Performance
http://support.microsoft.com/?id=827260
http://support.microsoft.com/?id=812915
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Those articles do not apply to SQLServer 2000.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
|||Bart
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
I have my doubt that a 'bad' written query will perform better if you add
more memory. I'd run SQL Server Profiler to identify long running queries
and start tunning them
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri, but none of the provided solutions helped.
> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
> is a DESKTOP version.
> The 'real' SQL Server used for our data is an ENTERPRISE edition.
> FYI. I'm not interested in the performance of the DESKTOP version. So
> those may be eliminated from the Perfmon.
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
|||Hello Bart,
Can you pl. check the AWE option?
http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
Cheers,
MB
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri, but none of the provided solutions helped.
> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
> is a DESKTOP version.
> The 'real' SQL Server used for our data is an ENTERPRISE edition.
> FYI. I'm not interested in the performance of the DESKTOP version. So
> those may be eliminated from the Perfmon.
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
|||Try here: http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
John
Bart Steur wrote:
> I've looked into this, but I don't think this option applies to my
> situation. First we are using Window 2003 Server Standard, and second this
> is for very large memory sizes. We only have 4 GB. But thanks for the
> support. Any other suggestions are welcome.
> Regards
> Bart
> "MB" <MB@.MB.com> wrote in message
> news:uedn1jGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
>
|||You're welcome.
John
Bart Steur wrote:
> Works like a charm. Thanks John John
> "John John" <audetweld@.nbnet.nb.ca> wrote in message
> news:OdafzcHQHHA.1380@.TK2MSFTNGP05.phx.gbl...
>
I'm nbot sure uinderstand you. Click Start --Run --perfmon.
On the sceen right click and Add counters. There is Perfomance Object
combobox where you can choose many counters for SQL Server as well
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23EaGga8PHHA.404@.TK2MSFTNGP02.phx.gbl...
> Hello
> We are running W2003 Server with SQL Server 2000. Now I want to monitor
> performance using the administrative tool Performance.
> According to the SQL Server 2000 System Administration (Chapter 14), there
> should be several SQL Server Performance Objects to track SQL server
> activity, but...
> I can't find them. What happend, where are they.
> Anyone?
> Thanks,
> Bart
>
Bart
http://support.microsoft.com/kb/227662
http://support.microsoft.com/kb/246328
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
> Some Extra information (Had a chat with our System Administrator).
> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another that
> came with our backup program (BACKUPEXEC) to store backup information. The
> last one I can see in the Performance Monitor. But that's not the one I
> would like to monitor. Any idea how to get the objects of the other SQL
> server into the performance monitor, or maybe how to swap them.
> thanks
> Bart
>
|||Bart
You are right, my mistake, sorry
For detailed information, please reference the following article:
330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
http://support.microsoft.com/?id=330088
SQL Server 2000 Performance Counters Do Not Appear in the Performance
http://support.microsoft.com/?id=827260
http://support.microsoft.com/?id=812915
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Those articles do not apply to SQLServer 2000.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
|||Bart
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
I have my doubt that a 'bad' written query will perform better if you add
more memory. I'd run SQL Server Profiler to identify long running queries
and start tunning them
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri, but none of the provided solutions helped.
> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
> is a DESKTOP version.
> The 'real' SQL Server used for our data is an ENTERPRISE edition.
> FYI. I'm not interested in the performance of the DESKTOP version. So
> those may be eliminated from the Perfmon.
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
|||Hello Bart,
Can you pl. check the AWE option?
http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
Cheers,
MB
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri, but none of the provided solutions helped.
> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
> is a DESKTOP version.
> The 'real' SQL Server used for our data is an ENTERPRISE edition.
> FYI. I'm not interested in the performance of the DESKTOP version. So
> those may be eliminated from the Perfmon.
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
|||Try here: http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
John
Bart Steur wrote:
> I've looked into this, but I don't think this option applies to my
> situation. First we are using Window 2003 Server Standard, and second this
> is for very large memory sizes. We only have 4 GB. But thanks for the
> support. Any other suggestions are welcome.
> Regards
> Bart
> "MB" <MB@.MB.com> wrote in message
> news:uedn1jGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
>
|||You're welcome.
John
Bart Steur wrote:
> Works like a charm. Thanks John John
> "John John" <audetweld@.nbnet.nb.ca> wrote in message
> news:OdafzcHQHHA.1380@.TK2MSFTNGP05.phx.gbl...
>
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
>> >
>> >
>>
>
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
>> >
>> >
>>
>
Monday, February 20, 2012
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
>
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
>
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
>
Performance monitor question
I'm trying to setup logging of my perfmon data into and MSSQL database, but
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
Tom
The schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>
|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> but
> that
>
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
Tom
The schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>
|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> but
> that
>
Performance monitor question
I'm trying to setup logging of my perfmon data into and MSSQL database, but
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
TomThe schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> > I'm trying to setup logging of my perfmon data into and MSSQL database,
> but
> > what I can't seem to find is the schema definition for the SQL Table(s)
> that
> > I need to create. Anyone know where I can find this at?
> >
> > Thanks,
> > Tom
> >
> >
>
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
TomThe schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> > I'm trying to setup logging of my perfmon data into and MSSQL database,
> but
> > what I can't seem to find is the schema definition for the SQL Table(s)
> that
> > I need to create. Anyone know where I can find this at?
> >
> > Thanks,
> > Tom
> >
> >
>
Performance Monitor Miscalculates SQLSERVR Time?
When I run perfmon against the sqlservr.exe process, its
percent of processor time goes over 350% percent. Is this
a bug in perfmon. If so, how do I report it? We are
running a 4-cpu box, WINNT 4 with SQL2K sp3a.the percent processor time reported for the sqlservr
process is relative to a single processor, not the entire
system,
hence 350% on a 4-CPU system is 350/400 = 87.5% of the
overall system
>--Original Message--
>When I run perfmon against the sqlservr.exe process, its
>percent of processor time goes over 350% percent. Is
this
>a bug in perfmon. If so, how do I report it? We are
>running a 4-cpu box, WINNT 4 with SQL2K sp3a.
>.
>
percent of processor time goes over 350% percent. Is this
a bug in perfmon. If so, how do I report it? We are
running a 4-cpu box, WINNT 4 with SQL2K sp3a.the percent processor time reported for the sqlservr
process is relative to a single processor, not the entire
system,
hence 350% on a 4-CPU system is 350/400 = 87.5% of the
overall system
>--Original Message--
>When I run perfmon against the sqlservr.exe process, its
>percent of processor time goes over 350% percent. Is
this
>a bug in perfmon. If so, how do I report it? We are
>running a 4-cpu box, WINNT 4 with SQL2K sp3a.
>.
>
Subscribe to:
Posts (Atom)