SQL Server with Mr. Denny


February 19, 2014  2:00 PM

Old vSphere, Large VMs, and Some Really “Funky” Problems

Denny Cherry Denny Cherry Profile: Denny Cherry
RAM Chips

Courtesy of http://www.flickr.com/photos/chrissinjo/5368405044/in/

Recently a client was having some IO performance problems so they decided that the easiest solution would be to through an SSD under the log files on the VM. Easy and cheap solution. The server in question has about 50 databases on it. In order to speed up the transaction log file transfer I decided to run multiple file copies at once. However doing so pushed the CPU on the server to 100%, the network would randomly crap out causing my RDP connection to drop and the file transfer was taking forever.

All the settings in both vSphere, Windows, the Array we were copying from, etc. all looked fine. This VM happened to be running on vSphere 4.1 Update 1 and couldn’t be upgraded because the vCenter server hadn’t been upgraded yet. The guest had 8 vCPUs and 96 Gigs of RAM. The hardware had two physical sockets which had 8 physical cores each (hyperthreading is turned on) and 128 Gigs of RAM on the host (this VM was the only VM on the host). According to the vSphere 4.1 Best Practices a VM will by default be run with all the CPUs within a single NUMA node. This means that this VM has it’s CPUs in a single NUMA node and some of the memory is in each NUMA node.

I wouldn’t expect this to be a problem, but apparently it was. Each of the file copies was running a CPU core up to 100%. When I’m running 10 file copies at once that’s over 100% of the CPU needed.

We reduced the amount of RAM available to the VM from 96 Gigs of RAM to 60 Gigs of RAM. Suddenly the guest was able to copy the files very fast, the file copies took next to no CPU power, and the network problems went away.

Why vSphere and Windows was having these problems I’ve got no idea, but this did fix the problem.

Denny

February 14, 2014  7:48 PM

Recommended reading from mrdenny for February 14, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry

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.

This weeks SQL Server person to follow on Twitter is: PASS_RM_Canada also known as PASS RM Canada

Hopefully you find these articles as useful as I did.

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

Denny


February 12, 2014  2:00 PM

How Much Is That Query Costing?

Denny Cherry Denny Cherry Profile: Denny Cherry

When we think about the cost of the query we typically think about the cost which SQL Server assigns to execute the query. However when we look at performance tuning queries there is another cost that we need to think about. This additional cost is the cost to the company while the query is running. As we start looking into these costs we can start to see just how important it is to performance tune every query within our applications.

Say we have a query which takes 0.5 seconds to run. We might think that is pretty good and we don’t need to do any more performance tuning on it. Now lets assume that this query is the customer lookup query that every one of our customer service agents uses every time that a customer calls into the call center. As a large company we have on average 1500 customer service reps on the phone at any time (working three shifts), and they each take 10-15 calls per hour. That query is now being run 18000 times per hour (give or take). Assuming that our customer service reps make $10 per hour it is costing us ~9000 seconds per shift or ~7.5 hours of staff time per day. That’s a cost of $75 per day just to run this specific query. Multiply this out for a year and that’s ~$27,375 per year, just to watch the query run. By tuning the query so that it runs within 0.1 seconds we can cut these costs down to ~$5,475 per year. That is a decent amount of financial savings to the company for just one query.

As we start looking at the rest of the queries in the application and we start adding up the costs for those queries suddenly we can easily be looking at $100,000 a year or more is employee costs just to use the application.

Wouldn’t it be nice to be able to tell management that you’ve just saved the company $100,000 a year just by adding a couple of new queries to the database?

This is why we need to do performance tuning.

Denny


February 8, 2014  1:10 AM

Recommended reading from mrdenny for February 07, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry

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.

This weeks SQL Server person to follow on Twitter is: grengifo2011 also known as Gonzalo Rengifo

Hopefully you find these articles as useful as I did.

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

Denny


February 5, 2014  2:00 PM

I Want This Project Done Properly, Done Quickly, and Done Cheap

Denny Cherry Denny Cherry Profile: Denny Cherry

This is the standard requirement that I get from a lot of clients. Sadly those three requests aren’t going to go well together. In IT (and really in anything) you have to pick two:

  • Done Properly
  • Done Fast
  • Done Cheap

