[Video] Office Hours in My Vegas Backyard
I took 5 of your top-voted questions from https://pollgab.com/room/brento while hanging out in the backyard on my second gin & tonic. I, uh, might have lost my temper on the last one.
Here’s what we covered:
- 00:00 Start
- 02:38 Frozt: are you familiar with SQL Server assemblies? and if they can cause assertion or IAM corruptions? Thank you Happy New year Brent
- 04:41 SQL_Linux: Hey Brent, My company is wanting to use AI for data analytics. What common misconceptions have you seen companies hold concerning using AI to find “insights”?
- 07:12 Culloden: my company is small and we don’t have devs for reporting. Finance team use prod for report development bc data is near real-time. I want to move them to a dev instance, but data can only be a few hours old. What are options for this? we only use simple recovery model in prod.
- 09:38 App Developer At Crossroads: When did you decide to stick with SQL Server? After 3 jobs over 8 years all with quite different tech stacks I’m feeling like its time to become an expert in something (tech and business domain)/have a long stay at a company so I can become more and more senior/have a big impact.
- 11:24 MyTeaGotCold: Did resumable online index rebuilds change any of your opinions on or approaches to index maintenance? I love them too much.
7 Comments. Leave new
Remember the easy button from Staples? Maybe you can get a similar button recorded with “What’s the problem you’re trying to solve”. Just give that a good whack when you get frustrated by the questions. Just make sure it’s made of durable material.
I bet you could do a whole comedy routine with your Clippy voice talking to your ChatGPT voice.
where index rebuilds (or better reorganize) makes still sense today are ColumnStore index (preferly on a partitioned table) where you have updated e.g. 30% of the rows, which causes a DELETE of the old and INSERT of the changed records. This way you suddenly have ~30% unused space in your table and since you use ColumnStore usually for the very big tables, this could become relevant and a performance matter too, since all the deleted garbage has not just to sit on your cheap drives but in the RAM too.
Sadly the common views or even Ola Hallengrens maintenance solution are REALLY bad at showing / deciding about the fragmentation of a Columnstore Index, since they return just the fragmentation of the Delta Store (this small not yet compressed part of the table with the most recent changes) instead of using the percentage of deleted vs. total rows in a rowgroup / partition instead.
INDEX Rebuild doesn’t even fix the problem I was trying to solve. In this case the covering value fields in the Index were not reflecting the actual table value (I’d love to know how that happened).
I know this because when I ran my stored proc and looked at the execution plan I could see a SEEK on a non clustered index which returned a value for the record that didn’t match a query that showed all the fields of the record and used the clustered index. The fix I got to work was to DROP and CREATE the index.
If anyone has a method of validating the values in indexes I would be grateful to know.
DBCC CHECKDB with the extended logical checks option does that.
Brent thank you very much , I will use that next time. Sorry for slow reply
I lived in Vegas for 2 years and loved it! I worked at the Nevada Test Site as a geologist (back in the early 90s when it was not open to the public). Love the proximity to the National parks. Would love to visit soon and have a G&T with y’all.