July 9, 2009 at 11:40 am
We are experimenting with a particular query to improve the performance. This query is an ad-hoc query but does have some views. The particular query is causing some blocking on a table. The Execution Plan shows a Key Lookup on this table, with SQL Statistics shows over 200,000 logical reads on this table.
By adding an index with an Include on the column involved in the Key Lookup, the performance improved and the logical reads was dropped to 4. In order to try to reproduce the original condition, I deleted the new index and run DBCC FLUSHPROCINDB. Running the following query verifies nothing is cached -
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
where DB_NAME(dbid) = 'FIP_MAIN_20090618'
ORDER BY dbid,usecounts DESC;
When running the same query again, I would have expected the logical reads to go back to over 200,000, but it remains at 4. The view does show up when running the above DMV.
Am I missing a step. I would like to be able to verify conclusively that the index is doing it's job before deploying it to production.
Thank you.
July 9, 2009 at 12:50 pm
try dbcc freeproccache
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 9, 2009 at 1:10 pm
Thank you for your response -
The only reason I did not run that is that I was not prepared to remove all the cached plans for the entire SQL Server instance. Since I was only interested in the one database, I thought the DBCC FLUSHPROCINDB would do the same thing - removed the cached plans but only for the database I'm working with. Is that not correct.
July 9, 2009 at 1:14 pm
I don't even see DBCC FLUSHPROCINDB in SQL Server 2005 Books Online. Either it doesn't exist or is undocumented.
July 9, 2009 at 1:18 pm
Did a little Googling and found it. Did you run it with the db_id? Example: DBCC FLUSHPROCINDB(4)
July 9, 2009 at 1:37 pm
Yes, I used the dbid of the database I was working with.
July 9, 2009 at 6:55 pm
That should have done it for you. I have seen though that the freeprocindb sometimes doesn't clear it like we would want. Though all plans would be removed - it would only be temporary and has not been of significant negative effect for me in the past - it might be worth a try to run the freeprocincache.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 9, 2009 at 7:56 pm
I appreciate everyone's feedback and will run DBCC FreeProcCache and let you know my findings. Thanks again.
July 10, 2009 at 4:23 am
DBCC FREEPROCCACHE and DBCC FREEPROCINDB are both fairly blunt instruments.
To selectively remove just Ad-Hoc plans from cache (leaving the 'good' object plans like triggers and stored procedures) use:
DBCC FREESYSTEMCACHE('SQL Plans')
It is 'documented' here
There is also a trick to remove just one plan cache here. It probably doesn't apply in your case, but is fascinating nonetheless.
Paul
July 10, 2009 at 6:58 am
Thank you for the information and the documenation. I'll take a look at it.
July 10, 2009 at 7:17 am
You're welcome!
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply