John McCormack DBA

SQL Server Databases and Cloud

Free space in SQL server filegroups query

By 1 Comment

I just realised that in all my scripts that I use on a regular basis, I didn’t have one for working out free space in SQL Server filegroups. It’s not something that comes up too often but it’s handy to know. For methods of working out space in individual files, you could refer to this post on mssqltips.

-- Free space by filegroup
SELECT
FILEGROUP_NAME(data_space_id) as FilegroupName,
SUM(size/128.0) AS CurrentSizeMB, 
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS FreeSpaceMB,
(
	SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) / -- FreeSpaceMB
	(SUM(size/128.0))  -- CurrentSizeMB
	* 100 -- Convert to percentage
) AS FilegroupPercentFree,
COUNT(*) as NumberOfFilesInFilegroup
FROM sys.database_files
WHERE data_space_id <> 0
GROUP BY FILEGROUP_NAME(data_space_id);
colourful box files

POST #100

I just realised as well that this is post #100 on my blog. I started it just to save some of my queries and ideas. Post #1 was a really simple query but it got me going.

Logical query processing

By Leave a Comment

Logical query processing

Logical query processing describes the way in which the SQL Server processes a select query. This post used to be called “How does the SQL Server engine process my select query” but no-one was really searching for that. It was originally posted in Feb 2016, and was updated on September 2020.

Declarative Language

T-SQfwghsoL is the dialect of SQL used with SQL Server and is a declarative language. A declarative language takes the user’s request (or query) and works out the best way how to return the data. This differs from an imperative language in which the programmer tells the program how it should process the statement. With this is mind, it should be fairly easy with some simple syntax just to tell SQL Server what we want and get the results back.

An example in English could be “Bring me all my black shoes from the cupboard”. In SQL (and with a bit of stretch of the imagination), this would be written as:

SELECT item FROM dbo.Cupboard
WHERE item = 'shoes'
AND colour = 'black';

Despite the way we write the query, the SQL Server engine has other ideas about the processing order. The table below shows the order in English versus the order in SQL.

ENGLISH SQL SERVER
SELECT FROM
FROM WHERE
WHERE SELECT

So conversely, if we were to say this in English, it would be something like “From the cupboard, find all the black shoes and bring them to me”.

Why is it done this way?

SQL Server uses Logical query processing. First, I should point out we will be discussing 6 main clauses (but there are more). They are listed below in logical order with a description. If you read all 6 descriptions, it should be fairly clear why SQL Server processes the select query in this order. e.g. You cannot select something if the place it supposed to be (a table) does not exist. So we go to the table first.

THE MAIN CLAUSES
(In logical order)		  DESCRIPTION
FROM The FROM clause is executed first. This also includes JOIN which is a way of combining data from multiple tables into one result set. Thinking about this logically, the table(s) being queried must be identified first or we would have nothing to SELECT from.
WHERE A clause such as WHERE WHERE item = 'shoes'means that rows are filtered. Rows are not returned if the item does not equal shoes. Remember you must refer to the column names rather than an alias you have given as the WHERE clause is evaluated before the SELECT.
GROUP BY You can retrieve a set of all distinct row values based on what is included in the SELECT. e.g. SELECT Item, Colour FROM [TestDB].[dbo].[Cupboard] GROUP BY Item,Colour

This can also be aggregated. In this example, I show how many times these combinations of values are repeated throughout the table by using COUNT(*). SELECT Item, Colour, Count(*) AS [Count] FROM [TestDB].[dbo].Cupboard] GROUP BY Item,Colour
HAVING The HAVING clause is similar to WHERE in that it filters the data based on a predicate. The main difference is that it is evaluated AFTER the data has been grouped (and so is evaluated per group) unlike a WHERE clause which you can see is evaluated before a GROUP BY.

If we enhance the example above to include a HAVING clause (say for groups with a count > 1), the syntax would be: SELECT Item, Colour, COUNT(*) as [Count] FROM [TestDB].[dbo].[Cupboard] GROUP BY Item,Colour HAVING COUNT(*) > 1
SELECT Whilst it is written at the beginning of the T-SQL query, the SELECT clause is evaluated near the end. Remember to include a DISTINCT after your SELECT clause if you want to ensure you return no duplicates.

At this stage, you are returning a relational result. You have not guaranteed the order in which you want to see the result set. This means the same query could return the same results in a different order time and time again.
ORDER BY The ORDER BY clause allows you to present the data in a deterministic way. e.g.[sql]ORDER BY [Count] Desc[/sql] Without an order by, there is no guarantee that the same query will return the rows in same order if executed more than once.

Notice here that because this is evaluated last and unlike the WHERE clause, you can refer to column Aliases because they have now been created.

Automate Your Stress Away – Run sp_whoisactive on a schedule

By 1 Comment

Automate collection of sp_whoisactive on a schedule

