SQL Azure

Finding Columns with Skewed Data

by

Queries with parameter sensitive plans can perform poorly when an inappropriate query plan is used.

Even if your statistics are up to date, parameter sensitive plans can be caused by skewed data,
so performing a data skew analysis can identify which filter columns might be involved in poor query plans.

I’ve adapted the code found here into a SQL Server stored procedure that can be run across an entire database, a schema, a single table or just a single column.

It should be relatively easy to convert this to other RDBMS.

Here’s an example of the output when run on the Stackoverflow 2013 downloadable database (approximately 50GB):

SQL Server: Script out all indexes in a database

by

Kendra Little has a gist to script out all indexes HOW TO SCRIPT OUT INDEXES FROM SQL SERVER but it didn’t include XML or columnstore indexes, so I’ve forked her gist and added a few things to it. I changed the FOR XML/STUFF trick into STRING_AGG() (which is SQL Server 2017 onwards) for no other reason than I’m not working with any instance versions less than that.

The updated gist is here.

SQL Server: Compressing a Table and/or Indexes

by

I always forget whether the first syntax compresses the NC indexes as well, so posting here so I don’t forget again!

This compresses just the clustered index (i.e. the table data):

-- Just clustered index
ALTER TABLE dbo.Table
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

This compresses all indexes including the clustered index:

-- All indexes including clustered index
ALTER INDEX ALL ON dbo.Table
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

Tuning SQL Server Queries 101

by

First, get an actual query execution plan. Look for warnings in the query plan.

Basic

  • First look for large scans and lookups: these can often be resolved by creating a new index or extending an existing one with additional included columns. Seeks are usually preferable to scans.
  • Then look for significant variance of Actual versus Estimated row counts: you can provide the optimiser with more accurate information by updating statistics, creating new statistics objects, adding statistics on computed columns, or by breaking the query up into simpler parts. Might be caused by ‘parameter sniffing’.
  • Then look for expensive operators in the query plan, especially those that consume memory such as sorts and hashes. Sorting can sometimes be avoided by altering/adding indexes.

More Advanced

Joe Sack has an excellent walkthrough here: The Case of the Cardinality Estimate Red Herring

Date and Time Dimension

by

Almost every fact table in a data warehouse uses a date (or calendar) dimension, because most measurements are defined at specific points in time. A flexible calendar date dimension is at the heart of most data warehouse systems; it provides easy navigation of a fact table through user familiar dates, such as weeks, months, fiscal periods and special days (today, weekends, holidays etc.).

I’ve created a date dimension generator here at Github

It targets SQL Server, but should be easy to convert to other RDBMS.

It features:

  • User defined start and end dates
  • Computed Easter dates (for years 1901 to 2099)
  • Computed Chinese New year dates for years 1971 to 2099.
  • Computed public holidays for US, UK, Canada, Ireland, Malta, Philippines, Australia (with state specific for WA, NSW, QLD, SA, VIC).
  • Date labels in US, UK and ISO formats.

Things to Note:

  1. The [TodayFlag] needs to be updated once per day by a scheduled task (timezone dependent: might need a flag for each timezone).

  2. If you use an unusual Fiscal year (say 5-4-4), it will need to be loaded from an external source (such as an Excel/Google spreadsheet).

  3. The precise start date of the month of Ramadan is by proclamation, so these need to be added, year by year. It is possible to calculate but can be a day out, and can vary by region.

    https://travel.stackexchange.com/questions/46148/how-to-calculate-when-ramadan-finishes

    https://en.wikipedia.org/wiki/Ramadan_%28calendar_month%29

Do You Name All Your SQL Server Database Constraints?

by

If you define a constraint without explicitly giving it a name, SQL Server will generate one for you.
You know the ones, they look something like this PK__MY_TABLE__3213E83FA7739BB4.

Why might that be a bad thing? It makes writing deployment scripts harder because you won’t know up front the names of constraints you might want to refer to.

Michael J Swart describes a query to discover the system generated names in your databases (with a small modification):

SELECT 
    [Schema] = SCHEMA_NAME(o.schema_id),
    [System Generated Name] = OBJECT_NAME(o.object_id),
    [Parent Name] = OBJECT_NAME(o.parent_object_id),
    [Object Type] = o.type_desc
FROM 
    sys.objects o
    JOIN sys.sysconstraints c ON o.object_id = c.constid
WHERE 
    (status & 0x20000) > 0
    and o.is_ms_shipped = 0

According to the sys.sysconstraints documentation page:

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You can query the same information by using the individual views unioned together:


SELECT 
    [Schema] = SCHEMA_NAME(schema_id),
    [System Generated Name] = OBJECT_NAME(object_id),
    [Parent Name] = OBJECT_NAME(parent_object_id),
    [Object Type] = type_desc
FROM sys.check_constraints 
WHERE is_system_named = 1

UNION ALL

SELECT 
    [Schema] = SCHEMA_NAME(schema_id),
    [System Generated Name] = OBJECT_NAME(object_id),
    [Parent Name] = OBJECT_NAME(parent_object_id),
    [Object Type] = type_desc
FROM sys.default_constraints 
WHERE is_system_named = 1

UNION ALL

SELECT 
    [Schema] = SCHEMA_NAME(schema_id),
    [System Generated Name] = OBJECT_NAME(object_id),
    [Parent Name] = OBJECT_NAME(parent_object_id),
    [Object Type] = type_desc
FROM sys.key_constraints 
WHERE is_system_named = 1

UNION ALL

SELECT 
    [Schema] = SCHEMA_NAME(schema_id),
    [System Generated Name] = OBJECT_NAME(object_id),
    [Parent Name] = OBJECT_NAME(parent_object_id),
    [Object Type] = type_desc
FROM sys.foreign_keys  
WHERE is_system_named = 1

SQL Server Error Code 4815 Bulk Insert into Azure SQL Database

by

If you receive error code 4815 while doing a Bulk Insert into an Azure SQL Database (including SqlBulkCopy()), it’s likely you are trying to insert a string that is too long into a (n)varchar(x) column.

The unhelpful error message does not contain any mention of overflow, or the column name! Posting in the hope it will save someone some time.

Azure SQL DB Storage Bottleneck

by

If you are using Azure SQL Databases, you should definitely read this post by Brent Ozar: There’s a bottleneck in Azure SQL DB storage throughput. The bottom line:
the transaction log throughput currently appears to bottleneck at 16 cores!

The bit where he compares AWS costs/relative performance is also an eye opener:

  • 8 cores, 1,991 per month: 64 minutes</li><li><strong>16 cores,3,555 per month: 32 minutes (and interestingly, it’s the same speed with zone redundancy enabled)
  • 80 cores, 18,299 per month: 32 minutes</strong></li><li>Just for reference: 8-core AWS EC2 i3.2xl VM,1,424 per month with SQL Server Standard Edition licensing: 2 minutes (and I don’t put that in to tout AWS, I just happen to have most of my lab VMs there, so it was a quick comparison)