SQL Server Blogs

When you create a temporary table you expect a new table with no past history (statistics based on past existence), this is not true if you have less than 6 updates to the temporary table. This might lead to poor performance of queries which are sensitive to the content of temporary tables.I was optimizing SQL Server Performance at one of my ...

Are you using SSD or SAN / NAS based storage solution and sporadically observe SQL Server experiencing high IO wait times or from time to time your DAS / HDD becomes very slow according to SQL Server statistics? Read on… I need your help to up vote my connect item – ...

  SQL Server is configured to use a lot of memory, but my query is slow and not using all the memory available and it is spilling the sort or the hash match operation to tempdb, how can you tune the configuration and the query?   Memory allocating queries request memory based on estimation (ideal memory) and query memory (workspace ...

SQL Server estimates Memory requirement at compile time, when stored procedure or other plan caching mechanisms like sp_executesql or prepared statement are used, the memory requirement is estimated based on first set of execution parameters. This is a common reason for spill over tempdb and hence poor performance. Common memory allocating queries ...

  The most common performance mistake SQL Server developers make: SQL Server estimates memory requirement for queries at compilation time. This mechanism is fine for dynamic queries that need memory, but not for queries that cache the plan. With dynamic queries the plan is not reused for different set of parameters values / predicates and ...

I want to inform you that we have organized the "3 Day Level 400 Microsoft SQL Server 2008 and SQL Server 2005 Performance Monitoring & Tuning Hands-on Workshop" in London, United Kingdom during March 15-17, 2011. This is a truly level 400 hands-on workshop and you can find the Agenda, Prerequisite, Goal of the Workshop and ...

More hardware normally delivers better performance, but there are exceptions where it can hinder performance. Understanding these exceptions and working around it is a major part of SQL Server performance tuning.   When a memory allocating query executes in parallel, SQL Server distributes memory to each task that is executing part of the ...

When SQL Server sorts or does merge join in parallel the query performance highly depends on other concurrent CPU intensive activities taking place on the server.   In the below example you will see that when we execute a query with MAXDOP 0 it completes in less than a second. When we have a CPU intensive query executing over a CPU core, ...

We are organizing the 3 Day Truly Level 400 Microsoft SQL Server 2008 and SQL Server 2005 Performance Monitoring & Tuning Workshop in London, United Kingdom during November 17-19, 2010.   Let us know if you or one of your colleagues is interested in participating in the workshop.   You can register for ...

Plan freezing is an interesting feature, targeting plan stability, performance predictability. We all want stability, especially during the good times. There are exceptions, there are always exceptions, that’s what software testing is all about, there is no software without flaws, you have to learn to identify and navigate around the flaws. Most ...