Archive for July, 2010

CHECKDB and Physical_Only Option July 8th, 2010

Vinod Kumar

A number of you have asked me during my sessions on how long will CHECKDB run for my 1 TB DB. Though there are no specific or scientific answer to this just like what Paul mentions over his blog. There can be tons of reasons for the same. The CHECKDB has been enhanced from every version and it gets better and better from versions from performance perspective but tons of other structures or additional checks get also added. During one of the presentation recently I did answer around using PHYSICAL_ONLY option for running on large databases.

If Indexed views and XML indexes are present in a SQL Server 2005+ database, the time taken to complete DBCC CHECKDB, DBCC CHECKTABLE will be longer. This is because of the extra checks that SQL Server 2005+ versions performs by default on these new data structures. If this is turning out to be a problem in such a way that the DBCC CHECK’s are not completing within the maintenance window, you can use the PHYSICAL_ONLY option.

Using the PHYSICAL_ONLY option skips the indexed view and xml index checks. Also data purity checks are skipped when PHYSICAL_ONLY option is specified for the DBCC CHECK command. Please refer to the Books Online topic discussion on DBCC CHECKDB statement for a full list of checks done when the PHYSICAL_ONLY option is specified.

Though this is handy in some way, you might still want to run CHECKDB on the full database once in a while – may be once a month to see your IO subsystem is not acting cranky …

Continue reading...


 

SQL Server 2008 R2 – DMV Changes July 5th, 2010

Vinod Kumar

Majority of the folks over various conversations tell me there are not much of changes with the R2 version. Well, there are tons of little things that make the product better and better both for Developers and ITPro’s alike. In this list is some subtle additions to the DMV’s which make some interesting insights into the product itself. I will make another post to get some interesting insights with these DMV’s for you … Some of the DMV extensions made are around –

SYS.DM_OS_THREADS

  • processor_group

SYS.DM_OS_WORKERS

  • processor_group

SYS.DM_OS_NODES

  • online_schduler_mask
  • processor_group

SYS.DM_OS_SYS_INFO

  • affinity_type
  • affinity_type_desc
  • process_kernel_time_ms
  • process_user_time_ms
  • time_source
  • time_source_desc

SYS.DM_OS_MEMORY_NODES

  • cpu_affinity_mask
  • online_scheduler_mask
  • processor_group

Surely, these don’t form the complete list but can be something to explore for the future.

Continue reading...


 

SQL Server – RECOMPILE doesn’t happen ? July 3rd, 2010

Vinod Kumar

This is a wonderful topic and very close to my heart in some way because it is part of  Performance optimization techniques. If you had a chance to read this whitepaper on TechNet on “Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005” then you will understand where I am really coming from. That whitepaper has a lot of interesting internal information hidden between the lines. This post is not to mimic or copy parts from there by any chance but take a slightly different route of when the RECOMPILE doesn’t happen. The whitepaper exhaustively outlines when it happens in detail but there are some conditions when it can get differed by the optimizer. Let me call out some of them here –

There are specific conditions when the recompilation gets short circuited.

– When the plan is a “trivial plan.” A trivial plan results when the query optimizer determines that given the tables referenced in the query and the indexes existing on them, only one plan is possible. Obviously, a recompilation would be futile in such a case. A query that has generated a trivial plan may not always generate a trivial plan, of course. For example, new indexes might be created on the underlying tables and so multiple access paths become available to the query optimizer.

– When a query contains “KEEPFIXED PLAN” hint, its plan is not recompiled for plan optimality-related reasons.

– When all of the tables referenced in the query plan are read-only, the plan is not recompiled.

So some of these are really interesting conditions in understanding the RECOMPILE architecture esp on heavy workloads. So one of the main readings I see on any perf-tuning exercise is the recompile perfmon counter. In SQL Server 2008 onwards we introduced the Stmt level recompile and it will surely form another long post some other day.

On the Performance Tuning topic – I also recommend reading this whitepaper. Though a bit dated, they are very relevant even today with SQL Server 2008 versions.

Continue reading...