Percentage of total: disk

Showing posts with label disk. Show all posts

Friday, March 30, 2012

Performance in Cube generation

during the cube generation, I saw the disk utilization in C drive is 100%.
How can I change the temporary drive for the cubes generation?
or
can I share the work load on others drives? nHow?There are lots of tips and tricks mentioned in the AS Operations and
Preformance Guides.
Pointers to them are at:
http://www.microsoft.com/sql/evalua.../bianalysis.asp
1) changing the temp folder -- yes. In Analysis Manager, right-click on the
server and select Properties...
2) different folders -- no. Everything in Analysis Services sits under the
same data folder.
You can modify where that is located, but everything sits under that single
folder.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:613802AE-92ED-4B83-9C5D-0DAB5576B781@.microsoft.com...
> during the cube generation, I saw the disk utilization in C drive is 100%.
> How can I change the temporary drive for the cubes generation?
> or
> can I share the work load on others drives? nHow?

Wednesday, March 21, 2012

Performance counter : PhysicalDisk: % Disk Time

We have a DataWarehouse process that, at times, causes this Perfmon counter to exceed > 1800 (for several minutes at a time. Not good).

Question: How can that counter exceed 100 * (number of spindles in array)? (I suspect that numbers in that range are meaningless except to point out a disk bottleneck.)

TIA,

barkingdog

P.S. We use sql 2005, x64 edition, with SP1.

http://www.demandtech.com/FAQsdisk.htm#DiskFAQ1

Frequently Asked Questions about Disk
1. The Logical/Physical Disk % Disk Time counters look wrong. What gives?

http://support.microsoft.com/kb/269481

not about:: We use sql 2005, x64 edition, with SP1.

Performance Counter

