THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

  • Surface Pro 3 first impressions

    I traded in my Surface 2 (the trade-in program is now over) and bought a Surface Pro 3 with an i7 processor and 8 GB of ram. I greatly prefer the 3 by 2 aspect ratio of the Surface 3. After only one day of ownership, I’ve decided to purchase a docking station. I have a 7 year old desktop with a quad core Q6600 processor overclocked to 3.0 GHz and 8 GB of ram. It has a Plextor 512 MB SSD as the primary drive. It’s a very capable machine, but it does have a little bit, and I do mean only a little bit, of fan noise. I’d like to eliminate even that small amount of noise.

    My subjective initial impression was that my new Surface Pro 3 was significantly outperforming my desktop. Time to use PerformanceTest to get the facts. The Surface Pro 3 outperforms my desktop significantly on every measure except one of the 2D graphics tests. The desktop was slightly better drawing 2D vectors at 18.7k vectors/sec compared to the Surface’s 15.7k vectors/sec.


    Figure 1. PerformanceTest 8.0 test results.

    I have 6 TB of storage on my desktop. The desktop could be moved to the closet where my router and switch are. With the docking station, the Surface can use my gigabit network to access the files on the desktop. My desktop cannot run the latest version of Hyper-V, which is most inconvenient. Installing Visual Studio 2013 installs Hyper-V. As a reader points out below, this alters the behavior of how Windows runs on the device.

    What I’ve found is that I don’t want to go back to non-touch enabled devices. I use both touch and a Bluetooth mouse with my Surface Pro 3, which is what I also did with my previous Surface 2. Having said that, I use the mouse with SSMS. I’m a little afraid of using touch when doing things to a database. If you’re going to use the Surface Pro 3 for development or doing demos of SQL Server, I recommend that you get 8 GB of ram. Both i7 models have 8 GB of ram. Only the higher version of the i5 Surface that has 256 MB of storage comes with 8 GB of ram.

    UPDATE: After taking the Surface to work today, I concluded that I want a lunch bag with a padded slot to hold the Surface. That way I can carry everything in one convenient bag. To the entrepreneur who creates such a lunch bag, send me one and we’ll call it even, okay?

  • Unsupported way to secure unsupported XP

    The free software tool nLite can be used to customize an XP installation. It isn’t supported by Microsoft, but neither is XP. It is possible to remove Internet Explorer or networking components from an installation. Sometimes there is no need for the XP machine to ever connect to a network. This is particularly true when XP is used to host software used to run a piece of specialized industrial or medical equipment that requires vendor software that runs only on XP. Or maybe your application needs to connect to a network printer but not surf the web.

    I have used nLite and was pleased with the results. When I used it to customize XP builds for restricted use, I would remove the games. This reduces the temptation for users to use the XP machine for anything other than its intended use.

    I recommend that people move off of XP. But I do understand that isn’t feasible for everybody. For those who continue to use XP, nLite might provide a means to reduce the attack surface area of an XP installation. Using an unsupported tool to remove components of an operating system of course introduces new risks of possibly removing something that is needed. Test thoroughly and retest.

  • Free Azure HDInsight ebook from Microsoft

    Microsoft has released a free ebook on Azure HDInsight. Full details are available here. HDInsight is Microsoft’s implementation of Hadoop. Azure HDInsight is Hadoop in the cloud. The ebook gives you a quick overview of what Big Data is and what you can do with it.

  • Windows 8.1 Boot into Desktop Mode

    If you primarily work in desktop mode, you can easily reconfigure Windows 8 to start in desktop mode when booting up. Once in desktop mode, go to the Taskbar, right-click and then select Properties to change the Navigation on your machine.


    Figure 1. Right-click on the Taskbar select Properties.

    On the Navigation tab, check When I sign in or close all apps on a screen, go to the desktop instead of Start to enable booting into desktop mode.


    Figure 2. Check Show my desktop background on Start, Show the Apps view automatically when I go to Start, and List desktop apps first in the Apps view when it’s sorted by Category.

    There are other things you might want to enable. If you check Show the Apps view automatically when I go to Start, you’ll see your applications instead of the tiled view when you switch back to the Modern UI. Since I prefer the category view, I also checked List desktop apps first in the Apps view when it’s sorted by Category. Notice that the desktop background appears because Show my desktop background on Start is enabled.


    Figure 3. The Modern UI with Show my desktop background on Start, Show the Apps view automatically when I go to Start, and List desktop apps first in the Apps view when it’s sorted by Category all checked.

  • Windows Not Sleeping All Night

    Having a computer wake up when you don’t want it to wastes electricity and drains the battery on mobile devices. My desktop had been waking up at night, so I assumed it was some network traffic on my home network. I unchecked Allow this device to wake the computer on my network adapters.


    Figure 1. Network adapter Power Management tab.

    That didn’t solve the problem. I included the screen capture in Figure 1 because it could be part of the solution for someone else.

    To identify the root cause instead of guessing, the System log was examined. As you can see, pressing the sleep button was putting the machine to sleep.


    Figure 2. Timestamp of when the computer was put to sleep.

    To figure out what was waking the machine up, the System log was checked to review all events following the sleep event.


    Figure 3. System log showing what woke the computer up.

    This is what requested the computer to wake up:

    Wake Source: Timer - Windows will execute 'NT TASK\Microsoft\Windows\TaskScheduler\Regular Maintenance' scheduled task that requested waking the computer.

    A Bing search provided more information:

    To change this behavior, go to the Action Center and click on Change maintenance settings.


    Figure 4. Automatic Maintenance section in the Action Center.

    There are two options for modifying the Automatic Maintenance schedule. You can change the time or disable the ability for automatic maintenance to wake your device.


    Figure 5. Options for controlling maintenance events waking the computer.

  • Web-based data generator

    One of my coworkers told me about Mockaroo, a web-based data generator. I needed some test data for upcoming blog posts, so I decided to give it a try. It’s pretty good. I had to use Firefox because of problems running Mockaroo on Internet Explorer 11. Using the defaults except for changing the format to SQL, it generated output that looked something like the following. Mockaroo is so good that it generates fake data that could accidentally be real, such as email addresses. Consequently, I edited the values shown below so that they are hopefully actually fake.

    create table MOCK_DATA (
        id INT,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(50),
        country VARCHAR(50),
        ip_address VARCHAR(20)
    insert into MOCK_DATA (id, first_name, last_name, email, country, ip_address) values (1, 'John', 'Deaux', 'jdeaux@fakeDomain.fakeDomain', 'Made', '');

    It has lots of options, but some edits to the generated output may be necessary for Unicode compatibility in SQL Server. Look at the following output (which my always helpful friend Greg Low says is politically correct):

    create table TEST_DATA (
        chinese VARCHAR(50)
    insert into TEST_DATA (chinese) values ('空调);

    That’s not what is needed for SQL Server to properly handle Unicode characters. The preceding example needs to be changed to the following:

    create table TEST_DATA (
        chinese NVARCHAR(50)
    insert into TEST_DATA (chinese) values (N’空调);

    Mockaroo is extensible because it can use regular expressions to generate data.

  • TechEd 2014 Day 4

    Many people visiting the SQL Server booth wanted to know how to improve performance. With so much attention being given to COLUMNSTORE and in-memory tables and stored procedures, it is easy to overlook how important tempdb is to performance. Speeding up tempdb I/O improves performance. The best way to do this is to not do the I/O in the first place. With SQL Server 2014, tempdb page management is smarter. Pages are more likely to be released before being unnecessarily flushed to disk. Read more about it here.

    Of course, not all tempdb I/O can be eliminated. Performance can be improved by moving database files to flash memory storage. To maximize the performance benefits of flash storage, buffer pool extensions should be enabled.

  • TechEd 2014 Day 3

    There is some confusion about durability of data stored in SQL Server in-memory tables, so some review of the concepts is appropriate. The in-memory option is enabled at the database level. Enabling it at the database level only gives you the option to specify the in-memory feature on a table by table basis. No existing tables or new tables will by default become in-memory tables when you enable the feature at the database level.

    If you choose to make a table an in-memory table, by default it is durable with changes being recorded in the transaction log. You do not have to worry about data loss. However, you have an additional option of making an in-memory table not durable. If you actively choose to do this, you will have data loss if, for example, the server crashes. There are legitimate use cases of choosing to override the default behavior and create in-memory tables that are not durable. If you need staging tables for ETL, non-durable in-memory tables will provide high performance. If you need temporary tables for a particular processing need, non-durable in-memory tables can outperform tables in tempdb.

  • TechEd 2014 Day 2

    Today people asked me about backing up older versions of SQL Server to Azure. Older versions back to SQL Server 2005 can be easily backed up to Azure Storage by installing Microsoft SQL Server Backup to Windows Azure Tool. It installs a service of the same name that applies rules to SQL Server backups. You can tell the tool to backup or encrypt your SQL Server backups. You can have it automatically upload your backups to Azure Storage. Even if you don’t want to upload your backups to Azure, you might want to use the tool just because it can compress or encrypt your backups. Download the tool from here.

    At the Ask the Experts dinner, I heard a joke about DBAs. A group of lions is called a pride or a sault, a group of crows a murder or a parcel, a group of cats a clutter or a nuisance, a group of bison a herd or an obstinancy. What is a group of DBAs called? An obstinancy.

  • TechEd 2014 Day 1

    Today at TechEd 2014, many people had questions about the in-memory database features in SQL Server 2014. A common question is how an in-memory database is different from having a database on a SQL Server with an amount of ram far greater than the size of the database. In-memory or memory optimized tables have different data structures and are accessed differently using a latch free and lock free approach that greatly improves performance. This provides part of the performance improvement.

    The rest of the performance improvement comes from natively compiled stored procedures that can only access memory optimized tables. Conventional stored procedures can access either conventional or memory optimized tables. While it is true that conventional stored procedures are compiled, they do not compile all of the way down to native machine code. Natively compiled stored procedures are faster than conventional stored procedures.

    For obtaining an in-depth understanding of in-memory database features, I recommend that you read the excellent whitepaper written by my friend Kalen Delaney which can be downloaded from Microsoft here.

  • TechEd 2014 Day 0

    Microsoft’s TechEd 2014 conference opened today. It doesn’t fully open until tomorrow, but was open for doing hands on labs and other side activities. The labs provide you with a convenient, guided tour of new features. To make the best use of your time, I recommend reading the instructions carefully and thoughtfully. Try to understand why and what you are doing instead of just following the instructions. I did three SQL Server 2014 labs today and found them to provide a good introduction to new features. Working with new features provides a level of understanding that you can’t obtain from just reading about them.

    Not at TechEd? No problem. You can find virtual labs online here. SQL Server 2014 labs from TechEd will be added to the online library labs sometime after TechEd. Running the labs on your own machine requires that you install an application for which local administrator rights and a reboot are required.

  • Links and resources for understanding windowing functions

    Today at SQL Saturday in Houston I gave a presentation on SQL Server 2012/2014 windowing functions. The focus was on analytic functions. I used several different resources that I want to share with you.

    First, purchasing Itzik Ben Gan’s excellent book on windowing functions is a must. I used some of his examples because they are clear and useful. You should be able to refactor his queries to solve real world problems.

    Red Gate’s Simple Talk website has several good articles on windowing functions. I used this one. It’s worth a read and has good sample code.

    Itzik has some very good articles on windowing functions you can find on the SQL Server Pro website, such as this one. He wrote a quite advanced article about a problem I encountered as a graduate nursing student at the Veterans Administration Hospital. The problem is calculating when to reorder drugs for patients. He came up with an elegant solution he wrote about here.

  • Changes to Azure SQL service tiers and pricing

    Today Microsoft announced changes to Azure SQL Database service tiers. I’ll simplify it for you: You’ll get larger databases for less money and a better uptime SLA. The Web and Business editions of Azure SQL Database are going away with retirement in 12 months. New service tiers of Basic and Standard are now available.

    Full details on the new tiers are available here and pricing details are here.

  • Windows 8.1 Start Menu partial workaround

    I want a Windows 7 Start Menu when I’m in desktop mode on Windows 8.1 without installing third party applications or going to a lot of trouble. I implemented a simple workaround that provides some of the functionality I’m seeking. It’s limited to operating system specific features. I’ll call it a half-full glass.

    What I want is the box on the Windows 7 start menu where I can type what I’m looking for and quickly find it. Today I needed to scan something. I use the scan feature that comes with Windows. Because I have a settings folder named settings.{ED7BA470-8E54-465E-825C-99712043E01C} on my desktop, I knew I could go to my desktop, double-click the settings folder and bring up a settings window with a search box.


    Figure 1. Settings box showing the search feature.

    But I wanted a simpler, more direct way to bring up the settings folder. I wanted to simply click on my taskbar and have magic happen. I tried to pin the settings folder to the taskbar, but that was not allowed. I created a shortcut to the settings folder and tried to add the shortcut to the taskbar. That didn’t work, either. I renamed the shortcut file to settings.exe so that the taskbar would treat it just like any other exe file. I was able to drag the shortcut named settings.exe to the taskbar and then pin it to the taskbar as shown below. Now a single click of the taskbar invokes the settings window.


    Figure 2. Settings folder on desktop. Shortcut named settings.exe in Windows Explorer and also pinned to the taskbar.

  • SQL Server 2014 Backup to the Cloud

    Backing up SQL Server to the cloud makes a lot of sense, particularly for small businesses. Managing the physical media takes up too much time and effort for a small shop. Azure storage simplifies the backup process allowing a small business to allocate its limited resources more effectively.

    As I was preparing this post and editing the screen captures showing how to back up SQL Server 2014 to Windows Azure blob storage, I discovered that earlier today Microsoft released a whitepaper covering all of this and more in depth. Better to have discovered that early in the writing process instead of later! You can download the SQLServer 2014 and Windows Azure Blob Storage Service: Better Together whitepaper to get the whole story.

This Blog


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement