SQL Server Performance Office Hours Episode 9
| My company (using SQL Server 2019 Standard) has an algorithm that keys addresses into a varchar(40) has a cross-reference database that assigns an identity property to each new value, allowing us to post the numeric in our datasets. Production has to search their generated string keys in this database’s table to get the integer key in return. We have ensured proper string data typing on lookups and have unique compressed indexes on the string values. What would your next tuning step be if this was not getting the performance you needed? |
| What are your thoughts on a proc with heavy dynamic SQL used to provide for flexibility – to include extra tables to join, table variables of IDs, and usually resulting in multiple query plans? Is there a best practice to handle this other than “create separate procs” (and stop using table variables)? |
| What options do we have for optimizing queries against tables that store JSON data in SQL Server? There are often queries that update the JSON in place or depend on some JSON value for filtering. Would indexing a computed column make sense over an indexed view? At what point do we start trying to design a normalized table structure to store this data? |
| Filtered index over IS (NOT) NULL – good, bad, or ugly? |
| how does RCSI/Sanpshot isolation work when crossing databases, if either the calling DB or target DB does not have it enabled? |
To ask your questions, head over here.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
In case you didn’t notice (yet), you were featured on Brent Ozar’s Weekly Links thingie.