Feeds:
Posts
Comments

Archive for the ‘Queries’ 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.

Advertisement

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 – 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 »

SQL Server – Find all the LOB Data Type Columns in a Database Using T-SQL Script

Posted in LOB, Queries, T-SQL, tagged , , , on September 12, 2013| 1 Comment »

Today morning when I was working on a huge database containing lots of LOB data, I was required to know what tables have LOB data, and the list of LOB columns by table name along with the data type.

I found that starting SQL Server 2005, we can easily retrieve this information from the Information Schema Views, by specifying “COLUMN_NAME” in view_name. There may be even a better way to do this, but here is what I came-up with.

USE [AdventureWorks2012]
GO
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE IN ('FILESTREAM','XML','VARBINARY','TEXT','NTEXT','IMAGE') 
OR(DATA_TYPE IN ('VARCHAR', 'NVARCHAR') AND CHARACTER_MAXIMUM_LENGTH = -1)
ORDER BY TABLE_NAME

HERE is an explanation by Pinal Dave, why I have to include CHARACTER_MAXIMUM_LENGTH = -1 in the above query

Sample Output:

Untitled

Below column types are considered LOB (Large Objects):
VARCHAR(MAX), NVARCHAR(MAX), FILESTREAM, XML, VARBINARY,
TEXT, NTEXT, IMAGE

Note: Data types TEXT, NTEXT and IMAGE were deprecated in SQL Server 2012 and are going to be removed in future versions, so Microsoft doesn’t recommend using them in new applications. For more information, please refer HERE

Read Full Post »

SQL Server – Get all Login Accounts Using T-SQL Query – SQL Logins, Windows Logins, Windows Groups

Posted in Queries, Security, T-SQL, tagged , , , on August 6, 2013| 39 Comments »

Earlier today I was required to pull the list of all SQL Login Accounts, Windows Login Accounts and Windows Group Login Accounts (basically all the Logins along with the Account Type of the Login) on one of the SQL Server instance where there are close to a hundred Login Accounts existing.

Doing it from SSMS GUI will take forever. So, I wrote a simple T-SQL script using which I was able to pull out all that information in less than a second!

Get the list of all Login Accounts in a SQL Server

SELECT name AS Login_Name, type_desc AS Account_Type
FROM sys.server_principals 
WHERE TYPE IN ('U', 'S', 'G')
and name not like '%##%'
ORDER BY name, type_desc

Get the list of all SQL Login Accounts only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'S'
and name not like '%##%'

Get the list of all Windows Login Accounts only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'U'

Get the list of all Windows Group Login Accounts only

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'G'

Note: Requires ALTER ANY LOGIN server permission to be able to view all the logins.

Read Full Post »

SQL Server – Get the Default Data File and Log File Location using T-SQL Query

Posted in Files and FileGroups, Queries, tagged on May 23, 2013| Leave a Comment »

In SQL Serve 2012, we can get the default data file and log file locations using a simple T-SQL query as below:

SELECT SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') AS [Default_Data_path]
,SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') AS  [Default_log_path]
GO

Sample Output:

Default-Data-Path-Log-path

These parameters INSTANCEDEFAULTDATAPATH & INSTANCEDEFAULTLOGPATH are new in SQL Server 2012 and are not documented yet. Usually undocumented features like these aren’t tested rigorously which is why Microsoft says they are not intended to be used by customers.

Read Full Post »