t-sql tuesday logoThis post is part of the September 2020 edition of #tsql2sday. Thanks to Elizabeth Noble for hosting (blog|twitter) and for coming up with a great topic. SQL Server has a great tool for scheduling repeatable tasks called SQL Agent. SQL Server also has a great community, who have written SQL Scripts so established and well known that some might think they come with SQL Server (or at least should). This post combines using SQL Agent with one of those invaluable community scripts, to help solve a real world problem.

When I took over a fairly troublesome SQL Server, I was always being asked what was running when the SQL Server was slow. That was fine when it was during the day and I could look. But often I would be asked, why was the server slow at 9pm last Friday? Or some other time. It’s not always the easiest thing to do as SQL Server doesn’t natively keep this information for you.

A quick but effective way to automate this is to download and schedule Adam Machanic’s sp_whoisactive stored procedure. I run this every 10 minutes but I have seen it scheduled as often as every 30-60 seconds. It depends on your system and your needs.

How to automate running sp_whoisactive on a schedule

  1. Download sp_whoisactive from GitHub if you don’t have it already
  2. Run the code to create the stored procedure on your SQL Server
  3. Use my t-sql below to create an agent job that collects the data (and deletes older rows)
  4. Change any of the parameters to suit your own needs
  5. Review data next time you are asked “What was running at X time”? by simply querying the table

Create SQL Agent Job

! Important ! I use a utility database called DBA for stored procedures such as sp_whoisactive. If you don’t have a database called DBA, please either create one or change the reference to the name of the database you want to create your table in.

USE [msdb]

GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'sp_whoisactive_log_to_table',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Log output of sp_whoisactive every x minutes',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Write output to table',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'IF NOT EXISTS ( SELECT 1 FROM dba.sys.tables WHERE NAME = ''sp_whoisactive_log'')
BEGIN
CREATE TABLE dba.dbo.sp_whoisactive_log
(
id INT IDENTITY(1,1) PRIMARY KEY,
[dd hh:mm:ss:mss] VARCHAR(20) NULL,
session_id SMALLINT NOT NULL,
sql_text XML NULL,
login_name sysname NULL,
wait_info NVARCHAR(4000) NULL,
CPU VARCHAR(30) NULL,
tempdb_allocations VARCHAR(30) NULL,
tempdb_current VARCHAR(30) NULL,
blocking_session_id SMALLINT NULL,
blocked_session_count VARCHAR(30) NULL,
reads VARCHAR(30) NULL,
writes VARCHAR(30) NULL,
physical_reads VARCHAR(30) NULL,
query_plan XML NULL,
locks XML NULL,
used_memory VARCHAR(30) NULL,
[status] VARCHAR(30) NULL,
open_tran_count VARCHAR(30) NULL,
percent_complete VARCHAR(30) NULL,
[host_name] sysname NULL,
[database_name] sysname NULL,
[program_name] sysname NULL,
start_time datetime NULL,
login_time datetime NULL,
request_id SMALLINT NULL,
collection_time datetime NULL
)
CREATE INDEX idx_collection_time ON dba.dbo.sp_whoisactive_log (collection_time)
END

