[Video] Office Hours in Osaka, Japan
Kon’nichiwa! My vacation tour continues in Osaka, Japan, a city famous for its street food, nightlife, and host and hostess clubs. I’m usually the kinda guy who does classy dinners at 5PM and then hits the sack by 9PM at the latest, so you might wonder how I got here. The answer is that Yves planned this trip out, hahaha, so here we are. As long as we’re here, let’s go through your top-voted questions from https://pollgab.com/room/brento.
Here’s what we covered:
- 00:00 Start
- 00:58 Craig: Should you start your procs with BEGIN, so all the proc code is inside a BEGIN…END block?
- 02:18 MyTeaGotCold: 75% of my skills seem worthless after moving from being the boss of a 1 TB server to the boss of many 10-20 TB servers. Is such a big loss normal?
- 03:19 Josef: Restoring a large database from a native SQL backup can take a long time. Would you recommend using log shipping to another server and switching over in case of failure instead of a full restore from backups?
- 04:07 Craig: I see that you use temp tables to pass sets of data from one proc to another. What are your thoughts on serializing data as JSON and passing that into a proc via a parameter?
- 05:05 BobTheDeveloper: I have a customer that claims his SQL Server is slow. He doesn’t want to pay for a bunch of my time to dig deep with the First Responder Kit. None of CPU, Memory, Disk and Network are being hammered. Even simple queries are taking too long. Is locking the likely problem?
- 06:04 DogsBarkingAngrily: hi Brent! What is your learning process, how do you learn new things? I started watching your PostgreSQL series and am amazed how you know so much, so I’m curious where do you learn from? (emphasis on you … because I learnt so much from you, but where do you learn from?)
- 07:10 Dopinder: How do you like to troubleshoot Connection Timeout errors at the client? We originally thought ours was a server side issue but turns out the VM / Client had a CPU pressure issue.
- 07:36 Craig: If you want to find out what procs get called when a user does something on an application, what tools do you use? Profiler, Extended Events, or something else?
- 08:26 ayed: Hello Brent, thanks for providing free content! Throughout your career, what is the maximum number of CPU cores and the largest amount of memory you’ve encountered in an operational SQL Server VM?
- 09:11 Mary Puppins: What’s your opinion of the new copilot support in azure SQL DB for generating queries from English prompts? Seems like this could generate some inefficient queries.
- 10:29 SQL_Linux: Hey Brent, I recently enabled the setting Optimize for Ad Hoc Workloads to resolve an issue with many single-use plans. Anyway, do you disable this setting when you do consulting if you find it enabled?
9 Comments. Leave new
Are you sure about the BEGIN…END question? I’d always thought the proc ended at the end of the batch, so at the GO statement/end of the script, and my testing bears that out.
This is the test I did:
CREATE OR ALTER PROCEDURE dbo.TestProc
AS
BEGIN
SELECT 2
END
SELECT 1
GO
CREATE OR ALTER PROCEDURE dbo.TestProc2
AS
SELECT 2
GO
SELECT 1
I ran everything as one statement, then executed each of the procs in turn.
I tried your example. It looks as if you are right.
But don’t forget to run:
drop procedure if exists dbo.TestProc2
drop procedure if exists dbo.TestProc
Yes, definitely. This is just the set-up for the test. The full test is:
CREATE OR ALTER PROCEDURE dbo.TestProc
AS
BEGIN
SELECT 2
END
SELECT 1
GO
CREATE OR ALTER PROCEDURE dbo.TestProc2
AS
SELECT 2
GO
SELECT 1
EXEC dbo.TestProc
EXEC dbo.TestProc2
DROP PROCEDURE IF EXISTS dbo.TestProc
DROP PROCEDURE IF EXISTS dbo.TestProc2
Who said anything about GO or two procedures? 😉
Remove that and try the permissions thing I describe in the video.
Always amazes me when people try a totally different experiment than what I describe, and they’re surprised that they don’t come to the same results…
Ok, fair enough. This is test 1:
CREATE OR ALTER PROCEDURE dbo.TestProc
AS
BEGIN
SELECT 2
END
GRANT EXECUTE ON dbo.TestProc TO TestUser1
And when I run that and script out the proc I get:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestProc]
AS
BEGIN
SELECT 2
END
GRANT EXECUTE ON dbo.TestProc TO TestUser1
GO
So it looks like the GRANT EXECUTE did get into the proc despite the BEGIN…END
And this is test 2:
CREATE OR ALTER PROCEDURE dbo.TestProc2
AS
SELECT 2
GO
GRANT EXECUTE ON dbo.TestProc TO TestUser1
And when I run that and script out the proc I get:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestProc2]
AS
SELECT 2
GO
hi Chris,
So a “GO” is more important that a BEGIN & END.
Thank you for posting.
Best regards
Henrik
That’s been my experience, and my tests seem to bear that out. But there’s a very good chance I’m missing something, so if I were you I’d wait until Brent replies before taking anything I say as the truth (and also do your own checks).
[…] very touristy, but as touristy places go, it’s fantastic. Last year when I was here, I filmed Office Hours at the river, but this time around let’s go into one of the side streets where the food vendors […]