Category Archives: Performance Tuning

A list of blogs pertaining to performance tuning in particular

3 server settings you can enable in 3 seconds to improve performance

By | May 28, 2020

There are a number of dials and knobs in SQL Server that allow the DBA to fine tune almost every aspect of the server. In this post I highlight five quick wins that every DBA can enable on their server to improve performance. The best part is they will take all of three seconds of… Read More »

How to Increase transfer rate when copying to External HDD

By | May 23, 2020

Recently I had to purchase an additional external hard disk to back up my data. While creating a system image of the laptop I noticed that it was taking far too long. On further inspect I found out the transfer rate was far lower than promised. This post will explain how to identify and fix… Read More »

Series on Tuning SQL Server using Fill Factor to control page splits

By | May 21, 2020

Is it better to take a hit on performance due to Fill Factor or Page Splits? That is the question being answered in this video. As with most things in SQL Server there is a trade-off. Is the impact of additional pages caused by a fill factor of 70% more damaging than the impact of… Read More »

Why you should continue to defrag your indexes:-What are page splits 1/4

By | May 18, 2020

Recently I came across a video in which it was suggested the Page Splits/sec counter was not useful in identifying Page splits and that using fill factor to reduce the number of page splits doesn’t really fix the issues and often makes things worse. Naturally this goes contrary to a number of practices DBAs have… Read More »

DB options: – Recovery intervals and Checkpoint

By | May 5, 2020

In this video we talk about recovery intervals. Specifically how checkpoint allows recovery intervals to be maintained. Checkpoint is a feature that allows SQL server to recover faster during an unexpected shutdown. It is important for DBAs to understand how this functionality works. We explore different type of checkpoints and the role they play in… Read More »

Using Snippets in SSMS

By | May 3, 2020

A little known and used feature by database developers is Snippets. Database developers understandably do not use this feature because we have long been accustomed to using prebuilt templates and code blocks from other sources. Often we have a readymade template which we customize as needed and that is it. Database administrators have mastered SQLCMD… Read More »

DB Options: – Parameterization

By | April 24, 2020

In this video we cover the two options available under parameterization in MSSQL database properties. Ad hoc queries are queries that aren’t stored procedures. Since the code is ad-hoc in nature we see that the WHERE condition often has hard coded values. Each time the hard coded value changes we see a new execution plan… Read More »

DB Options: – Delayed Durability

By | April 17, 2020

https://www.youtube.com/watch?v=Yj8rKeSx7jw&feature=youtu.be Delayed durability is one the few features available to database professionals to improve write performance. Often for highly transnational databases we encounter disk level latency on writes. Most of the improvements would require changes to server hardware etc. Delayed Durability when done right is Simple, Effective and easy to do. It should not be… Read More »

DB Options: – Optimistic Concurrency

By | April 11, 2020

https://youtu.be/MaGrNFHZrH8 It’s very common for databases to reach its limit over time even with good indexing and hardware. At some point the database administrator is going to have to consider if using Optimistic Concurrency. Optimistic Concurrency is a feature that allows the database to reduce the overlap of concurrent read and write operations on the… Read More »

DB options: – Auto Shrink = OFF Why you shouldn’t shrink if possible.

By | March 2, 2020

https://youtu.be/ph070n6lLU4 The use of AUTO SHRINK I think needs no explanation. Almost anybody who has worked on SQL Server understands the way databases can quickly grow. In these cases we can see growth happens in two places, the database file and the log file. The database file gets highly fragmented when it is shrunk. The… Read More »