Analyzing I/O Subsystem Performance at PASS 2014

On November 5, 2014, I had the pleasure of presenting Analyzing I/O Subsystem Performance at the PASS 2014 Summit in Seattle. I was in room 6C, coming on after Conor Cunningham’s presentation on Query Store.

People seemed to like the session, and I was answering questions  for about 30 minutes after the session was over (with some great assistance from my friend and co-worker Jonathan Kehayias).

I just want to thank everyone who attended, and showed so much interest!

You can get a zipped version of my deck and the demos that I used here.

Posted in PASS, Storage Subsystems, Teaching | Leave a comment

Are Electric Cars Practical?

Elon Musk unveiled the new Tesla Model S P85D last Thursday at a big event at Hawthorne Airport in California. In my opinion, this was one of the better public events for Tesla that Elon Musk has done (at least among the ones I have seen).

This prompted some spirited discussion on Twitter on Fraiday, where my friend Brent Ozar made the case that a Tesla Model S does not make sense for him, due to his living situation. Brent is obviously far more familiar with his daily life and priorities than I am, so whether he wants to get a Tesla Model S is really his own business. No argument from me there.

Brent also tried to make the larger case that electric cars simply don’t make sense for most urban dwellers, due to parking and charging concerns. He also trotted out the range-anxiety argument and the cold weather range reduction argument.

I have owned my Tesla Model S P85 for about 18 months, and I have nearly 16,000 miles on it. I also read and contribute to the Tesla Motor Club forums on a regular basis (where every imaginable Tesla subject gets discussed ad nauseum), so I feel somewhat qualified to talk about Tesla vehicles, and electric vehicles in general.

The Tesla Model S is not for everyone. It is rather expensive ($70-120K), although less so when you factor in the tax credits (that some people get very upset about), and the greatly reduced operating and maintenance costs compared to conventional cars in its price range. Still, I would not try to argue that it would be less expensive to own and operate a Model S compared to the average priced internal combustion engine (ICE) vehicle.

Elon Musk made the Tesla Model S to show that electric cars can be better than conventional internal combustion engine cars. I think he has been very successful in turning Tesla into an aspirational brand. For example, younger people (who are typically much less enthused about cars) seem to be much more familiar and excited by Tesla than many older people are. They seem to “get it”.

The Tesla Model S is making the upcoming Tesla Model III (which is supposed to cost about $35K and have a 200 mile range) possible. The Model III will be the mass market car that many more people will be willing and able to buy. By the time the Model III is available in 2016/2017, the public charging infrastructure will be much better than it is today. Even today, the public charging infrastructure is adequate for most people’s needs, but not for everyone’s.

Tesla Superchargers

Tesla is rapidly building out a large Tesla Supercharger network, which currently has 116 stations in North America, 71 in Europe, and 23 in Asia. These SC stations typically have anywhere from four to twelve charging stalls, where you can get a 50% charge in 20 minutes, and a full charge in about an hour. It takes longer to get a full charge, since the charge rate tapers off as the battery has a higher state of charge to protect the battery pack. These SC stations are free for Tesla owners, and they are meant for road trips, although local owners sometimes use them for daily charging. The idea is you drive for 2-4 hours, and then spend 20-30 minutes charging, before continuing your journey.  I recently used Tesla Superchargers to drive from Parker, CO to Kansas City, MO, spending nothing on fuel (compared to spending about $180 for gas in my previous 2012 Acura TL SH-AWD).

Public Charging

If you are going somewhere where there are no Tesla Superchargers, you can use applications like Recargo, PlugShare and RVParky to find places to charge a Tesla or any other EV. A typical 30 AMP, L2 public charging station will give you about 20 miles of range per hour of charging, while a 240V, 50 AMP NEMA 14-50 outlet will give you about  28 miles of range per hour of charging (since it is limited to 40 AMPS).

There are are also higher output chargers available in some locations, that can give you as much as 60 miles of range per hour of charging.

Many businesses, such as hotels, shopping malls, and airports, have some sort of EV charging equipment available, often, for free. The typical Tesla owner does not usually need to do any charging besides at home, unless they are on a road trip, because of their fairly high range.

Home/Work Charging 

Most Tesla owners do virtually all of their charging at home, using an inexpensive 240V, 50 AMP NEMA 14-50 outlet. If you live somewhere with a dedicated garage or parking space, this is pretty easy and inexpensive (typically about $500-$1000).

It is also possible to use a High Power Wall Connector (HPWC) from Tesla, and charge at 80 amps (if you have dual, onboard chargers in your Tesla), and get about 60 miles of range per hour of charging. Tesla has a program where they will work with businesses to install HPWC for their customers.

If you are in Brent’s situation, this can be more complicated and expensive. Despite this, many Tesla owners in high population density places like Hong Kong, Europe, and cities like New York and San Francisco have figured out some way to charge their vehicles in a reasonable manner.

People have done things such as getting their landlords to install charging equipment (at the Tesla owner’s expense), finding a local business or parking facility and getting them to install charging equipment (at the Tesla owner’s expense), etc. Of course, not everyone is willing to take these measures, and it may simply not be possible in some situations.

I predict this situation will improve pretty rapidly in the next several years. As EVs become more common, forward-thinking business will respond to the demand for EV charging facilities. For example, gas stations might want to do something as simple as providing a HPWC or NEMA 14-50 outlet (for an hourly fee). Parking garages could easily do the same thing. Ultimately, I see 3rd party businesses installing Tesla Superchargers in downtown locations.

Range Anxiety

One argument that is typically used against EVs is “range anxiety”, which is the supposed fear of exhausting the charge in your battery pack and becoming stranded on the side of the road somewhere. Avoiding this comes down to using some common sense, just like in a gasoline vehicle. You don’t go somewhere unless you have the range to get back (factoring in some reserve), or you know where you can charge. In a Tesla Model S, you have more than enough range for most people’s daily commute and errand running type activities. A very high percentage of people in the U.S. drive less than 40 miles a day. Unless you need to drive more than 120-130 miles each way (with no place to charge except at home), a Tesla would work just fine for you.

Cold Weather Concerns

Extreme cold weather (below 20-30 degrees Fahrenheit) does affect the range of a Tesla. A Tesla will use electrical power to keep its battery pack warm, which will affect your range. Using your forced air heater and defroster will also affect your range. Last winter, in the Denver area, I noticed about a 10-15% decrease in range due to these two factors.

You can mitigate this by keeping your Tesla plugged in at night (as you would normally do) so that it uses shore power instead of battery power to keep the pack warm. You can also use the seat heaters to let you not use the forced air heating as much.

I would also point out that Tesla has sold a lot of vehicles in Norway and in Canada, which shows that they can handle cold weather.

As I said before, having an electric car is not a practical solution for everyone, for a variety of reasons. On the other hand, much of what you think you know about electric cars may not be 100% accurate. There are a lot of people and vested interests that are very adamantly against electric cars, for many different reasons. They don’t hesitate about misinforming people and spreading false information. 

Posted in Automobiles, Electric Cars, Tesla | Tagged | Leave a comment

New Types of Azure Virtual Machines

Microsoft has announced a new set of VM sizes for Azure Virtual Machines, known as the D-series, that have faster vCPUs (compared to their older A-series Azure VMs) and local, temporary SSDs.

These local SSDs are touted as being good locations to use the new Buffer Pool Extensions (BPE) feature that was added to SQL Server 2014, for both Standard Edition and Enterprise Edition. The size of this local SSD ranges from 50GB to 800GB, depending on what size Azure VM you have.

Microsoft has a small text file on the root of this temporary SSD, as shown in Figure 1. 

image

Figure 1: Local SSD Data Loss Warning Text File

I have only created one D-series Azure VM so far, which is a Standard D1 in the U.S. South Central data center, so my sample size is extremely small. My little Standard D1 VM has an Intel Xeon E5-2660 processor in the host (see Figure 2).

image

Figure 2: System Information for Standard D1 Azure VM in U.S. South Central Data Center

I ran a single, five-pass CrystalDiskMark test run on the 50GB, temporary SSD on the smallest Standard D1 Azure VM, which is shown in Figure 3.

