Press "Enter" to skip to content

Category: Cloud

Clearing The Azure Procedure Cache

Published 2016-03-30 by Kevin Feasel

Tim Radney shows us a new way of clearing the procedure cache in Azure SQL Databases (and in 2016 RC0 or later):

It turns out that DBCC FREEPROCCACHE is not supported in Azure SQL Database. This was troubling to me, what if I’m in production and have some bad plans and want to clear the procedure cache like I can with the box version. A little Google/Bing research lead me to find the Microsoft article, “Understanding the Procedure Cache on SQL Azure,” which states:

SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache.  However, if you make changes to the table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache.

In discussing this with Kimberly Tripp after not seeing that described behavior, it does not flush the plan from cache, but it does invalidate the plan (and then the plan will be eventually aged out of the cache). While this is helpful in certain situations, this was not what I needed. For my demo I wanted to reset the counters in sys.dm_exec_cached_plans. Generating a new plan would not give me the desired results. I reached out to my team and Glenn Berry told me to try the following script:

Read on for the new command, and just like DBCC FREEPROCCACHE, be careful where you point that thing.

Comments closed

Pausing Azure SQL Data Warehouse

Published 2016-03-29 by Kevin Feasel

Brian Davis shows us how to pause Azure SQL Data Warehouse:

This is where automation comes to the rescue again! Most of our SQLDWs can be paused after 6:00 PM on weekdays, as well as the entire weekend. Now, I could manually go and pause each individual SQLDW at the end of the day, but what happens if I have plans for dinner or something else during that time? I decided that I needed an automated process to check each SQLDW and pause it if it is running. Using Azure Automation andAzure Runbooks, I was able to create a scheduled task that looks for any running SQLDW and pauses it.

Here are the basic steps to implement the automated solution I came up with:

  1. Create a credential in your automation account with access to all SQL Data Warehouses.

  2. Create a PowerShell Workflow Runbook with the code below.

  3. Create and link the schedule(s) needed for it to run.

Azure gripe #4 for me is that they’re so inconsistent about what I can do not to pay money.  Apparently you can pause Azure SQL Data Warehouse, which is good.  But DocumentDB or HDInsight?  Nope, deletion is the only way to stop running up charges.  Check out Brian’s script if you use Azure SQL Data Warehouse and save your company a bit of cash.

Comments closed

Powershell With Azure SQL Database

Published 2016-03-17 by Kevin Feasel

Mike Fal introduces us to Azure SQL Database operations using Powershell:

What is this all about? It took me a bit of digging, but what it boils down to is that Microsoft made a fundamental change to how things are managed within Azure. You will now find documentation on these two different deployment models: Classic Deployments and Resource Manager Deployments. These two different set of Powershell cmdlets reflect these different models, as anything for Classic Deployments are handled by cmdlets in the Azure and Azure.Storage modules. All the Resource Manager Deployment stuff is handled by the AzureRM* modules.

This is the first in a series and serves as an introduction to the topic.

Comments closed

Azure SQL Database Cross-Database Queries

Published 2016-03-03 by Kevin Feasel

James Serra reports that Azure SQL Database now allows cross-database queries:

A limitation with Azure SQL database has been its inability to do cross-database SQL queries.  This has changed with the introduction of elastic database queries, now in preview.  However, it’s not as easy as on-prem SQL Server, where you can just use the three-part name syntax DatabaseName.SchemaName.TableName.  Instead, you have to define remote tables (tables outside your current database), which is similar to how PolyBase works for those of you familiar with PolyBase.

For one-off tables you join to frequently, I suppose this isn’t terrible, but it’s certainly less convenient.

Comments closed

Azure SQL Database Wait Stats

Published 2016-03-01 by Kevin Feasel

Grant Fritchey discusses Azure SQL Database wait stats:

You’ll notice that these results are wildly different from those above. What we’re looking is largely a server versus a database, but not completely. I mean that sys.dm_os_wait_stats is showing the waits for the instance on which my primary Azure SQL Database is currently running. Most of those waits are mine, but because it’s part of the management structure of Azure, sys.dm_os_wait_stats shows some information that’s not applicable, directly, to me. The “server” is not really that. It’s a logical container holding your database. There’s a lot more to it under the covers. To get the waits that are absolutely applicable to me and my databases, I have to go to sys.dm_db_wait_stats.

Azure SQL Database is going to behave a bit differently from on-premise SQL Server, so if you’ve got an Azure SQL Database, pay attention to those differences.

Comments closed

Azure SQL Database Security

Published 2016-02-18 by Kevin Feasel

James Serra has a number of links to Azure SQL Database security features:

Life we be so much easier if we could just trust everyone, but since we can’t we need solid security for our databases.  Azure SQL Database has many security features to make you sleep well at night:

Most of these are exactly the same as the on-premise product—at least the SQL Server 2016 version—but it goes to show that Azure SQL Database has grown up quite a bit.

Comments closed

Loading Azure SQL Data Warehouse

Published 2016-02-16 by Kevin Feasel

Grant Fritchey enumerates various pains he went through to load data into Azure SQL Data Warehouse:

Let’s bottom line this. Data is dirty. Any ETL process is likely to expose the trashy data, so what are the real issues here? First up, Powershell is my bestest buddy. Scripting everything through Posh right out of the gate made a huge difference in my ability to constantly reset. The fact that our most common processes spit out UTF16, while not a problem, is something you need to get on top of (see Posh above). AZCopy works really well and the command line is easy to implement, but you’ll need to go through the process of setting up all the appropriate connections. Polybase is wicked cool. Yes, I had to hop through hoops to get the data clean, but, what you should note in the above descriptions, at no point did I need to go and recreate my EXTERNAL TABLE from Azure SQL Data Warehouse. I just kept updating the files through my scripts and as soon as they were loaded up to blob storage, I ran a query against them. No additional work required. That’s nice. You’re going to want to go through your data types on your data before you start the migration. You’re going to have to plan for a lot of time cleaning your data if you haven’t already.

Grant’s method is the right way, especially for early tests.  In practice, for the type of data you’d put into Polybase, you might want to create the external table to allow rejecting a certain number of rows—Grant didn’t specify the REJECT_TYPE and REJECT_VALUE attributes creating his external table, so the default of 0 rows was used.  In a warehouse with billions of rows, hand-fixing all of that data is a nasty proposition, and if you’re writing queries whose results likely won’t change if a few (dozen?) records get dropped, rejecting bad data might be a good way to keep some of your sanity.

Comments closed

SQL Server Express Images In Azure

Published 2016-02-12 by Kevin Feasel

Microsoft now offers an Azure image for SQL Server Express:

We just announced that we added images for SQL Server Express with Tools 2014, 2012, and 2008R2 in the Azure Gallery. SQL Server Express is a free version of SQL Server that you can use for dev/test and for web and mobile apps with lightweight relational database needs.

I’m not the world’s biggest fan of Express edition, but if you’re cost-conscious enough, this might be a nice move for you.

Comments closed