Hi,
Is there a way to access non Sql Server related performance counters from
Sql Server.
Like % Processor time or % Disk Time?
I'm trying to build a table to establish trends and would like to collect
all the data from via a sp.
ThxYou could try
DBCC sqlperf(THREADS)
Tobes
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||Non- SQL Server perf counters are NOT stored in SQL the same way that the
SQL Counters are..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||You can write Windows performance counters to SQL Server database tables
using the Windows 2003 server or XP performance monitor tool. Create a ODBC
system DSN for your database repository and specify the DSN under log
settings. The account used for the 'Performance logs and alerts' service
needs to be granted full access to the database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> Hi,
> Is there a way to access non Sql Server related performance counters from
> Sql Server.
> Like % Processor time or % Disk Time?
> I'm trying to build a table to establish trends and would like to collect
> all the data from via a sp.
> Thx
>|||Thx,
I've tried this and have tested the ODBC connection which is successfull.
However when I go to start the counter log it fails and the application
event viewer has the following message:
Description:
18456 :
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
I then went and added a local group on the server and added 'NT
AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for this
local group to Sql server and added it as a user to a repository db with full
permissions on the db... get the same error.
Thx again.
"Dan Guzman" wrote:
> You can write Windows performance counters to SQL Server database tables
> using the Windows 2003 server or XP performance monitor tool. Create a ODBC
> system DSN for your database repository and specify the DSN under log
> settings. The account used for the 'Performance logs and alerts' service
> needs to be granted full access to the database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> > Hi,
> >
> > Is there a way to access non Sql Server related performance counters from
> > Sql Server.
> > Like % Processor time or % Disk Time?
> >
> > I'm trying to build a table to establish trends and would like to collect
> > all the data from via a sp.
> >
> > Thx
> >
> >
>
>|||You might try using a domain account for the 'Performance logs and alerts'
service. This has always worked for me.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ap001" <ap001@.discussions.microsoft.com> wrote in message
news:2D40BCD1-40C4-4F34-B6E5-6FDDABE109FC@.microsoft.com...
> Thx,
> I've tried this and have tested the ODBC connection which is successfull.
> However when I go to start the counter log it fails and the application
> event viewer has the following message:
> Description:
> 18456 :
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> I then went and added a local group on the server and added 'NT
> AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for
> this
> local group to Sql server and added it as a user to a repository db with
> full
> permissions on the db... get the same error.
> Thx again.
>
>
> "Dan Guzman" wrote:
>> You can write Windows performance counters to SQL Server database tables
>> using the Windows 2003 server or XP performance monitor tool. Create a
>> ODBC
>> system DSN for your database repository and specify the DSN under log
>> settings. The account used for the 'Performance logs and alerts' service
>> needs to be granted full access to the database.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "ap001" <ap001@.discussions.microsoft.com> wrote in message
>> news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
>> > Hi,
>> >
>> > Is there a way to access non Sql Server related performance counters
>> > from
>> > Sql Server.
>> > Like % Processor time or % Disk Time?
>> >
>> > I'm trying to build a table to establish trends and would like to
>> > collect
>> > all the data from via a sp.
>> >
>> > Thx
>> >
>> >
>>|||Thx, that worked.
"Dan Guzman" wrote:
> You might try using a domain account for the 'Performance logs and alerts'
> service. This has always worked for me.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> news:2D40BCD1-40C4-4F34-B6E5-6FDDABE109FC@.microsoft.com...
> > Thx,
> >
> > I've tried this and have tested the ODBC connection which is successfull.
> > However when I go to start the counter log it fails and the application
> > event viewer has the following message:
> > Description:
> > 18456 :
> > Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> >
> > I then went and added a local group on the server and added 'NT
> > AUTHORITY\NETWORK SERVICE' to the group. I then granted login access for
> > this
> > local group to Sql server and added it as a user to a repository db with
> > full
> > permissions on the db... get the same error.
> >
> > Thx again.
> >
> >
> >
> >
> > "Dan Guzman" wrote:
> >
> >> You can write Windows performance counters to SQL Server database tables
> >> using the Windows 2003 server or XP performance monitor tool. Create a
> >> ODBC
> >> system DSN for your database repository and specify the DSN under log
> >> settings. The account used for the 'Performance logs and alerts' service
> >> needs to be granted full access to the database.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "ap001" <ap001@.discussions.microsoft.com> wrote in message
> >> news:469B9D70-D7E3-452C-BAE3-2D5E0320909C@.microsoft.com...
> >> > Hi,
> >> >
> >> > Is there a way to access non Sql Server related performance counters
> >> > from
> >> > Sql Server.
> >> > Like % Processor time or % Disk Time?
> >> >
> >> > I'm trying to build a table to establish trends and would like to
> >> > collect
> >> > all the data from via a sp.
> >> >
> >> > Thx
> >> >
> >> >
> >>
> >>
> >>
>
>sql

Performance Counter

Tuesday, March 20, 2012

Performance and TempDB