image

Figure 3: CrystalDiskMark Results on Standard D1 VM

These are not very impressive results for an SSD. This is the smallest D1 VM available.

I am guessing that these local SSDs are shared across multiple VMs on a host. Perhaps Microsoft is using something to throttle the I/O performance of an individual VM to get a guaranteed level of performance?

I will need to run some more I/O testing before I draw any lasting conclusions here.

Update: After some interesting conversation on Twitter, I ran the same benchmark on a “top-of-the-line” Standard D14 Azure VM, as shown in Figure 4. These are dramatically better results.

As I said originally, this subject bears more investigation and testing before we jump to any conclusions.

image

Figure 4: CrystalDiskMark Results on Standard D14 VM

Posted in Azure Virtual Machines | Tagged , | Leave a comment

SQL Server Diagnostic Information Queries for September 2014

I revised a number of the queries this month in all five versions of the script. I have also added several new queries to the SQL Server 2012 and SQL Server 2014 versions of the script. Here are the current query counts for each version:

SQL Server 2014         72 Queries

SQL Server 2012         69 Queries

SQL Server 2008 R2    65 Queries

SQL Server 2008         59 Queries

SQL Server 2005         51 Queries

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware.

You need to click on the top left square of the results grid in SSMS to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet. There are also some comments on how to interpret the results after each query.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

It is also very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server.

If you want to understand how to better run and interpret these queries, you should consider listening to my latest Pluralsight course, which is SQL Server 2014 DMV Diagnostic Queries – Part 1. This course is short and to the point (only 67 minutes), and I think you will enjoy it!

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Posted in Diagnostic Queries, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Tagged , | 6 Comments

Tesla Model S Road Trip Results

I recently drove my red Tesla Model S P85 from Parker, CO to Kansas City, MO, which ended up being a round trip distance of about 1240 miles. This was my longest road trip ever in the Tesla, and it went very smoothly, with absolutely no problems from the car. There was also no “range-anxiety” on my part, especially since I am quite familiar with the car and what it can do.

I did learn something on the trip that was somewhat counter-intuitive. In an old-fashioned gasoline vehicle, you will generally make better time to your destination if you drive faster (subject to your fear of getting a speeding ticket). It is very common for the flow of traffic to be at least 5-10 mph above the posted speed limit on U.S. Interstate highways. The common limit in Colorado and Kansas is 75 mph, so traffic is generally going 80-85 mph.

As you go faster in any vehicle, your aerodynamic drag goes up quite dramatically, which affects your fuel consumption, whether you are burning gasoline or using electrons. In the Tesla, going 85 mph definitely has a noticeable effect on your power usage and range, even though the Tesla has a very low coefficient of drag.

Going east on this trip, I was going with the flow of traffic, which meant my range was less than the normal rated range. There was never any danger of running out, since I had planned my route, but it meant that I spent more time charging than I initially thought I would. I also charged higher than I needed to, to leave a larger battery reserve than I really needed.

Going west on the trip, I was going 75 mph. This meant that my energy usage per mile was lower, and I would arrive at each Supercharger station at a higher state of charge. I also did not spend extra time charging higher than I needed to. I would charge long enough to have enough range to make it to the next Supercharger, with a 50-60 mile reserve, which cut down on my total charging time. Consequently, I made much better overall time going home than I did on the outbound leg. Driving a little slower, charging less, and not charging as high at each stop made a huge difference in the overall trip time.

As far as trip impressions go, it was an interesting journey. When I pulled in to the first Supercharger in Limon, CO on the east-bound leg (which is next to an Arby’s restaurant), the car drew a bit of a crowd, with several people coming out of the Arby’s to take pictures of the car and ask me questions about it. The car drew similar attention at every other Supercharger stop.

In the Tesla community, we call this phenomena “Tesla Time”, where complete strangers will walk up to you and start asking lots and lots of questions about the car.

