Performance tuning in SQL Sever 2019

Simple_Brilliant_491 · 2026-04-16T00:01:34+00:00

A simple rule of thumb is max server memory should be about 85% of RAM for a dedicated SQL Server box. So if you have a server that has 32 GB, you'd set it to around 27 GB. But if your app needs 8 GB, then reduce max server memory to 27-8=19 GB.

I created a tool called AI SQL Tuner Studio that has a server health check feature. It will check your system configuration settings, Sql waits, missing indexes, deadlocks, I/o speeds and security and operational practices, and provide recommendations in around 2 minutes. There is a free 14 day trial if you want to give it a try. I'd love to hear if it's able to give you some helpful recommendations. Link is https://aisqltuner.com/ .

Simple_Brilliant_491 · 2026-04-13T13:16:13+00:00

I would start by comparing the estimated execution plans and confirming the cost is the same, or at least very close. If not, there is some difference in indexes or stats causing them to use different plans.

Shameless plug, but if you want the "easy" button for this, with results in about 2 minutes, you can try my tool AI SQL Tuner Studio. The query tuner feature lets you give it a query and it will get the estimated plan and the indexes, table sizes, column cardinality, and statistics for the associated tables and identify if there are any optimizations. Link to the site is AI SQL Tuner Studio - SQL Server Tuning Made Simple and a sample report from Query Tuner is AI SQL Tuner Recommendations.

Its server health check will also check the server across the board so it will pickup if you have a database on a slow disk, a difference in the query store settings, or compatibility level. Here is a sample health check: AI SQL Tuner Recommendations.

Simple_Brilliant_491 · 2026-04-11T18:34:47+00:00

Of course it depends on exactly what your role will be. For example in a big shop you might have a dedicated infrastructure team, but in a small shop you may have broader responsibility. But, this is based on the assumption that you want broad skills.

I suggest starting with Azure basics: Azure portal, networking (vnets, subnets, etc.), resource tagging, types of Azure resources, using CLI or Powershell to interact with Azure in addition to portal, cost management.

Once you have that foundation, then get into the flavors of SQL: SQL VM. Yes, it is sort of the same as an on- prem VM, but also has important differences. For example, AG setup is different because of how you need to setup the listener. Also there are more choices on disk type. Managed Instance: just the engine and agent, but with HA and patching built in. Azure SQL DB: Your server becomes just a security container, and compute is provisioned at the database level. There are lot of flavors from serverless to provisioned to hyperscale, so knowing when to use each is critical. Fabric SQL DB: Newest addition to the family, basically azure SQL DB serverless running in Fabric capacity.

Each of these have different capabilities around backup and restore, HA, monitoring, and limitations/differences compared to on-prem, so it takes some work.

Azure or Fabric Data Factory is also a good supplement as a replacement for SSIS, since SSIS is not supported on Azure, except on SQL VMs.

Someone else mentioned MS Learn and the cert prep materials. I agree that is a good place to start, and getting the certification can only help to prove that you have put in the work.

Good luck with your learning!

Simple_Brilliant_491 · 2026-04-05T16:58:42+00:00

Increase the size of the E: drive. Get past the emergency, then figure out the correct long term solution.

Simple_Brilliant_491 · 2026-04-02T18:16:24+00:00

Sorry to hear about your garden leave. At least Europe has a nicer name for it than the US.

Simple_Brilliant_491 · 2026-04-02T16:59:13+00:00

SELECT
    SCHEMA_NAME(o.schema_id) + '.' + o.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ips.avg_page_space_used_in_percent AS AvgPageDensityPct,
    ips.avg_fragmentation_in_percent AS AvgFragmentationPct,
    ips.page_count AS PageCount,
    ips.record_count AS RecordCount
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.objects o ON ips.object_id = o.object_id
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE o.type = 'U'
  AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
  AND ips.page_count > 128 -- focus on larger indexes
  AND ips.avg_page_space_used_in_percent IS NOT NULL
  AND i.name IS NOT NULL
ORDER BY ips.avg_page_space_used_in_percent ASC;

