0

I recently wrote a query that, rather surprisingly, filled our tempdb. I was simply doing a string comparision on several thousand records, and I imagine because the strings where so long that the transactional data in the tempdb filled.

I would like to prevent this in the future by adding logic to my query that checks to see if tempdb is nearing its threshold, and if so ends the query. Is this possible?

iamdave
  • 12,023
  • 3
  • 24
  • 53
mgmedick
  • 686
  • 7
  • 23
  • 6
    Do you want to think about maybe being sure that this is actually what caused the problem before you write code that supposedly cures the "problem" you're not even sure is the problem? – Aaron Bertrand Oct 07 '14 at 17:38
  • Well we have a 3rd party that polices our servers, they where the people who reported the query as the one that caused the issue. As for what caused the problem, I'm relatively sure it was this string comparison, which has since been changed to just grouping on this string field. The problem is that theres no getting around the query being relatively expensive, because in the end its looking for duplicate messages, and in the end the only way to know if a message is a duplicate to go compare the entire message string. which can be not huge but like 8000 varchar. – mgmedick Oct 07 '14 at 18:17
  • Just simply wondering if there is a safeguard you can build into a query that will stop the execution if its filling up the tempdb. – mgmedick Oct 07 '14 at 18:21
  • Even using a checksum, or as I'm doing grouping, still results in an expensive query when there's hundreds of thousands of records is processing, and I'm not even talking about using a temp table – mgmedick Oct 07 '14 at 18:23
  • 1
    Maybe don't compare hundreds of thousands at a time then? You could easily narrow it down to rows where the first 20 or 50 characters match, and go from there (and you could have an indexed computed column to help with that). – Aaron Bertrand Oct 07 '14 at 18:56
  • ok that's good enough, I think what I have is fine. I could potentially run it in batches, but what I have I think is ok at this point. Just was looking for an easy answer other than changing the design, where I could check tempdb capacity. – mgmedick Oct 07 '14 at 19:10

0 Answers0