I am seeing high CPU.
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>>should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||My guess if that you have one or several queries that now uses a much worse plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
down is to work the query plans. Ideally, you would compared to before this happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that is
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:
>My guess if that you have one or several queries that now uses a much worse plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan, quite
>simply.
>>I am seeing high CPU.
>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||New statistics will force recompilation. Reindexing will produce new statistics (INDEXDEFRAG will
not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
>>My guess if that you have one or several queries that now uses a much worse plan that it used to
>>do.
>>Probably now using much worktables (hence tempdb usage) compared to earlier. Only way to track
>>this
>>down is to work the query plans. Ideally, you would compared to before this happened to track down
>>why. Reasons could be more/less/skewed data, less/more precise statistics, lack of/new index,
>>alignment of moon, Jupiter and Mars. Anything that could result in a different execution plan,
>>quite
>>simply.
>>I am seeing high CPU.
>>[quoted text clipped - 18 lines]
>> lead to TempDB growth? Or does a growing TempDB send off any flags that I
>> should be aware of?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>

Performance and TempDB

I am seeing high CPU.
My disk counters are normal and memory is in good shape.
I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
density and logical fragmentation is all down near zero.
I have approximately 100 reads for every write. My FillFactor is set at 90%.
I have one stored proc in particular that is taking much longer (duration)
than usual. In addition, there are several stored procs that recompile at a
high rate, sometimes recompiling multiple times per call.
I am looking at rewriting the stored procs that recompile, which should
reduce the CPU load.
Could the recompiles have an adverse affect on the stored procedure that is
long in duration? I have not yet looked into locking/blocking/deadlocks.
Also, it appears my TempDB has grown considerably. Could any of the above
lead to TempDB growth? Or does a growing TempDB send off any flags that I
should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1As a followup, I wanted to additionally clarify that
1. update stats has been run after the reindexing.
2. TempDB is 39Gb.
Data = 22 Gb with only 227 mb being used, meaning the data file for
TempDB is 99% free.
Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% free.
cbrichards wrote:
>I am seeing high CPU.
>My disk counters are normal and memory is in good shape.
>I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
>density and logical fragmentation is all down near zero.
>I have approximately 100 reads for every write. My FillFactor is set at 90%
.
>I have one stored proc in particular that is taking much longer (duration)
>than usual. In addition, there are several stored procs that recompile at a
>high rate, sometimes recompiling multiple times per call.
>I am looking at rewriting the stored procs that recompile, which should
>reduce the CPU load.
>Could the recompiles have an adverse affect on the stored procedure that is
>long in duration? I have not yet looked into locking/blocking/deadlocks.
>Also, it appears my TempDB has grown considerably. Could any of the above
>lead to TempDB growth? Or does a growing TempDB send off any flags that I
>should be aware of?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||One more addition. My TempDb is located on its own disk array.
cbrichards wrote:[vbcol=seagreen]
>As a followup, I wanted to additionally clarify that
>1. update stats has been run after the reindexing.
>2. TempDB is 39Gb.
> Data = 22 Gb with only 227 mb being used, meaning the data file for
>TempDB is 99% free.
> Log = 17 Gb with 16 Gb being used, meaning the log file is about 8% fr
ee.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
Probably now using much worktables (hence tempdb usage) compared to earlier.
Only way to track this
down is to work the query plans. Ideally, you would compared to before this
happened to track down
why. Reasons could be more/less/skewed data, less/more precise statistics, l
ack of/new index,
alignment of moon, Jupiter and Mars. Anything that could result in a differe
nt execution plan, quite
simply.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6490735c56893@.uwe...[vbcol
=seagreen]
>I am seeing high CPU.
> My disk counters are normal and memory is in good shape.
> I have reindexed and DBCC ShowContig has all indexes greater than 90% scan
> density and logical fragmentation is all down near zero.
> I have approximately 100 reads for every write. My FillFactor is set at 90
%.
> I have one stored proc in particular that is taking much longer (duration)
> than usual. In addition, there are several stored procs that recompile at
a
> high rate, sometimes recompiling multiple times per call.
> I am looking at rewriting the stored procs that recompile, which should
> reduce the CPU load.
> Could the recompiles have an adverse affect on the stored procedure that i
s
> long in duration? I have not yet looked into locking/blocking/deadlocks.
> Also, it appears my TempDB has grown considerably. Could any of the above
> lead to TempDB growth? Or does a growing TempDB send off any flags that I
> should be aware of?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]|||So, after doing a reindex, do all the stored procedures recompile next
execution, or is it a good idea to clear the cache after a reindex so the
stored procedures can recompile?
Tibor Karaszi wrote:[vbcol=seagreen]
>My guess if that you have one or several queries that now uses a much worse
plan that it used to do.
>Probably now using much worktables (hence tempdb usage) compared to earlier
. Only way to track this
>down is to work the query plans. Ideally, you would compared to before this
happened to track down
>why. Reasons could be more/less/skewed data, less/more precise statistics,
lack of/new index,
>alignment of moon, Jupiter and Mars. Anything that could result in a differ
ent execution plan, quite
>simply.
>
>[quoted text clipped - 18 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||New statistics will force recompilation. Reindexing will produce new statist
ics (INDEXDEFRAG will
not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:649385f582fc2@.uwe...[vbcol
=seagreen]
> So, after doing a reindex, do all the stored procedures recompile next
> execution, or is it a good idea to clear the cache after a reindex so the
> stored procedures can recompile?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>[/vbcol]

Wednesday, March 7, 2012

performance

I have been looking at the performance of my SQL Server 2000.
For %Disk Time I get the following values for 24 hours (3AM to 3AM next day):
MIN MAX AVERAGE
0.494 39244.5 113.686
If I take out the busiest 45 minutes and look at the Statistics for 23 hours
and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
MIN MAX AVERAGE
0.912 4860.8 42.423
Microsoft suggests a threshold value of 90% for this counter. Do these
values look OK? I am not having any performance problems on this machine.
Even the average %Processor Time is less than 3%. Any insight will be
appreciated. Thanks.%Disk Time is almost a useless counter and should be used very sparingly or
even not at all. If it is at 100 or less you can be pretty sure the disks
are not being taxed too much. Anything over that and it becomes pretty
unreliable and hard to correlate. The disk Q's will give a much better
overall indication of how busy they are but there are other disk counters
that will give more detailed insights to how efficient they are. The
fn_virtual_filestats are also a good place to start for disk related info.
Here are some other places:
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/28/don-t-forget-the-business-transaction-throughput-and-response-time.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
IO Basics
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:159A4413-BD9B-4123-A669-4D4140603888@.microsoft.com...
>I have been looking at the performance of my SQL Server 2000.
> For %Disk Time I get the following values for 24 hours (3AM to 3AM next
> day):
> MIN MAX AVERAGE
> 0.494 39244.5 113.686
> If I take out the busiest 45 minutes and look at the Statistics for 23
> hours
> and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
> MIN MAX AVERAGE
> 0.912 4860.8 42.423
> Microsoft suggests a threshold value of 90% for this counter. Do these
> values look OK? I am not having any performance problems on this machine.
> Even the average %Processor Time is less than 3%. Any insight will be
> appreciated. Thanks.|||In addition to what Andy said, you should also look at the I/O response
counters (Avg Disk sec/read and Avg disk sec/write). These counters tell you
how long in average an I/O read or write take to complete. Together with Avg
Disk bytes/read and Avg Disk bytes/write--which tell you the sizes of your
I/O, you can get a pretty good picture of how your disks are doing. For
instance, if you are doing small I/Os and your avg disk response time is long
(e.g. > 20ms or a smaller threshold), something is up with your disk and you
should take a closer look.
Linchi
"sharman" wrote:
> I have been looking at the performance of my SQL Server 2000.
> For %Disk Time I get the following values for 24 hours (3AM to 3AM next day):
> MIN MAX AVERAGE
> 0.494 39244.5 113.686
> If I take out the busiest 45 minutes and look at the Statistics for 23 hours
> and 15 minutes then I get the following values (3:45 AM to 3 AM next day)
> MIN MAX AVERAGE
> 0.912 4860.8 42.423
> Microsoft suggests a threshold value of 90% for this counter. Do these
> values look OK? I am not having any performance problems on this machine.
> Even the average %Processor Time is less than 3%. Any insight will be
> appreciated. Thanks.

Saturday, February 25, 2012

perfomance related questions

Why would a stored procedure take longer to run on an OLTP environment
although %processor time doesnt seem to be pegged ( 30%), %disk time is
around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
an average but runs faster on development boxes like around 1 sec. It has
the same exact execution plan on both environments. My question is what else
do i need to look into since it doesnt seem to be hardware bound here. Using
SQL 2000. CPU and reads in trace are also higher than in our dev
environment. but the perfmon for h/w looks decent or atleast not that bad .
Any other counters to look at or suggestionsDo you have the same amount of data on both servers? For example if you
have a cursor that runs through all the records in one table and in the
development database you have only 100 records but in the production server
you have 100000 records, then I'd exepect that the stored procedure would
take longer to run on the production server. Also if you have lots of
users that are working with the database in the production, you might have
locking problems, that slow the execution.
Adi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eY8NNr7YDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Why would a stored procedure take longer to run on an OLTP environment
> although %processor time doesnt seem to be pegged ( 30%), %disk time is
> around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
> an average but runs faster on development boxes like around 1 sec. It has
> the same exact execution plan on both environments. My question is what
else
> do i need to look into since it doesnt seem to be hardware bound here.
Using
> SQL 2000. CPU and reads in trace are also higher than in our dev
> environment. but the perfmon for h/w looks decent or atleast not that bad
.
> Any other counters to look at or suggestions
>
>|||perfmon cpu is the instantaneous cpu usage, profiler cpu
is the cumulative cpu for the query, regardless of how
long it took,
unless a query takes several cpu secs, and the difference
between profiler duration and cpu is not larger, you may
not even notice the cpu in perfmon
>--Original Message--
>Assuming the stored procedure was only selecting , why
would the CPU from
>perfmon do not show it as busy yet consume more time in
CPU in profiler to
>execute the sproc. There is no parallelism. There are
some temp tables
>..There are some nested joins.
>"joe chang" <jchang6@.yahoo.com> wrote in message
>news:000d01c36401$27d19f60$a101280a@.phx.gbl...
>> what specifically are the production and development
>> systems?
>> processor, frequency, cache, #of cpu, memory, # of
disks,
>> status of hyper-threading?
>> what is in the execution plan of your stored proc ?
>> joins - what type, loop, hash, merge, row count for each
>> table, is there a temp table involved? is a parallel
plan
>> involved? if so, disabled HT or try OPTION (MAXDOP 1)
>> sometimes the dev env is JBOD (no raid) the prod env is
>> raid5, with absolutely horrible write performance, so
>> anything that involves writes will performance much
worse
>> on the $40k production system than the $1k dev system
>> >--Original Message--
>> >Why would a stored procedure take longer to run on an
>> OLTP environment
>> >although %processor time doesnt seem to be pegged (
30%),
>> %disk time is
>> >around 10% and pages/sec is 0. It takes around 10 secs
to
>> run everytime on
>> >an average but runs faster on development boxes like
>> around 1 sec. It has
>> >the same exact execution plan on both environments. My
>> question is what else
>> >do i need to look into since it doesnt seem to be
>> hardware bound here. Using
>> >SQL 2000. CPU and reads in trace are also higher than
in
>> our dev
>> >environment. but the perfmon for h/w looks decent or
>> atleast not that bad .
>> >Any other counters to look at or suggestions
>> >
>> >
>> >
>> >.
>> >
>
>.
>

perfmon to remote server

I'm not running SQLServer on my own workstation.
I am running perfmon, just as a matter of course, showing CPU and disk
and net traffic. I'd also like to monitor some remote SQLServers.
When I first clicked on the Add Counters dialog and typed in the name
of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
happened. But after I opened a fresh connection to MySQLServer1 for
the query analyzer, when I went back to perfmon, now it does list
\\MySQLServer1. But, it shows no properties for it.
Do I need some more advanced admin privileges on the remote boxes (or
network generally) for this to work?
Thanks.
JoshLocal Admins to read performance counters. Probably more specialized
security could be given, but Local Admins have the rights.
Sincerely,
Anthony Thomas
"jxstern" wrote:
> I'm not running SQLServer on my own workstation.
> I am running perfmon, just as a matter of course, showing CPU and disk
> and net traffic. I'd also like to monitor some remote SQLServers.
> When I first clicked on the Add Counters dialog and typed in the name
> of a SQLServer (say, "MySQLServer1"), it didn't connect, nothing
> happened. But after I opened a fresh connection to MySQLServer1 for
> the query analyzer, when I went back to perfmon, now it does list
> \\MySQLServer1. But, it shows no properties for it.
> Do I need some more advanced admin privileges on the remote boxes (or
> network generally) for this to work?
> Thanks.
> Josh
>

Monday, February 20, 2012

Perf Mon

I want to monitor avg disk queue length and cpu for the
last ten minutes to see if one or the other has exceeded
a certain threshold during that time. If so, I want to
get an email.
1. Can this occur with Perf Mon?
2. Are there any third party tools that can do this?NoMoFlo,
#1. Iam unaware of an alert facility in perfmon.
#2. MS has another tool called Microsoft Operations Manager (MOM)
http://www.microsoft.com/mom/ .
A good third party tool is SiteScope:
'Mercury SiteScope'
scope/" target="_blank">http://www.mercury.com/us/products/...e
scope/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"NoMoFlo" <anonymous@.discussions.microsoft.com> wrote in message
news:20be901c45a31$7a505980$a601280a@.phx
.gbl...
> I want to monitor avg disk queue length and cpu for the
> last ten minutes to see if one or the other has exceeded
> a certain threshold during that time. If so, I want to
> get an email.
> 1. Can this occur with Perf Mon?
> 2. Are there any third party tools that can do this?|||Hi,
There is anothor best tool from BMC . Name of thre tool is BMC PATROL.
Using this we can monitor the online performance , Email Alert, as well as
see the history .
http://www.bmc.com/products/
Thanks
Hari
MCDBA
"NoMoFlo" <anonymous@.discussions.microsoft.com> wrote in message
news:20be901c45a31$7a505980$a601280a@.phx
.gbl...
> I want to monitor avg disk queue length and cpu for the
> last ten minutes to see if one or the other has exceeded
> a certain threshold during that time. If so, I want to
> get an email.
> 1. Can this occur with Perf Mon?
> 2. Are there any third party tools that can do this?|||Go to Administrative Tools then to Performance. In the
PerfMon console tree, go to the Performance Logs and Alerts
node and then select Alerts. Right click and select New
Alert Settings to create a PerfMon alert. After providing a
name for the alert, you can set up the counters and
thresholds. On the Action tab, you can set up the response
to the counters exceeding the threshold.
You can right click on alerts, select help and get more
information on setting this up.
-Sue
On Thu, 24 Jun 2004 14:23:22 -0700, "NoMoFlo"
<anonymous@.discussions.microsoft.com> wrote:

>I want to monitor avg disk queue length and cpu for the
>last ten minutes to see if one or the other has exceeded
>a certain threshold during that time. If so, I want to
>get an email.
>1. Can this occur with Perf Mon?
>2. Are there any third party tools that can do this?

Perf Mon

I want to monitor avg disk queue length and cpu for the
last ten minutes to see if one or the other has exceeded
a certain threshold during that time. If so, I want to
get an email.
1. Can this occur with Perf Mon?
2. Are there any third party tools that can do this?NoMoFlo,
#1. Iam unaware of an alert facility in perfmon.
#2. MS has another tool called Microsoft Operations Manager (MOM)
http://www.microsoft.com/mom/ .
A good third party tool is SiteScope:
'Mercury SiteScope'
http://www.mercury.com/us/products/application-management/foundation/monitors/sitescope/
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"NoMoFlo" <anonymous@.discussions.microsoft.com> wrote in message
news:20be901c45a31$7a505980$a601280a@.phx.gbl...
> I want to monitor avg disk queue length and cpu for the
> last ten minutes to see if one or the other has exceeded
> a certain threshold during that time. If so, I want to
> get an email.
> 1. Can this occur with Perf Mon?
> 2. Are there any third party tools that can do this?|||Hi,
There is anothor best tool from BMC . Name of thre tool is BMC PATROL.
Using this we can monitor the online performance , Email Alert, as well as
see the history .
http://www.bmc.com/products/
--
Thanks
Hari
MCDBA
"NoMoFlo" <anonymous@.discussions.microsoft.com> wrote in message
news:20be901c45a31$7a505980$a601280a@.phx.gbl...
> I want to monitor avg disk queue length and cpu for the
> last ten minutes to see if one or the other has exceeded
> a certain threshold during that time. If so, I want to
> get an email.
> 1. Can this occur with Perf Mon?
> 2. Are there any third party tools that can do this?|||Go to Administrative Tools then to Performance. In the
PerfMon console tree, go to the Performance Logs and Alerts
node and then select Alerts. Right click and select New
Alert Settings to create a PerfMon alert. After providing a
name for the alert, you can set up the counters and
thresholds. On the Action tab, you can set up the response
to the counters exceeding the threshold.
You can right click on alerts, select help and get more
information on setting this up.
-Sue
On Thu, 24 Jun 2004 14:23:22 -0700, "NoMoFlo"
<anonymous@.discussions.microsoft.com> wrote:
>I want to monitor avg disk queue length and cpu for the
>last ten minutes to see if one or the other has exceeded
>a certain threshold during that time. If so, I want to
>get an email.
>1. Can this occur with Perf Mon?
>2. Are there any third party tools that can do this?

Perf Mon

I want to monitor avg disk queue length and cpu for the
last ten minutes to see if one or the other has exceeded
a certain threshold during that time. If so, I want to
get an email.
1. Can this occur with Perf Mon?
2. Are there any third party tools that can do this?
NoMoFlo,
#1. Iam unaware of an alert facility in perfmon.
#2. MS has another tool called Microsoft Operations Manager (MOM)
http://www.microsoft.com/mom/ .
A good third party tool is SiteScope:
'Mercury SiteScope'
http://www.mercury.com/us/products/a...ors/sitescope/
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"NoMoFlo" <anonymous@.discussions.microsoft.com> wrote in message
news:20be901c45a31$7a505980$a601280a@.phx.gbl...
> I want to monitor avg disk queue length and cpu for the
> last ten minutes to see if one or the other has exceeded
> a certain threshold during that time. If so, I want to
> get an email.
> 1. Can this occur with Perf Mon?
> 2. Are there any third party tools that can do this?
|||Hi,
There is anothor best tool from BMC . Name of thre tool is BMC PATROL.
Using this we can monitor the online performance , Email Alert, as well as
see the history .
http://www.bmc.com/products/
Thanks
Hari
MCDBA
"NoMoFlo" <anonymous@.discussions.microsoft.com> wrote in message
news:20be901c45a31$7a505980$a601280a@.phx.gbl...
> I want to monitor avg disk queue length and cpu for the
> last ten minutes to see if one or the other has exceeded
> a certain threshold during that time. If so, I want to
> get an email.
> 1. Can this occur with Perf Mon?
> 2. Are there any third party tools that can do this?
|||Go to Administrative Tools then to Performance. In the
PerfMon console tree, go to the Performance Logs and Alerts
node and then select Alerts. Right click and select New
Alert Settings to create a PerfMon alert. After providing a
name for the alert, you can set up the counters and
thresholds. On the Action tab, you can set up the response
to the counters exceeding the threshold.
You can right click on alerts, select help and get more
information on setting this up.
-Sue
On Thu, 24 Jun 2004 14:23:22 -0700, "NoMoFlo"
<anonymous@.discussions.microsoft.com> wrote:

>I want to monitor avg disk queue length and cpu for the
>last ten minutes to see if one or the other has exceeded
>a certain threshold during that time. If so, I want to
>get an email.
>1. Can this occur with Perf Mon?
>2. Are there any third party tools that can do this?

Friday, March 30, 2012

Performance in Cube generation

Wednesday, March 21, 2012

Performance counter : PhysicalDisk: % Disk Time

Performance Counter

Performance Counter

Performance Counter

Tuesday, March 20, 2012

Performance and TempDB

Performance and TempDB

Wednesday, March 7, 2012

performance

Saturday, February 25, 2012

perfomance related questions

perfmon to remote server

Monday, February 20, 2012

Perf Mon

Perf Mon

Perf Mon

Percentage of total

Blog Archive

About Me