Are You Using All Your CPU Cores
We recently worked on a SQL Server Performance Assessment for a client and during that we assisted with the upgrade from SQL Server standard edition… Read More »Are You Using All Your CPU Cores
We recently worked on a SQL Server Performance Assessment for a client and during that we assisted with the upgrade from SQL Server standard edition… Read More »Are You Using All Your CPU Cores
I received this question from someone visiting the blog, and realized that rather than just replying to them, it might be good to put the… Read More »Guidance on Query Optimization
After my post yesterday on the performance implications of concatenating long strings with the + operator and the CONCAT function, today I am following up… Read More »Performance: Faster way to concatenate longer string
String Concatenation SQL Server is one of these things that might be sound until you try to concate longer strings. That is where it gets… Read More »Performance: String Concatenation in SQL Server
Do you need help with a slow running SQL Server? Take a look at these SQL Server performance tuning options that we offer. Stedman Solutions… Read More »SQL Performance Tuning
The team at Stedman Solutions, LLC (Steve, Bill, Derrick, and George) offer a variety of services, but one that I particularly enjoy is the performance… Read More »Do you need SQL Server Performance Tuning Help?
This Friday, October 14th I will be presenting at the Centricity Healthcare Users Group (CHUG) conference in Austin TX. The session is on “Tune, Monitor… Read More »Presenting this Friday at the CHUG Conference
One way to improve performance on SQL Server is with IFI or Instant File Initialization.
Normally files are zeroed out on a database growth during an AUTOGROW, RESTORE, CREATE DATABASE or ALTER DATABASE. This is done by SQL Server when the file grows, it runs through that file and writes zeroes to the entire new allocation in the file. The zeroing process can take a great deal of time, the Instant file initialization process skips this zeroing, and just allocates the file. This works since SQL Server will just write each 8k page to disk as they are used, thus overwriting the uninitialized file.
Running some tests on a local virtual machine running SQL Server 2012 (similar results tested on SQL Server 2014 and SQL Server 2016), here is what I found.
For IFI to work, the user account that SQL Server is running as needs the “Perform volume maintenance tasks” policy to be enabled.Read More »Instant File Initialization (IFI)
Here is an error that popped up in the SQL Server error log today:
5/6/2016 10:10:10 AM spid22s Database [DatabaseName] has more than 10000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
Virtual Log Files (VLFs) are part of the SQL Server log file. When space is allocated in the log due to growth, that new chunk of log is broken up into Virtual Log Files
After looking further into this server, the VLF count turned out to be around 163,000. That is certainly the highest VLF that I have ever seen.
My personal threshold for too many VLFs is usually around 200 with my preference to be less than 100 VLF files. Others may have their preferences, they will all be far less than 10,000, and certainly less than the 163,000 that I had the opportunity to see today.
Updating SQL Server statistics may not be as obvious as it may sound.
Imagine this scenario. I invite you over for dinner and a game of Scrabble. After dinner, I start to do the dishes, while you set up the game. Rather than just doing the dishes that are dirty from our dinner, I decide to take all the dishes from the shelves, and drawers in my kitchen. I create a gigantic pile of all of the dishes, both clean and dirty on the kitchen counter. You get the game setup, and we start playing the game. Between turns, I jump back into the kitchen, and wash a few dishes, then on my turn, I jump back into the game and play my turn, then back to the kitchen again. During the game you are constantly waiting for me to jump back into the game to make my move. You could see how long this would draw out the game with me washing all the dishes, even the ones that were already clean. 4 hours later, I finally finish the dishes, and shortly thereafter we finish the game. It is unlikely, even if you love to play Scrabble that you would ever return to my house for dinner and a game again. Tomorrow night, when I have dinner with my family, I do the same thing, and every night after dinner I wash all the dishes in the kitchen, even the clean ones. You can see how wasteful this sounds, and perhaps some people would even label me as OCD or something worse in this scenario.
Although this is obviously the wrong way to do things, often times we end up doing the equivalent of this in our SQL Server maintenance tasks.