Hi dear fellow SQL programmers,
I recently witnessed some strange behaviour of SQL Server 2005.
I was running some dynamic sql generated over a group of tables with same structure. Each of the tables holds the data in one date. So when I wish to do the query over one month for example I have to generate 30 or so dynamic queries over the 30 tables and execute them one by one.
All was fine and each of the dynamic SQL took like one second to execute until around 90 of them got executed. At this moment the SQL server looks like to be taking great pains to execute one such dynamic SQL (one minute or so compared to the normal one second).
At this stage, if you look at the CPU and memory usage it looks normal but SQL profiler shows up abnormally large amount of IO.
I searched around and ended up trying executing
dbcc flushprocindb(@dbid) periodically (after executing every 30 such dynamic SQL for one month) to clear the cached execution plans and this seems to fix the problem of sudden stall by some extent. The previous very slow execution of the dyanmic SQL was replaced by mad spinning of the harddisk but the speed do improve significantly, not down to origiinal one second albeit.
So why would cached execution plans suddenly slow down excution of consequent dyanmic queries so much when they got accumulated? And why the mad spinning of the harddisk if the cached plans are cleared?
Thank you
I recently witnessed some strange behaviour of SQL Server 2005.
I was running some dynamic sql generated over a group of tables with same structure. Each of the tables holds the data in one date. So when I wish to do the query over one month for example I have to generate 30 or so dynamic queries over the 30 tables and execute them one by one.
All was fine and each of the dynamic SQL took like one second to execute until around 90 of them got executed. At this moment the SQL server looks like to be taking great pains to execute one such dynamic SQL (one minute or so compared to the normal one second).
At this stage, if you look at the CPU and memory usage it looks normal but SQL profiler shows up abnormally large amount of IO.
I searched around and ended up trying executing
dbcc flushprocindb(@dbid) periodically (after executing every 30 such dynamic SQL for one month) to clear the cached execution plans and this seems to fix the problem of sudden stall by some extent. The previous very slow execution of the dyanmic SQL was replaced by mad spinning of the harddisk but the speed do improve significantly, not down to origiinal one second albeit.
So why would cached execution plans suddenly slow down excution of consequent dyanmic queries so much when they got accumulated? And why the mad spinning of the harddisk if the cached plans are cleared?
Thank you
