The Bit Bucket

I had seen this issue before but found another cause for it today.

In this case, the client was using SCCM (System Center Configuration Manager) and couldn't access reports from the reporting server. After resolving some other issues, I was left with "The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel" when I tried to go to http://servername/reports.

When I've seen this previously, it's been to do with the trust relationship between a pair of servers. However, I hadn't seen it come up before on a single server.

When I checked the SSL bindings in Reporting Services Configuration Manager, in the Advanced tab, I found that no certificate was associated with the SSL configuration. When I tried to edit it, and choose the cert from the dropdown list, the certificate binding could not be applied. (It would be useful if it told you why at that point). That had me puzzled until I opened Report Manager in IE and viewed the certificate. The certificate had been generated onsite and had expired.

Hope this helps someone. (Or helps me the next time I see it and can't recall what it was :-))

The T-SQL scripting tools (such as SQL Server Management Studio) provide two options for scripting names: either to quote them or to omit quotes.

If you avoid things like spaces in object names, you can mostly get away without quoting i.e.

Sales.Customers is just fine and doesn't need to be [Sales].[Customers].

Even then, one problem that can arise is that a name you had used in your code could become a SQL reserved word. In that case, you either need to change every reference to it (painful), or quote it wherever it's used (also painful). So quoting by default is always a safe option. However, it makes the scripts much harder to read due to visual noise.

What I feel is really needed is some way to determine if a name needs quoting i.e. a function such as:

ISVALIDASOBJECTNAME()

So for example:

ISVALIDASOBJECTNAME('Sales') would return 1 but ISVALIDASOBJECTNAME('Sales Targets') would return 0.

This functionality could then be used to extend the QUOTENAME function with an optional parameter that says:

QUOTENAME('Sales Targets',0) for "always quote" or QUOTENAME('Sales Targets',1) for "quote only if needed). They could leave 0 as the default so it is option and the function still works unchanged when no parameter is supplied.

That would then make it easy for the team to change SSMS to have a single setting for how you'd like scripts generated.

If you agree with this, please vote here: https://connect.microsoft.com/SQLServer/feedback/details/796172/isvalidasobjectname-and-quotename-enhancement-to-clean-up-script-readability

 

More and more practical information around working with tabular data models is starting to appear as more and more sites get deployed.

At SQL Down Under, we've already helped quite a few customers move to tabular data models in Analysis Services and have started to collect quite a bit of information on what works well (and what doesn't) in terms of performance of these models. We've also been running a lot of training on tabular data models.

It was great to see a whitepaper on the performance of these models released today.

Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services was written by John Sirmon, Greg Galloway, Cindy Gross and Karan Gulati. You'll find it here:

 http://msdn.microsoft.com/en-us/library/dn393915.aspx

Why learn?

I was listening today to one of Scott Hanselman's awesome podcasts with Miguel de Icaza and during the show they discussed things that you really should invest in. One list that came up was a good bed, a good chair, and to learn to type. Most of us spend 1/3 of the day in a bed and 1/3 of the day in a chair, so they seem like no-brainers. Typing is an interesting addition to that list.

Back when I was in school, in those sexist old days, typing wasn't something that the boys learned. It was something that was taught to the girls, and mostly to those that weren't heading in an academic direction. Up until about 1990 I was a pretty fast two-finger typist (probably more like a four-finger typist). I could cut code pretty quickly and I wasn't too concerned about it. Occasionally though, I kept feeling that I could do much better if I learned to type. Eventually I convinced myself that if a sizeable percentage of the population could do it, surely I could learn.

Is it hard to learn?

At that time, some pretty decent typing programs for the PC had started to appear. I decided to try to learn to type and chose TypeQuick. It's interesting (and good for them) to see that they still exist today. So many applications from those days have long since gone. I particularly liked the way that it constantly pushed you just a little bit faster than what you were achieving. I haven't tried the program since then but I hope it's just improved over time. But regardless, there are many of these types of programs around.

