Feeds:
Posts
Comments

Archive for the ‘Performance’ Category

SQL Server – Capture Execution Plan of a Currently Running Query/Process SPID

Posted in Performance, Queries, T-SQL, tagged , , , on June 4, 2015| 4 Comments »

DBAs most often face scenarios where they need to capture graphical execution plan of a query currently running on a live production instance because of multiple reasons like:

  • why a particular SPID is causing blocking
  • why is my query running slow
  • why isn’t the index getting used
  • which operator is costing more and why

While there are multiple ways to retrieve the execution plan, below is the query I always keep handy as I can run this safely on a live production server with minimal effort.

SELECT CONVERT(XML, c.query_plan) AS ExecutionPlan
FROM sys.dm_exec_requests a with (nolock)
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
OUTER APPLY sys.dm_exec_text_query_plan (a.plan_handle, a.statement_start_offset, a.statement_end_offset) c
LEFT JOIN sys.dm_exec_query_memory_grants m (nolock)
ON m.session_id = a.session_id
AND m.request_id = a.request_id
JOIN sys.databases d
ON d.database_id = a.database_id
WHERE  a.session_id = @@SPID --replace @@SPID with the SPID number for which you want to capture query plan
ORDER BY a.Start_Time

Hope this will be a good addition to your query bank.

Read Full Post »

SQL Server – How to Calculate Length and Size of any field in a SQL Table

Posted in LOB, Performance, Queries, T-SQL, tagged , , , , , on June 4, 2015| 7 Comments »

Recently I came across a situation where queries are loading extremely slow from a table. After careful analysis we found the root cause being, a column with ntext datatype was getting inserted with huge amounts of text content/data. In our case DATALENGTH T-SQL function came real handy to know the actual size of the data in this column.

According to books online, DATALENGTH (expression) returns the length of the expression in bytes (or) the number of bytes SQL needed to store the expression which can be of any data type. From my experience this comes very handy to calculate length and size especially for LOB data type columns (varchar, varbinary, text, image, nvarchar, and ntext) as they can store variable length data. So, unlike LEN function which only returns the number of characters, the DATALENGTH function returns the actual bytes needed for the expression.

Here is a small example:

Use AdventureWorksLT2012
GO
Select ProductID, DATALENGTH(Name) AS SizeInBytes, LEN(Name) AS NumberOfCharacters
FROM [SalesLT].[Product]

–Results

DATALENGTH

If your column/expression size is too large like in my case, you can replace DATALENGTH(Name) with DATALENGTH(Name)/1024 to convert to KB or with DATALENGTH(Name)/1048576 to get the size in MB.

Read Full Post »

SQL Server – Aborted Online Index Operation & Observed Elevated Values for PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK Wait Types

Posted in Indexes, Performance, TempDB, tagged , on August 12, 2014| Leave a Comment »

Last week on one of our production servers (version 2011.110.3373.0), we’ve encountered a strange issue which is relatively new (only applicable for SQL Server 2012 and 2014 versions). Based on the symptoms observed and doing a quick Bing search found we encountered a product bug!

I observed multiple SPIDs, around 300+ running the same stored procedure which is doing an INSERT operation and are in a suspended status, they seemed to be hanging there for atleast like 45 minutes (usually they finish in few seconds). There is no blocking and I found nothing wrong with the SQL Server except for started seeing elevated values for PWAIT_MD_RELATION_CACHE and MD_LAZYCACHE_RWLOCK wait types. And, oh! I also noticed that tempdb data was at 99% full.

So I thought I found the root cause and tried mitigating the problem by adding extra tempdb space but still wasn’t any help. As the last resort I had to failover the services to another node, basically restarted SQL Server, to bring the server fully functional.

On further investigation we found the root cause as someone tried to create an index (online) to improve performance, but later he cancelled the create index and we hit this bug. However, this bug only effected the table on which index operation was attempted and so, everything else was operational except for that Stored Procedure running multiple SPIDs trying to INSERT to the table on which the user attempted CREATE INDEX command was blocked, impairing that part of functionality of the application.

Resolution: Cumulative Update 9 for SQL Server 2012 SP1; Cumulative Update 1 for SQL Server 2014

Here are more details about this problem: http://support.microsoft.com/kb/2926712

Read Full Post »

SQL Server – Using Tools to Analyze Query Performance

Posted in Performance, Queries, Videos, tagged , , on February 11, 2014| Leave a Comment »

Here is the session I gave for the techies at Microsoft about in-built tools available in SQL Server to analyze a query performance. This is a bit lengthy, but covers ALL out of the box tools in SQL Server to get query metrics.

Read Full Post »