How to understand CXPACKET wait type resource description in Sys.dm_os_waiting_tasks

Question : When checking on waitin tasks through the Sys.dm_os_waiting_tasks view , I noticed in the resource description column some references which are unfamiliar to me . The wait type was a cxpacket and the resource description was similar to: exchangeEvent id=Pipe5677568b0 WaitType=e_waitPipeNewRow nodeId=2 How do I parse these details to make sense? Answer: The resource description column is a "description of the resource that is being consumed". For different types of wait types there are different nomenclatures. For the wait type cxpacket , the breakdown meanings are: exchangeEvent id={Port|Pipe} WaitType= nodeId= Believe it or not , this is useful... Read more →

SQL Indexed View limitations

SQL Server Indexed Views remain a powerful technique for improving sql query performance , if utilised in the right context. The most effective use is to provide a pre-aggregated or pre-calculated view of the base data. In other words, users are able to query an aggregation of data without having to process the complex JOINS and aggregates when executing a request. For example, in a CRM , a daily batch job may pump data into the warehouse. A workflow may run which pre-aggregates the data into an indexed view. The pre-aggregation process may take 15 minutes, but the user does... Read more →

Precalculated SQL Views data

Question: I have a SQL View returning data .The SQL Server View is not schema bound. Normally users do a SELECT col1,col2,col3 FROM MYVIEW . The data sets have grown and the users want to start filtering data , such as SELECT col1,col2,col3 FROM MYVIEW WHERE COL1 > xxxx. If the users filter data on the SQL data , will all the data be returned via the VIEW and then filtered or will just the filtered data be returned? Answer: A View is a pre-defined query. The Microsoft documentation defines a View as “virtual table whose contents are defined by... Read more →

Performance Tuning – The wrong way

Example 1: There are no performance requirements. No one has set any expectations. Job Done! It is very common for performance requirements to be an afterthought. Setting some performance criteria as part of the planning phase , will help DBAs focus on server architecture and implementation. Example 2: Tuning with the power of mysticism or relying on religion . Performance Tuning is all about understanding the applications and setting performance expectations . This requires measuring . You can read all the performance manuals – they won’t give you the power of context. To paraphrase Malcolm Gladwell “SQL Servers are sensitive... Read more →

Find Table Scans in Query Plan Cache

Question: I’m managing a SQL Server with queries running much slower than normal. I’ve analysed the sys.dm_db_index_usage_stats for heap scans. To identify the heap statistics I identified rows where the column index_id returned a value of zero. I’ve identified some heaps with a very high level of usage . How can I find the queries which are creating these large amounts of user scans? Answer: Using the sys.dm_db_index_usage_stats is a good method to identify heaps. Checking the user scans is a good way of finding clues for inefficient queries. It does not guarantee these queries are the root cause, but... Read more →