But then the real problems began. Most of these types of programs assume that you can spend an hour a day learning to type, and in a few weeks you'll be able to do so. The implicit assumption though is that's the only time that you'll be typing during that time. The hassle with this is that if you type for a living (by cutting code or interacting with IT systems), spending an hour a day doing it the right way, then the rest of the day doing it the wrong way isn't a formula for success.

I decided that I needed to break that impasse. Early one fine Sunday morning, I started doing the course and I kept going until I had pretty much finished the course, at around midnight. By then I could barely move my hands at all but I had learned all the basics.

What I hadn't figured on though was that the hardest part was to come. During the next four weeks or so, I had tight deadlines that I knew I could meet by typing my old way, and yet I had to force myself to type correctly, even though it was at a fraction of the speed that I could do the wrong way.

I did force myself to put up with the stress and after about four weeks I was back to about the same typing speed. The brilliant part is that I was then able to keep getting faster and faster. I'd probably now average about three or four times faster than what I could do in my heyday of four-finger typing.

What difference does it make?

Clearly if you are writing a bunch of code, you can do so much more productively but there are also many subtle improvements in what you achieve, not just how quickly:

• You are much more likely to use clear (but often longer) names for objects
• You are much more likely to write documentation while coding (Miguel noted that people who can't type tend to always put off tasks like documentation and I have to say that I've seen the same)
• You are much more prepared to throw away code and replace it with better quality code (I've noticed that people who don't type are much more likely to cling onto their first attempts at any piece of code)
• While it's possibly an even more minor point, you will simply seem more professional to those that you work with. Professionals invest time in skills that matter in their profession. If you're reading this, chances are that typing is in that category for you.

I'm really surprised that typing isn't considered a core skill for IT people today. Perhaps there will be a time in the future when it won't matter but it does matter today. Curiously though, it's often viewed as a less-important skill to master. But those who have done so will all tell you how important it is.

When I worked at a university, I was pushing for students to learn typing in the first two weeks of their Computer Science degrees. The idea was almost mocked by most lecturers as typing wasn't something that was taught by universities. Yet the same university struggled to have enough resources for students to use, and the resources they had were tied up by people that took forever to type anything. The real irony is that in most courses, there isn't much other useful work that the students could do in the first week or two anyway.

So, if you have made it to this point in the blog, and you can't yet type, I hope that you'll consider it.

I have days where I can't decide if I'm frustrated or sad about how I see SQL Server being used by applications, or if I'm happy that this keeps us in ongoing work.

Today I've been looking at a system that's having performance issues. There are three key applications on the system. Each comes from a different vendor and when I look at how each one of them interacts with the server, it's really frustrating. I've come to the conclusion that it's mostly due to application frameworks that are being used. Here's an example:

System A that I'm working with (name hidden to protect the guilty) never just issues a query to SQL Server. What it does instead is:

1. Issues a SET FMTONLY ON, then sends the command, then SET FMTONLY OFF
2. Creates a static cursor based on the command
3. Fetches from the cursor
4. De-allocates the cursor

This simply isn't sensible. Apart from just slowing down the data access immensely, here are my issues with this:

SET FMTONLY ON returns the columns and data types for every code path through the code that you submit to it. That works ok for simple commands but is fragile for anything else. Regardless, I can only presume that it's performing step #1 as a way of creating an empty dataset/recordset/rowset in the programming language, without having to specify the columns and data types. This is concerning for multiple reasons. First is that SET FMTONLY ON is now deprecated but more importantly, it means that the system is being constantly asked to evaluate something that isn't actually changing. On the system I was monitoring, the application was asking SQL Server to do this over 60,000 times per hour, yet the answer isn't going to change. And it is probably occurring just to simplify the creation of the rowset in code (i.e. a one-off one-time effort).

Creating a static cursor involves executing the query and materializing a copy of all of the data that was retrieved into tempdb. For almost all of the queries in question, tempdb has no need to be involved at all. The fetch command then retrieves the data from tempdb. The de-allocation then removes the copy of the data and the cursor structure from tempdb.

It's a tribute to the product that it performs as well as it does, given how it's often used or (more likely), abused.

