Vinod Kumar Mindmap
Performance Tuning – STATISTICS IO

Today I was sitting inside a training that my friend Pinal Dave was doing. Spent just about over an hour with the attendees, what came to my notice is some of the simple text statistics techniques that have been with SQL Server for more than a decade are still so relevant and interesting. One such method of measuring the SELECT statement is using the SET STATISTICS ON. It still works with SQL Server 2008 R2.

Getting back to the basics … The command SET STATISTICS IO ON forces SQL Server to report actual I/O activity on executed transactions. It cannot be paired with SET NOEXEC ON option, because it only makes sense to monitor I/O activity on commands that actually execute. Once the option is enabled every query produces additional output that contains I/O statistics. In order to disable the option, execute SET STATISTICS IO OFF.

An typical output would have details like:

Table 'Employees'. Scan count 1, logical reads 55, physical reads 0, read-ahead reads 0

  • The scan count tells us the number of scans performed.
  • Logical reads show the number of pages read from the cache.
  • Physical reads show the number of pages read from the disk.
  • Read-ahead reads indicate the number of pages placed in the cache in anticipation
    of future reads.

These are vital information to tune your SQL Server workloads. Use them more when you have narrowed down your query for tuning.

Unicode Compression with SQL Server

Unicode as such is an interesting topic to explore. But compression on top of Unicode data is yet another interesting hidden feature we introduced with SQL Server 2008 R2 … I got hold of these interesting links that will make some of these concepts clear.

Unicode Compression in SQL Server 2008R2

Unicode Compression example

Coming from the SQL Storage team … A worthy read !!!

Memory OOM condition–SQL Server

Many a times there are needs to figure out if really there are instances where SQL Server components were raising Out-Of-Memory conditions. A quick way to keep track of this is to track the ring_buffers table. Typically you will use something like this:

   1:  SELECT
   2:      record 
   3:  FROM
   4:      sys.dm_os_ring_buffers 
   5:  WHERE
   6:      ring_buffer_type = 'RING_BUFFER_OOM'

Obviously a row here means you do have memory pressures that were being forced. For some explanation on the various values ring_buffers might send, see this blog post from PSS which gives you some starters. I highly recommend reading this whitepaper in your leisure for Performance Troubleshooting which also talks about some of the cool things ring_buffers expose when you start working with SQL Server.

Happy weekend Smile

New AppFabric WCF DataService Template (C#)

Today marks the availability of a new Feature Builder based template for Windows Server AppFabric and WCF Data Services

  • WCF Data Services enables the creation and consumption of OData services for the web (formerly known as ADO.NET Data Services).
  • Windows Server AppFabric provides tools for managing and monitoring your web services and workflows.

The AppFabric WCF DataService template brings these two products together providing the following features:

  • Monitor the queries to your service across multiple servers with AppFabric Monitoring
  • Properly report errors to Windows Server AppFabric (WCF Data Service errors are not recognized by AppFabric otherwise)
  • Eliminate the .svc extension from the URI by using routing
  • Provide a simple HTML page for invoking the service with or without the .svc extension
Get It

If you have the guidance and the template along together in one package you simply add a new AppFabric WCF Data Service to your project and everything you need to know shows up right in Visual Studio – how cool is that?

image

So go download this template today and give it a go …

Microsoft Assessment and Planning Toolkit 5.0 now available

The new Microsoft Assessment and Planning (MAP) Toolkit 5.0 is now available for download! MAP 5.0 is an agentless tool designed to simplify and streamline the IT infrastructure planning process across multiple scenarios through network-wide automated discovery and assessments. This Solution Accelerator performs an inventory of heterogeneous server environments and provides you with usage information for servers in the Core CAL Suite and SQL Server, SQL Server 2008 discovery and assessment for consolidation, Windows 2000 Server migration assessment, and a readiness assessment for the most widely used Microsoft technologies—now including Office 2010.

Is your organization spending valuable resources planning its IT infrastructure? Download the MAP Toolkit 5.0, and let this tool do the heavy lifting for a wide variety of your IT planning projects.

Next steps:

MAP Toolkit 5.0 Key Features and Benefits

What's new with MAP Toolkit 5.0?

  • Heterogeneous server environment inventory
  • Software usage tracking for Windows Server, SharePoint Server, System Center Configuration Manager, Exchange Server, and SQL Server
  • Microsoft Office 2010 readiness assessment
  • SQL Server discovery and assessment for consolidation
  • Windows 2000 Server migration assessment

The MAP Toolkit 5.0 includes the following new features:

Heterogeneous Server Environment Inventory: Know what’s in your IT environment. The Microsoft Assessment and Planning (MAP) Toolkit performs a network inventory of IT assets remotely without the use of agents, identifying heterogeneous server environments consisting of Windows Server and Linux operating systems, including those running in a virtual environment. MAP 5.0 also automates the discovery of Linux-powered LAMP stacks.

Software Usage Tracker: Right-size your IT environment with MAP Toolkit 5.0, and simplify your software license management and compliance processes. MAP 5.0’s new usage tracking feature provides consistent software usage reports for key Microsoft server products: Windows Server, SharePoint Server, System Center Configuration Manager, Exchange Server, and SQL Server. Run updated reports whenever you need to accurately assess current software usage and client access history in your environment. This reduces time and administrative costs for managing your server and client access licenses (CALs) and helps you to streamline the management of your software assets.

Microsoft Office 2010 Readiness Assessment: Streamline your migration to Office 2010 with MAP 5.0’s hardware compatibility proposal document. The MAP Toolkit provides readiness details and specifics of your IT infrastructure inventory, including hardware and OS requirements and upgrade recommendations for planning a seamless deployment of Office 2010. The actionable recommendations and assessments presented shorten the time it takes to plan your Office 2010 migration and prepares your IT environment for Office 2010 deployment and migration scenarios.

Database Discovery for SQL Server Consolidation: MAP 5.0’s new database discovery feature gives you the information you need to optimize your database resources and investments. MAP helps you simplify database administration and provides wide-ranging details of databases and server instances—information you can utilize for consolidation. Use the MAP Toolkit’s proposals to better utilize hardware and database resources, reduce administrative costs, and streamline your software licensing needs—all essential for cost-effective IT planning and operations.

Windows 2000 Server Migration Assessment: As support for Windows 2000 Server ends soon, MAP 5.0’s Migration Assessment feature helps you prepare for migration to Windows Server 2008 R2 by assessing the Windows 2000 Server environment and legacy workloads in the form of proposals and reports. The MAP Toolkit’s actionable recommendations help you to understand the potential business impact of maintaining legacy workloads and the benefits of migrating to the robust Windows Server 2008 R2 environment. With migration to Windows Server 2008 R2, you’ll be able to utilize the increased IT flexibility and efficiency from such technologies as Hyper-V and Remote Desktop Services, as well as tap into power-savings features to decrease TCO.

CHECKDB and Physical_Only Option

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 …

SQL Server 2008 R2 – DMV Changes

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.

SQL Server - RECOMPILE doesn't happen ?

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.

Outlook 2010 – New mail Icon missing?

I have been using Outlook 2010 for a while and one of the interesting part of the new version while using with Win 7 is this interesting icon that gets set with “Large Icons” on the taskbar.

image

In the recent past, I suddenly did some changes to the settings in my usual play and suddenly found the icon never changed on the taskbar when a new mail arrived. I have been wanting to explore this and I got time today to crack this. What I did – well:

  • On the File tab, click Options.
  • Select Mail in the left navigation pane.
  • In the Message arrival section, click to select the Show an envelope icon in the taskbar check box.
  • image

    Thatz it, my lovely icon was back :) … I see a lot of queries like these that come my way where the options are hidden. Hope this helps …

    Presentation Experiences and tips

    I think there is enough and more to this topic on the internet than I just writing it here. But I still thought these are some of my perspectives when it comes to effective Technical presentations. I don't want to call myself a veteran but close to 8+ years of delivering sessions at 100’s of venue makes me feel pretty comfortable in front of audiences. From closed groups of ~5 to open audi of 5000+, I have had the privilege of connecting to many till date. Thanks again for listening to me and I will surely keep improving to meet your expectations.

    Now back to the topic of interest, Presentations. What are some of these tips that I have learnt over these years and I just don't seem to stop learning from others.

    Presentation via PPT

    I don't think there is any presentation anywhere in the world where the powerful tool of PowerPoint is not being used. Though this is a great visual tool to convey your message, please refrain from just reading from the slides. 9 out of 10 times, people in the audience can read faster than you when put on giant screens.

    • PPTs are just pointers for your flow of thoughts. Slides are mere mnemonics.
    • They are there to organize your story in your head.
    • Create visual impact if needed.

    I have learnt this over the years that this is very critical to make a compelling PPT as part of your preparations. There is some method to the madness while preparing your presentations – there is a fundamental story that you want to narrate. There is a lot of time and energy spent in this stage, be very careful of how you make your story consistent from starting till the end.

    There are tons of Golden rules – here are some more for you to digest :) …

    # Know Thy Content

    Content is king – I have used this phrase a lot of times when I talk to external speakers for evaluating them at forums like TechEd and MSDN sessions. There is no substitute to quality content. In this internet world, content is all over the place and the only challenge as a speaker is how are we going to make this relevant to the topic under discussion for that very day.

    Making your own content is so difficult and this is my first advice to all. Try to make your own content inside-out. When it is your very own content you are much more relaxed, you deliver with passion / conviction and your presentation is so smooth that everyone is glued to your talk till the end.

    Think like audience – Many miss this part. It is always good to take inputs from the organizers on the type of audiences expected and what is their experience. My story line changes in the first 2-3 mins of interaction with the audience. You must have two sets of story, one for basic knowledge and other for advanced users. But in a open forum you must balance on audience who are completely new to the topic to someone who has worked on the technology for a while. This is a fine act of balance that you will need to practice over a period of time.

    Prepare FAQ: including harsh ones – As a presenter know what you are talking. There is no substitute to preparation. The toughest part when presenting in front of open audiences is the factor of unknowns. So even if your presentation is on a particular product / feature discussion, be prepared for the questions around competition or even depth content within your own products. This can come and hit you hard.

    “What matters is the effect …. Not the effort”

    # Know Thy Competition

    Know the standards and Industry trends – There is no running away from this. You must speak the language of the industry and have your very own style of delivering the contents & concepts. Know what standards are supported by your products and know the roadmap of your products to support the same. There is no getting away with this point.

    When talking about the competition, know the strengths and weakness of your competition. Least assured one of the audience might be from competition and is trying to test you or catch you on a wrong foot. DONOT get into an argumentative mode with the audience, you must keep such discussion as a side conversation post the presentation.

    As much as you might be prepared for your products presentation, chances are there that you might just get audiences who have just worked on the competing products. So know the right terminologies or parallel terminologies that can be used during such presentations to drive home a concept. DONOT just keep Selling, but make the audience aware of the use for technology. Selling becomes an outcome if they start using your product :) …

    # Know Thy Context

    I have used this to my advantage most of the time and it works 100% of the times. Be clear on what the favorite regional sports, current affairs, politics or even movies and try to make interesting anecdotes to drive home a concept. It takes a bit of creative thinking but it works.

    Typical example I used when I talk about “Locking and Blocking” with SQL Server, I use the Traffic Jam’s in Bangalore as an analogy. It works very well and an immediate connect is made.

    Now though this is a very powerful tip, also now the Ethnic background, the idols of the region before making a witty comment. Like you want to be the last person to comment on Ganguly in Kolkata.

    Please use this tip with caution else if it misfires you might be in trouble even before the session ends :) …

    # Know Thy demo

    Demo’s are super critical when you are on a Technical Presentation. However, big or small the content material, when represented visually with the aid of a compelling demo will surely stick to the mind of the audience. Follow the 4 step process of -

    • Visualize
    • Articulate
    • Asses
    • Improvise

    Make it a visual treat to the audience and have a concise story line. If possible make it to the same storyline you have during your presentation. The connect is so critical. Keep reinforcing on the concept to the demo mapping during the compete demo timeframe. With simple questions asses if the audiences are getting the whole point and on stage you will need to improvise on what you are talking to drive the content effectively.

    Though these are valid simple tips, a lot of presenters get carried away when they present in front of the audience. As much as possible, use the familiar tools used by the audience to build your scenario. There needs to be enough oomph to the demo presented to create a natural learning process and finally usage of the tools for the concepts delivered.

    Finally, let me state that the demo neednot be 100% production ready code. It is just an illustration of the theoretical concepts discussed as part of the presentation. So dont try to get bogged down with Coding standards etc. It might be a good to have factor but not always perfect. You might improvise it every time you do this very topic.

    # Know Thy non-negotiable  

    Time Slot – Though this is not in our control on when you deliver the actual session we must be careful enough to gauge our audience by reaching the venue well in advance. Chances of Murfy’s law of projector not working or any of the logistical issues are solved well in advance. DONOT assume anything.

    More than anything else on the timing, make sure to rehearse the complete session a minimum of 3 times if not more. I personally would suggest and walk through the same atleast 5 times. Finishing session on time is very critical. If you have 60 mins session, be prepared for 45 mins, 30 mins, 60 mins and 75 mins with the same session. The impact is more important than just running through the slides.

    Never in front of audience rush through your powerpoint slides. It is very annoying for someone watching your session. Here is a presentation tip, know your slide before the Summary slide – type that slide number in the presentation mode and hit enter. Vola, you are now at the slide before the Summary slide and ready to conclude in less than 5 mins.

    If you find yourself running over, cut it off at the end of the next complete thought or slide segment. Don’t show any more slides and don’t apologize for the lack of time. Avoid saying “There’s no time to show this in more detail.” It serves no purpose. If you hadn’t mentioned it, the audience would never know there was more to be covered.

    Practice – Though the importance of this point can never be underestimated as discussed in the previous point. I want to again bring it to your attention. Practice, practice and practice till you know in your mind the exact flow of slides and the story board has got itched into your mind.

    Esp when you are the first speaker in the afternoon, your practice comes to your help. I have done tons of afternoon sessions and with a lot of success without getting the audience to sleep but attentive to each and every minute of my session. It is not an art, but practice …

    Real World Examples – We are talking to our audience who are investing their time on us. So being relevant to them is critical. We cannot give hypnotically examples during a session. Examples and scenario’s mentioned must be near-real world and practical as possible.

    Though, I discussed some of the content specifics, we need to know the region and plan out our presentation styles. Like I have seen many presentations in India where the slides carry “Releasing this summer”. Now a person in Chennai will be wondering if there is any other season other than Summer in Chennai :) … So the context and local relevance is very important. Be real – period.

    # Know Thy Do’s-Dont’s

    Internet access – Even though we are in this age of Internet everywhere (even on phones), dont assume the same in the presentation location. A lot of hotels atleast in India have a pathetic internet connection speeds. So if you can secure your internet on your own, you are good. Always have a “Plan B” when your demo’s are so closely linked to internet scenario’s. Data cards are cheap and having one is always an advantage. 

    Don’t drink alcohol before a presentation – Being professional is most important and the last thing you want is to is to feel sleepy or worst make a fool out of yourself on stage. Keeping your senses in full alert during a session is critical to a great public speaker.

    Charge yourselfDont skip your meal or lunch before a session. Having some energy to sustain the course our your session is very critical. Have a chocolate bar, energy bar in your bag for quick glucose into your session. Having too much is also not advised and you dont want to burp in front of your audience :) … So find your right balance.

    Warm up your voice - Our voice is the primary instrument in our performance, so we need to give it the attention it deserves. With sessions on SQL Server everytime my AV guy asks me - “any audio output sir?”, I always reply - “My throat is the only audio output” :) … So practice for that perfect comfortable flow.

    Strong “Welcome” and “Thank you.” - Come up with a short, sincere welcome statement you might want to use. Esp when you say – “I am really excited to be in front of you”, the excitement must be in the voice and dont forget to look into the eyes of your audience. I have seen terrible speakers esp falter in this first step and this is what sets-up the tone for rest of your presentation. Please rehearse this part as many number of times as possible till your get it right !!! As much as it can be said for the beginning, the ending also must be on a good strong note. So do take time to practice your summary and closing note.

    Watch the handsCreating a visual connect is very important. When you are narrating a story people use your visual aid’s to connect the dots. Like if while talking about servers you show it on right side and client desktops on left side – this visual congruence cannot get reversed later in your story. It creates a lot of distraction for people.

    Keep them out of your pockets and under no circumstances cross your arms while presenting. Your goal should be to connect with them. Use your hands to help you tell stories and convey emotions. Your audience listens with both their eyes and ears.

    Don’t forget to smile - Deliver your talk with lively enthusiasm even if you’ve presented the material 100 times already. Remember, it’s a brand new audience and they want an enjoyable presentation. As speakers it is upto us to upgrade the content the next time we get on stage based on the repeated questions from the audience.

    Zoom-in to emphasis - Use the free Zoomit tool to focus in on areas of the screen that are being discussed. The current 4th version is very powerful and can be used to make arrows, highlighter etc. So first play with the tool and you will find it very useful.

    Final thoughts

    Make yourself approachable - Be sure to share your contact information on screen and pass out your website link, blog, Facebook, twitter handle etc. Encourage anyone with follow up questions to contact you at any time via the social connects. It can be really fast and you can quickly create your own brand …

    Publish your content online - It should be trivial for your audience to obtain the materials you presented. And also make sure you make it available via your website, blog and other channels easily.

    Don't leave your mobile or wallet or other valuables on podium - There is every likelyhood, that after the presentation you will be preoccupied with questions from audience and you will forget to collect your items back. 

    Good nights sleep and bottle of water handy – However good speaker you are, you are likely to have some nervousness the previous day. Keep al these aside and take a good nap before the big day. Have a bottle of water handy to avoid dry cough during your sessions …

    Though these tips are generic, these might not yet be exhaustive. Dont try to take these tips like something written on stone. You need to be aware of these mistakes but dont try to be too conscious of not doing something. Be comfortable and be yourself to create your own style and aura on stage.

    All the best to all future presenters … Thanks for reading this far :) …

    If you are interested in these topics, do feel free to read my posts around Technology Evangelism -

    Evangelism beyond boundaries with an Evangelist !!!

    Technology Evangelism Demystified

    New face of Online Technology Evangelism

    DECLARE with Initializer

    In SQL Server 2008, the DECLARE statement has been extended to allow specifying an initial value along with the declaration. Not sure how many of you have taken a chance to use this feature … Beyond the normal looks there is more to this than the eye can read, typical example is -

    declare @var varchar(30) = 'Hello'

    This is very much to the tone of declaring and then issuing a SET command like -

    declare @var varchar(30)
    Set @var = 'Hello'

    Interestingly, this might not be the case. Note that even though the value is specified in the DECLARE it is not a parse/compile time operation to initialize it. The initialization to that value still happens at execution time. On previous versions of SQL Server, the variable was implicitly initialized to NULL and the DECLARE statement didn’t actually have an execution component. That is, if you traced the SQL:StmtStarting or SQL:StmtCompleted events there was no event produced for the DECLARE statement.

    When you use this construct in SQL Server 2008, the value is still implicitly initialized to NULL at compile time and the variable’s value is assigned at runtime, which internally is exactly the same as what you would get for a SET statement initializing the variable. But now you will start seeing this part of Profiler if used with initialization.

    image

    There is surely an implicit conversion that occurs as part of the initialization incase you dont use the right castings and now DECLARE statement also must be part of the try-catch block because of this very reason :) …

    The best part doesnt end here, the optimizer still treats the variable’s contents as unknown (NULL) for cardinality estimation:

    declare @state varchar(2) = 'CA'
    select * from authors where state = @state    -- card estimate is 2.875
    select * from authors where state = 'CA'    -- card estimate is 15

    Just check on the “Estimated number of rows” column and these values be seen … Though some of these features look so trivial, there are tons of interesting behaviors these features open. So feel free to look at these features closely and enjoy the usage with deeper understanding …

    Undocumented CHECKDB options

    Many of my previous posts do talk a lot about how CHECKDB works within SQL Server with various interesting combination of scenarios. Though the use of CHECKDB and understanding of this topic is quite complex, in this post let me talk about the undocumented option of TABLERESULTS. It is quite handy and I used it in one of the demo at TechEd India 2010.

    WITH TABLERESULTS

    This option will allow CHECKDB to return a TDS based result set for all messages (informational and errors) returned as part of executing CHECKDB. This option maybe undocumented for multiple reasons:

    * Maybe the code is not yet tested or there can be bugs that can disrupt the behavior end-users want.

    The option is interesting as the result set if implemented fully could help produce a report of true errors encountered, specific repair actions needed for each error, etc.

    The command is beneficial to analyze corrupt databases when brought in-house for troubleshooting. Esp for cases when there is corrupt index pages, you can quickly look at the INDID and figure out is they are only non-clustered indexes that have got damaged or if there are Clustered indexes also which have to be examined …

    Microsoft is not under any obligation to provide information or support these commands or tools as these are undocumented. My previous posts around CHECKDB are -

    CHECKDB and TempDB

    CHECKDB and DATA_PURITY

    CHECKDB is ONLINE now

    CHECKDB and ResourceDB

    CHECKSUM and SQL Server

    CHECKSUM and TempDB

    CHECKDB and TempDB

    Continuing the conversations around CHECKDB and my session at TechEd. Here is another interesting trivia about CHECKDB and its usage against TempDB. If you remember the post around CHECKDB going ONLINE on the blog the usage of Snapshot internally is something unique. If you get into the documentation of DATABASE SNAPSHOTs on BOL you can read – we cannot create a SNAPSHOT on system databases including TEMPDB. The only exception is MSDB but that is a conversation for another day :) …

    So our internal snapshot for CHECKDB also fails gracefully and you will notice this when you issue the command against TEMPDB.

    DBCC results for 'tempdb'.
    DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.

    This is because of the behavior of Tempdb with ability to recover the database on restart. You cannot create a database snapshot on the Tempdb database.

    Interesting hidden within the message - the CHECKCATALOG and Service Broker checks are skipped. On closer inspection - it is not possible to run CHECKALLOC on Tempdb too.

    DBCC CHECKALLOC (tempDB)
    The check statement was aborted. DBCC CHECKALLOC cannot be run on TEMPDB.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    There are undocumented trace flags that can force this action but those are surely out-of-scope and most importantly, unsupported :). BTW, while I was on the first error – even the CATALOG are not checked and you can confirm it from CHECKCATALOG command which also fails just like above.

    DBCC CHECKCATALOG (tempDB)
    The check statement was aborted. DBCC CHECKCATALOG cannot be run on TEMPDB.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    When it comes to TempDB corruptions, a restart will solve the same. But I highly recommend further investigation to the root cause analysis because you dont want to be in this situation over a weekend when you are on vacation. Mostly, errors comes from IO subsystem and that should be your first point of troubleshooting. Then move to the next layers.

    CHECKDB and DATA_PURITY

    DBCC CHECKDB command performs several operations in additional to checking the consistency of allocation structures and various objects inside a database. One such important interesting activity added is the DATA_PURITY checks. In SQL Server 2000, DBCC CHECKDB did not evaluate the values present in the columns to ensure that they are valid and conform to the rules specified for the data types for those columns. But there was an undocumented trace flag which could be used to force DBCC CHECKDB to perform some limited data purity checks. But I will refrain from getting into those shortcuts …

    In SQL Server 2005, a documented option is available for the DBCC CHECKDB command that can be used to force the data purity checks. This is the WITH DATA_PURITY option. There are a couple of important points that you need to understand regarding these checks:

    • The data purity checks are performed automatically whenever DBCC CHECKDB is executed against a database created in SQL Server 2005
    • The data purity checks cannot be disabled for databases that were created in SQL Server 2005
    • The data purity checks are not performed automatically whenever DBCC CHECKDB is executed against a database created in SQL Server 2000 or earlier
    • If PHYSICAL_ONLY is specified, column-integrity checks are not performed.
    • In order for DBCC to perform data purity checks on a database created in SQL Server 2000 or earlier, you will need to execute DBCC CHECKDB with the DATA_PURITY option.
      • If this DBCC CHECKDB was successful, then a bit is turned on in the boot page to indicate future runs of DBCC CHECKDB perform data purity checks automatically. If you are still running SQL 2000 (you must upgrade), but for academic purposes you might want to see the bit info using the DBINFO command. This typical undocumented command can be used like this -

    DBCC DBINFO (AdventureWorks) WITH TABLERESULTS

    Paul on his blog talks about this interesting information of DBCCFlags bit out there. 0 means it will not be run and 2 will mean it will be enabled by default. There are specific checks performed for columns with the data types – bit, real, float, datetime, smalldatetime, decimal, numeric, binary, varbinary, nchar,nvarchar. There is a very good possibility that data purity checks show problems on upgraded databases because these checks were not performed in earlier versions unless the trace flag was used to force these checks.

    CHECKDB is ONLINE now

    The series of activities I have been writing on CHECKDB, I just dont seem to stop exploring interesting things about this command. It is so powerful that I just love it. In a session with MS Internal IT today I was talking about how CHECKDB is done as an ONLINE session and we changed to this mode since the SQL Server 2005+ days. So now CHECKDB is run of an internal snapshot database. This snapshot is similar in some respects as if you were to create a database snapshot using the CREATE DATABASE command. The snapshot created by CHECKDB is not visible to users in any form. When you query catalog views or system tables, there will be no reference associated to this snapshot.

    Having said all this, the world was way different in the SQL Server 2000 days. For academic purposes, the CHECKDB used to perform expensive log scans to bring individual pages to a consistent state to perform analysis on the pages and generate facts to check the consistency. Also there was expensive locking involved to keep the analyzed data consistent. All of this is done on the live database on which data modification is happening. So now you get the fact on how CHECKDB has evolved over a period of time.

    Coming back to the SQL 2005+ implementation, this internal snapshot are a transient replica of the database and is not a persistent replica like what we create using CREATE DATABASE with SNAPSHOT. So once the CHECKDB operation completes, we gracefully delete this transient database.

    Now that we are on this interesting point, let me give you a behavior that is interesting that I was discussing with Pinal Dave today over lunch. Take this simple scenario, we have an “Open transaction” when the CHECKDB gets initialized. Just like below -

    Begin Tran
    Insert into temp1 values (1)

    Now when I run the CHECKDB on this database (on a different window), when this above transaction is OPEN, there is an interesting message on the SQL Server Errorlogs that is worth noting.

    image

    Interestingly, this seems to have rolled-back our transaction without our consent? No way, dont panic. This has not rolled-back our database transaction, but since CHECKDB needs to run against a consistent database, it rolled-back our transaction on the “Transient Replica” aka internal snapshot. To double check this behavior, issue COMMIT back on our original query window (with INSERT stmt) and you can see the data with value of “1” has indeed got committed into the database :).

    More interesting trivia on SQL Server coming your way … Thanks and feel free to pass your comments.

    More Posts Next page »

    Search

    Go

    This Blog

    Tags

    Community

    Archives

    My Web Site

    External Sites

    Other Misc Blog Links

    Newsletter

    Syndication

    News


    • Vinod Kumar space
      Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

      Follow me on Twitter: vinodk_sql

      Site Counter: Free Hit Counter