• Facebook - Black Circle
  • Twitter Round
  • Instagram Black Round
  • LinkedIn - Black Circle

August 8, 2018

I wish page splits were delicious. 

What are page splits?

Page splits occur when a data page gets filled with data and a new page is needed to make room for new data.  There are times when data modification causes data to be inserted in the middle of a page, this causes...

July 30, 2018

Many of us have not made the effort to switch from profiler to Extended events.  It's 2018, if you haven't found a few hours to learn about this incredibly powerful tool, I urge you to do so now. 

I'm going to provide a quick means of tracking queries with extended...

July 19, 2018

In the past we're used to enabling Traceflags 1117 and 1118 in order to help with TempDB Contention. With the release of SQL 2016, these were no longer needed...however

Now it's been suggested to enable Traceflag 3427.

Why? Because  a new type of contention has emerged c...

July 12, 2018

Page Life Expectancy is the number of seconds the average page of data has been in the buffer pool.SQL server keeps data pages in memory for quicker access instead of having to read from disk every time.

Now, keep in mind that Page Life Expectancy doesn’t prove or dispr...

July 5, 2018

Why should I use Query Store?

Query store can be used for a number of things.  For example:

  • Find out what was happening on my server last night?

  • Identify and fix queries suffering from parameter sniffing or plan regression

  • Testing (ie - when upgrading to a...

June 25, 2018

It's amazing that we never had a built in feature that logged historical record of queries. Sure we had the plan cache, but that was cleared with a service restart or reboot. How many times have you walked into an environment and they would say the server was slow yest...

January 9, 2018

Sometimes for testing purposes (or an emergency) one might find it useful to remove query plan for a single stored procedure. This way you won't have to disrupt the entire instance (and possibly anger your fellow developers!).

I often forget that DBCC FREEPROCCACHE acce...

December 19, 2017

I know, it sounds like it could be a desert. But it's poison! 

You ever have one of those Saturday morning emergencies and come face to face with a wait type that you're completely unfamiliar with? 

I  recently got contacted that a highly transactional SQL Server was per...

December 5, 2017

It's important to test your storage performance especially prior to installing or deploying a new SQL Server. 

Microsoft has provided us with a great tool called Diskspd, which was meant to replace SQLIO. Diskspd synthetically generates workloads to run against your ser...

October 25, 2017

An elegant function for a more civilized age:

Another way to detect deadlocks is to utilize the TRY CATCH block to raise errors in TSQL code. I found this to be a very elegant way of detecting deadlocks pragmatically. You can use the below code sample in a stored proced...

Please reload

Please reload

RECENT POSTS: 

FOLLOW ME:

WHO AM I ?

My name is Aamir Syed and I run SQLEvo. I optimize the speed, efficiency, and stability of SQL Server.  When not working I like to lift weights, play music, and travel with my wife.

  • Facebook - Black Circle
  • Twitter - Black Circle
  • Instagram - Black Circle
  • LinkedIn - Black Circle