Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2008 R2’

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.

Advertisements

Read Full Post »

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 »

Most people prefer to have “sa”  account as the database owner, primary reason being sa login cannot be removed/deleted unlike any user account or service account and so the databases will never end-up in an orphaned stage.

I came-up with the below method to change the ownership to sa on all the 40 databases in our environment recently.

Step 1: Check the databases that does not have sa account as the owner

SELECT name AS DBName, suser_sname(owner_sid) AS DBOwner  
FROM sys.databases
WHERE suser_sname(owner_sid) <> 'sa'
 

Step 2: Generate the scripts to make sa account as owner for all the databases

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];'
from sys.databases
where name not in ('master', 'model', 'tempdb', 'msdb')
AND suser_sname(owner_sid) <> 'sa'
 

Step 3: Execute the result set from step 2 above to change the ownership to sa

--Sample result set from step2 above
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2012] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[Northwind] TO [sa];
ALTER AUTHORIZATION ON DATABASE::[Pubs] TO [sa];
 

For more information on sa account you can check my previous blog post HERE

Read Full Post »

Most of us know the default port for SQL Server is 1433, but there are various ports being used by SQL Server for other database services and SQL features.

In the below table I tried to list the ports that are frequently used by the Database Engine

Item Port
Default instance TCP port 1433
Named instance in default configuration Dynamic port. You can configure named instances to use fixed TCP ports
Dedicated Admin Connection (DAC) TCP port 1434
SQL Server Browser service UDP port 1434
SQL Server instance running over an  HTTP end-point TCP port 80 for CLEAR_PORT traffic   TCP port 443 for SSL_PORT traffic
Service Broker TCP port 4022
Replication TCP port 1433 for default instance
Transact-SQL Debugger TCP port 135
Analysis Services TCP port 2383 for default instance
Reporting Services Web Services TCP port 80
Reporting Services configured for use through HTTPS TCP port 443
Integration Services: Microsoft remote
procedure calls
TCP port 135
Integration services run time TCP port 135
Microsoft Distributed Transaction Coordinator (MS DTC) TCP port 135
SQL Server Management Studio browse connection to browser service UDP port 1434

You can find more information about the TCP and UDP ports SQL Server uses from books online here: Configure the Windows Firewall to Allow SQL Server Access

Read Full Post »

An auto-growth event is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file should grow is determined by the value that is set in the SQL configuration. While every organization has its own standards for this setting, however there are some widely followed best practices like for instance the auto-growth setting should not be in percent value

In this post I have put down two scripts that you can customize per your organizational standards. The first script helps you to identify the current settings and the second one can be used to update auto-growth settings. Both the scripts are targeted for all the databases by default, however you can call out the databases that you want to exclude from this change. Please make sure that you test these changes before pushing out to production servers.

Script 1: Identify the current database settings

select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN
CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN
CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size)
END AS [max_size]
, physical_name
from sys.master_files mf

Script 2: Update database auto-growth values

/*
****MODIFICATION REQUIRED for AUTOGROWTH -- See line 64 below****
1) Use this script to change the auto growth setting of
   for all databases
2) If you want to exclude any database add the DBs in the
   WHERE Clause -- See line 50 below
3) Tested in 2012 and 2014 SQL Servers
*/

IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')
DROP TABLE ConfigAutoGrowth
GO
CREATE TABLE DBO.ConfigAutoGrowth
(
iDBID INT,
sDBName SYSNAME,
vFileName VARCHAR(max),
vGrowthOption VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth Created'
GO
-- Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT
SD.database_id,
SD.name,
SF.name,
CASE SF.status
WHEN 1048576 THEN 'Percentage'
WHEN 0 THEN 'MB'
END AS 'GROWTH Option'
FROM SYS.SYSALTFILES SF
JOIN
SYS.DATABASES SD
ON
SD.database_id = SF.dbid
GO

-- Dynamically alters the file to set auto growth option to fixed mb
DECLARE @name VARCHAR ( max ) -- Database Name
DECLARE @dbid INT -- DBID
DECLARE @vFileName VARCHAR ( max ) -- Logical file name
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql

DECLARE db_cursor CURSOR FOR
SELECT
idbid,sdbname,vfilename,vgrowthoption
FROM configautogrowth
--WHERE sdbname NOT IN ( 'master' ,'msdb' ) --<<--ADD DBs TO EXCLUDE
--AND vGrowthOption = 'Percentage' or 'Mb'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)

/******If you want to change the auto growth size to a different 
value then just modify the filegrowth value in script below *********/

