[Video]: Office Hours at Diamond Beach

Videos
2 Comments

I took a break from playing with glacier leftovers at Diamond Beach to answer the questions you upvoted at https://pollgab.com/room/brento/.

  • 00:00 Introductions
  • 1:21 Null Pointer: When we move to Azure SQL DB, should we worry about having all our data and backups in one place?
  • 03:27 Josh: We’re using indexed views, and we have to use the WITH NOEXPAND query hint. Am I doing something wrong?
  • 05:07 Uncompressed DBA: How should I troubleshoot PAGELATCH waits for a query?
  • 08:22 Recap

Updated First Responder Kit and Consultant Toolkit for September 2021

First Responder Kit
0

I’ve slowed the First Responder Kit update frequency down to once every 2 months. I know from firsthand experience working with clients that folks just can’t patch quickly enough, hahaha. Folks who want to be on the bleeding edge updates can always use the dev branch in Github, too, getting every new fix the moment it’s merged.

Wanna learn how I use it? Register for my free one-day class on Oct 19th or 20th on How I Use the First Responder Kit, or buy the recordings.

How I Use the First Responder Kit
Wanna watch me use it? Take the class.

To get the new version:

Consultant Toolkit Changes

I updated it to this month’s First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you’ve customized those, no changes are necessary this month: just copy your spreadsheet and querymanifest.json into the new release’s folder.

