Tag: sql indexes

A Little About Default Constraint UDFs in SQL Server

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.

SQL Server Performance Office Hours Episode 38

Questions:

• 3. What are your thoughts on using AI (non-Microsoft) for query optimization and index maintenance? I’ve had 50-70% success tuning queries/indexes with 2-3 LLMs by providing schema/index/stat context first. Considering AI automation: identify poor-performing queries, add env context + tuning rules, use MCP tooling to test in read-only dev env; 3 models propose optimizations, best performer wins and goes to DBA for review. Thinks this is worth pursuing now, since AI will get better over time too? Not worried it will replace my job, performance tuning is only about 5% of my work and changes, generally, only make it to prod if prod reports performance issues. Even when I took the worst offending query and reduced runtime by 12X (no exaggeration) using indexed temp tables and a CTE to do aggregates then join on the temp tables instead of repeated joins on the same tables over and over. The change was *I think* was “too good to be true” and “might have a logic flaw that we can’t properly test for in dev” and thus hasn’t made it to prod… sorry for the rant.
• Lets say there is a table with Key column of number but it has trailing zeros (may be obe or two zero), i had that column as varchar, but business would never put zero when searching for that column. All out queries had like clause with ‘%%’, that killed the perofrmance. How would you approach the problem ?
• I’ve never seen anyone use a plan guide, not even in a blog post or quick video. Any idea why? When do you use them?
• We have a UDF as a default expression for a column on a table. Does SQL Server still evaluate the UDF in default expression even when I explicitly insert values into that column?
• Hey Erik. It took me many months, but I’ve finished watching all of your videos and turned your lessons into a drinking game. What do you think? I’m playing it right now https://dbfiddle.uk/CQbdBNH5 

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.

Row Goals: Part 4

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.

Row Goals: Part 3

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.

Row Goals: Part 2

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.

Row Goals: Part 1

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.

SQL Server Performance Office Hours Episode 37

* Does the order of INNER JOINs in a view impact how the queryoptimizer chooses how to build the execution-plan?

* Is there any performance gains when inserting into temp-tables to use the WITH (TABLOCK) hint? Thanks!

* Hey Erik! Is it data or data? I’ve heard some people saying data but I say data like you, so I think I’m right.

* 1. In our high-volume OLTP fintech environment on SQL 2017 EE, we have poor DB design leading to increasing deadlocks and blocking (readers blocking writers). As a short-term fix, we’ve set low deadlock priority on readers and high on critical writers, to avoid even more NOLOCK. We want to enable RCSI but management worries about safety, especially inaccurate results from in-flight version store data that might roll back. Previous DBA enabled snapshot isolation on a few DBs, but devs likely aren’t using it correctly or understand isolation levels. We have ample tempdb space (rarely used, as devs prefer table variables). How can I prove RCSI is safe? Dev env is limited—only some DBs match prod, with slower HDDs/CPU.

* 2. About 80% of our tables (millions to hundreds of millions of rows) have outdated indexes (5+ years old), despite daily dev work. Queries mostly from EF Core; servers reboot weekly (working to stop), losing index stats—but workload is consistent week-to-week. Biggest issue: slow upserts/deletes on tables with 50+ child tables (no cascading). I propose starting with narrow compressed indexes on FK relationships to avoid table scans. Tried sp_indexcleanup, but low uptime limits results beyond compression. Thoughts on this approach and overall indexing strategy?

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.

Lost In Costs: Part 3

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.

Lost In Costs: Part 2

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.

Lost In Costs: Part 1

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.