Special Discount Code for PASS Summit and Precon

On Monday, October 24, 2016, I will be doing an all-day, Pre-Conference session on how to interpret my SQL Server diagnostic information queries. I have done many shorter versions of this session (such as 60 minutes, 75 minutes, or even a half-day) before, but I have always felt a little rushed as I went through the complete set of diagnostic queries, explaining how to interpret the results of each one, and also talking about related background information and guidance that is relevant to each query.

Now, I will have a full day to go into much more detail, without having to hurry to cover everything. I will be using the SQL Server 2016 version of the diagnostic queries, which have even more useful information, including information about many new SQL Server 2016 features. If you are on an older version of SQL Server, most of the queries will still be relevant (depending on how old of a version of SQL Server you are using).

Based on past experience and feedback, Dr. DMV has always been a very popular session that people really seem to enjoy. This all-day, expanded version is going to be really fun and useful, and I hope to see you there!

Here is the abstract for the session:

Dr. DMV: How to Use DMVs to Diagnose Performance Problems

SQL Server 2005 introduced Dynamic Management Views (DMVs) that allow you to see exactly what is happening inside your SQL Server instances and databases with much more detail than ever before. SQL Server 2016 adds even more capability in this area. You can discover your top wait types, most CPU intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session (which is applicable to SQL 2005-2016), presents and explains over seventy DMV queries that you can quickly and easily use to detect and diagnose performance issues in your environment. If you have ever been responsible for a mission critical database, you have probably been faced with a high stress, emergency situation where a database issue is causing unacceptable application performance, resulting in angry users and hovering managers and executives. If this hasn’t happened to you yet, thank your lucky stars, but start getting prepared for your time in the hot seat. This session will show you how to use DMV queries to quickly detect and diagnose the problem, starting at the server and instance level, and then progressing down to the database and object level. This session will show you how to properly analyze and interpret the results of every single query in the set, along with lots of information on how to properly configure your instance and databases.

I wanted to share a unique discount code: PRE250GB for $250.00 off of a full, three day Summit registration and a pre-conference session. This is a pretty good deal for this late in the process! This code expires at midnight, Thursday, October 13, 2016. You can register here.

The PASS Summit is always a fun and very useful and educational event. It is a great way to get to know more people in the SQL Server community and to connect with people that you may only know online. I am looking forward to seeing as many of you as possible in Seattle for PASS Summit 2016!

Posted in PASS, Teaching | Tagged | Leave a comment

SQL Server Diagnostic Information Queries for June 2016

This month, I have several improvements in the SQL Server 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. One new feature is a series of six dashes after each query to make them easier to parse by people who use PowerShell to run the queries and collect the results. I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. So, I will be doing that pretty soon.

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

Here are links to the latest versions of these queries for SQL Server 2016, 2014 and 2012:

SQL Server 2016 Diagnostic Information Queries (June 2016)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (June 2016)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (June 2016)

SQL Server 2012 Blank Results

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward.  I started this policy a while ago, and so far, I have not heard any complaints. I did update these queries slightly in January 2016 though.

SQL Server 2008 R2 Diagnostic Information Queries (CY 2016)

SQL Server 2008 R2 Blank Results

SQL Server 2008 Diagnostic Information Queries (CY 2016)

SQL Server 2008 Blank Results

SQL Server 2005 Diagnostic Information Queries (CY 2016)

SQL Server 2005 Blank Results

The basic instructions for using these queries is that you should run each query in the set, one at a time (after reading the directions for that query). 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. I also think it is very helpful to run each query, look at the results (and my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have some comments in the script on how to interpret the results after each query.

After running each query, you need to click on the top left square of the results grid in SQL Server Management Studio (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.

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 when they run these queries.

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, or that someone is running an incorrect version of the script for their version of SQL Server.

It is 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 are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is really the best way to thank me for maintaining and improving these scripts…

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 2012, SQL Server 2014, SQL Server 2016 | Tagged | Leave a comment

Processor Tall Tales From Best Buy

I was recently doing some shopping at my local Best Buy, and I wandered through the computer section to see what they were currently selling (as I periodically like to do). While I was standing in the laptop area, a woman approached one of the sales associates and said “I need some help choosing a new laptop. I want something good, but nothing too fancy. Can you help me?”.

I thought the sales associate’s response was pretty entertaining. He said “Well, there are just three different types of processors you can choose from. There is an i3, and i5, and an i7. The higher numbers are better. I like to call the i3 yesterday’s technology, while the i5 is today’s technology, and the i7 is tomorrow’s technology. The i7 is much more future-proof than the others”. Then he started telling her about how the processor is like the engine in your car…

Next, he moved on to the subject of memory. He said “Windows 8 really needs at least 4GB of memory or it will really bog down. Having 6GB is better, and 8GB of memory is the best.”

Finally, he moved on to the subject of storage. He said “Storage is not such a big deal anymore, since all of these laptops have plenty of it. I think the smallest drive in any of these is 500GB. How much storage do you think you need?”

In this guy’s defense, nothing he said was really glaringly wrong (and I have heard far worse explanations of computers at Best Buy). Most of what he said was just over-simplified, perhaps because he did not know any better, or perhaps because he did not want to get too technical for his audience.

I think a better approach might have been for the sales associate to ask her what she was planning on doing with her laptop, what her preference was about size, weight, screen resolution and battery life, and maybe what her budget range was. I probably would have asked her what kind of computer she currently had. Then based on those answers, make a few initial recommendations, and gauge her response.

I spent several years in retail during college and grad school, so I have quite a bit of sympathy for retail sales people. I still remember an acronym they taught us at one of my retail jobs, which was ANPOCS, which stood for:

Approach the customer

Determine the customer’s Needs

Present the merchandise

Overcome any objections

Close the sale

Suggest add-on sales

Since I am “in computers” all of my family and friends like to get free computer advice and technical support from me. I am sure that this is a familiar situation to many of you! Scott Hanselman wrote a great post about this back in 2011.

Given my knowledge and interest in the gory details of different processors and solid state storage, I might have quickly gotten far too technical for the average person. I would have probably tried to explain that there are different generations of processors, where the latest versions are slightly faster and have better battery life. I probably would have explained the actual differences between the Core i3, i5 and i7 lines, and maybe even pulled up Task Manager to show her how to look at the difference in the number of cores in a machine and how fast they were running. I probably would have told her about hyperthreading and Turbo Boost….

And then, I would have probably noticed her eyes glazing over from an overload of information. Who knows? Perhaps the over-simplified method is better in that situation!

And just to be very clear, the genders of the sales associate and the customer have nothing to do with this story (just in case you are wondering about that). I just hate to see people not do some research before they buy something like a computer, placing themselves at the mercy of a sales associate, who may or may not know what they are doing.

Posted in Intel, Laptops | Tagged | Leave a comment

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 | 1 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