Behind the scenes here at Brent Ozar Unlimited, we’ve got a place where we jot down blog post ideas. Anybody from the company can add ideas, and anybody can pluck those ideas back out and flesh them out into blog posts.
We’ve got a whopping 203 ideas sitting around.
So I cleaned house and deleted a bunch (some of which had been around since 2013!) Here are some of my favorites, and who knows, maybe one of you will go build one of these into a full blown post. Enjoy.
- DROP INDEX can drop multiple indexes in one statement. Who knew? Bear Golightly, that’s who – during our Mastering Index Tuning class, he turned in his homework with a comma-delimited list of indexes, and I nearly fell out of my chair. While reading the syntax in Books Online, I also discovered that you can pass in a MAXDOP hint when you need to drop a clustered index. Amazing.
- 4 T-SQL signs of dangerous technical debt. Using ISNULL/LTRIM/RTRIM because you can’t trust the data in the table, using UPPER/LOWER to work around case mismatches between data and input parameters, pulling specific positions out of strings on a regular basis, and building HTML in T-SQL.
- Query Bucks Reward Program. Put your users into Resource Governor workload pools, but don’t cap their usage. Then, send them an email “rewarding” them for all the terrible queries they’ve run.
- How to visualize index contents. Wanna see what an index looks like? SELECT (key fields, includes) FROM table ORDER BY (key fields).
- Things I Wish Developers Knew About Writing Their Own ORMS. Every now and then, I see somebody reinventing the wheel, and I wish they’d stop and learn a few lessons from Entity Framework, NHibernate, and Dapper first.
- Script to check for database birthdays – look at sys.databases to see when each of your databases has an upcoming birthday based on its creation date.
- SQL Server 2008 Memorial Guestbook – Eulogize it, talk about the good memories, how it was even better than its parents, and that it’s passed on now. Talk about its surviving relatives, SQL Server 2012 and its children. Encourage folks to leave their memories in the comments. Maybe include a photo gallery of its career.
- How the Coast Guard handles risk – before dispatching boats or helicopters to rescue someone at sea, the Coast Guard uses a Green-Amber-Red (GAR) risk assessment method. They look at supervision, planning, team selection, team fitness, environment, and event/evolution complexity to calculate a total risk score. Then it’s up to the base leadership to decide if the mission should go forward as-is, or change one of the variables. This PDF gives you an idea of how it works.
- The Biggest SQL Server Annoyances (and How to Fix Them) – maintenance plans should be switched to Ola Hallengren’s scripts, for example.
- A Tale of Two Databases – do a riff on A Tale of Two Cities.
- Why Higher Maxdop Isn’t Always Faster – take a single query and run it with higher maxdop threads, showing how performance growth isn’t linear (and how it impacts concurrency when multiple queries run simultaneously.)
- Things You Can Do with SQL Server (But Probably Shouldn’t) – queueing, using it as a file server, scaling out with distributed views, using it as a caching engine.
- The Developer’s Guide to the Plan Cache – what it is, what gets stored, when things age out, why it’s difficult to find specific queries or specific tables.
- Bare minimum requirements for setup – like Naked and Afraid. This won’t keep your instance from going offline, but will keep it alive in the jungle for a few days.
- “But nothing changed!” – things that will cause performance to slow down without code changes. (Bad plans, slipping plans, bad stats, concurrency changes.)
- The Best Command You’ve Never Heard Of – how BACKUP WITH NORECOVERY works, and when to use it.
- Are all hints bad? – which query hints are okay to use, and which ones are probably bad ideas that will come back to haunt you later as your data grows and your database changes.
- Why Table Modifications Stink – demo changing a clustering key and measuring when it’s faster to start a new table instead and push the data over.
- Maslow’s Hierarchy of Needs for Data Features – starting with OLTP design, performance, materialized reports, ad hoc reports, and rich analytics & data science.
- Memory Metrics 101 – page life expectancy, cache hit ratios, and stolen pages.
- Does updating statistics invalidate or erase the plan cache? – for example, when an object doesn’t meet the thresholds for getting a stats update, is anything affected?
- Development environment architectures – how many dev environments should you have, and what should they be configured like? Should they match prod? Where should you test patches and deployment scripts?
- OPTION (RECOMPILE) and Recompiles/sec – you would think that OPTION (RECOMPILE) queries would drive up the Perfmon counter for recompiles per second, but purposeful recompiles are compiles.
- 3 Signs Your Vendor Database was Created on Oracle – optimistic locking, heaps, and query hints.
- The performance impact of verifying backups – people just check this checkbox when setting up their backups because it sounds good, but it performs terribly.
- Worst Sysadmin Troubleshooting Steps – reboot, set up a job to automate the reboot, defragment the drive, run it under SA….
- T-SQL Habits of Experienced Professionals – ending statements with a semicolon, properly specifying N on NVARCHAR strings, error handling, logging, debugging options for dynamic SQL.
- Connection String Cheat Sheet – setting the application name, AG parameters for read-only, mirroring secondary, etc.
- Your Files are More Secure Than Your Database – trolling title, but it’s amazing how often people lock down file servers but leave the database wide open where everyone’s a sysadmin.
- The perils of using Database Mail – especially as a production customer-facing notification system.
- The PRIMARY file group is special – things that go into PRIMARY that you can’t control.
- Filtered index overhead – whenever we explain filtered indexes, everybody asks the same first question: is there a performance impact during inserts for evaluating the WHERE clause?
- Forced parameterization overhead – I have this sneaky feeling that if I built really big strings and executed them with SQLQueryStress, I could conceivably make performance worse. Just a thought exercise though.
- What should I do about memory dumps? – a lot of folks seem to think they should be analyzing these by hand, but you don’t have enough time to get good at that, fast enough. Call Microsoft right away.
- How do I choose a backup tool? A decision tree based on database size, database quantity, server quantity, and staff workloads.
- How can I see temp statistics on my AG replicas? If you’re worried about why queries are getting different query plans on different replicas, this would help.
- Database greeting cards – I’m sorry for your loss. Congratulations on the new instance.
- April Fool’s: fake KB articles – build a page template that looks like support.microsoft.com and pen unbelievable stuff.
Whew. I’m exhausted just reading that list!
Erik Says: Can you believe there was a blog post I wouldn’t write?