Blog Archives

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

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