Here are some of the most common questions:

  1. What kind of car is that?
    1. It is a Tesla Model S
  2. Who makes it?
    1. Tesla Motors, in California
  3. How much does it cost?
    1. Anywhere from $70-110K, depending on the battery size and options
  4. Is it a hybrid?
    1. No, it is 100% electric
  5. What is the range?
    1. The 85 kWh battery model has a rated range of 265 miles. Your actual range depends on how fast you drive, the weather, elevation changes, etc.
  6. How long does it take to charge?
    1. That depends on what you plug it into. A Tesla Supercharger can do a complete charge in about 40-50 minutes. A Tesla HPWC can do a complete charge in about 4 hours. A 240V, 50 amp NEMA 14-50 outlet (like I have in my garage) can do a complete charge in about 8 hours. I just plug in whenever I come home, and I always have a full battery when I leave the house.
  7. How fast is it?
    1. The P85 model will do 0-60 in about 4.0 seconds. It is pretty quick…
  8. How much does it cost to charge?
    1. Tesla Superchargers are free. It costs me about $9.00 to do a full charge at home.

DSC02637

Figure 1: Charging in Salina, KS

DSC02639

Figure 2: Charging at a Supercharger, with the battery about 2/3 full

Posted in Tesla | Tagged , | 5 Comments

Tesla Road Trip to SQLSaturday #300 in Kansas City

I am going to be driving my red Tesla Model S P85 from Parker, Colorado to SQLSaturday #300 in Kansas City (which is happening on September 13, 2014). I will actually be leaving on Thursday morning, and driving to Topeka, Kansas (where I have family).

Then, on Saturday morning, I’ll drive from Topeka to Kansas City to the event. Finally, I will drive back from Topeka to Parker on Sunday. I’ll be able to do this nearly 1200 mile round trip with no cost for gasoline or electricity, using the free Tesla SuperCharger Network. This trip would cost about $150-160 in a typical gasoline car that got around 30 mpg.

Tesla Superchargers represent the most advanced charging technology in the world, capable of charging the Model S 16x faster than most public charging stations. Many of the stations are capable of delivering up to 120 kW to a Tesla Model S, which can replenish half a charge in as little as 20 minutes, for free. It works by delivering DC power directly to the battery using special cables that bypass the regular onboard charging equipment that you would use at a public charger or when charging at home.

These Supercharger stations are spaced out about every 100-150 miles along many of the major Interstate highways in the United States. There are currently 112 Supercharger stations in the United States, with more being opened every week. They typically have four to eight charging bays at each Supercharger station, and they are usually located near restaurants and shopping areas. The idea is that you drive for two-three hours, then stop for 20-30 minutes to charge while you take a short break.

Normally, most Tesla Model S owners do the vast majority of their charging at home. You simply plug in when you come home, and your battery is fully charged when you leave. I use a common and inexpensive charging method, with a 240V, 50 amp NEMA 14-50 outlet in the garage that cost about $500 to have a licensed electrician install. This circuit can fully charge the Tesla Model S 85 kWH battery in about eight hours, but my charging time is usually much less, since I very rarely run my battery down very low.

It is also possible to use a Tesla High Power Wall Connector (HPWC) charger (if you have dual, onboard chargers) to fully charge a Tesla Model S 85 kWH battery in about four hours.

I will be hitting the following Tesla Superchargers on this trip:

2221 6th St, Limon, CO 80828

2631 Enterprise Rd, Goodland, KS 67735

4101 Vine St, Hays, KS 67601

755 W. Diamond Dr., Salina, KS 67401

Since the Supercharger in Topeka is not yet completed, I will be charging at my sister’s house in Topeka (using a NEMA 14-50 circuit) on Thursday night and on Saturday night.

I plan to blog about how the trip goes, which should be fun.

Posted in SQLSaturday, Teaching | Tagged | 4 Comments

SQL Server Diagnostic Information Queries for April 2014

I made some small improvements to a few of the queries this month. I plan to add several more SQL Server 2014 specific queries over the next couple of months, along with a lot more comments on how to interpret the results of each query in the entire set.

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid in SSMS to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet. There are also some comments on how to interpret the results after each query.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server. Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Posted in Diagnostic Queries, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 | Tagged , | 1 Comment