Archive for October, 2012

Picture Post: Opinions and Perspectives October 31st, 2012

Vinod Kumar

Everything we hear is an opinion, not a fact. Everything we see is a perspective, not the truth – Marcus Aurelius

People always have opinions and there is something to give others but we think differently when it comes to self. In this context, I thought this picture post has 4 different quadrants that give different ways we look at how we see opinions based on our view / knowledge and others views / knowledge.

You can also apply this concept for work, your job, handling kids, handling seniors, handling teachers etc. Almost every place this holds good.

Quadrant 1: Neither you or other know about this. This is a classic example where a lot of learning exists. Research is one such area.

Quadrant 2: You know more than the average Joe and this can be frustrating sometimes. A classic example will be for people who are into Niche Product Engineering teams

Quadrant 3: If others know more than you then you might be into this soul searching mode why you are so lost. A classic example for me is when people talk about Photography techniques and it is completely blank for me :)

Quadrant 4: Both you and the other party know enough details. Now this is an area of intellectual talk and we are open to learning mode in this zone.

Other people’s opinion of you does not have to become your reality – Les Brown

All I have to say is – Don’t let the opinions of others control the way you look at life / work or anything. It is not what others think, it is what you think about yourself that matters. You take bold decisions and steps based on what is best for you and your life, not based on what is best for others – be yourself and make your way and mark.

Thanks for read this far. Feel free to drop your comments.

Continue reading...


SQL Server Script: When were Statistics last updated October 30th, 2012

Vinod Kumar

At my job I often help customers with Performance tuning their SQL Server environments and their application. One of the common recommendation we give is to check their maintenance plans and see if the Statistics of tables are up-to-date. This is a very common exercise and given that you have 1000s of tables to monitor, you might want an easy script to figure out which are those tables / indexes that need attention. I am giving a simple script that we use at our environments to identify the same.

OBJECT_NAME(ind.object_id) AS ‘Table Name’, AS ‘Index Name’, 
STATS_DATE(ind.object_id, ind.index_id) AS ‘Statistics Date’
FROM  sys.indexes ind
WHERE OBJECTPROPERTY(object_id,‘IsMSShipped’)=0

With the above script the output would be as below:


Look at the “Statistics Date” column and this gives you information of how dated the statistics are. Once you have figured out the statistics are out-of-date by few weeks or months, use the UPDATE STATISTICS command to update the same. Yet another command used for this purpose is sp_updatestats.

Also, even if you have AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC (Statistics documentation) options enabled inside your database Statistics *can* be out-of-date. So analyze your environments from time-to-time and then come up with your updating strategy.

Continue reading...


SQL Server: How to find mapping of tables with filegroups October 29th, 2012

Vinod Kumar

One of the recommendations I give to customers coming to MTC is to do proper placement of tables to appropriate filegroups. The default behavior is – SQL Server would create a default PRIMARY filegroup and all the data would be placed into the .mdf file. And this is something I have seen at a lot of ISV’s forget to work on and configure.

Recently, when this recommendation was given, one of the DBA did ask me – “How do we know which table is in which filegroup? Is there an instant script for the same?”. Well, there are a number of ways to find this information and I am attaching the same for reference here.

Method 1: sp_help

The simplest and the old technique is to use our proven sp_help. The last result set has the filegroup this table dbo.t1 is associated with.

sp_help ‘[dbo].[t1]’

A typical output looks like below:


Method 2: sp_objectfilegroup

The limitation of the method 1 is that it gives in different result set and it is difficult to take this information for future use. Here is an undocumented procedure which can get you this information.

DECLARE @ObjectID INT = (Object_id(‘[dbo].[t1]’))
EXEC sys.sp_objectfilegroup @ObjectID

The output for the above call is:


Method 3: DMV’s

Since SQL Server 2005 we have DMVs that give us important information and interesting insights that we are not aware off. And this information is also hidden inside few DMVs like the query below.

SELECT AS Data_located_on_filegroup
FROM   sys.data_spaces d
               FROM   sys.indexes i
               WHERE  i.object_id = OBJECT_ID(‘[dbo].[t1]’)
                      AND i.index_id <= 1
                      AND d.data_space_id = i.data_space_id)


The output of this query would look like:


A small extension to the same query can be made in such a way that we can get all the tables and the filegroups these tables are mapped to. So feel free to use the same.

SELECT OBJECT_NAME (a.object_id) AS ObjectName,
       (SELECT name
        FROM   sys.data_spaces
        WHERE  data_space_id = a.data_space_id) AS FileGroupName
FROM   sys.indexes a
WHERE  index_id <= 1 

I just ran this on my AdventureWorks2012 DB to get the output as below.


Thanks for reading so far. Hope this query will help you plan placing your tables in the right filegroups.

Continue reading...


SQL Server Script: Finding past connectivity issues October 25th, 2012

Vinod Kumar

In my day job I meet a lot of DBA’s who try to send some really interesting requirements. Recently, one of these proactive customer wanted to know if there were any past errors that they can query from the DMVs about connectivity issues. This was interesting and I had sent them the below script for use.

CONVERT (VARCHAR(30), Getdate(), 121)
AS [Run_Time],
Dateadd (ms, ( ORB.[timestamp]  OSI.ms_ticks ), Getdate())
AS Time_Stamp,
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/RecordType)[1]’, ‘varchar(50)’) AS [Action],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/RecordSource)[1]’, ‘varchar(50)’) AS [Source],
Cast(record AS XML).value(‘(//Record/ConnectivityTraceRecord/Spid)[1]’,‘int’) AS [SPID],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/RemoteHost)[1]’, ‘varchar(100)’) AS [RemoteHost],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/RemotePort)[1]’, ‘varchar(25)’) AS [RemotePort],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/LocalPort)[1]’, ‘varchar(25)’) AS [LocalPort],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]’, ‘int’) AS [isPhysicalConnectionIsKilled],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]’, ‘int’) AS [isDisconnectDueToReadError],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]’, ‘int’) AS [isNetworkErrorFound],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]’, ‘int’) AS [isErrorBeforeLogin],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]’, ‘int’) AS [isSessionKilled],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]’, ‘int’) AS [isNormalDisconnect],
Cast(record AS
XML).value(‘(//Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout)[1]’, ‘int’) AS [isNormalLogout],
Cast(record AS XML).value(‘(//Record/@id)[1]’, ‘bigint’)
AS [Record_Id],
Cast(record AS XML).value(‘(//Record/@type)[1]’, ‘varchar(30)’) AS [Type],
Cast(record AS XML).value(‘(//Record/@time)[1]’, ‘bigint’)
AS [Record_Time]
FROM   sys.dm_os_ring_buffers ORB
CROSS JOIN sys.dm_os_sys_info OSI
AND Cast(record AS XML).value (‘(//Record/ConnectivityTraceRecord/Spid)[1]’, ‘int’) <> 0
ORDER  BY ORB.timestamp ASC 

There are so many interesting hidden information that is available inside a DMV that we miss to visualize. If you want, we can also look into why the connection got lost or why it didn’t go through.


If you want to be a proactive DBA then using the DMV can be tricky as the values can be lost in a restart of the instance. But using other mechanisms will also help –

  1. Use default logging of Invalid Logins.
  2. Use SQL Server Auditing to track Failed Logins

Being proactive DBA is a challenge and we need to use every trick inside SQL Server to achieve our requirements. Hope you found the script useful.

Continue reading...


SQL Server 2012: Offline Documentation October 24th, 2012

Vinod Kumar

With the installation of SQL Server 2012, a lot of people ask how to get the documentation offline. Every time we fire the F1 for help it tries to connect online for content. This is not always useful and people want to have an offline version when they are on the move and getting online is not an option. Just initialize the Help library Manager from the SSMS Tools –> Help –> Manage Help Settings. Select the Install from Online.


Select SQL Server 2012 and Add the Books Online + Developer reference and Select Update.


Another way to get the same as an offline version is to download it from the Download Center. “Product Documentation for Microsoft SQL Server 2012 for firewall and proxy restricted environments”. Please keep checking the Download Center for the latest update for the documentation.

Continue reading...