If you want something done properly and done fast it isn’t going to be cheap. If you want it done properly and cheaply it won’t be done quickly. If you want it done fast and cheap then odds are it won’t be done properly.

Doing good work takes money to get the right resources to get the project done correctly. Getting the project done quickly requires that you get someone who has done this sort of thing before. When it comes to large complex systems or with cutting edge platforms your pool of potential people to work on the project gets very small very quickly. The smaller the pool of people who can work on something the more those people are going to charge to get the work done. The reason for this is simple, with skill and practice comes higher rates.

When working on projects that require that you bring in outside help from consultants to need to pick the two of those three things above that you want. If you’ve gone through this thought exercise and you still want all three you need to rethink the thought exercise. And to make the whole process worse you’ve only really got two of those items to choose between. Do you want the project done quickly, or do you want it done cheaply. The reason for this is that having the project done incorrectly isn’t an option.

When I say quickly I don’t mean actually quickly based on a calendar. I mean on what ever “short” time schedule the project manager has some up with. If the project takes 6 months and it needs to be done in exactly 6 months no matter what that means it’s getting done quickly. If however the project takes 6 months, but you don’t need it done for the next 18 months you can probably get people who are a little less expensive to work on it because there is extra time built in to the schedule so things can run a little slower.

When putting those projects together keep these things in mind as talking to your consultants so that you are prepared for the answers that they give you.

Denny


February 1, 2014  6:08 AM

Recommended reading from mrdenny for January 31, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry

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.

This weeks SQL Server person to follow on Twitter is: SQLBalls also known as Bradley Ball

Hopefully you find these articles as useful as I did.

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

Denny


January 29, 2014  10:42 PM

eForensics Magazine SQL Server Issue

Denny Cherry Denny Cherry Profile: Denny Cherry

The digital magazine “eForensics Magazine” has put together their 1st issue talking specifically about SQL Server security.  There are articles written by a bunch of SQL Server experts including the two articles which I have in the issue.  There’s a free teaser of the issue which you can download as well (it’s down at the bottom of the page) before you buy.

Denny


January 29, 2014  2:00 PM

Log Shipping Databases and Permissions Problems

Denny Cherry Denny Cherry Profile: Denny Cherry

Recently I was doing log shipping for a client in preparation to move their databases from one data center to another, when I was asked to change the drive that one of the target databases was being restored to.

No problem, I’ll just use ALTER DATABASE … MODIFY FILE (name=’xxx’, filename=’yyy’) to change the files, stop SQL, move the database files, and restart no problem.

Well apparently when doing this I managed to not reset the permissions correctly but only on the ndf file. Because SQL was able to access the MDF the database came up on NORECOVERY waiting for more logs to be restored. But when the next transaction log file was attempting to be restored, the error message that I got was not all that helpful to the problem at hand.

Msg 4319, Level 16, State 5, Line 1
A previous restore operation was interrupted and did not complete processing on file ‘xxxxxxx’. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

I tried restoring the older log backup and that didn’t help any. So I went and looked at the permissions again, and apparently SQL didn’t have write access to the NDF file so it wasn’t able to roll the logs forward. Thankfully I noticed the problem right away when I manually did the next log restore otherwise this could have been a nightmare to track down given that oh so helpful error message.

Denny


January 24, 2014  2:00 PM

Recommended Reading from mrdenny for January 24th, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
EMC Arrays, EMC VNX, SQL, SQL Server, VMFS, VMworld, VMworld 2013

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.

This weeks SQL Server person to follow on Twitter is: DoktorKermit also known as Kenneth M. Nielsen

Hopefully you find these articles as useful as I did.

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

Denny


January 23, 2014  9:56 PM

SQL PASS BA Conference 2014 Discount Code

Denny Cherry Denny Cherry Profile: Denny Cherry

I’m pleased to say that I’ve been able to get a discount code setup for all my clients and blog post readers to get you $150 off the cost of entry to the SQL PASS BA Conference. To use the code simply register for a new registration and enter the discount code “BABN9H” in the discount code field. And that’s it. You save $150 off the registration fee for the PASS BA Conference.

If you don’t know what the BA Conference is, it is the premiere Business Analytics / Business Intelligence conference and unlike most conferences it is put on by data professionals not venders who have an agenda to push on you.

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: