microsoft / sqlworkshops-sql2022workshop Goto Github PK

Synapse Link exercise instructions reference a Landing Zone in the Prereqs. However, there do not appear to be any prerequisites for a landing zone (not in the Readme.md on the main page nor in the exercises, nor in the slides).

Step 7, add a note to tell the reader to move on to stop 8 while the workload runs.

Exist check uses IP without port in the WHERE clause, but creates the cert with the port number in the name.

Current:

USE MASTER
GO
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 's3:///backups')
DROP CREDENTIAL [s3://:9000/backups];
GO

Should be:

USE MASTER
GO
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 's3://:9000/backups')
DROP CREDENTIAL [s3://:9000/backups];
GO

(https://github.com/microsoft/sqlworkshops-sql2022workshop/tree/main/sql2022workshop/04_Engine)/tempdb/readme.md

Missing scale for SQL Server:Wait Statistics/Page latch waits/Waits started per second (per your videos on YouTube, should be 0.01)

Add more details on how we identify CE feedback candidates based on estimates vs actual

In Module 4, Ledger Exercise, Exercise 3: Protecting Ledger tables from DDL changes

Issue: Attendee wanted to know who dropped the table in Exercise 3 (aka which login). The principal login for the user that dropped the table is not in the sys.ledger_table_history DMV.

getledgerobjects.sql queries sys.ledger_table_history and sys.ledger_column_history.

Fix:

add the follwing script which joins sys.ledger_table_history. with sys.database_ledger_transactions in order to get the principal_name column from the sys.database_ledger_transactions.

SELECT
t.[principal_name]
, t.[commit_time]
, h.[schema_name] + '.' + h.[table_name] AS [table_name]
, h.[ledger_view_schema_name] + '.' + h.[ledger_view_name] AS [view_name]
, h.[operation_type_desc]
FROM sys.ledger_table_history h
JOIN sys.database_ledger_transactions t
ON h.transaction_id = t.transaction_id

There is a type for Azure Arc exercises in this sentence

est Practices Assessment (BPA) is a service to scan the configuraiton

Preqreqs doc says you can use a VM in azure, so I spun up an Azure SQL VM. But it appears that Polybase Query Service is not included in a SQL VM (I did SQL 2022 Developer on Windows Server 2022), and I didn't notice any options to include Polybase when building the VM so I assumed it was just included. Either way it definitely couldn't be enabled.

So if there is in fact no way to enable Polybase when building a SQL VM, I'd note that in the prereqs doc because right now it looks like I should be able to use that.

Step 1 under Setup has the Perfmon counter SQL Server:SQL Statistics/Page latch waits/Waits started per second but the counter is SQL Server:Wait Statistics/Page latch waits/Waits started per second.

current instructions from readme.md for pspopt demo: https://github.com/microsoft/sqlworkshops-sql2022workshop/blob/main/sql2022workshop/03_BuiltinQueryIntelligence/pspopt/readme.md

...

See a PSP problem for a single query execution

1. Set the actual execution plan option in SSMS. Run query_plan_seek.sql twice in a query window in SSMS. Note the query execution time is fast (< 1 second). Check the timings from SET STATISTICS TIME ON from the second execution. The query is run twice so the 2nd execution will not require a compile. This is the time we want to compare. Note the query plan uses an Index Seek.
2. In a different query window set the actual execution option in SSMS. Run query_plan_scan.sql in a query windows in SSMS. Note the query plan uses an Clustered Index Scan and parallelism.
3. Now go back and run query_plan_seek.sql again. Note that even though the query executes quickly (< 1 sec), the timing from SET STATISTICS TIME is significantly longer than the previous execution. Also note the query plan also uses a clustered index scan and parallelism.

See a workload problem for PSP

1. Setup perfmon to capture % processor time and batch requests/second.
2. Run workload_index_seek.cmd 10 from the command prompt. This should finish very quickly. The parameter is the number of users. You may want to increase this for machines with 8 CPUs or more. Observe perfmon counters.
3. Run workload_index_scan.cmd. This should take longer but now locks into cache a plan for a scan.
   ...

Between those two demos, attendees will need to clear the proc cache again so that the second demo does not start by using the parallel scan plan.

Prerequisistes

The lab could use more explanation / story telling on what they are seeing between the various tables to help people understand more about what they are seeing and why it is so cool.

Add a link/reference to Azure ArcBox because it makes sense in a class of this nature.

How do all the IQP features work together like PSPO would kick in first and then CE feedback could work against a variant. DOP feedback is just looking at DOP so could use a variant or could still work on a query with CE feedback.

Add to the prereqs that Polybase can be installed only once so that attendees know ahead of time and can make the right decision of how to set up their lab environments.

This addition may save a lot of time and pain for getting the labs done.

Download a backup of a customized version of the WideWorldImporters sample database from https://aka.ms/wwi_pspopt from and copy it into c:\sql_sample_databases directory.

In the example you create a parquet file format, however later on when you query the data the format = delta. What is the purpose of defining the parquet file format ?

The first backup command will fail:

BACKUP DATABASE WideWorldImporters
TO URL = 's3:/:9000/backups/wwi.bak'
WITH CHECKSUM, INIT, FORMAT;
GO

with this error:

Msg 3078, Level 16, State 1, Line 5
The file name "s3:/10.191.80.74:9000/backups/wwi.bak" is invalid as a backup device name for the specified device type. Reissue the BACKUP statement with a valid file name and device type.
Msg 3013, Level 16, State 1, Line 5
BACKUP DATABASE is terminating abnormally.

We need to add another / before the IP address in the URL specifier.

I don't know if it's the underscore that stops it from being included in the zip or not...but you don't get this folder

https://github.com/microsoft/sqlworkshops-sql2022workshop/tree/main/sql2022workshop/05_DataVirt/delta/people-10m/_delta_log

For whatever reason, my queries were running in about 5 seconds and didn't break the 10 second threshold. I'd suggest adding a second XE session so people can quickly identify whether or not their queries are running fast.

https://github.com/microsoft/sqlworkshops-sql2022workshop/tree/main/sql2022workshop/05_DataVirt/s3objectstorage backuprestores3.ipynb title within notebook is incorrect: references the delta query as opposed to backup restore

In here: https://github.com/microsoft/sqlworkshops-sql2022workshop/tree/main/sql2022workshop/04_Engine/sqlledger

Step 10, says including brackets, but JSON uses braces for enclosing the document.

https://github.com/microsoft/sqlworkshops-sql2022workshop/tree/main/sql2022workshop/03_BuiltinQueryIntelligence/pspopt#solve-the-problem-in-sql-server-2022-with-no-code-changes Solve the problem in SQL Server 2022 with no code changes, step 5, references the top resource consuming queries report in ssms, but not specifically that it is in the Query Store vs Standard Reports

The instructions in the PSPOPT README near the end of the script ae as follows:

"Execute the script query_store_dispatcher_plan.sql. If you click on the dispatcher_plan value you will see a graphical plan operator called Multiple Plan."

In the actual script, there is no label "dispatch_plan" - the relevant XML column in the resultset has no label at all.

Also - the resultset for the query returns three columns, but all three values are the same for both plans - I suggest adding the query_variant_query_id column to the query to differentiate the two rows from one another

Thanks for a great session!

Consider adding a script or to the script about controlling DDL changes to Ledger Tables. This could show the sorts of "normal" table ALTERs that can be done: certain datatype changes, adding columns, removing columns, etc.

This might help alleviate the concerns by devs and DBAs that Ledger tables are set in stone.

It's common for printers and organizers to request proof of license to use materials with the actual content.

Needed to add /m"SQLCMD" to startsqlminimal.cmd for the net start to prevent single user getting taken
Need to restart SQL in full mode after modifications before running other scripts

"Is Parameter Sniffing Gone" slide has a typo on first bullet point.

"Only = operated supported" - guessing you meant "operator"

Add in skipped reasons and consider adding this to the exercise

When using SQL Server 2022 Developer edition, one needs to enable TCP/IP protocol in Server Protocols in SQLServer Configuration Manager.

When the SQL installation attempted to install the Polybase feature, I ran across the following error presented in the installer GUI:

"The specified local group does not exist."

Upon further investigation in the Bootstrapper Install logs (detail.txt), the group shown below is apparently a requirement. I had to create this group locally on my workstation for the feature installation to succeed. I am a local admin, the device is domain joined, I have SQL 2017 installed (default instance), but it does not have the polybase feature enabled. Note that the Polybase services failed to install, but to attempt again I had to change the initial installation to remove the feature and attempt reinstallation again. Oddly, although the initial attempts failed, the installer showed that the feature successfully installed.

Detail.txt

(01) 2022-11-13 18:45:52 Slp: SetValue: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.RC1\Polybase\Configuration, Name = DMSSvcAcct
(01) 2022-11-13 18:45:52 SQLPolyBase: --RegistrySettings: Wrote registry value DMSSvcAcct = softpro\jsharp...
(01) 2022-11-13 18:45:52 SQLPolyBase: --PolybaseSetupPrivate: Configuring Odbc driver pdwodbc17e -> sqlncli17e.dll
(01) 2022-11-13 18:45:52 SQLPolyBase: --PolybaseSetupPrivate: Creating performance counters: RC1...
(01) 2022-11-13 18:45:58 SQLPolyBase: --PolybaseSetupPrivate: Creating event sources: RC1...
(01) 2022-11-13 18:45:58 SQLPolyBase: --PolybaseSetupPrivate: Creating Access Group
(01) 2022-11-13 18:45:58 Slp: Sco: Attempting to initialize user group PdwComputeNodeAccess
(01) 2022-11-13 18:45:58 Slp: Sco: Attempting to check if user group PdwComputeNodeAccess exists
(01) 2022-11-13 18:45:58 Slp: Sco: Attempting to open root DirectoryEntry object for local computer
(01) 2022-11-13 18:45:58 Slp: Sco: Attempting to check if container 'WinNT://SPRO-L301511,computer' of group exists
(01) 2022-11-13 18:45:58 Slp: Prompting user if they want to retry this action due to the following failure:
(01) 2022-11-13 18:45:58 Slp: ----------------------------------------
(01) 2022-11-13 18:45:58 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
(01) 2022-11-13 18:45:58 Slp: Inner exceptions are being indented
(01) 2022-11-13 18:45:58 Slp:
(01) 2022-11-13 18:45:58 Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
(01) 2022-11-13 18:45:58 Slp: Message:
(01) 2022-11-13 18:45:58 Slp: The specified local group does not exist.
(01) 2022-11-13 18:45:58 Slp:
(01) 2022-11-13 18:45:58 Slp: HResult : 0x84bb0001
(01) 2022-11-13 18:45:58 Slp: FacilityCode : 1211 (4bb)
(01) 2022-11-13 18:45:58 Slp: ErrorCode : 1 (0001)
(01) 2022-11-13 18:45:58 Slp: Data:
(01) 2022-11-13 18:45:58 Slp: WatsonData = LOCAL
(01) 2022-11-13 18:45:58 Slp: DisableRetry = true
(01) 2022-11-13 18:45:58 Slp: Inner exception type: System.Runtime.InteropServices.COMException
(01) 2022-11-13 18:45:58 Slp: Message:
(01) 2022-11-13 18:45:58 Slp: The specified local group does not exist.
(01) 2022-11-13 18:45:58 Slp:
(01) 2022-11-13 18:45:58 Slp: HResult : 0x80070560
(01) 2022-11-13 18:45:58 Slp: Stack:
(01) 2022-11-13 18:45:58 Slp: at System.DirectoryServices.DirectoryEntries.Find(String name, String schemaClassName)
(01) 2022-11-13 18:45:58 Slp: at Microsoft.SqlServer.Configuration.Sco.UserGroup.Exists()
(01) 2022-11-13 18:45:58 Slp: ----------------------------------------

The 03 Built-in Query Intelligence / pspop lab requires a restore from c:\samples. People are having permissions issues doing the restore. People have successfully changed their sql service account to local system and it worked fine. Other people struggled due to lock-down policies from their IT department.

We could probably prevent this issue by having people copy the backups to the SQL data directory.

Exercise 1: Using an updatable ledger table
Add a step 8.5 : Run the verifyledger.sql to see that the ledger is consistent to this point.
Step 12: Because the ledger hasn't been checkpointed, the same last_verified_block_id is reflected.
Add a new step 13: (same as step 7) Let's generate a new digest by executing the script generatedigest.sql. Save the output value (including the braces) to be used for verifying the ledger.. You will use this in a later step. This provides me a snapshot of the data at a point in time.
Add a new step 14: Run the verifyledger.sql to see that the ledger now reflects the new last_verified_block_id.
Add a new step 15: Remove any character from the hash in verifyledger.sql and re-run the code. Notice the proc states the hash does not match, suggesting tampering.

Exercise 2: Using an append-only ledger table
Script within appchangemaryssalary.sql should be enclosed in a transaction, in case user forgot to create audit table.

Exercise 3: Protecting Ledger tables from DDL changes
Saw a misspelled "syadmin"

database name howboutthemcowboys, howaboutthemcowboys, and howbouthemcowboys... Doublecheck the spelling/syntax across all scripts

https://github.com/microsoft/sqlworkshops-sql2022workshop/tree/main/sql2022workshop/03_BuiltinQueryIntelligence/pspopt#see-a-workload-problem-for-psp "See a workload problem for PSP", step 5, it should be ctrl+c but it is just showing as + in the rendered markdown

Not all of the included wwi backups have unique filenames, preventing all 3 from being restored at the same time. Adjusting the filenames to be unique would make things easier for users to set up.

Mb used instead of MB in the 1 MB and 500 MB examples.
Sorry to nitpick, <3 you Bob

Add more in the steps that the append only scenario is to help web developers track web user initiated changes but ledger still need to track any changes even outside web app

Step 7 - Query the delta table by using a WHERE clause for a column that is a partiitione column. Not the peformance difference.

Should be "Note" the performance difference

https://github.com/microsoft/sqlworkshops-sql2022workshop/tree/main/sql2022workshop/03_BuiltinQueryIntelligence/pspopt#solve-the-problem-in-sql-server-2022-with-no-code-changes Solve the problem in SQL Server 2022 with no code changes, step number 7 is missing part of the file name; query_store_parent.sql should be query_store_parent_query.sql