I/O Issues on a newly deployed SQL Server cluster attached to a SAN [Windows 2008 R2]

We executed the migration of stand-alone database servers to an active/active cluster. This was for our enterprise-wide reporting solution which includes a scaled-out deployment of Reporting Services implementing F5 BIGIP as a load balancer.

I/O challenges:

We migrated over the Distributor Instance and needed to re-snapshot several tables; planned for a 36 hour downtime. Small tables were copied over quickly but we were struggling with large tables. We had IO_COMPLETION and ASYNC_NETWORK_IO as top waiters during the bulk copy. In this scenario, data was brought over to the distributor instance and then bulk copied onto the subscriber. Both instances reside on the same node, each one with their properly assigned storage on Dell Equallogic SAN.

The first thing we attempted was to correct the link negotiation from auto to 1000/full. Changes were made and we experienced no change. All instances bounced immediately after the change, a surprise as we never thought that changing the configuration for 1 NIC will affect the database instances. It was neither the heartbeat nor the iSCSI NICs the ones affected.

We continued with the bulk copy until we found that a 110 GB table was taking extremely long to complete. I decided to exclude the largest tables from replication and approach another plan for them (backup, restore, batch insert and partition switch-in – I might write about this in another blog post).

We continued with the remaining objects and declared success on a Sunday at 10 PM. Everything looked good except for four of the very large tables that were not included due to the time required to copy them over.

IO Pending Requests ms.

IO Pending Requests ms.

Re-Snapshot and subsequent Rollback:

Something interesting happened. A bulk copy was rolling back on Monday morning which took around 8 hours to complete. The log file grew to 125 GB and hit the storage limit. We were not sure why as the table was bulk copied successfully a night before. We removed the table from replication and decided to continue troubleshooting the I/O problems.

I/O resolution:

I was able to reproduce the I/O symptoms at the OS level by copying files larger than 4.4 GB. I would see rates of 15 MB/s instead of the 500 MB/s I was seeing with smaller files. We failed over expecting to isolate the issue to 1 node, but the issue reoccurred. We decided to upgrade the NIC drivers to the latest published a few weeks ago, but no improvements were experienced. The SAN admin then found an article about disabling Windows Scaling heuristics and decided to give it a try.


Registry was updated, nodes were bounced (1 at a time) and tests were re-ran. No more I/O contention and replication was just flowing with a a few ms. of IO pending requests instead of 200 ~ 400 we were hitting before.

from sys.dm_io_virtual_file_stats(NULL, NULL) AS t1
INNER JOIN sys.dm_io_pending_io_requests AS t2 ON (t1.file_handle = t2.io_handle)

Re-Snapshot and subsequent Rollback, again:

I/O was fixed, so now we needed to re-snapshot the missing table that rolled back. We did on a Tuesday night, everything went well and then on Wednesday morning a rollback was pending. We again hit the storage limit of 125 GB for log. I asked the SAN admin to extend it to 200 GB, asked one of the DBAs to exclude the table for replication and let the undelivered commands flow to the subscriber.

Undelivered Transactions:

The rollback took another 6 hours to complete and at that point I was just waiting for replication to catch up. 30 minutes later, nothing happened. We continued waiting 30 more minutes and nothing happened either. Jobs were bounced, SQL Server Agent restarted on the Disitributor and Subscriber and nothing happened either. I asked a question on Twitter and was suggested to call Microsoft. Before I did, I called a friend of mine, Anthony Sammartino (Twitter | Blog), and left a message. While calling Microsoft, Anthony called me back and explained that there might be some errors with DMLs attempted on the subscriber. He discussed it with one of the DBAs and we were able to get replication going by changing the distributor agent profile. We had over 5 million undelivered commands pending.

Stabilization of the Environment:

By 8 PM we were up to date with all tables except the bulk copy that rolled back. I decided to stop making changes to the environment for the long Thanksgiving weekend and ensured that the environment was stable enough to continue working on it on the next Monday.

Re-Snapshot and subsequent Rollback resolution:

