How to Grow All Files in a File Group Equally? – TRACE FLAG 1117 – Interview Question of the Week #201

Question: How to Grow All Files in a File Group Equally?

Answer: If you are a regular reader of this blog, you would have read my earlier blog post writing about this subject over here: SQL Server 2016 – Introducing AutoGrow and Mixed_Page_Allocations Options – TraceFlags. However, let us try to get an answer to our question in this blog.

Read More

SQL SERVER- High CPU and Transaction Type WorkFileGroup_fake_worktable and Workfile

As a part of my  SQL Server Performance Tuning and Optimization consultancy, I face interesting situations and every time I am able to resolve them. This was one of the more interesting ones where I was not getting any hint from the internet. Since it was a new thing to me, I am sharing it via this blog. In this blog, we would learn about the cause of WorkFileGroup_fake_worktable and Workfile.

Read More

SQL SERVER – Creating Temporary and Global Temporary Stored Procedures

During recent Comprehensive Database Performance Health Check, I had a very interesting situation I encountered where we found a stored procedure which was a root cause of the slowness. What we needed to do is to change the code and test if our new stored procedure is better than previous stored procedure or not. One of the options was to create a new stored procedure by renaming the older stored procedure and keep on testing. However, there was a small risk if we forget to drop the newly created stored procedure there will be junk created in the database. Let us see how we can avoid this situation by creating temporary or global Temporary stored procedures.

Read More

SQL SERVER – Improve Index Rebuild Performance by Enabling Sort In TempDB

During recent Comprehensive Database Performance Health Check, the customer put forward a compelling request. They wanted to improve their index rebuild performance as their index rebuilding process is taking forever to complete. When they asked me this, I exactly knew what we needed to do for this customer. We ended up fixing the issue of index rebuild by enabling sort in TempDB. 

Read More