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]