SET @Query = 'ALTER DATABASE '+ '[' + @name + ']' +'
MODIFY FILE (NAME = '+ '[' +@vFileName + ']' +',FILEGROWTH = 5MB)' --<<--ADD AUTOGROWTH SIZE HERE
EXECUTE(@Query)

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption
END
CLOSE db_cursor -- Closing the curson
DEALLOCATE db_cursor -- deallocating the cursor

GO
-- Querying system views to see if the changes are applied
DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)
SET @SQL=' USE [?]
SELECT ''?'' [Dbname]
,[name] [Filename]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''
ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''
END [Autogrow_Value]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM [?].sys.database_files'

IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')
DROP TABLE ##Fdetails
CREATE TABLE ##Fdetails
(Dbname VARCHAR(50),Filename VARCHAR(50),
Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))
INSERT INTO ##Fdetails
EXEC sp_msforeachdb @SQL
SELECT * FROM ##Fdetails ORDER BY Dbname

--Dropping the staging table
DROP TABLE ConfigAutoGrowth
GO

Read Full Post »

In order to reduce Tempdb contention one of  the best practices is to maintain multiple sized Tempdb data files, matching the number of processors and up to a maximum of 8. In this post I will show you T-SQL script to identify current Tempdb configuration and number of logical processors along with adding additional Tempdb data files as required.

Script 1: Find current tempdb configuration


select DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'

Script 2: Find number of logical processors

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info

Script 3: Add tempdb data files as per processor count from the above query

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
FILENAME = N'D:\DBA\Data\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --<<--Update the data file location/Size/AutoGrowth
GO

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',
FILENAME = N'D:\DBA\Data\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
GO
---ETC, add files as per processors count

Reboot/Restart of SQL services is not required for making the tempdb changes. Here is a great post that explains how to best remove extra Tempdb files.

For more information:

 

Read Full Post »

Often times it is required to find out the SQL name from the host names in a clustered environment so you can use it to connect through SSMS. There are many ways you can achieve this, however I am showing 2 methods here. Method 1 is the most common way using Failover Cluster Manager (ideal when there is one or just a few servers) and second method focuses on getting the same information using PowerShell (Ideal when you are dealing with a large number of servers)

Method 1:

The easiest way (if not the fastest) is to load up the Failover Cluster Manager and go to each Failover Clustered instance to look for the virtual SQL names. You can also easily see which FCIs are running on a particular node by selecting a node and viewing what resources it currently owns as shown in the below snippets.

Poweshell_SQLVirtualName

This is the same for Windows 2003/ 2008+ and SQL 2005/ 2008.

Method 2:

While the above method is pretty easy, its not practical to log into each server to load up FCM when you have tens of hundreds of servers. For this you can use this simple PowerShell script that can be run remotely and can be fed with as many servers as you want to. This will output the SQL virtual names including those with named instances. Please note that the PowerShell cmdlets used here require admin rights on all of the nodes in the cluster or a security exception will be thrown.

Steps:

1.  Create a notepad file “TestServers” on your desktop that contains the list of servers (Host names) that you want to run the code against

2

2. Open Powershell with Admin Privileges

3. Use the below script to load the servers list  into a Variable (Copy & paste the below code in PowerShell and click Enter – Make sure to change the location of your text file)

$machines = get-content C:\Users\Desktop\testservers.txt 

4. Use the below script to call the servers from the above variable to get the output

import-module failoverclusters
foreach ($machine in $machines) {get-clusterresource -Cluster 
$machine -ErrorAction SilentlyContinue| where-object 
{$_.ResourceType -like ?SQL Server?}| 
get-clusterparameter VirtualServerName,InstanceName | 
group-object ClusterObject | select-object @{Name = ?SQLInstance?;
Expression = {[string]::join(?\?,($_.Group | 
select-object -expandproperty Value))}}} 

Read Full Post »

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 »

Though there are several ways for importing SSIS packages into SQL Server either by creating a stored procedure or using a SSIS package itself, I find this simple code of line much easier and handy to work with.  It utilizes the “dtutil.exe” application which is installed by default with SQL Server installation. It is capable of importing and exporting packages and is found in the Binn folder of SQL Server (For eg: C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe)

Below is a post from Suresh, which describes how dtutil works for importing and exporting one package at a time.

How to Copy or Export an SSIS Package Using Command Prompt Utility – DTUTIL

In this article we will see deploying multiple SSIS packages.

Demo for deploying multiple packages.

1. Open command prompt where the packages (.dtsx) are stored

Tip: Hold ‘Shift’ and right click to open command prompt window at the desired location.

