John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

John McCormack's blogs

Logical query processing

30th September 2020 By John McCormack 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.

Filed Under: T-SQL Tagged With: 70-461, fwghso, logical query processing, SELECT Query, sql engine

How do I find the agent service account for Azure SQL Database Managed Instance

25th September 2020 By John McCormack 1 Comment

How do I find the agent service account for Azure SQL Database Managed Instance

Normally if you want to know which service accounts run your SQL Server Agent, you can just run:

SELECT servicename, service_account FROM sys.dm_server_services

However this draws a blank when you run it on Azure SQL DB Managed Instance. So how do you find the agent service account for Azure SQL Database Managed Instance?

I’ve came up with 3 suggestions so far:

  1. Run an agent job and check which user ran it
  2. Check the error log
  3. Check sys.dm_exec_sessions

Run an agent job and check which user ran it

Output of SQL Server Agent Job

This is not exactly a very efficient process.

Check the error log

EXEC sp_readerrorlog 0,1,'service account'

Even if you cycle the error log, it will always be there but you’ll need to do some string manipulation to just pull out the credentials you need.

Check sys.dm_exec_sessions

(Thanks to David Alcock for this suggestion)

SELECT login_name FROM sys.dm_exec_sessions
WHERE program_name = 'SQLAgent - Generic Refresher'

This might be the best because it will only return one result and will only the login name, rather than a bunch of extra text as well.

Do you have a better way? Please let me know in the comments if you do.

John

Filed Under: Azure SQL DB Managed Instance, front-page Tagged With: azure, azure sql db, azure sqldb managed instance, SQL Agent service account

Automate Your Stress Away – Run sp_whoisactive on a schedule

8th September 2020 By John McCormack 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

Filed Under: front-page, T-SQL Tagged With: #tsql2sday, sp_whoisactive, tsqltuesday

DP-900 – Exam prep reading list

17th August 2020 By John McCormack Leave a Comment

DP-900 Exam prep reading list  (further reading)

Data visualisation example for DP-900 Exam prep reading listThis DP-900 Exam prep reading list comes from Microsoft Learn. Links will be added as I go through the course material / suggested reading on the Microsoft Learn site until I have completed the full course. I am currently working my way through the material.

If you are thinking of sitting DP-900 Azure data fundamentals, I recommend following through the free course on the Microsoft learn site (scroll down past exam info) and also reading the recommended links. The links may add a bit of time onto your learning, and you can be the judge about about how intensively you need to study the extra material, however the more prepared you are, the greater your chance of success with the certification exam. Links duplicated in Microsoft Learn are only shown once in the list below.

I find it great that Microsoft are making these resources available for free these days. Some video learning would also be nice but this is a great start.

Azure Data Fundamentals: Explore core data concepts

Explore core data concepts

  • https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
  • https://docs.microsoft.com/en-us/azure/cosmos-db/introduction
  • https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blobs-introduction
  • https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview

Explore roles and responsibilities in the world of data

  • https://docs.microsoft.com/en-us/azure/databricks/scenarios/what-is-azure-databricks
  • https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-overview
  • https://docs.microsoft.com/en-us/power-bi/fundamentals/power-bi-overview
  • https://docs.microsoft.com/en-us/azure/data-factory/introduction

Describe concepts of relational data

  • https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
  • https://docs.microsoft.com/en-us/sql/odbc/reference/structured-query-language-sql?view=sql-server-ver15
  • https://docs.microsoft.com/en-us/azure/azure-sql/azure-sql-iaas-vs-paas-what-is-overview

Explore concepts of non-relational data

  • https://docs.microsoft.com/en-us/azure/architecture/guide/technology-choices/data-store-overview
  • https://docs.microsoft.com/en-us/azure/cosmos-db/index-overview
  • https://docs.microsoft.com/en-us/azure/cosmos-db/table-introduction
  • https://docs.microsoft.com/en-us/azure/cosmos-db/graph-introduction

