SQL Server with Mr. Denny


October 21, 2015  4:00 PM

The MERGE and large data sets

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

Recently I was doing some work at a customer and they were having some performance problems with their ETL into their data warehouse (see I do know at least some BI). One of the steps which was taking a long time was a MERGE statement which was updating existing rows and inserting new rows.

The target table in this case has several hundred million rows, so it’s a pretty good sized table.

It was a rather simple MERGE statement so we tried changing it from MERGE to separate UPDATE and INSERT commands because I remembered someone complaining at a conference about how performance went down when they started using MERGE.

So we changed the stored procedure and let the new version run the next morning. And boy was it a good decision. We reduced the run times of that step by about 50%. We changed another few MERGE statements which use large tables and got other great improvements, in one case reducing the run time of the job step by 75%. A heck of an improvement.

While the MERGE statement was easier to manage, the goal is to make the system run fast. We did that in this case, very nicely.

So the lesson here is that if you have large data sets that you are working with, MERGE isn’t for you.

Denny

October 16, 2015  8:21 PM

Recommended reading from mrdenny for October 16, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

PASS Elections: Fuzzy Math

Getting data into Azure Blob Storage

Idera Intends to Buy Embarcadero Technologies

Repairing a replication subscriber

Is a heap larger than a clustered table?

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


October 12, 2015  2:00 PM

Your PASS Speaker Idol 2015 Top 12 Are…

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

The time has come. It’s time to announce who the 12 people who will be competing for the glory that comes with beating down 11 of your #sqlfamily members in public. These 12 fine folks will be competing for the grand prize … a speaking spot at the PASS Summit 2016, and an awesome blog graphic that I’ll be showing publically for the first time during the speaker idol sessions at PASS this year.

The 12 people who are competing (in no specific order) are:

  • Luciano Caixeta Moreira
  • Rob Volk
  • Todd Kleinhans
  • Ronald Dameron
  • Amy Herold
  • William Durkin
  • Ginger Grant
  • Bill Wolf
  • Theresa Iserman
  • Ed Watson
  • Wes Springob
  • David Maxwell

These twelve poor people will be competing and being judged at the SQL PASS summit in three groups, one on each day. On Friday afternoon the winner from each of the three sessions plus one runner up (selected by the judges) will compete for the prize. Our 12 contestants have been randomly placed into three groups which you’ll find below.

On Wednesday (3:15pm – 4:30pm) we’ll have:

  • Todd Kleinhans
  • William Durkin
  • Ginger Grant
  • Ed Watson

On Thursday (4:45pm – 6:00pm) we’ll have:

  • Rob Volk
  • Amy Herold
  • Bill Wolf
  • Wes Springob

On Friday (2:00pm – 3:15pm) we’ll have:

  • Luciano Caixeta Moreira
  • Ronald Dameron
  • Theresa Iserman
  • David Maxwell

Then the finals will be Friday at (3:30pm – 4:45pm).

I look forward to seeing everyone at the Speaker Idol sessions while we cheer on our 12 great competitors. Good luck to all 12 competitors.

Denny


October 8, 2015  7:29 PM

Your PASS Speaker Idol 2015 Judges

Denny Cherry Denny Cherry Profile: Denny Cherry

2015_speakeridol

I’m pleased to announce the judges for this PASS Speaker Idol 2015 are:

  • Mark Simms
  • Joey D’Antoni
  • Allan Hirt
  • André Kamman
  • Hope Foley

These judges will be joined by Karen Lopez at the final session so that we have a judge who comes with a fresh set of eyes.

Stay tuned for who our contestants are…

Denny


October 7, 2015  4:00 PM

Basic Database Mirroring Monitoring

Denny Cherry Denny Cherry Profile: Denny Cherry
Database mirroring, Disaster Recovery, DR, High Availability, Monitoring, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014

