Monthly Archives: March 2012

XML Plans Saga –Twilight

Posted on by

XML plans were a new addition in SQL Server 2005 and above which enabled a DBA to fetch additional information from a SQL Server query plan. In this blog post, I shall list out the useful information that can be fetched out of a query plan which can be pertinent to your performance tuning activities. I am old-skool and hence I still prefer analyzing query plans/comparing them using the text plans. However, that doesn’t mean that I ignore XML plans as they have a plethora of information which can be useful for troubleshooting a SQL Server performance problem as well as for query tuning activities.

As you have already figured out from the post title that this is the first post in the XML Plans Saga. All the information that I need to dole out as part of this series will be covered in four blog posts with a break down as shown in the bulleted list below.

You can generate XML Showplan output by using the following methods:

  • Selecting Display Estimated Execution Plan or Include Actual Execution Plan from the query editor toolbar in SQL Server Management Studio
  • Using the Transact-SQL Showplan SET statement options SHOWPLAN_XML (for estimated plan) and STATISTICS XML (actual execution plan)
  • Selecting the SQL Server Profiler event classes Showplan XML (for estimated plan), Showplan XML for Query Compile, and Showplan XML Statistics Profile (actual execution plan) for tracing
  • Using the sys.dm_exec_query_plan dynamic management function

    XML Showplans are returned in the nvarchar(max) data type for all of these methods, except when you use sys.dm_exec_query_plan. XML Showplans are returned in the xml data type when you use this dynamic management view.

    The XML schema for Showplan is available with the SQL Server installation files at the following location:

    SQL Server 2008 and 2008 R2: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\20047\showplan\showplanxml.xsd
    SQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\20047\showplan\showplanxml.xsd

    For x64 platforms, the folder would be Program Files (x86) instead of Program Files.

    I am going to pick a simple query to demonstrate the use of XML plans and gather information from the graphic XML plans which are obtained from management studio. The query that I will be using is:

    select top 100 chardata
    from dbo.tblTechEd1
    where id = 1

    Now the above table doesn’t have a clustered index. The table size is approximately 781MB with 100K rows.

    • Continue reading →

    Advertisement

    Microsoft TechEd 2012–Multiple rows affected

    Posted on by

    WP_000363If you were at Microsoft TechEd India 2012 held at Bangalore, then you would agree with me without a shadow of doubt that it is quite difficult to write a wrap-up post of the entire event. The event for me was what I refer to as PPP. And by PPP, I am not talking about Point-to-Point Protocol. What I am referring to is a self-coined term…. People, Passion and Parallel.

    People because it was great to connect a face to the twitter handle or online persona that you have interacted with in the past. What amazed me was some of the folks that I met at TechEd introduced themselves with their Twitter handle first and their actual name later! It is phenomenal to see how social media can empower collaboration between a technical community! The #TechEdIn twitter hashtag was trending on Twitter India in less than 15 minutes after the event started on Day 1 and continued to trend for the next three days!

    Passion for technology that was evident from Day 1 to Day 3 where the rooms overflowed with people attending the sessions at different tracks. It was as if people were watching technical blockbusters at TechEd!

    Parallel is the name of the game as everything that was happening at TechEd was in parallel… Whether it was the networking that was happening between folks or be in the technical sessions being delivered in parallel at the different tracks or the technology showcases displaying the several cool stuff from the sponsors at the venue. I heard multiple conversations in the hallways where debates were being raged (among the attendees) to decide on which session they should attend under which track! And obviously Twitter stands testament to the fact that such a dilemma existed since there were numerous tweets on this topic!

    Since my daily bread and butter revolves around Microsoft SQL Server, it was quite obvious that I was lurking around the sessions which had remotely anything to do with data! On day one, I would be found mostly at “Data and Developer – Synergy with SQL Server” and on day three I was mostly at “Know Your Data – A Day in Life of a DBA”. Though there was a copious amount of information exchange in the session, there were also some really humorous moments which deserve a mention:

    Vinod [B | T] and Pinal’s [B | T] session on “SQL Server Misconceptions and Resolution – A Practical Perspective” probably landed them as top candidates for a thin one and the fit one series in case they ever choose to pursue a media related career option!

    Pinal Dave’s session on “Speed Up! – Parallel Processes and Unparalleled Performance” left people in splits…

    Balmukund’s [B | T] session on “Keeping Your Database Available – ‘AlwaysOn’“ made learning fun and even though he had presented this in the past at other forums… This time around he came back with a totally new look and feel along with a high quality technical session on SQL Server 2012 AlwaysOn features with demos.

    The VDI demo that I showed was using the “SQL Server Backup Simulator” which can be downloaded from here. The snapshot demo was shown using a modified version of the code present in the snapshot.cpp file from the Microsoft SQL Server Virtual Device Specification.

    I had a lot of fun presenting my session on the “Lesser Known facts of SQL Server Backup and Restore” on March 23rd at TechEd. The slide deck used for the presentation is available below without a few situational humorous elements which wouldn’t really make sense to a person who had not attended the session. (Had to add the disclaimer before I got pulled up with comments that this was the incomplete slide deck!)

    Here are some additional resources on VDI and VSS in case you want to indulge in some light reading Smile

    IO Frozen messages while taking NT Backup for SQL databases
    INFORMATIONAL- SHEDDING LIGHT on VSS & VDI Backups in SQL Server
    Troubleshooting SQL Server Backup Issues
    Incorrect Buffercount can cause Out of Memory Errors
    VDI (VSS) Backup Resources
    How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)

    The photographs from the session are available on TroubleshootingSQL’s Facebook page. Last but not the least, there needs to a be special vote of thanks to Pinal Dave who ensured that we had a seamless experience during the event.

    The session recording is embedded below:

    People… Passion… Parallel… @TechEdIn

    Missing indexes and Create Index command from the procedure cache

    Posted on by

    Recently I was working on a performance issue for SQL Server where I needed to identify the missing indexes on the database instance. This was quite easy since I have a T-SQL script to do this which I have already blogged about before. Now the next task was to identify the SQL statements for which these indexes were suggested. Now this was also easy since my friend Jonathan Kehayias [B | T] had already blogged about this. The next ask now was to get the Create Index command for the list of missing indexes received! Well…. this time I ended up with a strike as I didn’t have any options with me. So I got down to modifying Jonathan’s T-SQL script to add to new commands to provide the CREATE INDEX statement in a separate column of the temporary table #MissingIndexInfo which his script creates.

    ALTER TABLE #MissingIndexInfo ADD CreateIndexStatement varchar(8000)

    UPDATE #MissingIndexInfo
    SET CreateIndexStatement = ‘CREATE INDEX <index name>’ + ‘ ON ‘ + statement + ‘ (‘ + ISNULL (equality_columns,”) + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (inequality_columns, ”) + ‘)’ + ISNULL (‘ INCLUDE (‘ + include_columns + ‘)’, ”)

     

    Before you go ahead and start deploying these scripts to your environments, you need to be cognizant of the fact that the procedure cache may or may not have all the cached plans for all the queries that you want to examine. You would want to find out the missing indexes using the query here and compare it with the list that you retrieved using Jonathan’s query with the modification listed above. This sample script is an attempt to provide you with a list of indexes which may prove beneficial for your queries based on the Missing Indexes feature which was introduced in SQL Server 2005 and above. As always, you would still need to test before implementing these indexes onto a production server instance.

    TechEd India 2012 Session

    Posted on by

    TechEd

    TechEd India is always a much anticipated event and I always feel that this event keeps getting better year on year. This year I have the privilege of presenting at TechEd which is being held at Bangalore from March 21-23, 2012. My presentation topic is “Lesser Known Facts of SQL Server Backup and Restore”. The topic abstract is as follows:

    Backup and restore are considered the most common tasks performed by DBAs. Most of the time the knowledge does not go beyond memorizing the syntax of backup and restore. In this session we will uncover some hidden facts which will change the perspective of DBAs towards this process.

    What can you expect from this session?
    image1. I will be talking about the some of the unknown facts about SQL Server database backup and restore which will help you streamline your backup performance and DR strategy.
    2. Demo of a backup restore using a tool that I have written to show you some interesting statistics.
    3. Clearing up some common myths around backup/restore.

    I will delivering this session from 3:15PM IST – 4:15PM IST on SQL Server Administration track on Day 3 (March 23rd).

    There will be other speakers like Pinal Dave [B | T], Vinod Kumar [B | T], Balmukund Lakhani [B | T] and Jacob Sebastian [B | T] who will not only help you gain knowledge but also have amazing demos in their presentations which will have you running the following query: SELECT REPLICATE(‘awesome’,1000) in your head!

    If you haven’t already registered for TechEd yet, then you can do so by visiting the TechEd site. The Facebook event for my session is available here.