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
>