2

2. Execute the dtutil script from cmd.

for %I in (*.dtsx) do dtutil /FILE "%I" /COPY SQL;"/Packages/%~nI" /DESTSERVER localhost

3

Note to make the below changed before you run the above script.

  • Change the package folder location where the SSIS packages will be deployed
  • Change the server name at the end of the code to reflect the destination server. We can choose ‘localhost’ if running this within the server as shown in the below figure.

4

As seen in the above figure all our packages are imported into the server.

For more dtutil Utility options please see the MSDN article HERE.

Read Full Post »

Before we get started on this topic, here is a quick fact..in SQL Server 2000, there used to be hard limit on the data that can be stored in a single row, which is 8,060 bytes. So, if the data exceeds this limit, the update or insert operation would fail!

Fortunately, in later SQL Server versions, rows are dynamically managed to exceed this limit and the combined width of the row can now exceed the 8,060 byte limit. I wanted to refresh this in our memory as this will help us to better understand the allocation units concept.

What are Allocation Units in SQL Server:

Every partition in a SQL Server table can contain 3 types of data, each stored on its own set of pages. And each of these types of pages is called an Allocation Unit. Below are the 3 types of Allocation Units.

  • IN_ROW_DATA
  • ROW_OVERFLOW_DATA
  • LOB_DATA

So, an Allocation Unit is basically just a set of particular type of pages. Now, let us try to understand each of these allocation units using a demo.

  • IN_ROW_DATA 

When the row size stays within the 8,060-byte limit, SQL Server stores all of the data in the IN_ROW_DATA allocation unit and usually this unit holds the majority of data in most of the applications.

To better explain the concept, I came up with this simple Demo:

--Create a sample db AllocationUnitsDemo
USE master
GO
CREATE DATABASE AllocationUnitsDemo
GO

--Cretae a sample table ProductDetails in the AllocationUnitsDemo db
--Total length of the row in this table is 1000 + 4000 = 5000 (< 8000)
Use AllocationUnitsDemo
GO
CREATE TABLE ProductDetails
(
ProductName varchar(1000),
ProductDesc varchar (4000), 
)
GO

--Check the allocation unit type
Use AllocationUnitsDemo
GO
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))

Results:
In_Row_Data
  • ROW_OVERFLOW_DATA 

Remember the introduction? so, when the row exceeds the 8,060-byte limit, SQL Server then moves one or more of the variable-length columns to pages in the ROW_OVERFLOW_DATA allocation unit.

We still have a limitation here for the row size. Though the combined width of the row can exceed the 8,060 byte limit, the individual width of the  columns must be within the limit of 8,000 bytes. This means we can have a table with two columns defined as nvarchar(5000), nvarchar(5000), but we are not allowed nvarchar(10000)

Demo Continued..

--Add an extra column to the above table ProductDetails
--Make the total length of the row to become 5000 + 4000 = 9000 (>8000)
Use AllocationUnitsDemo
GO
ALTER TABLE ProductDetails ADD ProductSummary nvarchar(4000) 

--Now, Check the allocation unit type
Use AllocationUnitsDemo
GO
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))

Results:
Row_OverFlow_Data
  • LOB_DATA 

If a column with LOB data type is defined, then SQL Server uses the LOB_DATA allocation unit. To know what data types are considered LOB and to get the list of LOB columns from a database, please refer my previous post: “SQL Server – Find all the LOB Data Type Columns in a Database Using T-SQL Script

Demo Continued..

--Add LOB data type column to the table ProductDetails
Use AllocationUnitsDemo
GO
ALTER TABLE ProductDetails ADD ProductImage Image

--Again, Check the allocation unit type
Use AllocationUnitsDemo
GO
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))

Results:
LOB_Data
--Cleanup
Use master
GO
DROP DATABASE AllocationUnitsDemo

How many Allocation Units can a Table have?

It actually depends on the number of partitions and indexes on the table.

To simplify the concept, as shown in the below picture, assume there is one table having no indexes (HEAP) and no partitions. Having no partitions mean, all of the table’s contents are stored in a single partition, meaning every table has at-least 1 partition.

AllocationUnits_Figure1

Based on the above, we can have upto 3 allocation units for a table with no partitions and no indexes. And how about if we have partitions and Indexes? Below is the formula I came up with to get the maximum possible number of allocation units per table.

  • No of Allocation Units = No of Partitions × No of Indexes × 3

AllocationUnits_Count

So, as we see from the figures above, a table can have up to 45 million allocation units in SQL Server 2012!

Read Full Post »

Older Posts »