Thursday, December 30, 2010

Table Valued Parameters

There is one new feature in SQL Server 2008 that I am pretty excited about, that is the ability to pass table valued parameters onto stored procedures or functions. One scenario where this could be handy is when we have to insert multiple rows of data. For example, let say you have an application where you allow users to input multiple new employee information at once and you need to insert all those information into the employee table. Prior to SQL Server 2008, we probably would loop through each row (employee information) and call the stored procedure that insert the information to the employee table one row at the time. Alternatively we could parse those employees information into XML and pass the XML into a stored procedure where it will then process the XML and insert the information to the employee table. But now with SQL Server 2008, we could pass the new employees information as a table valued parameters into a stored procedure, which can make things simpler.

You might ask why not use bulk insert or bcp. According to Microsoft, in many cases, if the number of rows is less than 1,000, using table valued parameters actually might be better performance wise.

How to Use Table Valued Parameters

It is pretty simple actually, You just need to do the following:

  1. Create a user defined table type. This actually defined the structure for the table valued parameters.
  2. Create the stored procedure or user defined function that would utilized the user defined table type (created on Step 1) as its parameter.
  3. You can now pass table valued parameters to the stored procedure or user defined functions created on Step 2.


Let say that I have an table called EmpInfo. I created the table using the following SQL Script:

CREATE TABLE dbo.EmpInfo (
    EmpFirstName VARCHAR(25),
    EmpLastName VARCHAR(25),
    EmpTitle VARCHAR(25),
    CreatedDate    DATETIME);

Then I created a user defined table type called EmpInfoTableType using the following SQL Script:

CREATE TYPE dbo.EmpInfoTableType AS TABLE (
    EmpFirstName VARCHAR(25),
    EmpLastName VARCHAR(25),
    EmpTitle VARCHAR(25));

I now can create a stored procedure that utilized EmpInfoTableType user defined table type as one of its input parameter by using the following SQL Script:

CREATE PROCEDURE dbo.InsertEmpInfoBatch (@EmpInfoBatch EmpInfoTableType READONLY)
    INSERT INTO EmpInfo (EmpFirstName, EmpLastName, EmpTitle, CreatedDate)
    SELECT EmpFirstName, EmpLastName, EmpTitle, CURRENT_TIMESTAMP
    FROM @EmpInfoBatch

If you notice there is READONLY keyword when declaring the EmpInfoBatch parameter (table valued parameter). This is required for table valued parameters (or more of a restriction actually), as we can not do any data modification to the table valued parameters within the stored procedure and user defined function.

Now the fun part, we test it:

DECLARE @EmpInfoTable AS EmpInfoTableType; 

VALUES ('John', 'Doe', 'CEO'),
('Jane', 'Smith', 'CFO'),
('Mike', 'Fox', 'CTO')

EXEC dbo.InsertEmpInfoBatch @EmpInfoTable;

If we issue the following SQL Script:

SELECT EmpID, EmpFirstName, EmpLastName, EmpTitle, CreatedDate
FROM dbo.EmpInfo

We should get the following result:


Pretty simple, eh?

Friday, December 17, 2010

Hyper-V Clustering: Guest Connection Problem

Virtualization is pretty neat. It allows you to consolidate several servers that you have into fewer servers, this will allow us to cut down on power consumption (make us more green and save us some money). For server virtualization, Microsoft has Hyper-V. With Windows Server 2008 and Windows Server 2008 R2, we can set up Hyper-V to have failover clustering. This is cool since it would allow us to have a high availability environment associated with the virtualized environment. In addition, since Windows Server 2008 R2, Microsoft has added live migration capability in Hyper-V … super cool. Live migration basically allow us to move virtual system (while it is live/running) from one host node to another host node. You don’t have to shut down the virtual system. From the system administrator perspective, this is very helpful, let say that you need to reboot one of the host node (let say after a Microsoft security patch), you can just live migrate the guest system from that host node to another host node in the cluster, and reboot the host node without causing significant downtime to the guest system.


Recently, we have the opportunity to set up Hyper-V clustering on Dell PowerEdge 710 servers connected to a SAN box using Windows Server 2008 R2 Enterprise Edition. The set up itself went without any problem. We were able to set up a guest (virtual) system and was able to do the live migration back and forth from one host node to another without any problem.


Everything was running smoothly for a few days, then suddenly one day, the guest system just suddenly dropped from the network. We could not even ping the system. The only way to connect to the system is by using the Hyper-V manager. The guest system itself is up and running, but its network connection just died or hang. We waited to see if it would recover by itself (for about a half day), but it would not. The only way to get the network connection back is to restart the guest system. There are not much clue given by the Windows event log on the guest system, and nothing on the host system. The only error that we are seeing in the Windows event log on the guest system was:

The miniport ‘Microsoft Virtual Machine Bus Network Adapter’ hung.


The miniport ‘Microsoft Virtual Machine Bus Network Adapter’ reset.

In term of patches, it seemed that we have downloaded and applied the latest Microsoft patches on both the host and guest systems. We noticed that this problem happened during the time when we copied large sized files (about 500+ GB in total size) from the guest system onto another system in the network. So we tried that again (copying large sized files from the guest system onto another system in the network), and sure enough the network adapter on the guest system would stop working. Strangely, the problem did not appear when we tried copying large sized file from other system in the network onto the guest system. What could it be?


