Understanding SQL Server Query Optimization – Part 2. In the first of this four part series I gave an overview of the tools and techniques of query optimization in SQL Server.
In this second part, I will talk about the process of displaying and interpreting query execution plans. I will also talk about how to generate query execution plans using SQL Server Management Studio, SHOWPLAN_TEXT and SHOWPLAN_ALL. An execution plan is the sequence of operations SQL Server query optimizer performs to run the statements. The SQL Server query optimizer creates an execution plan before it actually runs a query. As per Microsoft Book Online, “Execution plans graphically display the data retrieval methods chosen by the SQL Server query optimizer. You can use SQL Server Management Studio to view the execution plan by clicking the Display Estimated Execution Plan or the Include Actual Execution Plan button on the query editor toolbar.
  Estimated vs. Estimate execution plan shows the expected steps and required resources. Benchmark SQL Server Wait Statistics. CREATE SCHEMA Waits Go CREATE TABLE Waits.WaitStats (CaptureDataID bigint, WaitType varchar(200), wait_S decimal(20,5), Resource_S decimal (20,5), Signal_S decimal (20,5), WaitCount bigint, Avg_Wait_S numeric(10, 6), Avg_Resource_S numeric(10, 6),Avg_Signal_S numeric(10, 6), CaptureDate datetime) CREATE TABLE Waits.BenignWaits (WaitType varchar(200)) CREATE TABLE Waits.CaptureData ( ID bigint identity PRIMARY KEY, StartTime datetime, EndTime datetime, ServerName varchar(500), PullPeriod int INSERT INTO Waits.BenignWaits (WaitType) --DROP PROCEDURE Waits.GetWaitStats CREATE PROCEDURE Waits.GetWaitStats @WaitTimeSec INT = 60, @StopTime DATETIME = NULL DECLARE @CaptureDataID int /* Create temp tables to capture wait stats to compare */ IF OBJECT_ID('tempdb..
DROP TABLE #WaitStatsBench IF OBJECT_ID('tempdb.. DROP TABLE #WaitStatsFinal. What is the CXPACKET Wait Type, and How Do You Reduce It? When you query sys.dm_os_wait_stats or use our wait stats sampling query, one of your biggest wait types is probably CXPACKET.
Let’s talk about what’s really going on. Here’s a 15-minute video explaining it, or you can keep scrolling down to read: Our SQL Server Stand-In: a Class. Who wants to help me scan the sales table? Pretend that you’re a schoolteacher in a classroom with a couple dozen students. There’s three ways you could do it: 1. An Introduction to SQL Server Wait Types and Stats. Ok, so in this post, I wanted to go over SQL Server wait types and statistics - what they are and how to check them.
My idea is then to start a series of posts which go into more detail about individual SQL Server wait types, their causes and how to improve them. At the time of writing this post, I do not know about all of the wait types in SQL Server and there are many of them. Some of them I have experience with, some of them I haven’t so this is a partially a learning experience for me, as well as hopefully providing you with informative content so that you can better understand your own SQL Server wait types.
So let us begin…. When troubleshooting performance issues in SQL Server, it is difficult to know where to start as there is so much to look at. The best way is to look at your SQL Server at a high level and then drill down to the root causes. Ultimately this is more efficient than just assuming that something is, when it may not actually be. Permissions needed…. Software to Improve SQL Server Health.
Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server. The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan.
This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel. Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately. This article discusses the general guidelines that you can use to configure the max degree of parallelism option for SQL Server when you use the sp_configure system stored procedure.
The OPTION (MAXDOP) Transact-SQL query hints can override the max degree of parallelism option in the sp_configure value if the query specifies this option. Soft NUMA Parallel index Use the following guidelines when you configure the MAXDOP value.