In my Twitch & YouTube live streams, some questions seem to come up every week. I’m not expecting this post to stop ’em by any means – most folks on the live stream don’t seem to read the blog regularly – but I thought I’d share ’em here.
Q: Why is the same query sometimes fast and sometimes slow, depending on where I run it?
It’s most likely parameter sniffing.
Q: My index maintenance jobs take forever. What should I do?
When you rebuild a 50GB table’s indexes, you’re basically reloading that table from scratch. SQL Server has to make a brand-new copy of the table on new data pages, and it logs all this stuff in the transaction log – which means your backups take longer and your Availability Group gets way far behind.
If you’ve been rebuilding indexes nightly, consider easing that off to weekends instead. If you’re worried that will affect performance, you’re probably mixing up the difference between rebuilding indexes and updating statistics. Read about how out-of-date statistics cause bad query performance, and then consider doing daily stats update jobs rather than rebuilding your indexes.
The more you stick around my blog and live streams, the more you’ll see me point out that daily stats updates are a bad idea for most databases too, but in the grand scheme of things, they’re still way better than daily index rebuilds.
Q: Which cloud provider is better for SQL Server?
Companies don’t pick their cloud hosting based on one specific database. They pick on an array of things including overall cost, available services, existing licensing agreements, etc. What’s better for you might be worse for somebody else.
Having said that, at this moment in time:
- If you run SQL Server in a VM, you can get more VM selection & performance at AWS
- If you want to rent SQL Server as a service, Microsoft’s Azure SQL DB and Azure SQL DB Managed Instances are quite a bit ahead of Amazon RDS
Q: What’s that thing on your wall?
It’s an Every Day Calendar by Simone Giertz. I use it to track the days that I work on building stuff that will produce passive income, like writing new classes or designing new apps. It’s a helpful visual reminder that I need to stay focused on building for the future.
Q: How should I get started learning SQL?
One of the nice things about SQL is that it’s a mature language. It changes very slowly and gradually over the years. Because of that, there’s a ton of training material out there available at a very low cost, like the edX classes on SQL or the book SQL for Dummies.
If you don’t find yourself immediately interested in the material, bail out and pick another author/speaker.
Q: Why would someone choose SQL Server over MySQL or Postgres?
Microsoft SQL Server costs about $2,000 USD per CPU core for Standard Edition, and around $7,000 per core for Enterprise Edition. What do you get for all that money? I’m going to give you a few reasons why companies choose SQL Server. I know you’re going to be tempted to argue with me, and you might have some great points – but you don’t need to convince me, dear reader. I’m a huge believer in other database platforms – I’ve already written about why we use AWS Aurora, plus we also use DynamoDB and MySQL. I’m just explaining why other companies often choose SQL Server, like my clients:
- Support – some companies want to know that they can call the company who wrote the database and get support 24/7.
- Enterprise-friendly features like auditing, encryption, and single sign on security – for example, if you’re a hospital, you may need to track everyone who queries George Clooney’s medical records.
- High availability and disaster recovery – SQL Server has so many built-in features around clustering, Availability Groups, log shipping, etc. that make it easier to support higher uptime. I’m not saying it’s easy by any means – but easier than some open source alternatives.
- Inertia – because a lot of companies just already standardized on SQL Server, have a big install base of it, have a lot of staff who know how to develop for it and manage it.
Q: You use a Mac – how do you manage SQL Server?
With a jump box, a VM set up with all of the tools I need. When I do long term work with a client, I have them set up a jump box for me. That way I can just use a VPN client and a remote desktop client from anywhere.
When I’m teaching training classes, I spin up fairly beefy (8-core, 60GB-RAM, 2TB SSD) VMs in the cloud with SQL Server. That approach lets me run really ugly performance workloads, maxing out CPU and storage, without worrying about the high workload interfering with my video stream.
Q: Why don’t you use Azure Data Studio during streams?
For one, the execution plan experience isn’t anywhere near as good as SQL Server Management Studio. It’s not bad, it’s just not complete – there are tons of property details you just can’t see with ADS. Since I teach performance tuning, I need those details.
Also, I gotta meet my audience where they are. The vast majority of SQL Server professionals are still using SSMS, not ADS. If I’m going to teach you something inside the span of an hour or a 1-day class, I need to pick my battles.
I do use Azure Data Studio a ton myself because I split my time between Microsoft SQL Server and AWS Aurora PostgreSQL.
Q: Will you teach classes on other databases?
Never say never, but it’s pretty unlikely. I’m terrible at Postgres.
I wouldn’t wanna teach a Postgres tuning class until I could explain how the optimizer works. I don’t have plans to even learn that, let alone build classes to explain it.
There’s another question that comes up constantly, but it deserves its own blog post, so stay tuned for that this week.
Another point is the third party applications available that support MSSQL. So if you already have Microsoft support contracts, who wants to haggle about one for a database that is used for one or two applications if it’s already available on MSSQL?
I think you can present on many topics and people would watch (and learn), case in point, I’ve enjoyed and learned from your coding sessions in the First Responder Kit and GitHub. I think Jorris (Richy) would resonate well with us developers seeing the work he has done, maybe a live stream with both of you one day? Get to see the man behind the curtain?!?!
“The mediocre teacher tells. The good teacher explains. The superior teacher demonstrates. The great teacher inspires.” –William Arthur Ward
Would it be fair to say that another reason to go with SQL Server over MySQL, Postgres, etc. is the lack of robust reporting/analytics tools such as SSRS/SSAS?
Nah, reporting tools are fairly easy to come by. For example, Power BI works on MySQL and Postgres.
J.V. – let’s not use that term in that way. It feels derogatory.
You are too sensitive 😀
I loved your Power BI Dashboard, Understand the support issues you had. How about adding a Power BI member to your team?
You mean pay an employee a full time salary to…do what exactly?
Obviously I don’t know your full business model. It would seem to fit with your services but I may be wrong. All of your Blitz collections really benefit from the front end, and Solar Winds is going to be loosing some customers.
Gotcha, no, it’s not a good fit for my services. The sp_Blitz% stuff is free – I don’t make money from those, not in a way that I could pay a full time employee to work on them.
Fine. Whatever. I’ll download Azure Data Studio and give it a try.
Maybe in a few releases it’ll even have those missing execution plan details.