Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
crabboy
Starting Member
2 Posts |
 Posted - 2011-03-23 : 17:21:14
|
| I've been seeing some high CPU utilization during testing on my SQL box and after some poking around I'm not sure that my sql 2008 instance is caching the plans correctly forcing hard parses. I've been running queries on the sys.dm_exec_query_stats table (or sp) and I consistently get just a few rows back while the load test is running, it will vary in size from 2-10 rows. Once the test is done, the table will drop back down to 0 rows. After reading some of the docs and google searches, I would think this table/view would behave more like the Oracle v$sql view, caching all the sql that has come into the machine. I also checked another instance of 2008 we have running here and it has 29k rows in the table/view. Why does mine keep emptying out? Is there some setting for this?TIA |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-23 : 17:34:58
|
| That view only reflects the stats of queries that are currently in the plan cache. When a plan is flushed from cache, any associated stats are removed as well.--Gail ShawSQL Server MVP |
 |
|
|
crabboy
Starting Member
2 Posts |
Posted - 2011-03-23 : 17:40:36
|
| What manages when a plan gets flushed? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-24 : 04:08:28
|
| Memory, frequency of use, change to statistics, change to schema, restart of SQL, close of DB, restore of a DB, etc.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
dm_exec_query_stats question