We figured that a filter was later giving an exception letting the publisher reinitialize replication for the object issuing a re-snapshot. It had been performing bulk copies and then rolling back several times during the night. The filter value was defined as column NOT LIKE ‘%value%’.

We decided to exclude the filter and re-snapshot the table today. 15 minutes later, 60 million rows were inserted into the subscriber. No issues so far.

Things are now looking much better.

I am my own brand [Un-SQL]

I am done with all my meetings, prepared for tomorrows migration and deleted all the fluff from my inbox. Now I have time to add a post for the Un-SQL Friday, an idea by the @MidnightDBA, yes the one that is rough to vendors that do not know anything about databases and DBAs wanna-be that are just incompetents (won’t argue with that). The original post is located here.

Anyways, I am my own brand. Does it matter? Maybe. That is why I created ozamora.com. That is my brand. But, do I want to be known? Not necessarily want but would like to be seen as someone that has helped building solutions or solved a problem, because that is pretty much what I do on a daily basis. I also like sharing what I have learned from others.

DJ Zamo

Now I wish I had a chance to post most interesting stuff I do so I can go back to it and say “oh, this is how I did it” instead of trying to remember. It has happened to me several times and no, searching emails not always answers my questions.

But why ozamora? Just to keep it simple. I have used ZamoTrance, Zamo, OZ, DJ Zamo, and even some have called me Zamorita, Zamito, and the Wizard of OZ. I have not registered them yet, as I am waiting for a GoDaddy Deal ha!

Now Twitter. Yes I am late to the game and started with @Oscar_Zamora, then changed to @__ZT and finally settled with @ZamoraO? Why not @ozamora? Well you guessed it. There is someone that has registered it a long time ago and never has tweeted. Twitter needs to phase out all the stale usernames…

I did register zamorao.com, so I guess I am covering my brand.

Has it helped? Certainly. I met many people at the SQL Pass Summit this year, and they recognized me just because of my Twitter name.

I will continue with ozamora.com and pack it with information that interests me and might be useful to the community. My goal is to excel at what I do and transfer knowledge to my team. If I do not learn something new today, I feel like I just wasted it. I make sure it does not happen.

That’s it. Now follow me.

Denali, the next release [SQL Server]

After a week at the Pass Summit United 2010 and after catching up at work with several tasks, I am now able to update my blog with some exciting information. SQL Server codename “Denali” CTP1 was released formally on 11/09/2010 (version 11.0). I was able to obtain a DVD copy while at the summit, and now it can be downloaded too.

The key takeaways:

  1. AlwaysON. This is a full blown HADR solution that allows multiple asynchronous and synchronous
    SQL Server Codename "Denali" Community Technology Preview 1

    SQL Server "Denali" CTP1

    mirrors of a database with transparent fail over option to a local and remote datacenter. This is a feature I am really looking forward to.

  2. VeritPaq embedded in the relational engine. The SQL Server engineers ported the same code used for PowerPivot into the RDBMS. The feature will allow column-level ultra-high compression of repetitive data which will dramatically increase query efficiency by ten times at least.
  3. BISM with DAX support. Business Intelligence Semantic Model in Analysis Services which will allow retrieval of data using DAX queries; the same ones used in PowerPivot.
  4. Sequencer generator. It is never too late for a feature like this.
  5. Enhanced Windowed Functions.
  6. File Table Support. I have been waiting this feature since the Oracle 9i days. It will allow us to manage files directly within SQL Server.

There are certainly more enhancements which will continue to pop-up as the product matures.

Now, it is a challenge for us to learn and prepare for the new features after just upgrading to 2008 R2. Well, Microsoft is doing a pretty good job by keeping us busy and I am OK with that. They were kindly enough to publish Books Online for Denali though.

Split a Partition and Expect Locks [SQL Server]

Not in every scenario. But as a rule of thumb, test before you perform a split operation in a production environment.

Every year around this time we plan, test and deploy partitions for tables that will hold historical data from the next year. We typically create one partition per month and in one case a partition per day (which has its own sliding window schedule every month).