Explore concepts of data analytics

  • https://docs.microsoft.com/en-us/power-bi/create-reports/
  • https://azure.microsoft.com/en-us/services/databricks/
  • https://azure.microsoft.com/en-gb/services/cognitive-services/  (Broken link on site)
  • https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/etl

Azure Data Fundamentals: Explore relational data in Azure

Explore relational data offerings in Azure

Explore provisioning and deploying relational database offerings in Azure

Query relational data in Azure

Azure Data Fundamentals: Explore non-relational data in Azure

Explore non-relational data offerings in Azure

Explore provisioning and deploying non-relational data services in Azure

Manage non-relational data stores in Azure

Azure Data Fundamentals: Explore modern data warehouse analytics in Azure

Examine components of a modern data warehouse

Explore data ingestion in Azure

Explore data storage and processing in Azure

Get started building with Power BI

 

Recommended posts:

SQL Server on Amazon RDS 

Put tempdb files on d drive in Azure IAAS

Filed Under: Azure, Certification, DP-900 Tagged With: azure data fundamentals, dp-900

Why I would like to be mentored

12th August 2020 By John McCormack Leave a Comment

Who wouldn’t want to be mentored?

This blog post is created as a direct result of Paul Randal’s offer to mentor people in the SQL Server community. I’ve had a good think about it and it seems obvious to me. Paul asks “Why would I like to be mentored?” I’m going to set out my personal reasons and although this isn’t one of them, my immediate response is “Who wouldn’t want to be mentored?” If you don’t have a big ego and understand that some people know a lot more than you, and have been in the game for longer, it stands to reason that some mentorship could be of a huge personal benefit.

My reasons

I was a  late-ish bloomer in tech. I had a different career from ages 16 – 32 so I always felt like I was playing catch up when I started as a DBA. At 32, coming out of university, I landed a job with Monster who just happened to have a large DBA team in Glasgow. I was lucky. I had a senior colleague who was only a month older than me. When I saw how good he was and how much he knew, I felt like I had a lot of catching up to do. But I was also inspired. He mentored me a lot over the 6 years I worked at Monster, as did others, and I benefited from that. I felt I was ready to move on to the next challenge and moved into contracting.

Contracting can pay more, it did for me but it can be a lonely game. You don’t always have the same team spirit that you feel as an employee and any choices about personal development are entirely yours. Sometimes, there is so much dazzling new tech that I don’t know what to learn next. I believe a mentor can help me get focussed. Some of my learning options include:

  • Focus more on public cloud vendors like AWS and Azure. (Not just relational databases).
  • Focus more on a specific area of SQL Server that causes people problems and become much better at it.
    • Performance tuning
    • HA/DR solutions
  • Improve my PowerShell competence
  • How to become a better trainer

I am also at a bit of a crossroads in terms of my career. I have the following choices and I don’t know what is best. I hope a mentor can talk me through the pros and cons of each option, and help me honestly evaluate how ready I am for each.

  • Start consulting (Do I have a niche?)
    • Performance consulting
    • Cloud migration consulting
    • Cloud cost optimisation consulting
  • Continue contracting
  • Go back into full time employment but seek a senior position
  • Launch a service offering a remote DBA service
  • Develop and launch a remote healthcheck service to identify pain points
  • Focus on creating engaging online training content
    • How to make money from training content but still benefiting the SQL community
    • I’m already active in the SQL community, and that will continue whether I’m chosen or not; but I do think that the increased confidence that may come after being mentored by Paul will help me improve the quality and quantity of my content.
    • Whether to design and deliver my own content or deliver Microsoft approved courses. (I recently achieved the MCT award but I’m don’t feel ready to deliver MS training yet)

So those are the main reasons that I think an experienced mentor like Paul Randal would be a huge benefit to me and I’d be delighted to be chosen.

Filed Under: Personal Tagged With: mentoring

  • « Previous Page
  • 1
  • …
  • 7
  • 8
  • 9
  • 10
  • 11
  • …
  • 22
  • Next Page »
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2025

 

Loading Comments...