sp_Blitz Changes

  • Fix: if databases aren’t online, don’t alert on non-default database settings. For example, if someone’s got a database offline or in restoring state, they shouldn’t be alerted about the delayed durability setting on that database. (#2984, thanks David Schanzer.)
  • Fix: added 1204’s explanation when the trace flag is enabled. (#2985, thanks Erik Darling.)

sp_BlitzFirst Changes

sp_BlitzIndex Changes

  • Improvement: scripting for unique constraints. (#2950thanks Erik Darling.)
  • Improvement: new informational (priority 250) check to tell folks when optimize_for_sequential_key is enabled for an index. Only shows up in Mode 4 since it’s a low-priority action that you don’t usually have to do anything about. (#2963 and #2991thanks Erik Darling and FlatlandR.)
  • Improvement: new unindexed foreign key check. (#2964thanks Erik Darling.)

sp_BlitzWho Changes

sp_DatabaseRestore Changes

  • Improvement: new @SetTrustworthyOn parameter lets you enable this on newly restored databases. (#2981, thanks jesusnac.)
  • Fix: if the database status isn’t already in restoring, try to set it into single user mode. (#2960, thanks jesusnac.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.


[Video] Office Hours at the Seltjarnarnes Lighthouse

Videos
0

You posted and upvoted SQL Server questions at https://pollgab.com/room/brento, and I went out for a walk on the Seltjarnarnes peninsula to the lighthouse to answer ’em.

Here’s what we covered:

  • 00:00 Introductions
  • 00:58 Gio: Any known gotchas from using Windows Update to apply CUs to SQL
  • 02:17 AlwaysLearningDBA: Hello Brent. A friend of mine wanted to know what camera/system do you use when you are outdoor doing Q&A office hour sessions? He is just curious as the camera follows your movement as if someone is moving the camera physically and he thinks it’s awesome.
  • 03:34 PGA: Hi Brent If I have firstname lastname in one index and city,age on other index if my query is Order by City , Age My second index will be used?
  • 04:47 Inpasta DBA: Hi Brent! Do execution plans replicate over to the secondary in an AG? Wondering if a failover, planned/unplanned, occurred would the plans be the same. Thanks for what you do for the community!
  • 05:49 Jack: Hi Brent, with all the certifications available now for Azure, AWS and GCP has your opinion on the value of certifications changed?
  • 06:02 I’m a potato: Hi Brent! Is there a way to know the health of your statistics on SQL server and prevent their most common issues?
  • 06:44 Thomas Horner: In your experience, is using Always-On Availability Groups require more DBA monitoring and maintenance (i.e. babysitting)?
  • 07:26 Rick Lively: How should we capture a “baseline”, or a series of captures, with the Blitz scripts that you wish would have been collected to help understand performance problems when the server is under stress at a later time?
  • 10:07 Willem: Do you know of any success stories of companies that have made a the move to Azure with a large SQL Estate(+- 80 Enterprise Servers and up)? We’re figuring out how to attempt this, but there seems to be so many pitfalls to be aware off.
  • 11:47 Stockburn: Hi Brent any advice on deadlocks where the two processes are an update and a delete, they both end up hitting the clustered primary key and then as you put it, one of them gets a bullet from the deadlock monitor. Thanks for these office hours!
  • 13:04 catmanjan: Applications now have tech like kubernetes and are more or less hands off. Why don’t database engines do this too?
  • 15:17 LazyD: Hi Brent In upgrading from SQL2014 to SQL2019 what can be done about CLRs so that they don’t “break” due to changes in SQLCLR security since SQL2017.
  • 15:43 VegasDBA: What do you think of RedGate SQL Toolbelt? A friend insists that it’s a necessary purchase to help production DBAs manage an environment. Any other similar tools or utilities you’re a personal fan of?
  • 17:13 NullPointer: I have been fighting with tuning a query/view, this view joins to another view that when run by itself is super fast (with good estimates) but when I join it to a larger query all the estimates are way off
  • 17:58 Dave: What would you think about a disaster recovery plan that involved taking differential backups of the production server every 2 hours? Asking for a friend.
  • 20:03 Scott McFadden: Hi Brent, appreciate your SQLServer book recommendations. They are spot on. Do you have any book or training recommendations for learning PostgreSQL?
  • 21:28 Champaign DBA: Brent, I read your 5 part blog on foreign keys, and your concern about locking when using cascade delete. Wouldn’t that not be an issue when using read committed snapshot?
  • 22:14 Pat: Hi Brent! I’m a accidental DBA. My DB monitoring software often alerts on “OS paging”. What’s “normal” to hone in on?
  • 23:17 Seven: How does sampling work for statistics? If a FULLSCAN has been specified previously…
  • 23:37 SpongeBob SquarePants: Do you have any recommendation for tools specific for ODS in SQL Server?
  • 23:49 David: Do you have an tips to deal with people who take other peoples advice or tips as there own in the workplace? Here I am with 1.5 years experience in SQL, teaching people with 5+ Years experience. But when they tell the Boss it them who came up with query. Some don’t know union
  • 26:01 Khaled Budajaja: Duplicates on Primary Key Column. Some of my customers on SQL Server 2014 have this weird issue.
  • 26:45 SQL_Deadwood: How do I perform index maintenance on a synchronous AG database with heavy fragmentation (there was no maint. job) without, well, ruining everything? On-prem VM.
  • 28:18 SQL_Deadwood: According to the tricksy documentation hobbit, “There are specific but uncommon scenarios when…index maintenance may be needed in Azure SQL Database” – what are they?
  • 29:23 Recap

[Video] Office Hours: Ask Me Anything in the Reykjavik Harbor

Videos
0

You posted and upvoted questions at https://pollgab.com/room/brento to pick what you’d like to see covered in a Q&A session:

Here’s what we covered:

  • 00:00 Introductions
    00:26 Mehdi: Hi Brent! What is the first step when SQL Server is overloaded? But we can still connect to SQL Server by SSMS.
  • 02:36 Allan: Do you think learning SSIS is still worth or should everyone hope on to AWS?
  • 04:15 Igor: Hi Brent. In a recent office hour you had a question about changing the char data type column into varchar. In your anwser you mentioned that “she” correctly spoted that char data type should not be used and that it should be avoided in the future development.Why is that?
  • 05:49 1440×1080: hi Brent ! Is there a way to remove completion time of JUST a specific query(something like nocount) and not use the “global” settings in ssms (for all queries)
  • 06:35 Steve: What is the funniest mistake you have seen a Developer make that took down the whole database? I was once writing an sp to delete data from multiple tables with a Begin trans and still don’t know how but it took down the DB offline for few hours for recovery.
  • 11:03 Alexey: Hi Brent. How hard would it be for Microsoft to rename the ‘master’ database in SQL Server to something else? (Just as the did on GitHub – renamed ‘master’ branch to ‘main’)
  • 14:17 Uncompressed DBA: Hi Brent! If I use compression for some tables where the keys are ever increasing, does that increase the odds to encounter PAGELATCH_XX waits?
  • 15:35 AGuyNamedJim: Hi Brent! Do you feel “with encryption” is the best remedy in an Enterprise software context where leadership argues against Stored Procedures & Functions as it means our code is on the customer’s servers? (That’s what we did before and it was painful from a support perspective.)
  • 17:49 Torben: Schema lock. Select * Into NewTableName From OldTableName Will that lock the schema while the select is running, i think if oldtable contains millions og record, it can take some time, and would it be better to run it first with “where 1=0”, and change it to insert select syntax
  • 18:52 AsianDBA: Love your work Brent! When would you choose read scale (clusterless) always on over traditional always on availability groups?
  • 20:24 Zack Jones: Follow up to my varchar(max) to varchar(2500) question. As an aside the max usage in that one column is only 1000 characters. Is there a performance impact for including a varchar(max) as an included column in a covering index? I’m basically trying to optimize for reports. Thanks
  • 21:41 Zack Jones: Is there a file size for a .MDF file when you should consider adding .NDF files? If so how’s the best way to approach such a task? What’s the benefit of doing so? Is an 18GB MDF file a bad thing?
  • 23:33 Still_a_Clarion_programmer: I’m a slob and am never consistent with indenting, columns stacked or on-one-line, capitalization of keywords, etc. Do you ever use a prettifier such as Redgate’s SQL Prompt? (And if so, which?) Or should I just try to develop some self-discipline in my old age?
  • 24:53 Recap

[Video] Office Hours: Ask Me Anything About SQL Server at Hallgrímskirkja

Videos
6 Comments

I hang out by Hallgrímskirkja, the modern church in Reykjavik, and take your highly-voted questions from https://pollgab.com/room/brento. Here’s what we covered:

  • 00:00 Introductions
  • 00:18 __normally_weird (Sean C): Can you dispel the now prevailing myth that you and your wife are the only8 people in Iceland at the moment?
  • 01:33 Andy Leonard: What “bugs” exist in SQL Server 2019?
  • 04:22 Michael Devor: You have said you want to retire (relatively soon). How have you gone about preparing to support yourself and your wife for your retirement.
  • 06:03 Kevin M: Have you encountered sp_BlitzCache, QueryStore, and SQL Profiler showing a query returning a large rowcount, high CPU time, and large read count, but running the same query from SSMS shows less rows, reads and less CPU?
  • 07:56 Philip Clark: Can I Just say, you’re awesome Brent. Hope I get to meet you in person one day!
  • 09:24 Mike Byrd: Possibly a dumb question, but is it possible to redirect output from SET STATISTICS IO ON to a file or table?
  • 11:35 Random Name: A certain security software vendor is pretty adamant that the application should be installed on the same VM as the SQL Server because “there could be a lot of traffic.” Any guiding principles for when (if ever) to have the full application installed onto the database server?
  • 13:15 Martin: Hi Brent, I know that using MERGE is advised against. Are there any other operators that are problematic?
  • 15:10 Philip Clark: What do you recommend these days in terms of what processor (Intel/AMD) with clock speed and number of cores to choose? Minimum to start with for an Enterprise 2019 dedicated server? And how would this differ when specing an Azure VM for SQL?
  • 18:06 Michael Devor: Can you have too few indexes and could that be my friends issue?
  • 19:32 Seven: I asked a question earlier about stats fullscan; maintaining the integrity of full scan during auto updates. i guess what i meant was if enough rows have been modified to trigger an auto stats – if it could “append” a fullscan rather than replace it, or ignore it (no recompute)
  • 20:40 CacheTellMeTheTruth: How do you find which COLUMN stats sql server used to create an execution plan? I’m trying to find a list la col stats “potentially” useless (double quotes are mandatory here )
  • 23:11 Daniel Moll: Hi Brent. We are monitoring sys.dm_os_sys_memory and did not have any warnings since I activated “lock pages in memory”. now we have several events (high=0) a day. (Why) is this a problem?
  • 25:38 Juan Falcon: Is there and automatic way to update OpenSSL which SQL Server installs with Python Services?
  • 25:54 Igor: Hi Brent, what is the best way to reduce table size, after drop column?
  • 27:00 __normally_weird (Sean C): I have an interest in contributing to the community to “pay it forward” as well as selfishly gain experience to meet a long term goal of becoming a performance tuning consultant. How can I, as a mere mortal, positively impact the community beyond answering stack questions?
  • 29:04 Aaron: I have several queries that generate plans that have an Index Seek + Lookup for their respective tables. What are some key things to take into consideration?
  • 30:16 marcus-the-german: Hi Brent, a friend of mine would like to know how she can determine that a parallel executed query is executed on one NUMA node? Let’s assume we have a 2 x 8 core server and MAXDOP is set to 8
  • 31:24 Artur: I’m doing the D.E.A.T.H. method. Is it a good idea to test any index changes in non prod environment before I apply them in prod environment? 32:28 Artur: Have you ever used Distributed Replay?
  • 34:11 Mike B: I’m testing a solution using triggers to compress LOB data on insert with the COMPRESS function with a view containing the DECOMPRESS function. Thinking to trick the app by replacing the table with a view and avoid changing code. Seems to work, but any gotcha’s to look out for?
  • 35:37 Andrew from Canada: Synching a recent stack users table to an older stack DB to do some tests. It takes a long time and the system appears underutilized. why? by design?
  • 37:30 qTechnik: How to monitor cpu load by each database on sql server? Don’t like statistics from plan cache because different lifetime for each plan in cache.
  • 39:24 marcus-the-german: Hi Brent, do you have any experience/recommendation on receive side scaling (RSS) on SQL Servers?
  • 40:01 David Nelson: What are the options for using a source control system for versioning of stored procedures (like git, svn, etc)
  • 40:33 Steven: Hi Brent, thank you for the anwser on “why does a join spill to tempdb”. The parallalism is nearly evenly distrubted, so the operator was not allocated enough memory. Is it possible to allocate more memory to a specific operator in a query or do I have to use min_grant_percent?
  • 41:16 AlwaysLearningDBA: what do you suggest to transition existing career as production DBA into Architect or Data Science role?
  • 42:20 Kyaw Than: The app admin wants to set system DBs to compat mode 2016. I could test it out but would appreciate your opinion.

[Video] Office Hours: Ask Me Anything About SQL Server in Stokkseyri

Videos
2 Comments

Before Fjöruborðið opens for lunch, I answer a few of your questions from https://pollgab.com/room/brento including:

  • 00:00 Introductions
  • 00:29 Peter: What is your opinion about the SQL generated by Entity Framework?
  • 02:00 Peter: When would you switch from EF to stored procedures?
  • 02:45 Always Learning DBA: What are free resources to help me transition into an architect or data scientist role?
  • 04:43 How is the auditing and monitoring different for Azure SQL DB?
  • 06:33 Recap


[Video] Office Hours: Ask Me Anything About SQL Server On My Patio

Videos
1 Comment

I sit on the porch and discuss your highly voted questions from https://pollgab.com/room/brento.

Today’s discussion includes:

  • 00:00 Introductions
  • 00:27 DK: What was your Gordian Knot problem?
  • 03:25 George: How exactly does locking work on INSERT INTO…SELECT FROM?
  • 05:48 Michael: Can I tell if hyperthreading is turned on or off?
  • 07:25 Party People: What are the most common anti-patterns you see?
  • 10:20 Recap

 


[Video] Office Hours: Ask Me Anything at the Westman Islands Harbor

Videos
4 Comments

You upvoted questions at https://pollgab.com/room/brento, and today I’m at the harbor in Heimaey to cover ’em.

Questions we talked through:

  • 00:00 Introductions
  • 01:00 Zach: What happens when I change a VARCHAR(MAX) to VARCHAR(2500)?
  • 04:26 Scott: What tools do you recommend for tracking down locks involved in blocking?
  • 07:23 Pointing out the beluga whale sanctuary
  • 07:58 Ice Horse Rider: Why aren’t you a Microsoft MVP?
  • 10:20 Kirby: What does changing MAXDOP do?
  • 12:44 Mr SQL Seeks: Should I upgrade to 2017 or 2019? What compat level do I use?
  • 14:19 Farooq: How do I get notified when SQL Server needs additional CPUs?
  • 15:45 Recap

,


Who’s Hiring in the Database Community? September 2021 Edition

Who's Hiring

Is your company hiring for a database position as of September 2021? Do you wanna work with the kinds of people who read this blog? Let’s set up some rapid networking here. If your company is hiring, leave a comment.

The rules:

  • Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
  • An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
  • Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
  • Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
  • Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
  • Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

I close the comments here after 1 week, and publish another post each month in the Who’s Hiring category so y’all can get the latest opportunities.

If you want to be notified as people leave new job comments here, there’s a “subscribe without commenting” option where you leave a comment.


[Video] Office Hours at the Eldfell Volcano Crater

Videos
2 Comments

You posted and upvoted questions at https://pollgab.com/room/brento, and I ascended – well, drove into – the Eldfell Volcano in the Westman Islands to answer ’em:

Questions we covered in the video:

  • 00:00 Introductions
  • 01:55 Do you notice big improvements for physical servers over virtual servers?
  • 05:14 Why aren’t my SQL Servers using all of the available RAM?
  • 07:16 Do you recommend periodic SQL Server restarts?
  • 09:36 How many times have you encountered a SQL Server in perfect shape that needs no work?
  • 11:55 Should I use Extended Events or Profiler? I find the XE interface dreadful.
  • 14:11 Why do so many people keep trying to ask questions in YouTube chat rather than PollGab?
  • 15:30 Is there a way to influence which keys SQL Server uses as the range high key in statistics?
  • 17:56 Why does a hash match join spill to TempDB when the estimates are accurate and the used memory is lower than granted?
  • 20:20 What SQL Server feature do you miss the most when working in Postgres?
  • 20:32 What Posgres feature would you love to see in SQL Server?
  • 22:30 My query execution plan takes 3-5 minutes, but transferring millions of rows to SSMS on my laptop takes more time. How do I make it go faster?
  • 24:09 When I’m using log shipping, can I still do separate full and log backups?
  • 25:05 Can an index with the same name as a column cause problems?
  • 25:21 Should I use backup devices rather than files?
  • 26:24 Do you have kids?
  • 27:17 How do I deal with a manager who insists on choosing technologies that IT should use?
  • 30:55 How much training time should I let my direct reports do?
  • 32:24 How should we pass client information to SQL for the session?
  • 33:40 Have you ever told someone to turn on trace flag 4199?
  • 35:00 Recap

[Video] Office Hours: Ask Me Anything About SQL Server at the EVE Online Monument

Videos
0

I sat down at the EVE Online Monument in Reykjavik to answer the highly-voted questions you posted here.

Questions covered in the video:

  • 00:00 The EVE Online Monument
  • 00:48 Why is SQL Server using so much memory?
  • 02:30 Will table compression make a 2GB database faster?
  • 03:28 Will you be doing Office Hours long term?
  • 04:39 Should I upgrade SQL Server?
  • 06:00 Should I store JSON in SQL Server for a new application?
  • 07:40 What’s the best way to speed up page loads?
  • 09:33 What’s your favorite ETL tool?
  • 11:20 What are your thoughts on Oracle, DB2, MySQL, and Postgres?
  • 12:12 How big of a server should I use for a 100GB database?
  • 14:30 Will In-Memory OLTP help me on a 1GB database?
  • 15:30 What’s a good alternative to NOLOCK?
  • 16:35 How do I explain the legacy CE and backwards compat to a non-DBA?
  • 17:20 Should I change my inherited CHAR columns to VARCHAR?
  • 18:43 Alberto, Office Hours probably isn’t a good fit – you need consulting or training.
  • 19:20 Should I run antivirus on my SQL Servers?
  • 20:59 Why should I move from SQL Server 2016 to 2017?
  • 22:16 Since a backup file is bigger during busier times, should I back up when the database is idle?
  • 23:19 Who are your references in your field?
  • 25:06 Where you start when analyzing someone else’s queries if you don’t know their tables?
  • 26:15 Is there a module to write T-SQL in PowerShell? Do you teach your Mastering classes with PowerShell?
  • 28:38 Wrap-up

[Video] Office Hours: Ask Me Anything About SQL Server, Mink Edition

Videos
3 Comments

In this episode, I’ve got my own question: what’s this animal running around at my feet? Commenters on the YouTube video explained that it’s a mink. They were imported into Iceland in the 1930s for their fur, and they escaped out into the wild. The country’s been trying to reduce their population, and they’ve even been part of the COVID19 news.

Anyhoo, back to databases. The way these videos work is that you post your questions here, upvote the ones you’d like to see me talk through, and I do your bidding:

Questions we discussed in this episode:

  • 00:00 How can I fix a slow query with 30 left outer joins?
  • 03:20 How do I track down unparameterized queries?
  • 04:10 Can I force parameterization without using Optimize for Ad-Hoc?
  • 04:54 How can I measure latency of sync Always On Availability Groups?
  • 07:17 Should my monitoring software point at my AG listener or the replicas?
  • 08:50 What index reorganize/rebuild thresholds do you recommend?
  • 11:36 Why does Query Store show 5-6 plans for the same query?
  • 13:10 My SQL Server goes down on its own. How can I find out why?
  • 14:58 (I’m visited by a cruise ship and an unidentified animal)
  • 16:37 My databases are 1-3TB. What should I warn managers about?
  • 18:15 When I’m doing the DEATH Method, how should I treat unique indexes?
  • 19:46 How do I save sp_BlitzCache’s results to a table?
  • 20:22 sp_Blitz warns me that I have a high number of plans created recently.
  • 21:55 Is there a simple way to do inserts faster?
  • 23:27 Why should we stay on-premises instead of move to the cloud?
  • 25:30 If queries always use ORDER BY, can changing indexes change the query results?
  • 26:39 (Moving the camera to show the animal)
  • 27:40 Should we interview candidates by making them write queries in Notepad?
  • 31:12 Wrapping it up

[Video] Office Hours: Ask Me Anything About SQL Server

Videos
1 Comment

You’ve got questions, I’ve got answers. Welcome to another edition of Office Hours, where you post your questions here, upvote the ones you’d like to see me talk through, and I do your bidding:

Questions we covered this week:

  • 00:00 Introductions
  • 01:30 Are DBAs transitioning to other roles, and why?
  • 04:48 Why is it hard for DBAs with 35 years of experience to get clients?
  • 06:40 How can I track down slowness in the app if it isn’t SQL Server?
  • 10:17 I’m a backend/frontend/mobile developer. Am I spreading myself too thin?
  • 12:00 Are users with multiple languages a problem for the plan cache?
  • 13:54 My query errors out in batch mode, but works in row mode. Why?
  • 14:48 What’s the oldest SSMS bug that causes you the most frustration?
  • 16:14 When would you recommend not to use SQL Server?
  • 18:29 What was your favorite thing in Iceland?
  • 21:00 Does lock escalation work the same way in Azure SQL DB?
  • 24:04 Why isn’t my query going parallel?
  • 26:43 We revisit fragmentation for the 1,043rd time
  • 28:12 Why is my database stuck in restoring state?
  • 30:05 What certifications will grow my career?
  • 33:49 Do you like SQL Server 2019’s automatic index tuning?
  • 34:51 My new coworkers love query hints. What should I do?
  • 36:50 What should DBAs document?
  • 37:24 What is your favorite data auditing method?
  • 41:00 When should the primary keys not be the clustered index?
  • 42:00 How much C#/Java does a DBA need?
  • 43:06 What’s the best way to delete 90% of my data?
  • 44:05 Can I turn an execution plan back into T-SQL?
  • 45:45 How should I do real time updates to keep two servers in sync?
  • 46:46 Are you going to teach Power BI?
  • 48:35 Will Azure SQL DB replace SQL Server?
  • 51:17 Is the SQL job market good, and what will it look like in 5 years?
  • 53:10 Is it safe to use Accelerated Database Recovery?
  • 56:09 Am I crazy to drop foreign keys to make my database faster?
  • 57:43 Can I automate scripting out a database schema without tools?
  • 58:52 Recap

Most T-SQL Queries Don’t Even Try to Handle Errors.

T-SQL
44 Comments

David Tovee asked a great question in yesterday’s Mastering Query Tuning class. He asked his fellow students, “How many of you actually use TRY/CATCH?”

I turned it into a Twitter poll because I wanted to check a wider audience:

The poll results mirror the experience I see with clients: the vast majority of T-SQL code doesn’t have any error handling whatsoever.

When I do run across error handling in client queries, I get all excited and I ask about it. The answer is usually, “Oh yeah, that was implemented years and years ago by a developer who used to work here, and they left.” It’s often legacy leftovers from when TRY/CATCH was first introduced, and then…never revisited again, and rarely included with new queries.

If you’d like to get started with error handling, check out these resources:

Right about here is the part of the blog post where you might expect a finger-wagging lecture from me about how queries should always handle errors, but, uh…I don’t do error handling either. I know, right? But the thing is, I can’t remember the last time a client hired me to write a new query from scratch. If I never do something, then I won’t be good at it, and I certainly can’t lecture you to be good at it, either.

If you DO put error handling into your queries, though, take a moment to hug yourself. You rock, and you’re unusual amongst the wider audience. The world needs more people like you.


You Probably Shouldn’t Index Your Temp Tables.

Indexing
35 Comments

When you’ve got a process that uses temp tables, and you want to speed it up, it can be tempting to index the temp table to help work get done more quickly. However, in most cases – not all, but most – that’s a bad idea.

To explain why, I’m going to take a large Stack Overflow database and write a stored procedure:

The first statement loads about 10 million rows into a temp table, and the second statement only pulls out the rows that match who we’re looking for. Now, that probably seems dumb: you’re over there yelling, “Brent, why would you put millions of rows into a temp table that you don’t need?” The answer is that I need to write a blog post quickly in order to explain a concept that I’ve had to teach clients a few times. I can’t copy/paste their real code here. That would be Bad™. Instead, let’s just keep going with this game for a minute.

I’ll turn on time statistics so I can get a rough idea of where SQL Server is spending its time – the first statement or the second:

I don’t often use time statistics here on the blog, so a quick explanation: in the Messages tab in SSMS, you get a line for each statement in the batch, plus a total:

The first statement – loading the temp table – took about 4.5 seconds.

The second statement – finding Brent – took about a second.

Could an index on DisplayName speed up the second query?

To find out, let’s add a new version of our stored procedure – this time one that creates an index on DisplayName before our data is loaded:

Now, when we run the new query:

The time statistics paint a horrifying picture:

Sure, the second statement drops from 1,017 milliseconds to 75, but…who cares?!?! The additional overhead of building the index is much, much higher, making the query take ten times longer overall.

Part of the problem is that our heap (table) has to be loaded first, and then the data has to be sorted by DisplayName, and then an index has to be created on DisplayName. We can’t do them both in parallel at the same time because the nonclustered index has to be able to point back to a specific row in the heap – and to do that, we need its physical location, like I talk about in How to Think Like the Engine.

Fine. What about a clustered index?

We can reduce the overhead of the process by only having one structure to store the data. Instead of a heap plus a nonclustered index on DisplayName, we can just define a single structure for the temp table: a clustered index on DisplayName. It can’t be a unique index because multiple users share the same DisplayName, but that’s okay. Here we go:

And when we execute it:

At first, it looks way faster than the last method:

Execution time dropped from 57 seconds down to 18 seconds, but there’s a catch. A big part of the reason why it’s faster is that now the query is going parallel. Note that CPU time is higher than elapsed time – that’s your clue that the query went parallel across more CPU cores. Now we’re going to have a CPU problem if a bunch of these queries run simultaneously.

And it’s still not as fast as our original solution, the heap.

So how do we make temp tables faster?

Only load them with the data you actually need. When loading temp tables – or any objects, really – be ruthless about filtering as early as possible.

If you’re only going to access the data once, leave it as a heap. Indexes make the most sense when the temp table is going to be reused repeatedly across lots of statements that all do filtering or joining or sorting using the same keys.

Try CTEs instead. If you have a multi-step process that involves a lot of filtering and joining, let SQL Server recalculate where the filtering logic should happen. I am by no means saying that CTEs are always better than temp tables – often it’s the reverse – but if you’re hitting a performance tuning wall on queries that use temp tables, try converting them to CTEs. SQL Server will reorder operations – it doesn’t have to execute the first CTE first, then the second CTE second, and so forth.

 


Mark Your Calendars: Free Live SQL Server Training Classes in October & November.

This fall, I’m going to teach you the fundamentals of Microsoft database performance tuning, live, for free.

The first class is my How to Think Like the Engine class. It’s already free online in both video and blog post formats, but this fall I’m updating and expanding it to a 3-hour version. You could totally start watching the recorded version now, but…I know how it is. Some folks prefer watching live because they can ask questions as I go along:

Now, we start getting to the fun stuff – things that normally cost $89 for each class’s recordings. These are all-day classes. On Tuesdays, I’m teaching them in Europe-friendly times, and on Wednesdays, I repeat the same class in America-friendly times.

Register here to attend all of those, for free. The Americas-friendly classes will be 9AM-5PM Eastern US time, and Europe-friendly classes will be 8:00-16:00 UTC. (How to Think Like the Engine is only a half-day class though.)

After you’re registered for class, set up your server.

All of the Fundamentals courses except Fundamentals of Columnstore have the same prerequisites. Set yourself up a SQL Server:

  • SQL Server 2017 or newer, either Developer Edition or Evaluation Edition. Download pages are linked from SQLServerUpdates.com. Express Edition, Azure SQL DB, and Amazon RDS won’t work, unfortunately.
  • Use the default collation during install, SQL_Latin1_General_CP1_CI_AS (don’t get fancy with binary collations)
  • Apply the latest Cumulative Update
  • Install the most recent SQL Server Management Studio

To follow along with the demos, download the 50GB Stack Overflow 2013 database (10GB 7z file). I’ll be using the medium-sized 50GB StackOverflow2013 database, and it’s vital that you use the same one. Query tuning and parameter sniffing is all about getting different behavior based on your query’s parameters, so I need you to have the exact same data distribution that I’ll be working with onscreen.

Fundamentals of Columnstore has more ambitious prerequisites because columnstore is about bigger data. You don’t have to follow along with the class demos at all – it’s totally optional, and you can just watch me do them on the screen – but if you do want to follow along, here are the columnstore prerequisites.

Register now – attendance is limited to the first 1,000 students who get in. See you in class!


[Video] Office Hours: Ask Me Anything About Microsoft SQL Server

Videos
1 Comment

Got questions that aren’t a good fit for DBA.StackExchange.com, and you want my opinion? Post them here and upvote the questions you’d like to see me talk through. This weekend, I hopped into my home studio and did a half-hour session because so many good questions had piled up:

Here are the questions I answered in this session:

  • 00:00 Introductions
  • 01:39 Why is connecting to SQL Server sometimes slow?
  • 03:52 How do I know when my indexes are hurting me?
  • 05:54 Should I enable Optimize for Ad-Hoc Workloads?
  • 10:15 What do you think about running SQL Server in Kubernetes?
  • 13:50 Can I restore everything except one table?
  • 18:15 How can I monitor 1,000 SQL Server instances?
  • 24:04 How can I explain that performance tuning never ends?
  • 25:58 (I rant about how much I hate tech support)
  • 27:15 How do I write a DBA resume when I have 8 years experience?
  • 30:28 Any alternatives for SSMS on Mac OS?
  • 32:52 What if I want to jump from a DBA role to project management?
  • 34:32 If users query nested views, can I see the resulting T-SQL?
  • 35:39 Should I dynamically craft update statements per column?
  • 38:04 Wrap-up

Wanna join a live session? Subscribe to my free YouTube channel and you’ll get notifications whenever I go live. See you there!


[Video] Office Hours: Ask Me Anything About Microsoft SQL Server

Videos
2 Comments

We took a break from roaming around Iceland long enough to check in at our home studio. While we were here, I did a live Office Hours session, taking your questions about Microsoft SQL Server.

Questions I answered in the video:

  • 00:00 Introductions
  • 02:35 How can I tell if my SQL Server has too much memory?
  • 05:20 Does normalization have a big performance impact?
  • 08:08 What performance metrics should we show to the business?
  • 11:17 Are SQL Server’s index recommendations any good?
  • 12:37 Thank you for all you do for the community.
  • 13:24 Are GUIDs a good clustering key for high concurrency?
  • 15:54 Should I stack multiple SQL instances on the same physical server?
  • 17:41 Do foreign keys help with performance?
  • 21:05 Is there a benefit to having an identity column?
  • 22:25 Why is my query blocking itself?
  • 25:29 Should I separate my database into multiple databases?
  • 27:46 What’s the best data type for primary keys?
  • 28:13 Should I enable RCSI locally since I use Azure SQL DB for production?
  • 29:32 Is my SQL Server slow because of my network?
  • 32:40 How should we move documents out of the database?
  • 33:30 Which query should I tune first?
  • 35:50 Is SQL Server 2019’s UTF8 support a game changer?
  • 38:13 Which query filled up my TempDB?
  • 40:35 Will there be a SQL Server 2021?
  • 43:35 What’s the best way to implement version control?
  • 44:23 What performance change yields the best bang for the money?
  • 45:49 Is 1,000 logical reads a big deal?
  • 46:58 What security role lets people administer SQL Agent jobs?
  • 48:35 How can I determine what permissions an application needs?
  • 50:35 How should I configure quorum for my cluster?

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.


[Video] Office Hours: Húsavík Harbor Edition

Videos
6 Comments

Today’s session comes to you from the harbor in Húsavík, the whale watching capitol of Europe.

Questions I answered in the video:

  • 00:00 Introductions
  • 00:29 Is SQL Server 2019 ready for prime time now?
  • 02:47 Does WITH NOLOCK cause high CPU usage?
  • 04:53 Is MERGE a good practice or bad?
  • 06:36 OPENROWSET is so easy – why shouldn’t I use it?
  • 07:52 How can you tell if a server has enough CPUs?
  • 11:47 Discussing cargo cult programming
  • 15:25 Wrap-up

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.


Want a Better Work/Life Balance? Use Jira at Home. #TSQL2sday

I’m only half-joking. Hear me out.

If you’re frustrated at how much you work, and how little time you spend with your family & friends, think about how much planning and task management you’re required to do at work. You’ve probably got a ticketing system that tracks a backlog of work you need to do. Someone monitors it, and you have to report your status regularly to them. You get fancy burn down charts that track your progress.

You get what you measure.

You’re being closely measured at work, so you hustle your rear end off at work, trying to get your tasks done. You work later and later, and put in time on weekends. It eats into your personal time, but you feel obligated to do it because work has effectively gamified your life.

Track your issues at home, too.

No, I certainly don’t recommend using Jira – I wanna poke my eyes with a rusty fork whenever I have to use that thing – but use something.

For day-to-day tactical home productivity, I’ve been using RememberTheMilk.com for over a decade, and here’s how I use it. I got started by using the productivity philosophy Getting Things Done, but that’s overkill when you’re just getting started. The point is to use any categorized to-do system that’s accessible from anywhere (including your phone), and gives you high-level metrics of how many to-dos you have in each category.

For longer-term home productivity, I use Steve Kamb’s Epic Life Quest strategy. I make a list of things I wanna do in the future, keep track of what I’ve done, and each time I finish 5 of those tasks, I celebrate finishing one level of my life.

I’ve been using this approach for over a decade, and now I have a wonderful new work/life balance problem. I kept focusing on the life stuff that I wanted to accomplish, and in 2021, I haven’t gotten enough work done – because I’ve been letting my life tasks take over my work time. We moved to Iceland in January for a 6-9 month vacation, and since getting here, I’ve done way, way less work than normal. We’re moving back to San Diego in October, and I’ll be getting my work/life balance back under control.

Thanks to Tjay Belt for hosting this month’s T-SQL Tuesday. The topic was work/life balance, and if you’d like to read more tips from the database community, check out the comments on that post.


Menu