Monthly Archives: September 2018

How to demonstrate the space usage of a Null Varchar(Max) column

Database Administrator, dba, SQL Server, T-SQL, tsql, , , , , , , , , , ,

An empty Varchar(Max) column uses a negligible amount of disk space. The script below demonstrates this by creating the table TestTb which contains one column named NullColumn that has a Varchar(Max) data type. When the table is created the column NullColumn is populated with 100,000 rows of Null.

The two readings below show the table when it has just been created and the table with 100,000 rows entered.

Results

IF OBJECT_ID('dbo.TestTb', 'U') IS NOT NULL
	DROP TABLE dbo.TestTb;

CREATE TABLE TestTb (NullColumn VARCHAR(MAX));
GO

sp_spaceused 'TestTb';

DECLARE @i AS INT;

SET @i = 0;

WHILE @i < 100000
BEGIN
	INSERT INTO TestTb (NullColumn)
	VALUES (NULL)

	SET @i = @i + 1
END;
GO

sp_spaceused 'TestTb';

IF OBJECT_ID('dbo.TestTb', 'U') IS NOT NULL
	DROP TABLE dbo.TestTb;

 

Grim Reaper icon

How to filter sp_who2 to create KILL statements

Database Administrator, dba, SQL Server, T-SQL, , , , , , , , , , , , , , , , ,

Until now the only way to kill wayward commands or queries in SQL Server was to run sp_who or sp_who2, look for the record with abnormal CpuTime or DiskIO readings (or look for the login of the guy who never knows what he’s doing), and take note of the corresponding Spid number by running your finger along the screen tracing it back to the ID so you don’t get confused and end up killing the wrong Spid from another record by mistake.

But now there’s a better way if you know the the likely culprit you want to kill in advance. The script below will allow you to filter the results of sp_who2 based on any of the returned columns. You’ll now be able to specify the database name or login name etc. and the query will return only the rows that match your constraints. Narrow the results down enough and you’ll be left with one record to kill. Then copy the results of the KillSpid column and paste to a new SSMS window. Now you should be left with one Kill command to run with no possibility of killing the wrong Spid.

IF OBJECT_ID('tempdb..#sp_who2') IS NOT NULL DROP TABLE #sp_who2
GO

CREATE TABLE #sp_who2 (
	Spid INT
	,Status VARCHAR(255)
	,LoginName VARCHAR(255)
	,HostName VARCHAR(255)
	,BlkBy VARCHAR(255)
	,DbName VARCHAR(255)
	,Command VARCHAR(255)
	,CpuTime INT
	,DiskIO INT
	,LastBatch VARCHAR(255)
	,ProgramName VARCHAR(255)
	,Spid2 INT
	,RequestId INT
	)

INSERT INTO #sp_who2
EXEC sp_who2

SELECT 'Kill ' + CONVERT(VARCHAR(MAX), SPID) AS KillSpid
	,Spid 
	,Status 
	,LoginName 
	,HostName 
	,BlkBy 
	,DbName 
	,Command 
	,CpuTime 
	,DiskIO 
	,LastBatch 
	,ProgramName 
	,Spid2
	,RequestId
FROM #sp_who2
-- Add any filtering of the results here :
WHERE DBName NOT IN ('master')
-- Add any sorting of the results here :
-- AND ProgramName = ''
-- AND DbName = ''
-- AND LoginName = ''
-- AND HostName = ''
-- AND Status = ''
ORDER BY Spid ASC
,DBName ASC;

DROP TABLE #sp_who2

 

How to convert pcm files to wav in Linux

Android, Audio, Linux, , , , , , , , , , , , , , , , ,

Over the weekend I had to make a recording on my Android phone. I used the app Virtual Recorder  thinking I’d be able to just share the file afterwards but Virtual Recorder creates pcm files not typical mp3 files. I sent the file over to my Linux laptop, running Linux mint, to convert it.

Initially I had some trouble getting the conversion to work. Here are the steps I took.

Open a terminal window and navigate to the where the pcm file is. If you don’t know how to do this use the GUI file explorer to navigate to the directory where the pcm file is and then right click on white space and click on the option to open a terminal window. The terminal will open already set to run against the directory the pcm file is located in.

If you try to run any of the commands below and you don’t have ffmpeg installed you will be notified ffmpeg needs to be installed and it will give you the command to type in and run to install it.

If you want to convert a Virtual Recorder file specially you can use this terminal command below changing the file names to what you need.

ffmpeg -f s16le -ar 11250 -ac 2 -i YouInputFileName.pcm -ar 44100 -ac 2 YouOutputFileName.wav

Converting pcm files created by other applications may have varying results due to the sample rate of the source file. For example if you replace 11250 with 22500 as in the command below your output file pitch and speed will be increased. So you may need to play around with sample rates to get the output right.

ffmpeg -f s16le -ar 22500 -ac 2 -i YouInputFileName.pcm -ar 44100 -ac 2 YouOutputFileName.wav

How to determine why a T-SQL command is unreasonably slow

Database Administrator, dba, How To, sql, SQL Server, System, T-SQL, tsql, , , , , , , , , , ,

If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.

The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.

/* Queries Not Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status <> 'running';

/* Queries Running */
SELECT ROW_NUMBER() OVER (
		ORDER BY r.total_elapsed_time DESC
		) AS Rn
	,st.TEXT AS SqlText
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.status = 'running';

 

This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.

Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.

  • status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
  • blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use EXEC sp_who2 to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session. 
    KILL blocking_session_id /*replace by the actual Id*/

    NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.

  • wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
  • wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
  • last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.