Azure SQL IaaS – TempDB Performance on SSD vs Premium Disk

When it comes to deploying SQL Server onto an Azure IaaS VM, it is sometimes difficult to know the best option to deploy the SQL Server tempdb database for your workload.

In many of the SQL templates on the marketplace it is often deployed to the C:\ by default after which you should redeploy the database to either D:\ (local SSD) or to an attached premium disk (P10, P20, P30).  The Microsoft SQL on IaaS Performance Best Practice article states both are possibilities under certain circumstances, however it does not provide empirical workload evidence as which to use when.

For those who have not seen the article – read here – https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/

The key comment of interest is this…

For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (e.g. for temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.

…and this…

So I thought lets test a OLTP type SQL workload!

AND SO – lets do some testing to validate this puppy!

Continue reading

Azure DocumentDB – Preparing, Loading and Querying Data

 

Recently I have been using Azure DocumentDB to validate several business use cases for a variety of application purposes.

For those SQL DBA’s and others who are new to Azure DocumentDB, its a recent entrant to the NoSQL document database world, and as its a PaaS document database cloud service it has the agility, scalability and availability of the Azure Cloud.

Being a schema-less Azure PaaS “document database” for my use case I wanted to verify…

  • basic costing and performance levels
  • methods to create valid JSON documents from SQL Server
  • methods to load JSON documents into Azure DocumentDB
  • performing basic like-for-like document query comparisons with SQL Server

Some homework reading for those interested…

 

22 May 2017 [EDIT]

Microsoft have just recently added significant new functionality and also formally renamed Azure DocumentDB as Azure CosmosDBa major evolution of the NoSQL database engine.  The details on the renamed service is here – https://azure.microsoft.com/en-au/blog/dear-documentdb-customers-welcome-to-azure-cosmos-db/

 

And so… let get into the belly of Azure DocumentDB CosmosDB!

Continue reading

Compression Performance with Low Key Selectivity

When it comes to enabling data compression in SQL Server I suspect most people don’t do a lot of testing as to the impacts of either enabling compression and its ongoing maintenance.  I assume most are interested in its ongoing performance for queries, and you know that’s damn fair enough too!

If you read the Microsoft Data Compression Whitepaper (and I mean whos got time to do that!) then it has some interesting technical tidbits burried deep in there which affect the impacts of applying and managing data compression.

https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

Here are some of the ones of interest…

SQL Server uses statistics on the leading column to distribute work amongst multiple CPUs, thus multiple CPUs are not beneficial when creating, rebuilding, or compressing an index where the leading column of the index has relatively few unique values or when the data is heavily skewed to just a small number of leading key values – only limited effective parallelism will be achieved in this case.


And this…

Compressing or rebuilding a heap with ONLINE set to ON uses a single CPU for compression or rebuild. However, SQL Server first needs to scan the table—the scan is parallelized, and after the table scan is complete, the rest of the compression processing of the heap is single-threaded.


And this…

When a heap is compressed, if there are any nonclustered indexes on the heap, they are rebuilt as follows:
(a) With ONLINE set to OFF, the nonclustered indexes are rebuilt one by one.
(b) With ONLINE set to ON, all the nonclustered indexes are rebuilt simultaneously.
You must account for the workspace required to rebuild the nonclustered indexes, because the space for the uncompressed heap is not released until the rebuild of the nonclustered indexes is complete.

 

That first one is a cracker – it hit me once when compressing a SQL Server table (600M+ rows) on a 64 core Enterprise SQL Server.  After benchmarking several other data compression activities I thought I had a basic “rule of thumb” (based on GB data size and number of rows)… of which just happened to be coincidence!

This also begs the question of why would you use low selectivity indexes?  Well I can think of a few cases – but the one which stands out the most is the identification of a small number of rows within a greater collection – such as an Index on TYPE columns (ie; [ProcessingStatusFlag] CHAR(1) = [P]rocessed, [U]nprocessed, [W]orking, [F]ailed, etc)

… AND SO – lets do some testing to validate this puppy!

Continue reading

SQL Filegroup/File Proportional Fill Algorithm

I had a question at work recently where there was some confusion around how SQL Server allocates data across data files within a filegroup in a user database.  There was a mention that data was not being distributed evenly across files and also that a trace flag was needed for SQL Server to distribute data evenly.  I am uncertain if those circumstances were database config related or something else outside of proportional fill.

So I thought I’d do a quick post just to clarify how proportional fill works via demonstration.

SQL Server has used a proportional fill strategy across data files in a filegroup for some time (as long as I care to remember anyway) and this has been pretty well documented in SQL BoL and a number of blog posts on the web already.

Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file.

As soon as all the files in a filegroup are full, the Database Engine automatically expands one file at a time in a round-robin manner to allow for more data, provided that the database is set to grow automatically.

https://technet.microsoft.com/en-us/library/ms187087(v=sql.105).aspx

When multiple files are involved, and if these are ideally located on different physical spindles on the underlying disk subsystem, then a rather nicely performing data striping can be achieved for the database.  If proportional fill kicks in and starts to focus on files with more free space then you may get hot spots for those files.  However nowadays with auto-tiering SAN’s, SSD and (abstracted) cloud storage (for IaaS deployments) this is beginning to matter less and less.

However – Lets get into breaking down the proportional fill algorithms!

Continue reading