After searching the Microsoft and Dell sites, we found one Microsoft KB article that seems to provide a hotfix to the problem that we have. It is KB article 974909 (The network connection of a running Hyper-V virtual machine is lost under heavy outgoing network traffic on a Windows Server 2008 R2-based computer). You can find it here. Well what do you know … the title of the KB article actually describe the problem that we were having. So here’s what we did:

  • Request the hotfix download from Microsoft
  • Download the hotfix
  • Backup the guest system
  • Shutdown the guest system
  • Apply the hotfix on all of the host nodes of the Hyper-V cluster
  • Restart the Hyper-V host nodes
  • Restart the guest system
  • Re-install the Integration service on the guest system

Once those steps were done, we tested the guest system again by copying large sized file from that guest system onto another system in the network. That hotfix seems to have fixed the problem.

One thing that I must say is that before you attempt to re-install the integration service on the guest system, I would strongly recommend that you backup the guest system, one possible way is by creating a snapshot in Hyper-V manager of that guest system. The reason for this is because, when I was trying to re-install the integration service on one of the guest system, I ran into the following error during the install process:

An error has occurred: One of the update processes returned error code 61658.

To resolve this error, we had to do the following:

  • Restore the guest system from the snapshot
  • While the guest system is off, add the Legacy Network Adapter to the guest system. We did this from the Hyper-V manager.
  • Turn on the guest system
  • Install the Integration service on the guest system
  • Turn off the guest system
  • Removed the Legacy Network Adapter
  • Turn on the guest system

Lots of steps but somehow that seems to do the trick. We got this solution from Michael Phillip blog post. Since then, the guest (virtual) system has been running great, no more connectivity problem.

Some Notes:

  • While researching on this problem, I came across Michael Hanes’ blog in which he listed the hotfixes needed for Windows Server 2008 R2. You might want to check his blog out. It can be found here.
  • You might want to also consider calling the Microsoft Support. They might charge you, but they are usually good at troubleshooting this type of problem.

Monday, December 6, 2010

Troubleshooting SQL Server Performance Problem

Probably one of the most inquiries that a Database Administrator get from users are regarding SQL Server performance. As data, number of users, and activities grow, SQL Server performance *might* take a hit. There are many reasons for SQL Server performance problem, such as missing indexes, out of sync query optimization statistics, lack of CPU power or memory, and others. Microsoft actually has come out with great papers on how to troubleshoot SQL Server performance problem. They provide some guidance on how to diagnose the SQL Server performance problem. The white papers are using tools that are readily available such as SQL Server Profiler, System Monitor and DMVs (so no third party is needed).

The white papers:

  • For SQL Server 2005, you can find it here.
  • For SQL Server 2008, you can find it here.

Thursday, December 2, 2010

SQL Table Space Utilization

Working as a database administrator, more often than not, I want to know which are my largest tables. This can be handy to help us to identify tables that might be good candidate for table partitioning. We could also use the information to help determine if data in a particular table might be suitable to be archived.

One stored procedure that can be use to get the detail about space usage of a particular table in SQL Server database is:


Detailed information about this stored procedure can be found here.

How to Use the Stored Procedure

Let say that we want to find the space usage for Person.Person table in AdventureWorks database. You can get that information by running the following query:

USE [AdventureWorks];

EXEC sp_spaceused 'Person.Person';

Ok, that's great, but that only return space usage information for one table. What if we want to get space usage information for all tables within one particular database (let say AdventureWorks). To do that, you can run the following query:

USE [AdventureWorks];

CREATE TABLE #TableStats (
Name NVARCHAR(128),
Rows CHAR(11),
Reserved VARCHAR(18),
Data VARCHAR(18),
Index_Size VARCHAR(18),
Unused VARCHAR(18));

EXEC sp_MSForEachTable "INSERT INTO #TableStats EXEC sp_spaceused '?'";

FROM #TableStats;

DROP TABLE #TableStats;

In the query script above, basically we created a temporary table and then run the sp_spaceused stored procedure against each tables within the AdventureWorks database using the sp_MSForEachTable stored procedure. Once that's done, we just query the temporary table and drop the temporary table.

Now, let say that I want to find the top 10 largest table in AdventureWorks database in term of data size. The Data column returned by the sp_spaceused is using VARCHAR data type. So one way to sort it correctly, we can use the following query (basically in the ORDER clause we just need to convert the VARCHAR into INT.):

USE [AdventureWorks];

CREATE TABLE #TableStats (
Name NVARCHAR(128),
Rows CHAR(11),
Reserved VARCHAR(18),
Data VARCHAR(18),
Index_Size VARCHAR(18),
Unused VARCHAR(18));

EXEC sp_MSForEachTable "INSERT INTO #TableStats EXEC sp_spaceused '?'";

FROM #TableStats

DROP TABLE #TableStats;

Some Notes:

  • As noted in the SQL Server Book on line, if you use sp_spaceused without passing any parameter, you will get the space utilization of the database that you are currently connected to.
  • There is a second optional parameter that you can passed onto sp_spaceused, and that's to specify whether or not to update usage statistics (using DBCC UPDATEUSAGE). The parameter accept 'TRUE' or 'FALSE' value, with 'FALSE' set as the default.