Category: Administration

David Fowler announces the SQL Undercover Catalogue:

Where Can I Get the Catalogue From?

The Undercover Catalogue is available from our GitHub site.

What Does the Catalogue Store?

The Undercover Catalogue stores all manner of useful information on your SQL Servers,

• Instances –
• Databases
• Logins
• Users and permissions
• Agent Jobs

with many more modules planned in future releases.

Check it out and I’m sure they’d love feedback.  Also, read on for where this toolkit is going.

Lori Brown walks us through the sys.dm_sql_referencing_entities and referenced entities DMVs:

In both cases you can see that sys.sql_expression_dependencies provides the same basic info.  But when it comes to tables are referenced by a stored procedure, sys.sql_expression_dependencies gives you less detail than you can get when using sys.dm_sql_referenced_entities.  The biggest difference in both examples is that with sys.sql_expression_dependencies you will get info on cross-database and cross-server dependencies which can be super helpful.

However, once again I have to sound a note of caution because even sys.sql_expression_dependencies does not catch things referenced in a dynamic SQL string.  Sales.Orders is found in dynamic SQL in the, [Application].[Configuration_EnableInMemory] & [DataLoadSimulation].[Configuration_ApplyDataLoadSimulationProcedures] stored procedures but it does not catch this.  So far the best way to find objects in dynamic SQL strings that I know of is to check for the object in sys.sql_modules.

Read the whole thing.

Jeff Mlakar looks at some basic guidelines for tempdb and shows how to configure this database:

The basic guidelines are:

• Each tempdb data file should be the same initial size

• Autogrowth to tempdb files should be an explicit value in MB instead of a percentage. Choose a reasonable value based on the workload. Ex. 64MB, 128MB, 256MB, etc.

• The number of tempdb files should be 1 per logical processor core up to 8. At that point the performance should be monitored and if more tempdb files are needed they should be added in sets of 4.

• Ideally the tempdb files are sized up to the max they will need and never have to autogrow.

• Use trace flags 1117 and 1118 for versions of SQL Server < 2016. In SQL Server 2016 these trace flags are defaults.

  • Trace flag 1117: when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow together

  • Trace flag 1118: Removes most single page allocations on the server, reducing contention on the SGAM page. TLDR; no more mixed extents – use the whole page.

There are some good pieces of advice here, and Jeff includes a great example of a terrible setup.

Andy Galbraith walks us through a scary-looking error message that turned out to be not quite accurate:

There were a stack of errors overnight in the DB123 database on SQL01, including one horror show error:

—

Log Name:      Application

Source:        MSSQLSERVER

Date:          5/24/2018 12:49:39 AM

Event ID:      3314

Task Category: Server

Level:         Error

Keywords:      Classic

User:          N/A

Computer:      SQL01.client99.com

Description:

During undoing of a logged operation in database ‘DB123’, an error occurred at log record ID (195237:96178:4). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

The catch is that when I checked the server, the database was online.

Say what?

Read on for root cause analysis and issue correction.  This is a good example where jumping to conclusions could lead you down the wrong path and miss the root cause entirely.