This has been a very busy year, and between the migrations and consolidations a historical table did not have partitions added for October and November. I took the decision to keep the data as-is and just add a new partitions starting on 11/12; one for each day. This table holds around 30 million rows per day.

Now, we usually partition tables using RIGHT functions in order to have the last boundary empty and able to split it beforehand without any locking. The table I mention in the above paragraph is using a LEFT partition function and all the current data is sitting in the rightmost partition.

Issuing a Split partition of the rightmost data will let the engine scan and move the data for the current partition. This is something that can be fine in systems with a few million rows, but not with a partition holding 500+ million rows. We tried on a testing environment and as expected it locked the table for a very long time. DML operations were not able to be performed against it until the split succeeded.

-- Using new filegroup
ALTER PARTITION SCHEME [ps_Left_day_sliding] NEXT USED [OEN_Lists_2010_11];
ALTER PARTITION FUNCTION [pf_Left_day_sliding] () SPLIT RANGE ('2010-11-12 00:00:00.000');

This is the table which has a partition per day.

We are not going to purse that route. We will create a brand new table with proper RIGHT partitions and swap it with the current one. We have that liberty as the data is purely historical, and DBAs retrieve from it when required. They will know where to look at after reading at the documentation they write after performing such a change.

We went ahead and also tested with RIGHT just to double make sure. It worked just fine.

CREATE PARTITION FUNCTION [pf_Left_day_sliding1](datetime) AS RANGE RIGHT FOR VALUES (N'2010-09-03T00:00:00.000', N'2010-09-04T00:00:00.000', N'2010-09-05T00:00:00.000', N'2010-11-12T00:00:00.000');

CREATE PARTITION SCHEME [ps_Left_day_sliding1] AS PARTITION [pf_Left_day_sliding1] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [OEN_Lists_2010_11]) ;
CREATE TABLE [dbo].[batchresults_buffer1](
[column1] [bigint] NOT NULL,
[column2] [datetime] NOT NULL,
[column3] [int] NOT NULL,
[column4] [int] NOT NULL,
[column5] [int] NOT NULL,
[column6] [varchar](200) NOT NULL,
) ON [ps_Left_day_sliding1]([column2]);
CREATE UNIQUE CLUSTERED INDEX [PK__batchresults_buf__2077C861] ON [dbo].[batchresults_buffer1]
[column1] ASC,
[column2] ASC
INSERT INTO [batchresults_buffer1] (column1, column2, column3, column4, column5, column6)
column1, column2, column3, column4, column5, column6
FROM batchresults_buffer; -- This table contains sample data, 50 million rows.
ALTER PARTITION SCHEME [ps_Left_day_sliding1] NEXT USED [OEN_Lists_2010_11];
ALTER PARTITION FUNCTION [pf_Left_day_sliding1] () SPLIT RANGE ('2010-11-12 00:00:00.000');

I do not see that need for us to use LEFT. All future deployment will use RIGHT instead.

Wi-Fi Hotspot [HTC G2]

After sending the OTA upgrades, TMobile enabled 3 nice features: WiFi Calling, USB Tethering and WiFi HotSpot. The HTC G2 can be used as a wireless router and be able to accept connections from any device that connects to Open and WPA2-PSK Wi-Fi networks.

Portable WiFi Hotspot

Portable WiFi Hotspot

Portable WiFi Hotspot Configuration

Portable WiFi Hotspot Configuration

Just follow the steps:

  1. On the Android phone, press Home, press Menu, and touch Settings to open the Settings application.
  2. Touch Wireless & networks > Tethering & portable hotspot.
  3. Check Portable Wi-Fi hotspot.
  4. Check Portable Wi-Fi hotspot Settings
  5. Configure the Netword SSID, Security and Save
  6. Your phone is now a Wireless Router using the Available HSPA+ network.

It seems that T-Mobile isn’t charging extra for this feature yet, but will throttle if bandwidth usage goes over 5 GB within a month.