/* Load data into table.
If you want to change parameters, you will likely need to add columns to table
*/
EXEC sp_whoisactive @get_locks = 1, @find_block_leaders = 1, @get_plans = 1, @destination_table = ''sp_whoisactive_log''',
@database_name=N'DBA',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete old rows',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DELETE dba.dbo.sp_whoisactive_log
WHERE collection_time < DATEADD(MONTH,-1,GETDATE())',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every x minutes',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20200209,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

This job will run sp_whoisactive on a schedule every 10 minutes and record the output in the table dba.dbo.sp_whoisactive_log. It also deletes any rows older than 1 month.

Hope this helps
John

When should I stripe SQL Server database backups

By Leave a Comment

What is a striped backup?

First of all a definition of what we mean when we say stripe SQL Server database backups. When we talk about striping a backup, it simply means the overall backup is distributed across several files, often on different drives. Microsoft’s description.

What are the benefits?

  1. Time
    1. Writing to multiple files can save time. This is because the files can be written to the file system in parallel. This is useful if your backups are taking too long and you would like/need them to complete faster.
  2. Storage
    1. If a backup drive is getting full, you can split your backup across multiple drives thus saving space on each drive. The total space used will be similar to keeping the backup on a single file but you may see a bit more overall space taken up.

Striped vs Single File

It’s a balancing act. My advice is stick with the defaults until you have an issue or would like something to perform better. Then it’s a good time to experiment and see how you get on. As a rule, the bigger your backups become, the more useful you may find striping your database backups.

How to stripe my backups?

We will discuss T-SQL and Powershell. For Powershell, I’m using the DBATools PowerShell module. DBATools covers far more scenarios than Microsoft’s own default module for SQL Server and is being used and tested all day every day. If you wish to run the powershell commands below, you will need to have dbatools installed. For the T-SQL commands, these all run natively in SSMS.

T-SQL

-- Split file across two drives as not enough space on E:
BACKUP DATABASE StackOverFlow2010
TO DISK = N'E:\Backup\StackOverFlow2010\StackOverFlow2010_1.bak',
DISK = N'F:\Backup\StackOverFlow2010\StackOverFlow2010_2.bak'
WITH COMPRESSION;

T-SQL (Using Ola Hallengren’s DatabaseBackup procedure)

EXECUTE dbo.DatabaseBackup
@Databases = 'StackOverFlow2010',
@Directory = 'E:\Backup\StackOverFlow2010\,F:\Backup\StackOverFlow2010\',
@BackupType = 'FULL',
@Compress = 'Y',
@NumberOfFiles = 2

Powershell (DBATools)

Backup-DbaDatabase -SqlInstance localhost -Database StackOverflow2010 -Path E:\Backup\StackOverflow2010\,F:\Backup\StackOverflow2010\ -CompressBackup

Restoring striped backups

It is therefore worth testing your backups and restore scripts every so often. A backup that cannot be restored is not really a backup.

T-SQL

RESTORE DATABASE StackOverFlow2010
FROM DISK = N'E:\Backup\StackOverFlow2010\StackOverFlow2010_1.bak',
DISK = N'F:\Backup\StackOverFlow2010\StackOverFlow2010_2.bak'

Powershell (DBATools)

Restore-DbaDatabase -SqlInstance localhost -DatabaseName StackOverflow2010 -Path E:\Backup\StackOverflow2010\,F:\Backup\StackOverflow2010\ -WithReplace

The curious case of the space in char(1)

By 1 Comment

What happens when you store a space in a char(1) column?

I was asked by a colleague why his where clause wasn’t being selective when filtering on a space value. The column was a char(1) data type. To understand the curious case of the space in char(1), we need to understand how the char data type works and also a bit more about the need for it in this scenario.

Scenario

My colleague had built a user table which included a gender column. A char(1) was chosen because one letter could be used to record the gender of the user. It was also small which helped with performance and overall size of the table. All non specified or non binary genders were assigned a ‘ ‘. (A space) The assumption was that the space character would behave the same as an M or F when used in the SELECT clause.

How the char column works

A char is fixed-length data type and any value that fall short of the fixed length is padded out with empty spaces. This means that inserting ” into the gender column has the same affect as entering ‘ ‘. This can be tested using the LEN function:

[sql]

DECLARE @tbl TABLE (name char(10))

INSERT INTO @tbl VALUES (”) — empty char(1)
INSERT INTO @tbl VALUES (‘ ‘) — a space in char(1)
INSERT INTO @tbl VALUES (‘John’)
INSERT INTO @tbl VALUES (‘John      ‘)

SELECT name,LEN(name) as len_name FROM @tbl

[/sql]

The spaces do not affect the LEN of the value of the char column

The spaces do not affect the LEN of the value of the char column

Build a test table – including putting a space in char(1)

[sql]

CREATE TABLE dbo.People (Description NVARCHAR (20),Gender CHAR(1))

INSERT INTO dbo.People(Description,Gender) VALUES(N’Male’, ‘M’)
INSERT INTO dbo.People(Description,Gender) VALUES(N’Female’, ‘F’)
INSERT INTO dbo.People(Description,Gender) VALUES(N’Other with space’, ‘ ‘)
INSERT INTO dbo.People(Description,Gender) VALUES(N’Other without space’, ”)
INSERT INTO dbo.People (Description,Gender) VALUES(N’Other as O’, ‘O’)

[/sql]

Query / Results

[sql]

— Select all rows and show the LEN of the gender column
SELECT
Description,
Gender,
LEN(Gender) AS Len_Gender
FROM dbo.People

[/sql]

Select space in char(1)

[sql]

— We get the same result with either query so the space is ignored
SELECT * FROM dbo.People WHERE Gender = ‘ ‘
SELECT * FROM dbo.People WHERE Gender = ”

[/sql]

Select char where = ' '

[sql]

— We get the row we want when we use a value such a O instead of a space
SELECT * FROM dbo.People WHERE Gender = ‘O’

[/sql]

Select char where = 'o'

Workarounds

The best option is use to a different character like a ‘O’ or something else suitable, anything other than a space in char(1) would work. If you weren’t too concerned about space used in this column, using a longer varchar data type would allow user to enter anything they wanted. Failing that, a TINY int would allow 256 options and still only uses 1 byte.

Its 2019

Yes, I know there a whole discussion about whether we should be storing gender and possible values but this is simply a post about using CHAR(1).

Further reading:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017 

https://stackoverflow.com/questions/4175878/storing-sex-gender-in-database 

Slightly tenuous link: https://johnmccormack.it/2016/02/how-does-the-sql-server-engine-process-my-select-query/