SSDT SQLCLR integration : a case of the cart before the horse

I’ve very recently needed to delete an XEvent session’s asynchronous file target as part of tearing down the session. Native support is non-existent. What better than a simple SQLCLR stored proc. Having first tried to accurately derive the file name from the session created time based on details by Jonathan Kehayias’ (blog) post an-xevent-a-day-6-of-31-targets-week-asynchronous_file_target, I […]

Getting out of the ground with TSQL Smells.

At SQLBits I had a number of conversations with a number of people over TSQL Smells, my open source project for static code analysis of SQL Code. The general opinion was that although the concept was sound, the process of getting it running ( and developing it further ) was not documented. Im sure that […]

The maths most SAN engineers do not understand in context of Database Servers

I suppose I write this post to mock some of the things I keep seeing in the industry today. Directly Attached Storage (DAS) has moved on dramatically in the past 5 years, a single SSD has the capability of up to 525MBytes/sec throughput (SATA/SAS 6Gbits/sec), 700MBytes/sec (M.2) and then there is PCIe connected flash operating […]

Increasing ColumnStore compression ratio for archival and “Cold storage”

At my present client, part of the task I have to under take is to re-engineer the “Cold Storage”. This is ~20Terabytes of compressed CSVs going back over 15 years or so. Presently these are held on aging and creaking centera servers that are way past end of life. The solution probably made sense 10 […]

My spaced used scripts

I suppose everyone has a set of scripts they lean on to help make sense of it all. These scripts are my version of sp_spaceused, one to query a specific table and the other at db level. Admittedly these are rough around the edges, that said they quickly allow me to break down a table […]

Please use my filtered index

Not long ago we had a requirement to timeout asynchronous application tasks not completing within an expected period (defined per task type). There is a high hit rate on the table with the number of pending modules at any one time in the 100s, and for this reason timeout processing needed to cause minimal blocking […]

SQLBits, Incremental Statistics and Demo Fail

So another great SQLBits is done and dusted, many thanks to the team, helpers and sponsors for putting on such a great event. It’s always a pleasure and honour to be able to present here and to get to spend time with a great crowd of intelligent SQL Geeks. I have uploaded the slides and […]

An application generated sequential GUID

Recently one of our development teams implemented a GUID based on this SqlGuid Structure. Initially I did not give much thought to the implementation, however later during a code review, noting the choice of uniqueidentifier as the primary key, I began to join the dots. Clearly this is meant to be sequentially generated, and leaving […]

CISCO VPN Client dropping with “Reason 422: Lost contact with the security gateway”

Writing this out of embarrassment but hopefully it will help somebody else if they have the same issue with the CISCO VPN Client or similar. When at my girlfriends I VPN over her WiFi into my client, that’s always worked, but, last Thurs/Friday I started getting issues with the VPN dropping out after just a […]

Entity Attribute Value (EAV) data model meets SQL Server Clustered Columnstore for dramatic performance improvement (Part #1)

<sarcasm>Want to have complete flexibility adding/removing attributes without DDL, store all your data in a single database table and still have performance then read on….</> It’s often the complaint of developers they can’t easily adjust the data model and add more attributes to it (columns), rightly or wrongly it happens and it’s a legitimate requirement. Ordinarily data […]