Pass Summit 2019 Precon Day 1: Performance Tuning With Query Store in SQL Server (Erin Stellato)

Pass Summit 2019 Precon Day 1: Performance Tuning With Query Store in SQL Server (Erin Stellato)

I am currently at the PASS Summit in Seattle, USA. This is a great conference for data professionals. Actually I am already fast asleep as it is like 2:40am in Germany by now. However I would like to use the chance to write up some quick learnings. The day was terrific. Erin Stellato  delivered a great workshop. Here’s a picture of me with Erin Stellato: I would like to share two (rather easy) things which were new to me: Query Store…

Read More Read More

Why I chose int rather than bigint for ID columns

Why I chose int rather than bigint for ID columns

This is my contribution to TSQL Tuesday #99: Dealer’s Choice hosted by Aaron Bertrand this month. I choose door #2 to write about T-SQL bad habits. One thing that comes to my mind immediately is how to handle id columns and which data type to choose. First of all: I don’t think there is any one-size-fits-all-approach you could use without thinking twice. My experience is based on working for a mid-sized business with fairly small sized tables which rarely have…

Read More Read More

What I like about PowerShell

What I like about PowerShell

Introduction First of all, I do not consider myself a professional let alone an expert on PowerShell. However, I think the learning curve of PowerShell is very appealing especially if you have got some background in programming. The first programming language I learned was Java…way back in 2005 at University. Well, frankly speaking my first programming language was Basic on a Commodore C128 computer…but that time as a teen I didn’t really get a deep understanding and ended up retyping…

Read More Read More

SQL Server transaction log and the need for roll backs and roll forwards

SQL Server transaction log and the need for roll backs and roll forwards

A few days ago Kendra Little did a great Podcast about Write Ahead Logging in SQL Server. Key point is that all transactions are written to the transaction log first and then to the data file. This enables SQL server to fulfil the Durability requirement (one of the ACID properties of transactions). Kendra did a good job of explaining but I didn’t get her right the first time and did some research myself on roll backs and roll forwards. The…

Read More Read More

Baselining queries

Baselining queries

The problem During the last few months I have done a lot of performance tuning. Being thrown in at the deep end I did my best and boldly changed existing indexes. Our server had problems regarding too much IO done therefore it didn’t seem right to just add one new index after another. However, doing this for the first time I happened to change an index which made the one query perform better but another query I have tuned a…

Read More Read More

A matter of data visualization

A matter of data visualization

Some time ago my boss asked me for advice on a report he just created. Here’s an example with sample data of what he liked to visualize: For different dates we have a count of objects which had a specific final status. You can reproduce the sample data using the following select statement: query-with-inline-sample-data Goal of the visualization should be to show the distribution across the different status and the overall development of the measured data. Our first approach was…

Read More Read More

Creating a report with a custom shapefile in Cubeware Cockpit

Creating a report with a custom shapefile in Cubeware Cockpit

Earlier I described how to create the custom shapefile. Now I show you how to create some sample data for the report. Now let’s create a report based on that shapefile. You can grab my shapefile here if you didn’t create your own. First of all we need some sample data. Take the following script to create a database with the relevant data. The script should be compatible with at least SQL Server 2008 (nothing special there). If you are…

Read More Read More

Dirty DBA Tricks: Convince SQL Server Cluster < 2012 to use local SSD for tempdb

Dirty DBA Tricks: Convince SQL Server Cluster < 2012 to use local SSD for tempdb

As the title suggests this blog post is about a neat trick, which a decent DBA typically wouldn’t consider if he/she had other options. It worked for me and it was a well thought option to go for. I am not very proud of this trick (therefore “dirty”) however it saved my day and perhaps it’s a valid option for you to go as well. Remember that you use SQL server in a non-supported way doing this and don’t blame…

Read More Read More

Create your own shapefile

Create your own shapefile

This blog post is about creating custom shapefiles to support your own visualizations. A shapefile is a vector graphic used for visualisation of spatial data (like maps) by numerous BI frontends or reporting tools.  The blogpost was inspired by a presentation on “free visualisation with the CSP C8 Frontends” given by Matthias Jüstel and Ivo Pawlow at the Cubeware Infotag 2015 in Rosenheim. The presenters repeated their presentation in a webcast on 25.02.2016 which can be viewed on the Cubeware…

Read More Read More

A new SQL and BI blog!

A new SQL and BI blog!

Here I am…finally starting my own blog. I have been thinking about blogging for quite some time now and it is good to start it now. I am a german database and BI professional. However this blog will be in English for the following reasons: 1. I hope to attract a bigger audience by writing in English. 2. It’s a good practice for me to improve my writing skills.  For english natives: Please be kind if my English might sound…

Read More Read More