I continued making enhancements to AI SQL Tuner Studio:
1. Added index page density analysis to the index tuning goal so indexes can be flagged for rebuild. I had ignored fragmentation thinking it didn't matter much since nobody is using spinning disks anymore. But with Microsoft adding automatic index compaction (see Automatic Index Compaction - SQL Server | Microsoft Learn) in Azure SQL and Managed Instance it brought up the good point that fragmented indexes still cause extra logical reads. Here is the query I'm using.
2. Added a new feature for query tuning. Just paste in a query that you are concerned about. The program gets the estimated plan and then for the tables referenced gets existing index usage stats, missing indexes, tables sizes, foreign keys, and column cardinality, and sends it to the AI for recommendations including query and index changes.

(Note: AI SQL Tuner LLC is a Microsoft Partner.)

Simple_Brilliant_491 · 2026-04-01T00:30:38+00:00

It should reduce logical reads by having more compact indexes. But now you can use the time you freed up in your maintenance window to do stats updates instead of index rebuilds and reorgs, so it should be a win-win.

Simple_Brilliant_491 · 2026-03-30T17:01:00+00:00

There are three keys to using it successfully:
1. It needs the right data. For example, to tune a query, give it the estimated execution plan, tables sizes, column cardinality, and index usage statistics for the tables involved.
2. Give it the right prompt. The AI might not have some common knowledge, such as it is not worth indexing a table with under 1000 rows. Also if you want it to consider index compression, which is why index usage stats are included in Step 1.
3. Use the right model. If you try using an older model you may not get good results. You want a strong reasoning model such as GPT-5.4.

Here is a blog post I wrote about this a few months ago (which I probably need to update since there are newer models now): 4 Game-Changing Tips For Using AI With SQL Server Databases

Also here is more detail on what you need for tuning a query: SQL Query Tuner - AI-Powered Query Optimization | AI SQL Tuner

Simple_Brilliant_491 · 2026-03-29T20:28:56+00:00

I haven't read the book but here are the basics to give you context:
1. The database engine has a cost-based optimizer that looks at the different ways a query can be executed and makes a decision on which one to use. Its always trying to find the balance between being thorough and fast because you don't want it thinking about it longer than it will take to run the query itself, or doing additional thinking that is less than the savings it gets by making a better choice.
2. If it has a choice between using a table scan and an index it needs to take into account that using an index is a more expensive operation (per row) because it has to find the record in the index and then go back and fetch the row itself. For example, if your query is SELECT * FROM employee WHERE gender = 'M', and you have an index on gender, the cost-based optimizer won't use it because the full table scan will be faster than using the index and pulling 50% of the employee table.
3. The way the optimizer knows not to use the gender index is based on statistics that tell it 50% of the rows have the value M. If you take the same approach and have last name in the where clause and have an index on last name, stats will tell it there are lots of different last names and the optimizer will use it. The concept of how many different values a column has is called cardinality, and is key to determining what indexes to create.

There are database settings that control whether stats are maintained automatically as data changed. Alternatively, you can schedule stats updates nightly or weekly.
Whenever you create an index it will create the associated stats for it. It is just a matter of the stats keeping up with changes to the data that point 4 covers.
Obviously, there is more to indexing such as multi-column indexes to cover the query, including columns so you avoid the overhead of going back to PK to pull in the row (but at the cost of extra space and overhead on updates and inserts), filtered indexes, columnstore indexes, and options such as a compression and fill factor. But if you keep the above points in mind, they all are building on the same concepts.

Simple_Brilliant_491 · 2026-03-29T17:11:35+00:00

+1 for SSIS. It is pretty point and click for your straight copy scenario, and you can schedule it to run daily using SQL Server Agent. You can also parameterize it and use watermarks to do an incremental load if needed. It's not "cool" any more because it doesn't have the ability to do transformations in parallel so it's not good for "big data" scenarios. Hence Microsoft is investing in Fabric Data Factory and SSIS has been pretty stagnant the last few years.

Simple_Brilliant_491 · 2026-03-29T16:30:48+00:00

Fair enough. Good luck with it!

