SqlXml Blogs

Performance Tuning – STATISTICS IO

vinodkumar — Fri, 03 Sep 2010 17:12:15 GMT

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...(read more)

Unicode Compression with SQL Server

vinodkumar — Thu, 02 Sep 2010 16:33:48 GMT

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 !!!...(read more)

Memory OOM condition–SQL Server

vinodkumar — Sat, 28 Aug 2010 01:57:29 GMT

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...(read more)

New AppFabric WCF DataService Template (C#)

vinodkumar — Wed, 25 Aug 2010 08:53:00 GMT

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...(read more)

Microsoft Assessment and Planning Toolkit 5.0 now available

vinodkumar — Tue, 03 Aug 2010 13:51:00 GMT

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...(read more)

CHECKDB and Physical_Only Option

vinodkumar — Thu, 08 Jul 2010 16:29:17 GMT

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...(read more)

SQL Server 2008 R2 – DMV Changes

vinodkumar — Mon, 05 Jul 2010 14:18:59 GMT

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...(read more)

SQL Server - RECOMPILE doesn't happen ?

vinodkumar — Sat, 03 Jul 2010 15:24:55 GMT

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...(read more)

Outlook 2010 – New mail Icon missing?

vinodkumar — Tue, 22 Jun 2010 05:14:55 GMT

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. 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...(read more)

Presentation Experiences and tips

vinodkumar — Mon, 21 Jun 2010 08:26:00 GMT

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...(read more)

DECLARE with Initializer

vinodkumar — Wed, 16 Jun 2010 04:18:52 GMT

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....(read more)

Undocumented CHECKDB options

vinodkumar — Fri, 28 May 2010 10:23:34 GMT

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...(read more)

CHECKDB and TempDB

vinodkumar — Mon, 24 May 2010 16:45:07 GMT

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...(read more)

CHECKDB and DATA_PURITY

vinodkumar — Fri, 21 May 2010 16:01:36 GMT

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...(read more)

CHECKDB is ONLINE now

vinodkumar — Tue, 18 May 2010 13:30:00 GMT

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...(read more)

CHECKDB and ResourceDB

vinodkumar — Thu, 13 May 2010 04:23:38 GMT

ResourceDB is a new system database that we introduced with SQL Server 2005+. Though this is hidden and cannot be access directly as we used to do with SQL Server 2000 days of changing metadata, there is some protection now. Anyways, there are lots more interesting trivia’s of using ResourceDB and I will reserve it for another day. Today the topic is around - “What if this hidden DB gets corrupt?” … Not exactly, but how can I run CHECKDB on this database? DBCC CHECKDB (mssqlsystemresource) This is...(read more)

CHECKSUM and SQL Server

vinodkumar — Sun, 09 May 2010 11:15:18 GMT

Checksum is a new feature introduced in SQL Server 2005+ to aid in data integrity and IO consistency issue detection. Checksum is a way to detect problems and not necessarily take any action. I wrote about CHECKSUM in my previous posts around CHECKSUM Vs Torn Page and many more. The basic functionality is as follows: When the unit of storage is prepared to be written to disk, the checksum is calculated and stamped in the page header Whenever this page is read back, the checksum on the storage unit...(read more)

SQL Server Database States

vinodkumar — Tue, 04 May 2010 14:13:20 GMT

Last year at TechEd India 2009 I talked about Recovery of database with SQL Server. In SQL Server 2005+, there are 7 database states. And there is a method to analyze these database states, which I will discuss later. At any point of time, a database can only be in one state. OFFLINE RECOVERING RECOVERY PENDING ONLINE SUSPECT EMERGENCY RESTORING A database changes its state after an operation that happens automatically due to some sequence of events on the server. Some of state changes result from...(read more)

Script: SQL Schedulers running tasks

vinodkumar — Mon, 03 May 2010 03:48:47 GMT

You can monitor the SQL Server schedulers using the sys.dm_os_schedulers view to see if the number of runnable tasks is typically nonzero. A nonzero value indicates that tasks have to wait for their time slice to run; high values for this counter are a symptom of a CPU bottleneck. SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 The number...(read more)

CHECKSUM Vs FileStream

vinodkumar — Wed, 28 Apr 2010 04:07:09 GMT

In the previous posts I have discussed briefly about the CHECKSUM options . Fundamentally, Database options allow you to establish checksum, torn page or no audit detection levels at a database level. File stream file groups do not apply the database level options to the files it stores as part of NTFS volume. It makes sense, as to checksum files would require the SQL Server process to read a file and calculate the checksum as it is streaming the big chunk from disk. This could be a very expensive...(read more)

Script: Checking for DAC connections

vinodkumar — Mon, 26 Apr 2010 04:35:42 GMT

I hope you are all aware of the DAC (Dedicated Admin Connection) concept which we introduced with SQL Server 2005+ onwards. It is an awesome backdoor entry into SQL Server when things are not going good on the Server. It is an awesome way to troubleshoot SQL Server when it is unresponsive. As a DBA, you might want to know if anyone is using this special dedicated connection somewhere on your environment. Then here is a simple query that returns you this information … SELECT t2.session_id, t1.name...(read more)

Torn Page Vs CHECKSUM

vinodkumar — Fri, 23 Apr 2010 06:49:27 GMT

This also comes from the various questions that were asked after my session at TechEd India 2010. Confusions exists what to turn ON. My suggestions from SQL Server 2005+ will be to use the CHECKSUM ON. A term torn page comes from the idea of a torn page in a book. A SQL Server database page is 8Kb in size. This page can be thought of in terms of 512 byte units called sectors, which is the lowest unit of I/O for disk drive. So a SQL Server page is 16 sectors. Since the disk will actually write the...(read more)

CHECKSUM and TempDB

vinodkumar — Thu, 22 Apr 2010 04:22:00 GMT

I just finished my sessions last week at TechEd India 2010 but during the session as I spoke about Recovery and Consistency of data with SQL Server and the enhancements of SQL Server 2005 adding CHECKSUM to the database, I found not many to know its importance. There has been an enhancement to SQL Server 2008 specifically around this which I thought is worth a mention over the blog. The database option page_verify_option can be set to CHECKSUM in tempdb database starting with SQL Server 2008. This...(read more)

TechEd India 2010

vinodkumar — Tue, 06 Apr 2010 05:52:16 GMT

I am all geared for TechEd India 2010 scheduled to happen next week in Bangalore. If you are in India don’t forget to meet me at the Event from April 12th – 14th. More details on the technical tracks are at www.microsoftteched.in … I am managing close to 4 different tracks this time and I am doing two sessions on the Database track. It is completely action packed and don’t miss the action !!! Are you coming to the event?...(read more)

New face of Online Technology Evangelism

vinodkumar — Fri, 26 Mar 2010 09:40:00 GMT

This post is in continuation to the series of articles I am writing around my view’s on Technology Evangelism. Your feedbacks are driving me to write more on this very topic and in this article let me move into more recent aspects of Technology Evangelism which is an unexplored territories for many companies. The Part I and Part II which I did post were fundamentally around traditional Technology Evangelism. Evolutions do happen and Technology Evangelism is no exception. We will *try* to get into...(read more)