SQL Server Concepts and Best Practices [Part 1]

Hi Folks, It’s been a while that I don’t post anything here, so it’s time to start a series of SQL Server concepts and Best Practices. Most of them, I’ve already shared in my repository on GitHub. So, take a

Tagged with: , , ,
Posted in Performance tuning, Tips

Determining a setting for Cost threshold for parallelism

The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan

Tagged with: ,
Posted in Administration, Performance tuning, Scripts

Finding Implicit Column Conversions in the Plan Cache

I’m just sharing with you guys a script to find all implicit data type conversions associated with mismatched data types in a specific database. It’s pretty handy when you are either developing or coding reviewing. More details here: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

Tagged with: , , ,
Posted in Administration, Performance tuning, Scripts

Clearing a Single Plan from Cache

Most of you guys know that you can clear all cached plans using DBCC FREEPROCCACHE. In SQL Server 2008 you are able to clean a single pan from cache. The query below helps you to find the plan. There is

Posted in Administration, Scripts

Sargable queries – Using indexes effectively

It’s really important to have effective indexes in your database, but also equally important to ensure that yours queries are written properly to use these indexes effectively. So, one thing that we always need to keep in mind is to

Tagged with: ,
Posted in Performance tuning, Tips

Forcing a parallel query execution plan

Sometimes you might need to convince the query optimizer to generate a parallel execution plan to make your query faster. There is an undocumented trace flag (8649) that will help you with that. Take a look the example below. Now, the same

Tagged with:
Posted in Performance tuning

Why ado.net queries run so slow and my SSMS queries are so fast?

Many times I face questions from development teams “Why ado.net queries run so slow and my SSMS queries are so fast?”. The most common reason of this behavior is about the option ARITHABORT. Ado.net defaults are different than SSMS settings: Your

Tagged with: ,
Posted in Issues, Performance tuning