THE SQL Server Blog Spot on the Web

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

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

  • Database on a diet (Part 1)

    Hi Folks,

    My next article for SQL Server Magazine is out now. I’ve decided that New Year is the time that most people discuss diets and databases don’t have to be an exception.

    I’m discussing one large client who had a database that really needed to be trimmed down. That involved a whole range of mechanisms and the outcome was great: major size reduction and significant performance improvement.

    I’ve decided to discuss this across a series of articles, each covering part of the story about how the reductions were produced and the rationale for each.

    You’ll find part one here now:

    Enjoy !

  • New-Object : Constructor not found. Cannot find an appropriate constructor for type

    This one is here so that I don’t forget it in future.

    I’ve been writing Powershell again all day today and I spent ages trying to find this error when instantiating a particular object.

    Bottom line is that there was a valid constructor that took a single string argument. However, I was passing an empty string to the constructor. Let’s just leave it at “The error message returned is interesting”.

    Hope it helps someone else.

  • Icons and the real world

    Just realised that book icons are another one that will probably lose meaning over time. That'll apply to book icons for dictionaries and thesauruses too.



    Phone icons are already pretty odd as many kids would never have seen a phone that looks like those icons.



    And the Save icons that are a picture of a 3 1/2 inch floppy disk are basically meaningless now.


    I had to laugh when I heard a kid who saw a 3 1/2 inch floppy for the first time comment that someone had made "a actual save icon". I suppose that's how it seemed to him.

    The open icons are often manilla folders. They still have some life left in them but not much.


    There are still some clipboards around but not many.


    Video icons that show sprocket holes each side of a film are basically meaningless now.


    All the mail, email, mail merge, etc. icons that show letters are fast becoming meaningless. (Ask Australia Post about that) And adhesive labels?

    image  image

    Even the latest versions of Office (2016) have old style lightbulbs for ideas. That'll quickly be meaningless.


    The slideshow icon in PowerPoint is an old style roll-up projector screen. And it's timing icons are all analog clocks (limited lifespan). It's screenshot icons are cameras that very few kids would recognize.

    image   image

    And so on and so on. I wonder how long it will be before many of the icons that were designed to represent common physical things will lose all their physical equivalence. Does that then make it harder for newcomers to computing to recognize anything?

  • SQL Down Under–Show 65–Jen Underwood and Power BI

    Hi Folks,

    We’ve taken a fairly long break but now back into the swing for SQL Server 2016 and Power BI.

    The first of the new series of shows is a show on the current state of Power BI with Jen Underwood.

    You’ll find it here:

    or subscribe download like any other podcast. Feed link is:




  • Data Tales #5: The Case of the Rogue Index

    Hi Folks,

    The fifth in my Data Tales series has been published at SQL Server Magazine. It’s the Case of the Rogue Index. You’ll find it here:


  • My Sessions from Ignite Australia on the Gold Coast now online

    Hi Folks,

    Couldn’t make it to Ignite? The team from Microsoft Australia recorded all the sessions and they are online now.

    Here are the three sessions that I delivered:


    Azure Datacamp Power Hour:

    Things I Wish Developers Knew About SQL Server:

    Working With SQL Server Spatial:



  • Data Tales #4: The Case of the Phantom Duplicate

    I’ve posted another in my Data Tales series for SQL Server Magazine.

    It’s “The Case of the Phantom Duplicate”. You’ll find it here:


  • Data Tales #3: The Case of the Stubborn Log File

    Hi Folks,

    Another of the Data Tales series that I've been writing for SQL Server Magazine has been published. It's The Case of the Stubborn Log File.

    You'll find it here:


  • AzureCon is this month–register now!

    AzureCon is the main #Azure related conference each year, and of course, it’s an online conference.

    It’s coming up at the end of this month (September).

    It’s time to register to see what ScottGu and the people from the Azure team have to tell us.

  • Data Tales #2: The Case of the Exploding Table

    Hi Folks,

    Another of the Data Tales series that I've been writing for SQL Server Magazine has been published. It's the Case of the Exploding Table.

    You'll find it here:


  • Data Tales #1: The Case Of The Auto-Truncating Table

    Tim Ford (SQL Agent Man: recently got me involved in writing a column for SQL Server Magazine.

    I’m planning to write an article each fortnight describing something of interest that I’ve recently found while consulting/mentoring at client sites.

    The first of those articles is now live:


  • FIX: Internal service error when refreshing Personal Gateway for Power BI

    We recently started working with the new Personal Data Management Gateway for Power BI. Overall, we really like it but the error messages in most of Power BI have left much to be desired.

    One error that we were encountering made us feel like the service was flaky as it seemed to happen randomly. When we tried to refresh a dataset, we got this error:




    The Power BI team came to the rescue and worked out what was happening. Turns out that you cannot currently refresh more than once every 5 minutes. That also includes within 5 minutes of your initial upload. Unfortunately, this is the error returned when you attempt it.

    Apparently this 5 minute limit is going to be removed soon and hopefully that will be one less error we might see.

  • Passed my Chinese HSK3 Exam–Thanks to all that helped

    One of my biggest goals for this year was to try to pass the HSK 3 exam. I wanted to do it as a validation of my efforts to learn Chinese.

    HSK (Hanyu Shui Ping Kaoshi - 汉语水平考试) is the exam given to foreigners to assess their level of Chinese. (Hanyu is the Chinese language, Shuiping basically means a level of achievement, and Kaoshi is an exam). The organisation that runs it is called Hanban.

    There are six levels of exam.

    • Level 1 is “Designed for learners who can understand and use some simple Chinese characters and sentences to communicate, and prepares them for continuing their Chinese studies. In HSK 1 all characters are provided along with Pinyin.”
    • Level 2 is “Designed for learners who can use Chinese in a simple and direct manner, applying it in a basic fashion to their daily lives. In HSK 2 all characters are provided along with Pinyin as well.”
    • Level 3 is “Designed for learners who can use Chinese to serve the demands of their personal lives, studies and work, and are capable of completing most of the communicative tasks they experience during their Chinese tour.”
    • Level 4 is “Designed for learners who can discuss a relatively wide range of topics in Chinese and are capable of communicating with Chinese speakers at a high standard.”
    • Level 5 is “Designed for learners who can read Chinese newspapers and magazines, watch Chinese films and are capable of writing and delivering a lengthy speech in Chinese.”
    • Level 6 is “Designed for learners who can easily understand any information communicated in Chinese and are capable of smoothly expressing themselves in written or oral form.”

    While I’d love to achieve Level 6 one day, my medium term goal is Level 5. That’s the level required for students entering Chinese universities. But my goal for this year was Level 3. It included 100 points for listening, 100 points for reading, and 100 points for writing. I managed 275 all up, which I am super happy about.

    I need to thank all my Chinese buddies on Facebook who endlessly answer my mundane questions about Mandarin.

    But my biggest thanks needs to go to all at Spending an hour one-on-one with a teacher three times each week has made an enormous difference. For most of this period, Amy was my teacher. Amy (and most of the teachers including my current teacher Bella) is based in Wuhan, China. If you have any interest in getting serious about Mandarin Chinese, I strongly suggest talking to them. If you mention me, we both get some free time but that’s not my main concern. I’d just love to see more people learning Mandarin. It’s going to be (and already is) a very important language in the future. Estimates are that 1 in 4 children born today will be native Mandarin speakers. (And for interest, 1 in 5 will be native Spanish).

    I’ve found that learning Mandarin has already opened up another whole world to me.

    Onwards to Level 4 !   加油!

  • Cannot determine next value for a sequence

    One of the most anticipated new features in SQL Server 2012 was the introduction of sequences. Prior to SQL Server 2012, developers had a choice of IDENTITY columns or a roll-your-own table mechanism.

    Sequences allow us to create a schema-bound object that is not associated with any particular table. For example, if I have a Sales.HotelBookings table, a Sales.FlightBookings table, and a Sales.VehicleBookings table, I might want to have a common BookingID used as the key for each table. If more than the BookingID was involved, you could argue that there is a normalization problem with the tables but we'll leave that discussion for another day.

    Recently when working with sequences however, I found a problem with their implementation. It works as described but is not useful.

    So let's start by creating the schema and the sequence:



    We could then use this schema as the default value for each of the three tables:


    All this is as expected. One question that often arises though, is "how do I know the last value for a given sequence". The answer provided is to query the sys.sequences view. We can do this as follows:


    The current_value colum in sys.sequences is defined as follows:

    Datatype: sql_variant NOT NULL

    The use of sql_variant here makes sense as the view needs to be able to provide the current value for all sequences, regardless of data type. Sequences can be created with any built-in integer type. According to BOL, the possible values are:

    • tinyint - Range 0 to 255
    • smallint - Range -32,768 to 32,767
    • int - Range -2,147,483,648 to 2,147,483,647
    • bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
    • decimal and numeric with a scale of 0.
    • Any user-defined data type (alias type) that is based on one of the allowed types.

    The output of that column is described as:

    The last value obligated. That is, the value returned from the most recent execution of the NEXT VALUE FOR function or the last value from executing the sp_sequence_get_range procedure. Returns the START WITH value if the sequence has never been used.

    And this is where I have a problem with how it's defined. When you have never retrieved a value from the sequence, there is no last value obligated. What it does return is the first value that will be generated, but has not yet been generated:


    The documentation is correct but the behaviour is bizarre. I believe that this column should return NULL. Otherwise, there is no way to tell that this value has not yet been generated.

    If I generate a new value and then query it again ie:


    Note that the same value is returned:


    It's only when I request it another time, that I see the expected value:


    So the problem is that when you read the current value from the sys.sequences view, there's no way to know if this is the last value obligated or the next one that will be obligated.

    I'd really like to see this behaviour changed. Given that the SQL Server team rates backwards compatibility highly, an alternative would be to add a new column to sys.sequences that indicates that the sequence has never been used. There is a column is_exhausted. At a pinch, that could be set for new sequences.


    If you agree, you can vote here:

  • Plan Cache Pollution: Avoiding it and Fixing it

    While SQL Server’s plan cache generally is self-maintaining, poor application coding practices can cause the plan cache to become full of query plans that have only ever been used a single time and that are unlikely to ever be reused. We call this “plan cache pollution”.


    The most common cause of these issues are programming libraries that send multiple variations of a single query. For example, imagine I have a query like:

    SELECT c.CustomerID, c.TradingName, c.ContactName, c.PhoneNumber FROM dbo.Customers AS c WHERE c.CustomerID = @CustomerID AND c.BusinessCategory = @BusinessCategory AND c.ContactName LIKE @ContactNameSearch ORDER BY c.CustomerID;

    The query has three parameters: @CustomerID, @BusinessCategory, and @ContactNameSearch. If the parameters are always defined with the same data types ie: @BusinessCategory is always nvarchar(35) and so on, then we will normally end up with a single query plan. However, if on one execution the parameter is defined as nvarchar(35), and on the next execution it is defined as nvarchar(20), and on yet another execution it is defined as nvarchar(15), each of these queries will end up with different query plans. A similar problem would also occur if any of the plan-affecting SET options are different on each execution ie: if DATEFORMAT was dmy for one execution, and mdy for the next, you’ll also end up with a different plan.

    For more details on the internal causes of this or for a list of plan-affecting SET options, you might want to read the whitepaper that I prepared for the MSDN site. The latest version was for SQL Server 2012 and can be found here: (Plan Caching and Recompilation in SQL Server 2012).

    So what on earth would cause someone to send parameters defined differently each time? The worst offenders are not queries that are written intentionally, they are queries written by frameworks.

    As an example, while using the SqlCommand object in ADO.NET, it is convenient to use the AddWithValue(parametername, parametervalue) method of the Parameters collection. But notice that when you do this, you do not specify the data type of the parameter. ADO.NET has to derive an appropriate data type based on the data that you have provided. For string parameters, this can be particularly troubling. If the parameter value is initially “hello”, a query plan with an nvarchar parameter length of 5 will be cached after the command is executed. When the query is re-executed with a parameter value of “trouble”, the command will appear to be different as it has an nvarchar parameter with a length of 7.

    The more the command is executed, the more the plan cache will become full of plans for different length string parameters. This is particularly troubling for commands with multiple string parameters as plans will end up being stored for all combinations of all lengths of all the parameters. Some later variants of these libraries are improved by always deriving strings as nvarchar(4000). That’s not ideal but it’s much better than the previous mechanism.

    While someone coding with ADO.NET can use another method to add a parameter ie: one that allows specifying the data type as well, developers using higher level libraries do not have that option. For example, Lync to SQL uses AddWithValue() within the framework. The user has no control over that. Ad-hoc queries generated by end-user query tools can also cause a similar problem where many combinations of similar queries can end up becoming cached.

    Avoiding Plan Cache Pollution

    As mentioned, to work around such a problem, the application should use a method to add the parameter that allows specifying the data type precisely.

    As an example, nvarchar(100) might be used as the data type for each execution in the above example, if we know that all possible parameter lengths are less than 100.

    Treating Plan Cache Pollution

    There are several additional options that can help in dealing with plan cache pollution issues:


    FORCED PARAMETERIZATION can be set at the database level. SQL Server will often auto-parameterize queries by determining that a value looks like a parameter, even though you didn’t specify it as a parameter. Using the FORCED PARAMETERIZATION setting makes SQL Server become much more aggressive in deciding which queries to auto-parameterize. The down-side of this option is that it could potentially introduce parameter-sensitivity problems. (This option was added in SQL Server 2005).


    OPTIMIZE FOR ADHOC WORKLOADS is an sp_configure server level option. When set, SQL Server only caches a plan stub on the first execution of an ad-hoc query. The next time the same query is executed, the full plan is stored. Plan stubs are much smaller than query plans and this option ensures that the plan cache is not filled by query plans that have never been reused. (This option was added in SQL Server 2008). We tend to enable this option on most servers.


    Sometimes you can get into a situation where you simply cannot avoid the queries from creating this situation and you need to deal with it. DBCC FREESYSTEMCACHE can be used to clear the query cache. One little understood option on it however, is that you can then specify a particular Resource Governor resource pool. It then only clears the plans associated with that resource pool. (This command was first available in SQL Server 2005 but the option to clear a specific resource pool was added in SQL Server 2008).

    We often use this method to work around plan cache pollution issues. We try to isolate the badly-behaved applications or ad-hoc queries into one or more separate resource pools using Resource Governor. Then periodically, (perhaps every 5 or 10 minutes), we clear the plan cache for members of this “tough luck” pool.

    Best advice is to try to avoid the situation in the first place by appropriate coding techniques but that option isn’t available to everyone.

This Blog



No tags have been created or used yet.


Privacy Statement