How to analyse SQL Server performance. Note that in this example every request is actually suspended. At this moment the server is basically doing nothing. We have 4 sessions 5. KEY 5 7. 20. 57. Session 5. So everybody waits on session 5. We can also see, from the waittime column, how long has each session waited for about 1. Notice how in the previous example we had only one request actually running out of 5. Im running these queries on a workstation with 8 cores, plenty of RAM and a decent disk, so there is plenty of hardware to carry these requests, but instead they are most of the times waiting instead of executing. To make your requests faster you need to have them be executing rather than waiting. Sql Server 2005 Tempdb Multiple Log Files' title='Sql Server 2005 Tempdb Multiple Log Files' />THIS TOPIC APPLIES TO SQL Server starting with 2008 Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse. Creates a new database and the files used. The 1 SQL Server community and education site, with articles, news, forums, scripts and FAQs. SQL Server Installation Checklist for new server configuration and auditing existing server configurations based on established best practices. Is having most or even all requests waiting, rather than executing, something unusual Not at all, this is the norm Any time you will look at a SQL Server executing an even moderate load, youll see that most requests are waiting and only a few are executing. What you need to watch out for a long waits or short but repeated waits that add up. Sql Server 2005 Tempdb Multiple Log Files' title='Sql Server 2005 Tempdb Multiple Log Files' />Download the latest version of the Free SQL Server database health monitoring tool by Stedman Solutions, LLC. Created by Steve Stedman sqlEmt. Should you enable trace flags 11 in SQL Server to help prevent tempdb contention Find out here. Answering the question of how many data files you should configure for your tempdb. The answer is NOT one per logical processor core What version of SQL Server do I have This unofficial build chart lists all of the known Service Packs SP, Cumulative Updates CU, patches, hotfixes and other. Long waits indicate some resource that is held for long times, and typically this occurs with locks. Repeated short waits indicate a resource that is being saturated, possibly a hot spot for performance. Before I go further I just want to show sys. SQL Server DMV specifically designed to show currently waiting tasks. Session 5. 3 is waiting the log to flush. Session 5. 7 is waiting for 4. Session 5. 4 is waiting for 3. Torrent Scat there. The situation we have here is much similar to the previous one, where we had 4 SELECT sessions blocked by an INSERT. He can see here two requests blocked by attempting to read a row so theyre probably SELECT and they blocking session is waiting for its transaction to durably commit. The information in this DMV is very similar to the one in sys. To understand CXPACKET wait types you need to look into child parallel tasks. When a statement can benefit from parallel execution the engine will create multiple tasks for the request, each processing a subset of the data. Each one of these tasks can execute on a separate CPUcore. The request communicate with these tasks using basically a producer consumer queue. The query operator implementing this queue is called an Exchange operator Im really simplifying here, read The Parallelism Operator aka Exchange for a more accurate description. If this producer consumer queue is empty meaning the producers did not push any data into it the consumer must suspend and wait and the corresponding wait type is the CXPACKET wait type. Requests that show this wait type are really showing that the tasks which should had produced data to consume are not producing any or enough data. These producer tasks in turn may be suspended, waiting on some other wait type and that is what is blocking your request, not the exchange operator. Here is an example. This article walks the user through installation of SQL Server 2012 on a Windows Server 2008 system using the SQL Server setup installation wizard. The. One of the most important areas of SQL Server performance is the tempdb database. It maintains temporary user tables and intermediate query results used to prepare. Here we can see that request on session 5. CXPACKET, one of the parallel child tasks is actually waiting on a page lock. Aggregated wait stats sys. SQL Server aggregates statistics about all wait types and exposes them in a sys. If looking at the currently executing requests and the waiting tasks showed us what was being waited at any moment, the aggregate stats give a cumulative status since server start up. Querying this DMV is straightforward, but interpreting the results is a bit more tricky. First, whats the deal with all those wait types that take the lions share at the top of the result DIRTYPAGEPOOL, REQUESTFORDEADLOCKSEARCH, LAZYWRITERSLEEP We did not see any request ever waiting for theseHere is why by adding the WHERE sessionid 5. SQL Server that are tasked with various maintenance jobs. Many of these background tasks follow a pattern like wait for an event, when the event is signaled do some work, then wait again, while other are patterned in a sleep cycle pattern sleep for 5 seconds, wake up and do something, go back to sleep 5 seconds. Since no task can suspend itself without providing a wait type, there are wait types to capture when these background tasks suspend themselves. As these patterns of execution result in the task being actually suspended almost all time, the aggregated wait time for these wait types often trumps every other wait type. The SQL Server community came to name these wait types benign wait types and most experts have a list of wait types they simply filter out, for example see Filtering out benign waits. WHERE waittype NOT IN. NCLRSEMAPHORE, NLAZYWRITERSLEEP. NRESOURCEQUEUE, NSQLTRACEBUFFERFLUSH. NSLEEPTASK, NSLEEPSYSTEMTASK. NWAITFOR, NHADRFILESTREAMIOMGRIOCOMPLETION. NCHECKPOINTQUEUE, NREQUESTFORDEADLOCKSEARCH. NXETIMEREVENT, NXEDISPATCHERJOIN. NLOGMGRQUEUE, NFTIFTSSCHEDULERIDLEWAIT. NBROKERTASKSTOP, NCLRMANUALEVENT. NCLRAUTOEVENT, NDISPATCHERQUEUESEMAPHORE. NTRACEWRITE, NXEDISPATCHERWAIT. NBROKERTOFLUSH, NBROKEREVENTHANDLER. NFTIFTSHCMUTEX, NSQLTRACEINCREMENTALFLUSHSLEEP. NDIRTYPAGEPOLL, NSPSERVERDIAGNOSTICSSLEEP. Now we have a more coherent picture of the waits. SQL Server instance. This can be an important step toward identifying a bottleneck cause. Furthermore, interpreting the data is data is subjective. Is the aggregate WRITELOG value of 4. I dont know Is an aggregate, accumulated since this process is running, so will obviously contiguously increase. Perhaps it had accumulated values from periods when the server was running smooth and from periods when it was running poorly. However, I now know that, from all non bening wait types, WRITELOG is the one with the highest total wait time. I also know that maxwaittime for these wait types, so I know that there was at least one time when a task had to wait 1. The waittaskcount tells me how many time a task waited on a particular wait type, and dividing waittimemswaittaskcount will tell me the average time a particular wait type has been waited on. And I see several lock related wait types in top LCKMS, LCKMIS and LCKMIX. I can already tell some things about the overall behavior of the server, from a performance point of view flushing the log is most waited resource on the server, and lock contention seems to be the next major issue. CXPACKET wait types are right there on top, but we know that most times CXPACKET wait types are just surrogate for other wait types, as discussed above. How about those other with types in the list, like SOSSCHEDULERYIELD, PAGELATCHEXSH or PAGEIOLATCHEXSHUPCommon wait types. In order to investigate wait types and wait times, either aggregate or for an individual query, one needs to understand what the wait type names mean. Many of the names are self describing, some are cryptic in their name and some are down right deceitfully named. I think the best resource to read a detailed description of the names is the Waits and Queues white paper. It has an alphabetical list of wait types, description and what physical resource is correlated with. The white paper is a bit old it covers SQL Server 2. The wait type names for important resources has not changed since SQL server 2. SQL Server 2. 00. The wait types are briefly documented also in the sys. MSDN. Disk and IO related wait types. PAGEIOLATCHThis is the quintessential IO, data read from disk and write to disk wait type. Administering Storage, IO, and Partitioning in Microsoft SQL Server 2. Whats New for DBAs When Administering Storage on SQL Server 2. This chapter focuses on the use and most effective configuration of storage components within a Database Engine instance of SQL Server 2. This chapter is from the book Storage and IO inputoutput within SQL Server 2. As a consequence, the well prepared DBA will want to spend a bit of extra time in planning, configuring, and tuning SQL Servers storage and IO settings. This chapter introduces the fundamental concepts around SQL Server storage and IO configuration and tuning. This overview includes hardware related topics, such as hard disks, RAID, and SAN. Going beyond overview, this chapter will delve into the best practices and industry standards for SQL Server administrator activities, such as the number and placement of database and transaction log files, partitions, and tempdb configuration. Most features related to SQL Server storage and IO are configured and administered at the database level. That means that administrative tasks in SQL Server Management Studio will typically focus on database level objects in the Object Explorer, as well as on database properties. Toward the end of this chapter, an important IO performance enhancing feature, data compression, is also discussed. Even though the chapter introduces and explains all the administration and configuration principles of the SQL Server 2. Storage Engine, you will occasionally be directed to other chapters for additional information. This is a result of the Storage Engine feature being so large and intricately connected to other features. SQL Server 2. 01. Storage Engine in several significant ways. The following are some of the important Storage Engine enhancements SQL Server 2. Velocity index. Columnstore indexes can improve read performance on read only tables by hundreds to thousands of time, with a typical performance improvement of around tenfold. Refer to Chapter 5, Managing and Optimizing SQL Server 2. Indexes, for more details on this new type of index. SQL Server has long supported creation, dropping, and rebuilding indexes while online and in use by users, with a few limitations. SQL Server 2. 01. XML, varcharmax, nvarcharmax, and varbinarymax columns may be handled while the index is still online and in use. Partitioning in SQL Server 2. SQL Servers storage methodology for storing unstructured data, FILESTREAM, has been improved. FILESTREAM allows large binary data, such as JPEGs and MPEGs, to be stored in the file system, yet it remains an integral part of the database with full transactional consistency. FILESTREAM now allows the use of more than one filegroup containing more than one file to improve IO performance and scalability. The Database Engine Query Editor now supports Intelli. Sense. Intelli. Sense is an autocomplete function that speeds up programming and ensures accuracy. To properly maximize the capabilities of SQL Server storage and IO, it is important to understand the fundamentals about storage hardware. The following section introduces you to most important concepts involving storage and IO hardware and how to optimize them for database applications.