June 2009 Blog Posts
It’s pretty easy to determine the CPU and disk resources that a given query or stored procedure will use. It’s more difficult to determine the resources that were used to compile that query plan. You can start by looking at sys.dm_exec_cached_plans. It has a column called “size_in_bytes” that will tell you how much memory the query plan is using. If you generate an XML query plan through SSMS or Profiler you can get some additional information. The XML plan includes this snippet: <QueryPlan CachedPlanSize="196" CompileTime="53" CompileCPU="53" CompileMemory="1896"> If you review the schema for the XML query plan...
In many of the PASS Conferences I’ve attended I can usually point to one thing I learned that was the most valuable. Usually this was enough to justify the cost of the entire conference. I learned something that would save me so much time or money it would “pay” for the conference. This made it pretty easy for me to justify to myself (or my boss) that it was money well spent. Quite a few years back I was sitting in a session on performance tuning. The speaker demonstrated a little utility named Read80Trace that I’d...
I wrote earlier on ways I’m storing some of my files offsite. Since then I’ve made a small change. Previously I used JungleDisk to copy shared folders on my HP Windows Home Server to Amazon S3. The newer HP MediaSmart’s come with special HP software that performs the same function. Last week HP released the MediaSmart Server Online Backup for S3 for owners of older devices and I migrated from JungleDisk. I never had any problems with JungleDisk. I just felt more comfortable with officially released and supported software. You can specify which shares you want to backup...
I’m speaking Tuesday morning on Understanding the Procedure Cache at DevTeach\SQLTeach in Montreal. This is the same presentation I delivered at the recent PASS European Conference. At PASS I missed being the top speaker in the Application Development track by 0.8%. Guess I’ll have to step it up here :) This session is what you need to know after you learn how to read a query plan and improve it. I spend a lot of time talking about stored procedures vs. dynamic SQL and the different types of dynamic SQL. I talk about parameterization, compilation and why procedures run...