Tag: Microsoft SQL Server | Page 4

Useful Scripts for SQL Server Performance Tuning

A couple of useful scripts of sql commands to aid performance tuning in SQL Server (T-SQL).

This handy T-SQL script was from SQL Authority shows all queries that are currently running on your server.

SELECT sqltext.TEXT,
  req.session_id,
  req.status,
  req.command,
  req.cpu_time,
  req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Any running query in the result can be killed by using the KILL command followed by the session id.

KILL [session_id]

Database I/O Information

This one is taken from the book:
Professional Microsoft SQL Server 2014 Administration

-- Database IO analysis.
WITH IOFORDATABASE AS 
(
  SELECT 
    DB_NAME(VFS.database_id) AS DatabaseName
    , CASE WHEN smf.type = 1 
      THEN 'LOG_FILE' ELSE 'DATA_FILE' END
	    AS DatabaseFile_Type
    , SUM(VFS.num_of_bytes_written) AS IO_Write
    , SUM(VFS.num_of_bytes_read) AS IO_Read
    , SUM(VFS.num_of_bytes_read + VFS.num_of_bytes_written) 
        AS Total_IO
    , SUM(VFS.io_stall) AS IO_STALL
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
  JOIN sys.master_files AS smf
  ON VFS.database_id = smf.database_id
  AND VFS.file_id = smf.file_id
  GROUP BY DB_NAME(VFS.database_id), smf.type
)
SELECT
  ROW_NUMBER() OVER (ORDER BY io_stall DESC) AS RowNumber
  , DatabaseName
  , DatabaseFile_Type
  , CAST(1.0 * IO_Read / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_Read_MB
  , CAST(1.0 * IO_Write / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_Write_MB
  , CAST(1. * Total_IO / (1024 * 1024) AS DECIMAL(12,2)) 
      AS IO_TOTAL_MB
  , CAST(IO_STALL / 1000. AS DECIMAL(12, 2)) 
      AS IO_STALL_Seconds
  , CAST(100. * IO_STALL / SUM(IO_STALL) OVER() AS DECIMAL(10,2))
      AS IO_STALL_Pct
FROM IOFORDATABASE
ORDER BY IO_STALL_Seconds DESC

How to Get Names of All Tables in SQL Server

Here’s a list of sql commands (T-SQL) to get the names of all tables, views, sprocs and triggers in SQL Server:

-- Get all Tables
SELECT name FROM sys.tables

-- Get all Views
SELECT name FROM sys.views

-- Get all Stored Procedures
SELECT name 
  FROM sys.procedures

-- Get all Triggers
SELECT name FROM sys.triggers

If you want to see all objects within the database, use the following SQL command:

SELECT name, type, type_desc
  FROM sys.objects

Try this one to also include all column names info:

SELECT * FROM sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id

How to Get Month Number from Month Name and Year (T-SQL)

From a set of data in SQL Server database, we needed to find a way to quickly get a numeric representation (add a field MonthNo) of a month name in order to do a comparison. Given we only have a month name and year fields here’s a simple way to do it.

Original SQL:

SELECT [Id], [Month], [Year] 
FROM Table1

SQL Server: Query Result (Id, Month, Year)

Add a field called MonthNo:

MONTH(CONVERT(NVARCHAR, [Month]) 
	+ ' ' + CONVERT(NVARCHAR, [Year])
) AS MonthNo

Final SQL with the new column:

SELECT [Id]
	  , MONTH(CONVERT(NVARCHAR, [Month]) + ' ' 
	  + CONVERT(NVARCHAR, [Year])) AS [MonthNo]
	  , [Month], [Year] 	
FROM Table1

SQL Server: Query Result (Id, MonthNo, Month, Year)

How to Calculate Duration Between Two Dates in T-SQL

A simple way to calculate the duration of a running program with a start and end time. The information should be presented in an hour:minutes:seconds format with leading zeroes (i.e., 00:00:00). Two local variables are used to demonstrate the query.

Typically, this is more useful if done as a stored procedure.

DECLARE @StartTime DATETIME = '2013-07-16 13:02:29';
DECLARE @EndTime DATETIME;
DECLARE @Duration INT;

SET @EndTime = '2013-07-16 15:29:31';

SET @Duration = DATEDIFF(second, @StartTime, @EndTime);

SELECT RIGHT('0' + CONVERT(VARCHAR(2), @Duration/3600), 2) 
   + ':' + RIGHT('0' + CONVERT(VARCHAR(2), @Duration%3600/60), 2) 
   + ':' + RIGHT('0' + CONVERT(VARCHAR(2), @Duration%60), 2) 
AS Duration;

The result here is printed with a column heading Duration and a value of 02:27:02.
SQL Server DateDiff Result

How to Copy Data from One Table to Another

A quick way to populate data from one table to another using Transact-SQL. The objective is to create a copy of the table and populating it at the same time and then create an SQL script to refresh the data when necessary. This can be achieved by using two similar SQL (three if we want to include the TRUNCATE command).

The first run we use “SELECT INTO” statement to query the source table and create a new table and populate it at the same time.

SELECT [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date] 
INTO [dbo].[DiscoveryAgentCopy] 
FROM [dbo].[DiscoveryAgent]
GO

You can use * if you want to copy all fields from the table.

SELECT * INTO [dbo].[DiscoveryAgentCopy] 
FROM [dbo].[DiscoveryAgent]
GO

If the copy table is already existed and we want to refresh only the data, we use “INSERT SELECT” statement to read the data from source and map them to the fields on destination table.

-- CLEAR COPY TABLE OF ALL DATA
TRUNCATE TABLE [dbo].[DiscoveryAgentCopy]
GO

-- POPULATE DATA
INSERT INTO [dbo].[DiscoveryAgentCopy] (
       [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date])
SELECT [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date]
FROM [dbo].[DiscoveryAgent]
GO

« Previous Page

Useful Scripts for SQL Server Performance Tuning

Database I/O Information

Further Reading

How to Get Names of All Tables in SQL Server

Further Reading

How to Get Month Number from Month Name and Year (T-SQL)

Further Reading

How to Calculate Duration Between Two Dates in T-SQL

Further Reading

How to Copy Data from One Table to Another

Recent Posts

Database I/O Information

Further Reading

Further Reading

Further Reading

Further Reading

Recent Posts

Tags