Hi. I’m Dave, and I’ve got a bit of a conundrum: I’m looking for my next career opportunity, but I’m not a fan of resumes and online job applications. Maybe you aren’t either? Would a blog post be better for both of us? Let’s find out!
I’ve been working with SQL Server for more than twenty years as a developer, a database administrator, and a consultant. I enjoy performance tuning and optimization. It’s so satisfying to take a slow process, stored procedure, or ad-hoc query that is painfully slow and make it fast for end users. Here are some of the tools and features I use on a day-to-day basis:
- SQL Server Management Studio: could you take me seriously if I didn’t mention SSMS? It’s open and running on my laptop virtually 100% of the time.
- SSMS Code Snippets: these are so convenient. I found myself running the same scripts so often, I started writing my own custom snippets, which I use repeatedly throughout a typical day. There’s a library of them here.
- SentryOne Plan Explorer: analyzing query execution plans with SSMS is OK. It’s so much better with Plan Explorer. I started using this tool after SentryOne (now part of SolarWinds) made the tool free in 2016. Much like SSMS, this app is always open on my laptop.
- Query Store: this is one of the best “recent” SQL Server features in my opinion. It helps me identify queries that are using the most resources, including those with execution plans that have regressed. Execution plans, runtime stats, and even wait stats can be persisted for analysis. Oh, and there’s the ability to force a specific plan or force a query hint. Good stuff!
- Columnstore Indexing: it’s not just for data warehouses and OLAP workloads. I’ve gotten some great results using columnstore for OLTP workloads too.
- ROW/PAGE Compression: surprisingly, I find compression gets overlooked in a lot of environments. ROW compression is practically a free lunch. I’ve discussed this with some other SQL pros in the community and many agree ROW compression can be (and should be) enabled by default. Don’t sleep on PAGE compression either. I used to be reluctant to use it, thinking it adversely affected CPU usage. I no longer feel that way. I use PAGE compression much more frequently now.
- T-SQL refactoring: this is where the big gains often lie. All things being equal, when I dive into a new environment, I can usually squeeze more performance out of a stored proc by refactoring code than by throwing hardware or a higher pricing tier at it. Some things like non-sargable WHERE clauses or implicit conversions are fairly straightforward to revise and improve. Other times the work goes much deeper, like unraveling badly nested views or finding ways to eliminate excessive use of user-defined functions. It’s always an adventure!
- Dynamic T-SQL: this seems to pop up a lot in my daily activities. Linked server queries are one example. “Pushing” queries through to a remote server (via EXECUTE AT or OPENQUERY) can make more sense than running them locally. Another example is code that’s just too tedious to write fully by hand. I let my queries create the code for me…code that writes code. My XEvent Shredder repo has scripts for “shredding” the output of of sys.fn_xe_file_target_read_file for an Extended Events session target file. Those are good examples of my style for building queries on the fly.
- SolarWinds Database Performance Analyzer: for day-to-day monitoring and insights for optimization.
A lot of people think of me as a DBA or just a “SQL guy”. But I’m a developer too. I started out my career with VB6 and made the transition to .NET. I was a full time or part time C# developer for at least a decade. That .NET background serves me well when I work with other developers. It also comes in handy for automation and PowerShell—knowledge of namespaces, classes, and static methods are a tremendous help with scripting efforts. I don’t do too much C# work these days, but I did pick up a certification in 2016. Every now and then I’ll create something interesting enough to warrant a Git repo. Here are a few:
I didn’t know anything about R until SQL Server R Services (later renamed Machine Learning Services) arrived with SQL Server 2016. I decided to try to learn R, and blogged about my experiences on ZeroToR.blogspot.com. I also earned a few Data Science certificates based on R (R Basics, Visualization, Probability, Inference and Modeling). As a SQL developer, I haven’t found a lot of uses for R, but I’m far from a data scientist. Still, R does some things much better than T-SQL does, such as pivoting data, authentic Regular Expression pattern matching, or encoding/decoding HTML strings.
Providing realistic non-production data for developers to use in non-prod environments has been a necessity for some of my clients. It’s also a bit of a challenge. Using R and the Generator package, I created a solution for this. I wrote a couple of blog posts and made a Git repo with all of the R and T-SQL code:
- Generating Fake Data in SQL Server With R
- Data Obfuscation for SQL Server
- SQL Server Data Obfuscation Repo
Is there a role out there for someone that likes T-SQL and development? I sure hope so!