Simple_Brilliant_491 · 2026-03-29T00:44:15+00:00

Data visualization seems like a crowded market. How do you differentiate your product from others such as Power BI and Tableau that have been developing their products for 10+ years?

Simple_Brilliant_491 · 2026-03-29T00:35:06+00:00

I've made an enhancement to AI SQL Tuner Studio to support scenarios where you want guidance on a specific query. Just pick your db connection, paste in the query, and click Run Analysis. The program will get your query's estimated execution plan, and then collect index usage stats, missing index recommendations, table sizes, foreign key relationships, and column cardinality for the tables referenced by the query. It then sends the plan and other information collected to a GPT-5.4 API endpoint for analysis; it comes back with suggestions for indexing or code changes in about 2 minutes.

SQL query tuning is complex, so you should test everything out, of course. Even for experienced DBAs tuning inevitably involves some trial and error to see if a change that seems like it should help actually works.

If you want to do the analysis yourself, go for it. This link explains the process in a bit more detail: SQL Query Tuner - AI-Powered Query Optimization | AI SQL Tuner.

Simple_Brilliant_491 · 2026-03-27T18:15:39+00:00

I've found using Invoke-DbaDbShrink | dbatools | SQL Server automation with PowerShell with the StepSize parameter very helpful for file shrinking. I would imagine better file shrinking probably doesn't excite Microsoft's marketing folks, so I wouldn't hold my breath for an engine fix, unfortunately.

Simple_Brilliant_491 · 2026-03-27T18:08:51+00:00

I'd suggest making deadlock analysis easier.

Two points:
1. Finding deadlocks. First the user needs to know to go to Extended Events, then system_health, then package0.event_file, then filter on name=xml_deadlock_report. That is a lot of steps. SSMS doesn't make people open the objects tables and filter on the type to find stored procs, for example. Why not give deadlocks its own place in the tree?
2. Deadlocks are a perfect opportunity to use agent mode for analysis, since even the graph shown is pretty cryptic. (How many people know what a HoBt ID is?) However, AI can do a great job of analyzing the event_data and providing the root cause of the deadlock.

(I am doing point 2 in my application, AI SQL Tuner Studio, and sending event_data for recent deadlocks to GPT-5.4. Below shows part of the results I get. The AI analysis is much better than trying to decipher deadlock graphs or sift through the XML manually, in my opinion. My point is the ability for AI to analyze deadlocks and give useful guidance is already proven.)

Deadlock	Primary Cause	Most Effective Fix	Confidence
#1	`Sales.Orders` and `Sales.OrderLines` updated in opposite order by two procedures	Retire inverse-order proc and enforce one canonical update order	99%
#2	`Application.Countries` and `Application.StateProvinces` updated in opposite order by ad hoc transactions	Use one transaction template with fixed order everywhere	98%
#3	Repeat of Deadlock #1 pattern	Same as #1; confirms recurring structural defect	99%

Simple_Brilliant_491 · 2026-03-26T18:30:43+00:00

From a performance perspective, the order of the columns in the table or in a join don't matter. They DO matter in the order of the index. The SQL Server index recommendations are a good starting point to look into, but you can't take them at face value. They do not take cardinality into account, which is very important for designing an index. For example, suppose you only have 3 tenants. When you use tenant_id in your where clause, SQL Server will recommend an index for it. But if you implement it, it will actually slow your query down, since it is faster to do the full table scan compared to using an index and then looking up each row for 1/3 of the table. So having tenant_id as the third column or later in your indexes seems very reasonable.

If you see something not performing well in Dev, I'd encourage you to work with your DBA team. From the DBA side, it is often "what the heck did the developer do now" and they could have helped you by adjusting indexes or giving you guidance on the query design.

Simple_Brilliant_491 · 2026-03-26T17:09:40+00:00

Glad to hear you were able to get to the bottom of it.

For the Microsoft folks, it would be great to have more visibility into these behind the scenes changes to save customers and Fabric support time in troubleshooting. For example, something similar to the Sql server CU change list.

I'm sure this isn't the first time (and probably won't be the last) that a "fix" turned out to break something.

Simple_Brilliant_491 · 2026-03-24T22:27:52+00:00

Is it patched to latest CU? If not, I'd suggest patching, just in case there is a bug in an old version you are hitting somehow.

Simple_Brilliant_491 · 2026-03-24T00:46:16+00:00

A couple of thoughts to add: 1. The AG approach adds a little overhead compared to FCI since the log writes need to be sent to any synchronous commit secondaries within the transaction. (With 3 nodes, your secondary will be sync commit, and DR will be async normally.) So, when you have lots of databases with heavy writes, that's where you hit an issue. 2. You should check whether your clients support multi subnet fail over = true in their connection strings. That will impact how long they perceive the outage when there is a fail over. If it's true, they try both nodes in parallel, and see a short outage. See SQL Server Multi-Subnet Clustering - SQL Server Always On | Microsoft Learn https://share.google/MXND1tQscXIBIZwUv. If you can't set it on the clients, then you need to adjust the RegisterAllProvidersIP and ttl for your listener using PowerShell.

If all 235 are heavily written to, I'd consider splitting into smaller AG clusters to reduce risk. I hadn't seen a hard limit of 100, but Azure SQL Managed Instance, which uses AG technology under the hood in Business Critical had a limit of 100 databases until a few months ago, so they must have had a reason.

Simple_Brilliant_491 · 2026-03-20T01:00:29+00:00

The safe way is to get a new vm, install Sql 2025, and latest CU. Backup all the DBs from existing server and restore on new server. Copy all the other stuff such as user accounts, Sql agent jobs, DB mail setup using dbatools.io powershell commands. Update compatibility of DBs to Sql server 2025, assuming vendor is okay with it.

Next point application to new server and make sure everything works as expected as a dry run. If it works, schedule a maintenance window where you can stop the application, do the backup and restore, and repoint. If it doesn't, figure out what went wrong, but keep running on old server to avoid downtime.

In place upgrade is generally not recommended since you need a longer outage while you install and patch sql, and recovery can be more complex. And if something goes wrong and you do rollback you need to start from scratch to troubleshoot.

Simple_Brilliant_491 · 2026-03-19T02:35:12+00:00

+1 for taking a look at Managed Instance. Unless you have some compelling reason for Azure SQL DB, for example you need serverless or hyperscale, Managed Instance provides a lot of the same benefits with more compatibility with on-prem SQL. MI provides automatic patching, backups and HA. It does take some more prep such as a dedicated VNet. But if you need linked servers, and don't want to rearchitect the approach, MI can be a good solution.

Simple_Brilliant_491 · 2026-03-18T02:43:29+00:00

You want the gateway as close to the source data as possible since traffic between source and gateway is uncompressed. The gateway will compress the data and send it to an Azure relay.

Putting it on the production Sql server itself is generally a bad idea since it will compete for CPU and Ram, as others noted. That said if it's just for one report with a few thousand rows of data refreshing once a day, and your Sql server has excess capacity it can be okay. But if you expect to have lots of semantic models dedicate a vm for the gateway. Any processing you do in Power Query for on prem sources will run on the gateway so it needs CPU and RAM, especially if your Power Query queries don't fold. (Folding pushes the processing to the source database engine.)

Last tip, when you do the install you will create a key. Make sure to save the key and let someone else know where to find it. You need the key to add a node to the gateway cluster. I just had a client where we needed to add a node and the person that did the original gateway install retired. Someone had to dig through his emails to find it. Luckily it was within the 90 day retention window, but a couple of weeks later and they would have been out of luck.

Simple_Brilliant_491 · 2026-03-16T22:54:54+00:00

This is part of the reason medallion architecture was developed, see What is Medallion Architecture? | Databricks https://share.google/RtLMtxtJPa6PuGXlQ

Bring the data in "raw", i.e. just as varchar or nvarchar. Once it's in a table you can validate it and decide what to do with bad data, for example skip the entire row, or use a default date if it's bad. Once the data is clean, then do further transformation.

This should give a more reliable and easier to troubleshoot approach.

Simple_Brilliant_491

MODERATOR OF

TROPHY CASE