admin

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

SQLFrontline Freemium Edition

by

I’ve recently changed what I was calling the ‘Demo’ version of SQLFrontline, to a ‘Freemium’ model. The demo version only displayed one recommendation result in each of the four severity categories (Critical, High, Medium, Info).

The free version does not include all the features of the paid premium version obviously, but still provides some useful recommendations, providing advice on 40 checks.

Both use the same lightweight metadata collection.

The Free Version:

  • Performs 40 checks (out of 350+), but doesn’t show all affected objects if a check results in a recommendation
  • Deletes all collected metadata after collection
  • No reminder list is shown
  • Does not display a list of issues that are no longer present since last collection
  • Sends a single email for all servers
  • No database specific recommendations are made
  • Can only collect metadata for a limited number of servers

The Premium Version:

  • 350+ checks performed across the categories of Configuration, Reliability, Performance, Security, Server Info, Table/Index Design
  • New checks are constantly being added
  • Reminder list of recommendations that have been made previously and not yet fixed
  • List of issues fixed compared to the last collection
  • Can choose how long to store collected metadata, so that point in time reports can be made, along with automated estimates of DB growth over time
  • Can send 1 email per server or a single email for all servers
  • Ability to ‘mute’ recommendations on an individual basis, or entire check (for non-critical checks)
  • No practical limit on the number of servers

If you want to try it out, click this link to request a free access token.

Once you have an access token, here’s how to run it: How to run SQLFrontline

Don’t Embed SQL into SSRS Reports

by

Reasons not to embed SQL in SSRS reports (.rdl) and create stored procedures instead:

  • Easier to version control in SCC
  • Better visibility in the database
  • Easier to debug
  • Easier to fix any performance problems (and to hot fix, if necessary)
  • Easier to re-use functionality (which is often lengthy to produce) with other systems/APIs etc
  • Stored Procedures get given a name that accurately describes what they do
  • Easier to gather execution statistics
  • Easy to see what parameters are used to filter
  • Can be secured with explicit permissions
  • Easier to write automated testing against a stored procedure

All seem fairly obvious, but it’s surprising how many people still embed SQL into SSRS reports.

Configurable Retry Logic in Microsoft.Data.SqlClient

by

Microsoft have recently released a long awaited retry mechanism for .NET SqlClient

I’m a fan of Polly for retry logic:

Polly is a library that allows developers to express resilience and transient fault handling policies such as Retry, Circuit Breaker, Timeout, Bulkhead Isolation, and Fallback in a fluent and thread-safe manner.

It will be interesting to see how they compare in terms of ease of use.

Configurable retry logic in SqlClient introduction

Windows File Splitter

by

Unlike Linux systems, Windows doesn’t have a built-in command line file splitter. Splitting large files into smaller chunks is something you often want to with data warehouses (such as Snowflake), in order to be able to use multiple threads for better bulk loading performance.

I saw a post by Greg Low, SDU_FileSplit – Free utility for splitting CSV and other text files in Windows where he had created one for Windows, but it wasn’t open-source.

I decided to create an open-source file splitter for Windows.

It’s a standalone .NET 5.0 executable, supports wildcards, can recurse sub-folders (careful with that option!) and automatically gzip compress output files.

Example use:

FileSplitter.exe -i c:\temp\*.csv -m 100000 -c -o c:\temp\TestFileSplitter

Splits all the files in specified folder c:\temp with extension .csv, and splits into max. 100K lines per file, storing the output files in folder c:\temp\TestFileSplitter

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

Babelfish for PostgreSQL

by

This has the capacity to be huge:

Babelfish for PostgreSQL is an Apache-2.0 open source project that adds a Microsoft SQL Server-compatible end-point to PostgreSQL to enable your PostgreSQL database to understand the SQL Server wire protocol and commonly used SQL Server commands. With Babelfish, applications that were originally built for SQL Server can work directly with PostgreSQL, with little to no code changes, and without changing database drivers.