A sampling of things overheard at our 2015 Performance Troubleshooting Class in Denver:
- “I’m not a GUID hater.”
- “Oh, God help me. I’ve clicked on XML.”
- “Reporting Services looks around at the available memory, makes itself at home, maybe takes its pants off.”
- “Where would I go to check if I — hypothetically — accidentally set my fill factor to 10%?”
- “I use the Database Tuning Advisor for humor.”
- “SQL Server gives you this great set of [partitioning] tools and then sprinkles broken glass all over them.”
- <sirens outside> “The partitioning police are here!”
- “If the index fits, I sits.”
- “You don’t need foreign keys in a data warehouse. Because you have a source system.”
- “This is the title slide. It is also the agenda slide.”
- “It sounds like a bad ad, but the more you query the more you save!”
- <Execution plan recognizes an indexed view and swaps it in> “Let it be known that for once, SQL Server did the right thing!”
- “Great questions. You should have prizes. We don’t have any prizes left, so have some smug.”
- “When there’s no uninstall, there’s your clue that this feature may not be a delicious lava cake. It may in fact be a disease.”
- Attendee: “So how do we know Hekaton ran out of memory?”
Kendra Little: “It stops working.” - “If your query lasts more than four hours, discontinue using Hekaton.”
- “Tune queries with this one simple trick: TRUNCATE TABLE”
- “There’s a lot of empty lines at the end of this file. I keep scrolling down, expecting to find a bonus album track or something.”
- “Even SHAREPOINT knows better!”
- “The SQL Server is hung. We need a back door.”
7 Comments. Leave new
“Where would I go to check if I — hypothetically — accidentally set my fill factor to 10%?”
I’ve had one very similar to this. ^.^
“You don’t need foreign keys in a data warehouse. Because you have a source system.”
I know that (checked!) constraints and FKs will help the query optimizer to take some shortcuts because the outcomes of the constraints are guaranteed, so really, they are good things to have in a DW or reporting database.
I have worked with many people in the DW space that insist that you shouldn’t have FKs in the DW, which does make sense during bulk loading, but makes no sense when it comes to reporting. I suspect these people are too focussed on ETL performance and are forgetting the real purpose of a DW (err, business value anyone?).
Also, if you’re going to switch off FKs during loading, remember to switch them back on.
I’m curious – How much performance benefit have you proven you’ve gotten from creating foreign keys in a data warehouse, vs the tradeoff of long data loading times.
I ask because I personally only find queries that help the optimizer take those shortcuts very rarely, and typically the tradeoff wouldn’t be worth it. Particularly because re-enabling those keys with CHECK requires scanning everything, or it can’t do the tricks you’re mentioning.
If time constraints allow, wouldn’t the scan done in order to reenable the constraints be useful as a validation of the data just loaded?
Some developers like to tell me “Just reboot SQL, that should make it faster.”
Attendee: “So how do we know Hekaton ran out of memory?”
Kendra Little: “It stops working.”
Ha ha ha
“Have some smug” LMAO