If you have database mirroring setup and running, you’ll notice that it has a lack of built in monitoring. And if you have a lot of databases having monitoring for each potential state that each database can be in can flood you with email if the network between sites goes down and comes back up. Especially if it happens a few times in a minute (the record that I’ve gotten using this sort of configuration is about 700 emails in one night of network maintenance.

So I’ve put together a slightly more simplified database mirroring monitor. It’ll still tell you if the network goes down and comes back up, but it’ll do so if a slightly nicer way. You’ll want to change the @operator_name value to a valid operator on your system before you run this, otherwise you’ll get an error as the operator doesn’t exist. I’ve set the delay between retries at 5 minutes, so even if the network is going nuts you’ll get at most 12 emails from each alert per hour. Depending on how stable your network is you may want to adjust this as needed.

Never fear, this works on SQL Server 2005 – SQL Server 2016 (CTP 2 at least).

USE [msdb]
GO
DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Mirror Connection Lost'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 6',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM Perf: Oldest Unsent Transaction Threshold'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=32040,
@severity=0,
@enabled=1,
@delay_between_responses=1800,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Mirroring Suspended'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 9',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

USE [msdb]
GO

DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Principal Connection Lost'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 5',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO

Happy monitoring,
Denny


October 1, 2015  1:08 PM

SQL PASS Summit 2015 Discount Code

Denny Cherry Denny Cherry Profile: Denny Cherry
discount coupons, IT conferences, IT conferences and events, SQL Server

Are you looking to register for the SQL PASS Summit and you haven’t already done so?  Would you like to save $150 off the current registration price?  Would you like to save that $150 with no strings attached?

Have I got a deal for you.  Simply use the discount code EXSP150 when you register for the PASS Summit and you’ll save $150.

That’s it.

Denny


September 30, 2015  6:00 PM

Upgrading a SQL FCI to Premium Storage in Azure Without Downtime

Denny Cherry Denny Cherry Profile: Denny Cherry
Azure, Cluster computing, SQL Server, Windows Azure, Windows Cluster

Recently I was asked by a client to upgrade their SQL Server Failover Cluster from standard storage to Azure premium storage with as little downtime as possible. Due to the fact that the SQL Server instance was clustered already this was actually a pretty straight forward process.

The first step was to figure out which node of the cluster was running as the active node, so we could start with the passive node. The next step was the tell cluster to not allow failovers of the SQL Cluster. Next we opened the SIOS Data Keeper Cluster Edition GUI and break the mirror for the disks that we are going to upgrade. Then I logged into the Azure portal and converted the VM from a G2 into a GS2 so that premium disks could be attached. After the VM restarted (don’t forget, this is the passive node so there’s no outage for the restart) the disks are removed from the VM and the new disks are added. The new disks were added via PowerShell like this:

get-AzureVM -name ServerName | add-AzureDataDisk -CreateNew -DiskSizeInGb 1023 -DiskLabel ServerName-T http://Something.blob.core.windows.net/vhds/ServerName-t.vhd” | update-AzureVM

After the disks are all added, they are formatted and given the correct drive letters.

Next SIOS Data Keeper Cluster Edition is told to restart the mirror. This forces it to do a full sync as there’s no data on the old drives. This takes forever as we are limited to reading data from the disk at the speed of the old standard disks (500 IOPs). Once it’s done (in this case there was about 200 Gigs of data to replicate across three disks) the cluster can be failed over (this is the only outage in the process).

We can now upgrade the second VM to support premium storage, then change out the disks and restart the replication again. It’s a long process but it works, and there’s just a single outage to the process.

Denny


September 25, 2015  5:00 PM

Recommended reading from mrdenny for September 25, 2015

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Using Microsoft DiskSpd to Test Your Storage Subsystem

DBTA – StretchDB, a Cool New Feature in vNext SQL Server

The Case Of The Auto-Truncating Table

Updating Your Data Strategy Requires a Shift in Thinking

Study Finds that CIOs Vastly Underestimate Shadow IT

 

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


September 17, 2015  10:59 PM

PASS Summit 2015 1st Timers Webcast Recording

Denny Cherry Denny Cherry Profile: Denny Cherry
IT conferences and events, SQL Server

This year’s PASS Summit 1st Timers webcast recording didn’t happen as planned. And sadly my voice is currently gone so I’m not able to record a new one in the few days that I’m at home. So because of that I’m reposting a link to last years video (which is mostly the same as this years) and you’ll find the updated information below. So without further delay here is your PASS Summit 2015 2014 1st Timers video. Last years video was kindly sponsored by SIOS, so they are sponsoring this years video as well. 🙂

The differences between this year and past year are:

  • This is 2015 not 2014
  • My SQL Karaoke party is on Tuesday October 27th, 2015
  • The URL to register for the party is http://www.dcac.co/go/2015-party, and this years party is being sponsored by SIOS just like last year
  • The URL to register for Argenis Without Borders is here
  • The URL to register for PASS Summit 2015 Speaker Idol can be found here
  • The convention center is a little bigger in 2015.
    • PASS is expanding into the convention space called the TCC (The Conference Center).  You can access the TCC by talking across Pike Street or by walking through the lunch hall (4E & 4F).
    • The registration area will be back where is used to be in the atrium under the escalator to the 6th floor.  I’m pretty sure I pointed it out in the 2014 recording as where it was in prior years.

Sorry for having to use the 2014 recording, but it is what it is.  Thank you to SIOS for coming through so quickly with the recording for last year’s webcast.

Denny


September 16, 2015  4:00 PM

Want to speak at the PASS Summit, but haven’t been selected?

Denny Cherry Denny Cherry Profile: Denny Cherry
SQL Server

The Speaker Idol may be your answer to how to get to the PASS Summit as a speaker. The Speaker Idol competition, in it’s second year now with PASS, will be held throughout the conference with four different rounds. During the competition people who haven’t spoken at the PASS Summit will be giving 5 minute presentations to a panel of judges and the audience. The competitors are competing for the grand prize, a speaking slot at the PASS Summit 2016.

What do you need to do to get into speaker idol?  Step 1 is to fill out the application form.  It’s got a few basic questions about where you’ve presented before and how to get in contact with you.  That’s pretty much it.  From the people that submit for the Speaker Idol we’ll select 12 people to be in the competition.

The full rules for being eligible can be found here.  With the rules as they are written probably 95% of PASS attendees are eligible as are most people who have spoken at an event (other than the PASS Summit) before.

The Speaker Idol is a great way for those speakers who aren’t as well known in the community to get in front of a national audience, which will include members of the content committee (the folks who select the sessions for next year).

So if you’ve ever wanted to speak at the PASS Summit, this is your chance.  Get signed up, and get ready to present.  If you don’t register you can’t earn yourself a speaking spot at the PASS Summit 2016.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: