erikdarlingdata / darlingdata Goto Github PK

View Code? Open in Web Editor NEW

423.0 423.0 121.0 5.17 MB

Open source SQL Server nonsense: sp_PressureDetector, sp_QuickieStore, sp_HumanEvents, etc.

Home Page: https://www.erikdarling.com/

License: MIT License

TSQL 99.97% PowerShell 0.03%

darlingdata's People

Contributors

Stargazers

Watchers

Forkers

ssitb mattsql danthesqlman steveearle86 ktaranov platzer mwatty7 csommerolo databasedave bulentgucuk splaxi sqlauto rodasasfaw-zz mbiasetti bigtoga dwainew ijeb spuriousscholars mnemonic23 kalxasath stanleyzaucha mitch-wheat pravkumar1234 ben-thul olegstrutinskii leod1976 baosql bassk vedaraja alihacks kermitcoder bryonh mayvac mojoerobocoder vikrantmyorg cristiantadres bibi92 virtualhurst andrewdp23 gsong0413 daniellepaquetteharvey protiguous hfz-r secondtry agmadkafaar woodymax gantz-at-incomm david-aacb sheleheda somun0891 nani1211 goodsoul1914 samyan19 automation555 tommyau20 mssqlserverdba nicthedataguyuk sdoubleday brovish andrewborst monotoringta jau1310 radekgaska pabloealvarez891 gdawdy rohitagio odubdubh3 claudioessilva sudhakarmandava samplesty hannahvernon sillydillywilly ndiiong commanderbond anibalmorales wolfanadb datanadi tomseilerdt larspl holidasa mostafaatef tugaycalik maryannselander esatdata ronaldgithub mjswart acappelletti louisvudba mgarlapati croky222 baleng gflpcantor31 davebabler vkayar7 janneilonen shmelev-1987 kendra-little highinorbit litknd matthend710

darlingdata's Issues

Clean up mode

Add a flag to clean up after ourselves.

events
tables
views

User will have to supply the database to target for views and tables, but can re-use existing variables for that.

sp_HumanEvents: Add query_parameterization_data event for people with compile issues

"Fired on compile for every query relevant for determining if forced parameterization would be useful for this database."

Azure SQL Database

Which script is your question about?

sp_PressureDetector

Attached is an Azure SQL Database Compatible Version of sp_PressureDetector.

sp_PressureDetector.txt

If you comment out the "DAC-enabled" check it works perfectly.

Thanks again for these Erik - brilliant scripts to have.

Invalid object name '#waits_agg' @debug = 1 mode

Version of the script
SELECT @Version = '1.0', @version_date = '20200301';

What is the current behavior?
Msg 208, Level 16, State 0, Procedure dbo.sp_HumanEvents, Line 1442 [Batch Start Line 12]
Invalid object name '#waits_agg'.

If the current behavior is a bug, please provide the steps to reproduce.
EXEC dbo.sp_HumanEvents @event_type = 'blocking', @debug = 1;

What is the expected behavior?
#waits_agg is not relevant?

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
WIN10 / SQL2019

get_numbers fails on NULL strings

The functions work but they don't handle NULL strings.

CREATE TABLE #dummy (col1 VARCHAR(50))

INSERT INTO #dummy VALUES (NULL)
INSERT INTO #dummy VALUES ('ABC123')

SELECT  d.col1, gn.*
FROM #dummy d
    CROSS APPLY dbo.get_numbers(d.col1) AS gn

Msg 1014, Level 15, State 1, Line 25
A TOP or FETCH clause contains an invalid value.

-- works if you denullify on the way in
SELECT  d.col1, gn.*
FROM #dummy d
    CROSS APPLY dbo.get_numbers(ISNULL(d.col1, '')) AS gn

col1   numbers_only
------ ------------
NULL   NULL
ABC123 123

If you update the function code to handle the NULLs you don't have to worry about it as the caller. Not sure the impact on performance but something like:

WITH x
  AS ( SELECT TOP (LEN(ISNULL(@string, '')))

Then call without errors, without checking for NULL:

SELECT  d.col1, gn.*
FROM #dummy d
    CROSS APPLY dbo.get_numbers(d.col1) AS gn

col1   numbers_only
------ ------------
NULL   NULL
ABC123 123

sp_HumanEvents raiserror/throw behavior

Which script is your question about?

Script Name: sp_HumanEvents

Script version

1.5

Is your question about how they work, or the results?
Why it does what it does

Okay, what's your question?

The catch block has a RAISERROR on line 3015 just before a THROW. What is the purpose of the RAISERROR in that place, to tell if there was any error in the ROLLBACK at 2986? If yes, that ROLLBACK should be in a TRY block, if not, line 3015 returns exactly the same information as the THROW, so line 3016 is either wrong or redundant.

This code shows the behavior: the ERROR_NUMBER() and other functions capture the first error in the TRY block, not the second one.

BEGIN TRY
RAISERROR(N'first error', 16, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
DECLARE @msg NVARCHAR(2048) = N'';
RAISERROR(N'second error', 16, 1) WITH NOWAIT;
SELECT @msg += N'Error number '
+ RTRIM(ERROR_NUMBER())
+ N' with severity '
+ RTRIM(ERROR_SEVERITY())
+ N' and a state of '
+ RTRIM(ERROR_STATE())
+ N' in procedure '
+ COALESCE(ERROR_PROCEDURE(),'')
+ N' on line '
+ RTRIM(ERROR_LINE())
+ NCHAR(10)
+ ERROR_MESSAGE();
RAISERROR(@msg, 16, 1) WITH NOWAIT;
THROW;
END CATCH

Username validation for AD accounts doesn't work

It seems like if domain accounts are tied to MSAs or gMSAs then my crafty username check doesn't work.

Not sure if I should

Skip the check and just validate the string like I do others
Skip the check if the username has a \ in it
Something else?

Make Azure SQL DB Compatible

This is an easy fix, all I need to do is change the event session to ON DATABASE instead of ON SERVER for it to work.

Azure DB/MI Detection

SELECT @Version = '1.5', @version_date = '20200501';

Current behavior - when running on a SQL Managed Instance, it properly detects that I'm in Azure, however, two issues immediately arise:

1 - The start commands for the event session refer to SERVER and not DATABASE as the path for Azure SQL DB should be.

2 - When in an MI (as I am), I have access to SERVER EVENTS. When I run a version where I just force the @Azure bit to be 0, it runs perfectly fine.

So:

the @Start_SQL needs to be fixed so that if the event is created for database, it starts it in database. other locations may be affected.
Is there a way to detect if running on a Managed Instance rather than Azure SQL? If so, should treat as if traditional.

Lovely product, looking forward to adding this to my arsenal.

Thanks,
Jonathan

sp_HumanEvents: Use sql_statement_post_compile instead of sql_statement_recompile

I think we can just flip to using this instead, but I need to double check if it has the recompile reason in there.

String mangling of @seconds_sample for WAITFOR DELAY

This works here

DECLARE @seconds_sample INTEGER = 100
DECLARE @waitfor NVARCHAR(20)

SELECT @waitfor = CONVERT(NVARCHAR(20), DATEADD(s, @seconds_sample, 0), 114)
WAITFOR DELAY @waitfor

sp_HumanEvents: Add ability to change XE definition settings

Stuff people might wanna change:

max memory
use a file instead of the ring buffer
change startup state

I don't wanna go down the causality tracking route right now, because I'm not sure how helpful it is to any of the events I'm grabbing. It seems more useful with multiple events in one session.

It also adds some additional stuff to the XML parsing which wouldn't populate with it off, which means I'd be stuck using dynamic SQL with XML parsing. Yeesh.

There are too many executions

Version of the script
SELECT @Version = '1.5', @version_date = '20200501';

What is the current behavior?
With event_type query the number of executions is counted twice.

If the current behavior is a bug, please provide the steps to reproduce.
Start sp_HumanEvents with @event_type = 'query', @gimme_danger = 1
Quickly jump to another tab and run a query three times, no more no less. Three shall be the number thou shalt execute, and the number of the executing shall be three.

What is the expected behavior?
The number of executions reported by sp_HumanEvents should be three.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server 2019

sp_HumanEvents error handling - transaction rollback

Hi, the transaction rollback part in the TRY CATCH block seems wrong. This is the current code:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

First, as you don't begin any transaction you should not roll back any; if there is a transaction open, it is not yours to rollback. Second, @@TRANCOUNT can be more than 1 and in that case ROLLBACK will just decrement @@TRANCOUNT by 1, but it will not do anything else. My suggestion is to leave the transaction part alone and remove this piece of code.

sp_QuickieStore Execution errors

Version of the script
sp_QuickieStore 2021
Executing on SQL Server 2017 Standard

What is the current behavior?
Compiles ok but getting the following error when executing with any parameters

Msg 50000, Level 11, State 1, Procedure sp_QuickieStore, Line 4300 [Batch Start Line 0]
error while (null)
offending query:
(null)
Msg 50000, Level 11, State 1, Procedure sp_QuickieStore, Line 76 [Batch Start Line 0]
This procedure only runs on supported versions of SQL Server

If the current behavior is a bug, please provide the steps to reproduce.
Execute with any variables flags, etc or debug

What is the expected behavior?
Program runs without error

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server 2017 on Windows Server 2016

Would be nice to have the query text in the query plan

Is your feature request related to a problem? Please describe.
When looking at the query plan from sp_HumanEvents, the query text is missing.

Describe the solution you'd like
Would be nice to have the query text in the query plan

Describe alternatives you've considered
You could have look in the column statement_text but ...

Are you ready to build the code for the feature?
Sure, just need to get friendly with GitHub

Error at line 2021 when running with @event_type = 'blocking'

Version of the script
1.5

What is the current behavior?
The following error is thrown:
Msg 205, Level 16, State 1, Procedure dbo.sp_HumanEvents, Line 2021 [Batch Start Line 1]
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Cause:
The column sqlhandle is missing from the INSERT INTO #blocking statement.

If the current behavior is a bug, please provide the steps to reproduce.
Run
EXEC dbo.sp_HumanEvents @event_type = 'blocking', @seconds_sample = 1, @database_name = '{aDatabase}';
Replacing {aDatabase} with the name of any available database

What is the expected behavior?
The sproc should not error out and should report on any blocked queries as per the documentation.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Tested (and reproduced) on Windows Server 2019 with SQL 15.0.4033.1

HumanEvents_Blocking fails in query

@Version = '1.5', @version_date = '20200501';

Running
SELECT TOP 1000 * FROM dbo.HumanEvents_Blocking;

Ouput:
Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Running the following works fine:
select top 1000 * from dbo.[eeper_HumanEvents_blocking

SQL Server 2019 Development CU4

Execution count for queries is 1

Started using this for real today, fantastic.

The latest version surfaced a bug in execution count for queries.
This should not do the OVER() part since you already group by the same columns.

WITH queries AS 
             (
                 SELECT COUNT_BIG(*) OVER ( PARTITION BY q.query_plan_hash_signed,
                                                         q.query_hash_signed,
                                                         q.plan_handle ) AS executions,
                        q.query_plan_hash_signed,
                        q.query_hash_signed,
                        q.plan_handle
                 FROM #queries AS q
                 GROUP BY --q.event_time,
                          q.query_plan_hash_signed,
                          q.query_hash_signed,
                          q.plan_handle

Help error message on running sp_HumanEvents

I run this using latest version in master:
EXEC sp_HumanEvents @output_database_name = N'HumanEvents', @output_schema_name = N'dbo';

and the messages tab has this at the end:

Generating insert table statement for keeper_HumanEvents_query
Msg 50000, Level 16, State 1, Procedure sp_HumanEvents, Line 3049 [Batch Start Line 5]

Msg 248, Level 16, State 1, Line 6
The conversion of the nvarchar value '8918235844' overflowed an int column.

Is this fixed in dev?

"String or binary data would be truncated" error while inserting #query_store_plan

I get the above error when running the SP on my database. Checking the code, the field all_plan_ids in #query_store_plan is declared varchar(1000), but the FOR XML that concatenates the values for this field is varchar(MAX). If I change the declaration of all_plan_ids to varchar(MAX), the SP runs fine.

sp_HumanEvents - Output Query

sp_HumanEvents

Current version (updated yesterday)

Hi Erik

Great shout out from Brent today on these scripts

"Live Coding T-SQL with Microsoft SQL Server" on Twitch

I'm trying to figure out where the difference is

Same version of sp_HumanEvents and StackOverflow

I get a different output to Brent (story of my life....!!)

See screen shots below

Any idea why they would be different -apologies if this is blindingly obvious

Cheers Erik

Love your stuff

Stored procedure creation script does not complete on SQL Server 2017 Web Edition

Version of the script
@Version = '1.5', @version_date = '20200501'

What is the current behavior?
When attempting to run the install script on SQL Server 2017 Web Edition (14.0.3370.1), the initial, almost empty stored procedure is created. However, the rest of the script throws the errors below:

Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'BEGIN'.
Msg 137, Level 15, State 1, Line 23
Must declare the scalar variable "@version".
Msg 137, Level 15, State 2, Line 25
Must declare the scalar variable "@help".
Msg 137, Level 15, State 2, Line 307
Must declare the scalar variable "@keep_alive".
Msg 137, Level 15, State 2, Line 309
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 311
Must declare the scalar variable "@keep_alive".
Msg 137, Level 15, State 2, Line 313
Must declare the scalar variable "@event_type".
Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'IF'.
Msg 137, Level 15, State 2, Line 317
Must declare the scalar variable "@max_memory_kb".
Msg 319, Level 15, State 1, Line 318
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 1, Line 319
Must declare the scalar variable "@max_memory_kb".
Msg 137, Level 15, State 2, Line 326
Must declare the scalar variable "@max_memory_kb".
Msg 137, Level 15, State 2, Line 384
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 385
Must declare the scalar variable "@client_app_name".
Msg 137, Level 15, State 2, Line 386
Must declare the scalar variable "@client_hostname".
Msg 137, Level 15, State 2, Line 387
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 388
Must declare the scalar variable "@session_id".
Msg 137, Level 15, State 2, Line 389
Must declare the scalar variable "@username".
Msg 137, Level 15, State 2, Line 390
Must declare the scalar variable "@object_name".
Msg 137, Level 15, State 2, Line 391
Must declare the scalar variable "@object_schema".
Msg 137, Level 15, State 2, Line 392
Must declare the scalar variable "@custom_name".
Msg 137, Level 15, State 2, Line 393
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 394
Must declare the scalar variable "@output_schema_name".
Msg 137, Level 15, State 2, Line 395
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 396
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 399
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 403
Must declare the scalar variable "@event_type".
Msg 319, Level 15, State 1, Line 404
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 409
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 1, Line 421
Must declare the scalar variable "@query_duration_ms".
Msg 137, Level 15, State 2, Line 425
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 1, Line 436
Must declare the scalar variable "@wait_duration_ms".
Msg 137, Level 15, State 2, Line 440
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 1, Line 451
Must declare the scalar variable "@blocking_duration_ms".
Msg 137, Level 15, State 2, Line 455
Must declare the scalar variable "@query_sort_order".
Msg 137, Level 15, State 2, Line 457
Must declare the scalar variable "@query_sort_order".
Msg 319, Level 15, State 1, Line 458
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 1, Line 459
Must declare the scalar variable "@query_sort_order".
Msg 137, Level 15, State 2, Line 463
Must declare the scalar variable "@wait_type".
Msg 137, Level 15, State 2, Line 470
Must declare the scalar variable "@wait_type".
Msg 137, Level 15, State 2, Line 473
Must declare the scalar variable "@wait_type".
Msg 137, Level 15, State 2, Line 477
Must declare the scalar variable "@wait_type".
Msg 137, Level 15, State 2, Line 498
Must declare the scalar variable "@client_app_name".
Msg 137, Level 15, State 2, Line 516
Must declare the scalar variable "@client_app_name".
Msg 137, Level 15, State 2, Line 543
Must declare the scalar variable "@wait_type".
Msg 137, Level 15, State 2, Line 553
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 561
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 565
Must declare the scalar variable "@fully_formed_babby".
Msg 319, Level 15, State 1, Line 566
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 571
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 591
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 593
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 595
Must declare the scalar variable "@database_name".
Msg 319, Level 15, State 1, Line 596
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 602
Must declare the scalar variable "@session_id".
Msg 137, Level 15, State 2, Line 606
Must declare the scalar variable "@session_id".
Msg 319, Level 15, State 1, Line 607
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 612
Must declare the scalar variable "@sample_divisor".
Msg 137, Level 15, State 2, Line 623
Must declare the scalar variable "@seconds_sample".
Msg 137, Level 15, State 2, Line 626
Must declare the scalar variable "@seconds_sample".
Msg 137, Level 15, State 2, Line 628
Must declare the scalar variable "@gimme_danger".
Msg 137, Level 15, State 2, Line 641
Must declare the scalar variable "@seconds_sample".
Msg 137, Level 15, State 2, Line 642
Must declare the scalar variable "@seconds_sample".
Msg 137, Level 15, State 2, Line 648
Must declare the scalar variable "@seconds_sample".
Msg 137, Level 15, State 2, Line 684
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 686
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 688
Must declare the scalar variable "@output_database_name".
Msg 319, Level 15, State 1, Line 689
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 695
Must declare the scalar variable "@output_schema_name".
Msg 137, Level 15, State 2, Line 699
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 702
Must declare the scalar variable "@s_sql".
Msg 137, Level 15, State 2, Line 703
Must declare the scalar variable "@s_out".
Msg 137, Level 15, State 2, Line 705
Must declare the scalar variable "@output_schema_name".
Msg 137, Level 15, State 2, Line 711
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 716
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 722
Must declare the scalar variable "@output_schema_name".
Msg 137, Level 15, State 2, Line 731
Must declare the scalar variable "@custom_name".
Msg 137, Level 15, State 2, Line 734
Must declare the scalar variable "@custom_name".
Msg 319, Level 15, State 1, Line 735
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 736
Must declare the scalar variable "@custom_name".
Msg 319, Level 15, State 1, Line 737
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 742
Must declare the scalar variable "@delete_retention_days".
Msg 137, Level 15, State 1, Line 744
Must declare the scalar variable "@delete_retention_days".
Msg 137, Level 15, State 2, Line 750
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 764
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 775
Must declare the scalar variable "@query_duration_ms".
Msg 137, Level 15, State 2, Line 777
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 779
Must declare the scalar variable "@query_duration_ms".
Msg 137, Level 15, State 2, Line 782
Must declare the scalar variable "@blocking_duration_ms".
Msg 137, Level 15, State 2, Line 784
Must declare the scalar variable "@blocking_duration_ms".
Msg 137, Level 15, State 2, Line 786
Must declare the scalar variable "@wait_duration_ms".
Msg 137, Level 15, State 2, Line 788
Must declare the scalar variable "@wait_duration_ms".
Msg 137, Level 15, State 2, Line 790
Must declare the scalar variable "@client_app_name".
Msg 137, Level 15, State 2, Line 792
Must declare the scalar variable "@client_app_name".
Msg 137, Level 15, State 2, Line 794
Must declare the scalar variable "@client_hostname".
Msg 137, Level 15, State 2, Line 796
Must declare the scalar variable "@client_hostname".
Msg 137, Level 15, State 2, Line 798
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 800
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 802
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 804
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 806
Must declare the scalar variable "@database_name".
Msg 137, Level 15, State 2, Line 809
Must declare the scalar variable "@session_id".
Msg 137, Level 15, State 2, Line 811
Must declare the scalar variable "@session_id".
Msg 137, Level 15, State 2, Line 813
Must declare the scalar variable "@session_id".
Msg 137, Level 15, State 2, Line 815
Must declare the scalar variable "@session_id".
Msg 137, Level 15, State 2, Line 817
Must declare the scalar variable "@sample_divisor".
Msg 137, Level 15, State 2, Line 820
Must declare the scalar variable "@username".
Msg 137, Level 15, State 2, Line 822
Must declare the scalar variable "@username".
Msg 137, Level 15, State 2, Line 824
Must declare the scalar variable "@object_name".
Msg 137, Level 15, State 2, Line 826
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 829
Must declare the scalar variable "@fully_formed_babby".
Msg 137, Level 15, State 2, Line 832
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 834
Must declare the scalar variable "@object_name".
Msg 137, Level 15, State 2, Line 837
Must declare the scalar variable "@requested_memory_mb".
Msg 137, Level 15, State 2, Line 839
Must declare the scalar variable "@requested_memory_mb".
Msg 137, Level 15, State 2, Line 840
Must declare the scalar variable "@requested_memory_kb".
Msg 137, Level 15, State 2, Line 842
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 850
Must declare the scalar variable "@wait_type".
Msg 137, Level 15, State 2, Line 854
Must declare the scalar variable "@wait_type".
Msg 137, Level 15, State 2, Line 885
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 943
Must declare the scalar variable "@session_with".
Msg 137, Level 15, State 2, Line 944
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 950
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 956
Must declare the scalar variable "@keep_alive".
Msg 137, Level 15, State 2, Line 995
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1001
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 1034
Must declare the scalar variable "@debug".
Msg 319, Level 15, State 1, Line 1041
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 1140
Must declare the scalar variable "@debug".
Msg 319, Level 15, State 1, Line 1147
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 1229
Must declare the scalar variable "@query_sort_order".
Msg 137, Level 15, State 2, Line 1232
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 1252
Must declare the scalar variable "@debug".
Msg 319, Level 15, State 1, Line 1259
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 1298
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1339
Must declare the scalar variable "@debug".
Msg 319, Level 15, State 1, Line 1346
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 1385
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 1404
Must declare the scalar variable "@debug".
Msg 319, Level 15, State 1, Line 1411
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 1452
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1469
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 1485
Must declare the scalar variable "@gimme_danger".
Msg 137, Level 15, State 2, Line 1490
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1550
Must declare the scalar variable "@event_type".
Msg 137, Level 15, State 2, Line 1579
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1613
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1648
Must declare the scalar variable "@keep_alive".
Msg 137, Level 15, State 2, Line 1650
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1658
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1701
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1731
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 1766
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1803
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1816
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1828
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1913
Must declare the scalar variable "@output_database_name".
Msg 319, Level 15, State 1, Line 1914
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 1916
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 1940
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1982
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 1987
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2003
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2014
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2284
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2304
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2311
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2319
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2331
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2353
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2358
Must declare the scalar variable "@delete_retention_days".
Msg 137, Level 15, State 2, Line 2368
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 2377
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2393
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 2396
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2398
Must declare the scalar variable "@executer".
Msg 319, Level 15, State 1, Line 2399
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 2403
Must declare the scalar variable "@executer".
Msg 137, Level 15, State 2, Line 2415
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 2418
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2420
Must declare the scalar variable "@executer".
Msg 319, Level 15, State 1, Line 2421
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Line 2425
Must declare the scalar variable "@executer".
Msg 137, Level 15, State 2, Line 2435
Must declare the scalar variable "@output_database_name".
Msg 137, Level 15, State 2, Line 2438
Must declare the scalar variable "@debug".
Msg 137, Level 15, State 2, Line 2446
Must declare the scalar variable "@debug".
Msg 178, Level 15, State 1, Line 2458
A RETURN statement with a return value cannot be used in this context.
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 15]
Statement(s) could not be prepared.
An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@pfb85fa3a74bc497b9a6efd2420ed5861' in statement or procedure 'ALTER PROCEDURE dbo.sp_HumanEvents
@event_type sysname=N'query', @query_duration_ms INT=500, @query_sort_order NVARCHAR (10)=N'cpu', @blocking_duration_ms INT=500, @wait_type NVARCHAR (4000)=N'ALL', @wait_duration_ms INT=10, @client_app_name sysname=N'', @client_hostname sysname=N'', @database_name sysname=N'', @session_id NVARCHAR (7)=N'', @sample_divisor INT=5, @username sysname=N'', @object_name sysname=N'', @object_schema sysname=N'dbo', @requested_memory_mb INT=0, @seconds_sample INT=10, @gimme_danger BIT=0, @keep_alive BIT=0, @custom_name NVARCHAR (256)=N'', @output_database_name sysname=N'', @output_schema_name sysname=N'dbo', @delete_retention_days INT=3, @cleanup BIT=0, @max_memory_kb BIGINT=102400, @version VARCHAR (30)=NULL OUTPUT, @version_date DATETIME=NULL OUTPUT, @debug BIT=0, @help BIT=0
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SELECT @version = '1.5',
           @version_date = '20200501';
    IF @help = 1
 ...' is missing in resultset returned by sp_describe_parameter_encryption.

Completion time: 2021-02-18T20:09:06.6604991+02:00

If the current behavior is a bug, please provide the steps to reproduce.

Copy the raw script for sp_HumanEvents.sql from Github
Paste it into SSMS and select the master database from the dropdown. Ensure the database server is running SQL 2017 Web edition.
Run the script. Observe the errors in the messages window.

What is the expected behavior?
The stored procedure should be installed in the master database with no errors. The script runs perfectly in SQL Server 2017 development edition.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server 2017 Web Edition (14.0.3370.1) on Windows 2012R2. This is the first time I am attempting to run this script.

Support needed for large amounts of RAM

DarlingData/sp_PressureDetector/sp_PressureDetector.sql

Line 161 in a468b03

DECIMAL(5, 2),

I have a large amount of memory on my server (about 1 TB) and this was giving an "arithmetic overflow error converting number to data type numeric" error. Changed DECIMAL(5,2) to DECIMAL(6,2) in three places in this query and that fixed it.

Problem collecting from XE QUERY "too many levels"

Version of the script
Latest

What is the current behavior?
SQL Agent collection job fails

Generating insert table statement for keeper_HumanEvents_query
[SQLSTATE 01000] (Message 50000) Error number 6335 with severity 16 and a state of 101 in procedure sp_HumanEvents on line 2896 XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
[SQLSTATE 42000] (Error 50000) XML datatype instance has too many levels of nest... The step failed.

If the current behavior is a bug, please provide the steps to reproduce.
I don't know how to reproduce this behavior.

What is the expected behavior?
Just run and fill tables with monitored data

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

sp_pressure_detector

SELECT @Version = '1.0', @versiondate = '20200301';

For versions of SQL Server if they don't have the following 2 columns.
deqmg.reserved_worker_count and deqmg.used_worker_count

The following error message occurs.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'ORDER'

Consider Changing the placement of the ,

            waits.wait_type'
            + CASE WHEN @helpful_new_columns = 1
                   THEN N',
            deqmg.reserved_worker_count,
            deqmg.used_worker_count,'
                   ELSE N''
            END

            waits.wait_type,'
            + CASE WHEN @helpful_new_columns = 1
                   THEN N'
            deqmg.reserved_worker_count,
            deqmg.used_worker_count,'
                   ELSE N''
            END

Running from Agent

Version of the script

SELECT @Version = '1.5', @version_date = '20200501';

What is the current behavior?

First, run:

sp_HumanEvents
      @event_type = 'query'
    , @query_duration_ms = 100
    , @client_hostname = 'VPS'
    , @database_name = 'V2_GATE'
    , @sample_divisor = 1
    , @object_schema = 'Web'
    , @keep_alive = 1

Then create job with step code below

sp_HumanEvents
      @output_database_name = 'dbaTools'
    , @output_schema_name = 'xeWeb'

Then I do first test run with code above (job step) in SSMS

Output

Do we skip to the GOTO and log tables?
Skipping all the other stuff and going to data logging
Starting data collection.
Sessions without tables found, starting loop.
While, while, while...
Updating #human_events_worker to set is_table_created for keeper_HumanEvents_query
Setting next id after 1 out of 1 total
Found views to create, beginning!
#view_check doesn't exist, creating and populating
Updating #view_check with output database (dbaTools) and schema (xeWeb)
Updating #view_check with table names
Starting view creation loop
creating view humanevents_queries
Setting next id after 3 out of 3 total
Sessions that need data found, starting loop.
Generating insert table statement for keeper_HumanEvents_query
Setting next id after 1 out of 1 total
Found views to create, beginning!
Sessions that need data found, starting loop.
Generating insert table statement for keeper_HumanEvents_query
Setting next id after 1 out of 1 total
Query was canceled by user.

For the second attempt got this error:

Updating #human_events_worker to set is_table_created for keeper_HumanEvents_query
Setting next id after 1 out of 1 total
Found views to create, beginning!
#view_check doesn't exist, creating and populating
Updating #view_check with output database (dbaTools) and schema (xeWeb)
Updating #view_check with table names
Starting view creation loop
creating view humanevents_queries
Msg 50000, Level 16, State 1, Procedure sp_HumanEvents, Line 3110 [Batch Start Line 11]
Error number 2714 with severity 16 and a state of 3 in procedure HumanEvents_Queries on line 1
There is already an object named 'HumanEvents_Queries' in the database.
Msg 2714, Level 16, State 3, Procedure HumanEvents_Queries, Line 1 [Batch Start Line 11]
There is already an object named 'HumanEvents_Queries' in the database.

What is the expected behavior?

That just works =)

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

sp_HumanEvents: Memory Grant info is gonna be wacky in views

The only one of the "query" events that gets memory grant information is the query plan event, which

Reports it for the batch which sucks for procs
But has it in the query plans

I can fix this in the proc easily by hitting the xml memory grant info real quick. It's not going to be as easy in the views. My options are to give misleading details for queries that are in stored procs, or add xml parsing to the view to try to get correct information. Neither one is awesome.

SP_humanevents

SP_humanevents failing with below error on SQl server 2012 Always on SP2.
sp_humanevents @event_type = 'Queries',@seconds_sample = 30;

Error:
Msg 50000, Level 16, State 1, Procedure sp_HumanEvents, Line 3050 [Batch Start Line 0]

Msg 25623, Level 16, State 3, Line 2
The event action name, "sqlserver.query_hash_signed", is invalid, or the object could not be found

sp_HumanEvents: Add ability to use lightweight plan collection XE

Add a flag to use this XE instead of the other plan collection XE: query_post_execution_plan_profile

CREATE EVENT SESSION query_thread_profile ON SERVER
ADD EVENT sqlserver.query_thread_profile
(
  ACTION(sqlserver.database_name, 
         sqlserver.plan_handle, 
         sqlserver.query_hash_signed, 
         sqlserver.query_plan_hash_signed, 
         sqlserver.sql_text))

sp_HumanEvents - Improve case sensitivity compatibility

For some parameters, I check the validity of inputs. This can fail on case sensitive instances.

Why are you so cute?

I'm having a serious issue with this.

sp_PressureDetector: Report high percentage of runnable queries

See here for details

Won't Run for SQL Server 2016 SP1 CU15 GDR

Version of the script

@Version = '1', @version_date = '20210423';

What is the current behavior?

Won't run on SQL Server 2016 build 13.0.4604.0. It reports:

Msg 50000, Level 11, State 1, Procedure sp_QuickieStore, Line 4312 [Batch Start Line 3]
error while (null)
offending query:
(null)
Msg 50000, Level 11, State 1, Procedure sp_QuickieStore, Line 83 [Batch Start Line 3]
This procedure only runs on supported versions of SQL Server

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

13.0.4604.0
4505221 Security update for SQL Server 2016 SP1 CU15 GDR: July 9, 2019 CVE-2019-1068

I don't know about previous versions of the procedure.

Thanks!

Typo in name of READEME.md

READEME.md change to README.md

Possible enhancement to dbo.get_numbers and dbo.get_letters

Hi, These are great functions and work very well and quickly as you say.

Having followed some of your suggested background material and landed on Jeff Moden's article on string splitting, I notice that he uses an inline CTE instead of a Tally Table and so I experimented with your code and a piece I borrowed from his and came up with the versions in the files in the attached zip file
Get_Numbers and Get_Letters Amended.zip
.
I would be interested to know whether you feel there is any added value or is it just an over complication?

Regards,

Patrick Holmes

Blocking Waittime in View

Version of the script
SELECT @Version = '1.0', @version_date = '20200301';

What is the current behavior?
The wait_time is updated for all individual events when you test the same blocking situation twice.
So if the wait on the first session was 10 sec and the other session is 20 sec, the first session is updated also with 20 sec.

If the current behavior is a bug, please provide the steps to reproduce.
Create a blocking situation check the waittime in the table dbo.keeper_HumanEvents_blocking
Wait a little bit and do the step above again.

What is the expected behavior?
The wait_times of the two sessions should differ, group by transaction_id?

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
WIN10 / SQL2019

Add views on top of logging tables to present data better

Logging to tables is done, but it's raw data. Getting data INTO the tables in good shape would have been terrible. As-is, it was pretty tedious getting everything right. Anyway, now I need to get the view data into a presentable format for people. They'll have to be created dynamically in the proc, which sucks, but maybe I can use synonyms.

sp_HumanEvents: Add functionality to write ring buffer data off to tables

Would need a mechanism sort of like sp_AllNightLog to grab from active sessions and push to tables.

What'd have to happen:

Sessions would need to be created with @keepalive = 1
That would prefix the session type with "permanent" or something
A separate code path would run and grab new data (which means I'd need a logging mechanism to keep track of when the last collection was) from any XE session with permanentHumanEvents in the name

Make it easier to filter wait stats

Should add some keywords here, like:

CPU
Memory
Disk
Lock
Poison

To grab related waits from each category, rather than making people list them out.

Suggestion to improve XML shredding performance

From what I can tell, #human_events_xml ends up with 1 row with entire XML. If possible it is often a good thing to pre-shred that to smaler parts. Here it looks like you could change #human_events_xml to have 1 row for each event instead and do this change without changing any other code and all will hopefully be fast and sweet.
For me with a session capturing about 50 queries the part that shreds the XML went from 4 seconds to 0.8 seconds.
Would be really interesting to see if you see the same. Perhaps you have other problem areas to test?

Sugested code change filling #human_events_xml

--Dump whatever we got into a temp table
declare @X xml;

select @X = convert(xml, t.target_data)
from sys.dm_xe_session_targets as T
  join sys.dm_xe_sessions as s
    on S.address = T.event_session_address
where S.name = @session_name 
  and T.target_name = N'ring_buffer' 
option (recompile);

select E.X.query('.') as human_events_xml
into #human_events_xml
from @X.nodes('/RingBufferTarget/event') as E(X)
option (recompile);

sp_HumanEvents: More reliable error handling

Right now I have error handling "implemented", but the catch block doesn't seem to fire reliably. I need to figure out why, so that orphaned sessions aren't left up on the server.

Add linguist override to .gitattributes file

# linguist overrides
*.sql linguist-language=TSQL

More details here: BrentOzarULTD/SQL-Server-First-Responder-Kit#2090

sp_HumanEvents: Set duration to 0 for compile and recompile

It's kinda stupid filtering on this because it's not query duration, it's compile duration.

arithmetic overflow error for type int

Version of the script
20200501

What is the current behavior?
arithmetic overflow error for type int when insert into keeper_HumanEvents_blocking

If the current behavior is a bug, please provide the steps to reproduce.
This only happens on an extraordinarily busy server, I think the transactionID is greater than the int range

What is the expected behavior?
As discussed

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL 2017 Windows 2016. This is my first attempt to use this
if I change the int to Bigint for transaction_id then everything will work. for table keeper_HumanEvents_blocking
N' transaction_id BIGINT NULL, resource_owner_type NVARCHAR(256) NULL, monitor_loop INT NULL, spid INT NULL, ecid INT NULL, query_text NVARCHAR(MAX) NULL, ' +

sp_PressureDetector: Add statement_start_offset to facilitate creation of plan guides

Hi Erik, thanks for the very useful sproc.

Would it be possible to have the column in subject to the right of the plan_handle column?
It would make creating plan guides much more convenient.

Thanks for the attention.

sp_HumanEvents: Maybe over-counting query executions

I think in certain parts of the code, grouping on event date is incorrect counts. Want to switch it to a MIN or MAX to aggregate instead.

Add ability to filter by recompile reason

Some people might only want to catch recompiles for specific reasons.

requested_memory_mb error

Msg 50000, Level 16, State 1, Procedure sp_HumanEvents, Line 3105 [Batch Start Line 1]

Msg 25713, Level 16, State 15, Line 3
The value specified for event attribute or predicate source, "requested_memory_kb", event, "query_post_execution_showplan", is invalid.

EXEC dbo.sp_HumanEvents @event_type = 'query', @query_duration_ms = 1000, @seconds_sample = 20, @requested_memory_mb = 1024;

Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64)
Nov 27 2019 18:09:22
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

sp_HumanEvents - Azure SQL Database DMV Error

Version of the script
SELECT @Version = '1.5', @version_date = '20200501';

What is the current behavior?
Several errors when running sp_HumanEvents in Azure SQL Database

If the current behavior is a bug, please provide the steps to reproduce.
EXEC [dbo].[sp_HumanEvents] @event_type = 'query',
@query_duration_ms = 1000,
@database_name = 'MyDB',
@keep_alive = 1;

EXEC sp_HumanEvents @output_database_name = N'MyDB', @output_schema_name = N'log';

/*
The first statement works fine and the event starts. However the second errors due to numerous references to sys.server_event_sessions, which I believe should be sys.database_event_sessions for Azure DB. There are also invalid references to dm_xe_database_session_targets and dm_xe_database_session.

Msg 208, Level 16, State 1, Procedure sp_HumanEvents, Line 2140 [Batch Start Line 6]
Invalid object name 'sys.server_event_sessions'.
*/
What is the expected behavior?
It appears that the @Azure parameter needs checking in these additional places and alternate statements
written. I quickly modified all references in my version

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Azure SQL Database

erikdarlingdata / darlingdata Goto Github PK

darlingdata's People

Contributors

Stargazers

Watchers

Forkers

darlingdata's Issues

Recommend Projects

Recommend Topics

Recommend Org