I posted another podcast over the weekend. Late last week, I managed to get a show recorded with Adam Machanic. Adam's always fascinating. In this show, he's talking about what he's found regarding increasing query performance using parallelism. Late in the show, he gives his thoughts on a number of topics related to the upcoming SQL Server 2014.

Enjoy!

The show is online now: http://www.sqldownunder.com/Podcasts

 

I have to say that I continue to be frustrated with finding out how to do things in Windows 8. Here's another one and it's recorded so it might help someone else. I've also documented what I tried so that if anyone from the product group ever reads this, they'll understand how I searched for it and might try to make it easier.

I wanted to add a network loopback adapter, to have a fixed IP address to work with when using an "internal" network with Hyper-V. (The fact that I even need to do this is also painful. I don't know why Hyper-V can't make it easy to work with host system folders, etc. as easily as I can with VirtualPC, VirtualBox, etc. but that's a topic for another day).

In the end, what I needed was a known IP address on the same network that my guest OS was using, via the internal network (which allows connectivity from the host OS to/from guest OS's).

I started by looking in the network adapters areas but there is no "add" functionality there. Realising that this was likely to be another unexpected challenge, I resorted to searching for info on doing this. I found KB article 2777200 entitled "Installing the Microsoft Loopback Adapter in Windows 8 and Windows Server 2012". Aha, I thought that's what I'd need. It describes the symptom as "You are trying to install the Microsoft Loopback Adapter, but are unable to find it." and that certainly sounded like me. There's a certain irony in documenting that something's hard to find instead of making it easier to find. Anyway, you'd hope that in that article, they'd then provide a step by step example of how to do it, but what they supply is this:

The Microsoft Loopback Adapter was renamed in Windows 8 and Windows Server 2012. The new name is "Microsoft KM-TEST Loopback Adapter". When using the Add Hardware Wizard to manually add a network adapter, choose Manufacturer "Microsoft" and choose network adapter "Microsoft KM-TEST Loopback Adapter".

The trick with this of course is finding the "Add Hardware Wizard". In Control Panel -> Hardware and Sound, there are options to "Add a device" and for "Device Manager". I tried the "Add a device" wizard (seemed logical to me) but after that wizard tries it's best, it just tells you that there isn't any hardware that it thinks it needs to install. It offers a link for when you can't find what you're looking for, but that leads to a generic help page that tells you how to do things like turning on your printer.

In Device Manager, I checked the options in the program menus, and nothing useful was present. I even tried right-clicking "Network adapters", hoping that would lead to an option to add one, also to no avail.

So back to the search engine I went, to try to find out where the "Add Hardware Wizard" is. Turns out I was in the right place in Device Manager, but I needed to right-click the computer's name, and choose "Add Legacy Hardware". No doubt that hasn't changed location lately but it's a while since I needed to add one so I'd forgotten. Regardless, I'm left wondering why it couldn't be in the menu as well.

Anyway, for a step by step list, you need to do the following:

1. From Control Panel, select "Device Manager" under the "Devices and Printers" section of the "Hardware and Sound" tab.

2. Right-click the name of the computer at the top of the tree, and choose "Add Legacy Hardware".

3. In the "Welcome to the Add Hardware Wizard" window, click Next.

4. In the "The wizard can help you install other hardware" window, choose "Install the hardware that I manually select from a list" option and click Next.

5. In the "The wizard did not find any new hardware on your computer" window, click Next.

6. In the "From the list below, select the type of hardware you are installing" window, select "Network Adapters" from the list, and click Next.

7. In the "Select Network Adapter" window, from the Manufacturer list, choose Microsoft, then in the Network Adapter window, choose "Microsoft KM-TEST Loopback Adapter", then click Next.

8. In the "The wizard is ready to install your hardware" window, click Next.

9. In the "Completing the Add Hardware Wizard" window, click Finish.

Then you need to continue to set the IP address, etc.

10. Back in Control Panel, select the "Network and Internet" tab, click "View Network Status and Tasks".

11. In the "View your basic network information and set up connections" window, click "Change adapter settings".

12. Right-click the new adapter that has been added (find it in the list by checking the device name of "Microsoft KM-TEST Loopback Adapter"), and click Properties.