Helper Objects
zys objects
The helper objects in the zys schema can help in various ways.
“Hard Core” part
zys objects exist both on STG and PROD and all objects are identical on both databases.
zys objects use the core.setting and core.event concepts which also exist both on STG and PROD databases and all objects are identical on both databases.
So you might say that together they are the “Hard Core” part of the databases.
The picture below shows how the “Hard Core” part lies underneath in both databases. Note that only objects are identical in both databases but the data in the tables is not identical, you might f.e. have the same setting_key in core.setting but different setting_value in STG and PROD which might f.e. happen if you wanted to have different index maintenance on STG and PROD.
zys.help
You can execute zys.help to get minimalistic help about other stuff, so in theory, if you can only remember one thing you should remember this one…
zys.helpThe zys.help proc prints out help about the “Hard Core” part and then it executes other help procs if they exist in the database. We currently have stg.help proc on STG and prod.help proc on PROD which means that if you execute zys.help on STG it will give you printout from zys.help + stg.help and if you execute zys.help on PROD it will give you printout from zys.help + prod.help.
If you want to limit the printout you have the following options…
zys.help 'zys'
zys.help 'stg'
zys.help 'prod'
stg.help
prod.helpHelper Procs
zys includes helper procs to help with various things, following are some examples but note that there are a lot more options…
Get info on what is running on the server
zys.top_sqlGet info for all kinds of things
zys.info
zys.info 'server'
zys.info 'database'
zys.info 'tables'
zys.info 'tables with rows'
zys.info 'top tables'
zys.info 'indexes', 'core.event'
zys.info 'heaps'
zys.info 'events'
zys.info 'errors'
zys.info 'system info events'
zys.info 'debug'Print template for various things
zys.template
zys.template 'proc'
zys.template 'cursor'Print description for object
zys.describe 'zys.date_year'Find usage of text inside objects
zys.find_usage 'date_year'Use a simple profiler to check proc execution stats (switch to Messages tab to see results)
zys.profilerGet proc and index execution stats info for an interval
zys.stats
zys.stats '3 minutes'Print without waiting for the print buffer to fill
EXEC zys.print_now 'Some important text'
EXEC zys.print_now 'Some important text with time info', 1
PRINT 'Some important text'
EXEC zys.print_flushHelper Functions
zys includes helper functions to help with various things, following are some examples…
Turn seconds into a nice readable string
SELECT core.time_string(63), core.time_string(3684)Date functions, f.e. nice to use in GROUP BY and to get beginning and end of month
SELECT GETDATE(), zys.date_day(GETDATE()), zys.date_week(GETDATE()), zys.date_month(GETDATE()), zys.date_year(GETDATE())
SELECT zys.date_bomonth(-12), zys.date_eomonth(-1)Numbers function (very fast) that can be used for all kinds of things
SELECT * FROM zys.numbers(100)List to table functions
SELECT * FROM zys.int_list_to_table('10,20,30,40,50', ',')
SELECT * FROM zys.string_list_to_table('aaa, bbb,ccc, ddd,eee', ',', 0)
SELECT * FROM zys.string_list_to_table('aaa, bbb,ccc, ddd,eee', ',', 1)
SELECT * FROM zys.int_list_to_ordered_table('10,20,30,40,50', ',')
SELECT * FROM zys.string_list_to_ordered_table('aaa,bbb,ccc,ddd,eee', ',', 0)Database Stats
Database stats are saved using the proc zys.every_ten_minutes which is normally executed every 10 minutes in a SQL Agent job. Index stats, wait stats and performance counters are saved once per day at midnight but proc stats can be saved more frequently.
SELECT TOP 30 * FROM zys.proc_stats ORDER BY [stats_date] DESC, reads DESC
SELECT TOP 30 * FROM zys.proc_interval_stats ORDER BY stats_date_time DESC, reads DESC
SELECT TOP 30 * FROM zys.index_stats ORDER BY [stats_date] DESC, reserved_kb DESC
SELECT TOP 30 * FROM zys.wait_stats ORDER BY [stats_date] DESC, wait_time DESC
SELECT TOP 30 * FROM zys.performance_counters ORDER BY [stats_date] DESC, [value] DESCSettings are used to control how database stats are saved…
SELECT * FROM core.setting WHERE setting_key like 'zys%'Saving proc stats more frequently
Proc stats are by default saved every 4 hours. If SQL Server is under memory pressure database stats are cleared meaning the stats are lost. For such cases it might be a good option to save proc stats more frequently. Setting zys_save_interval controls this and values allowed are 10, 20, 30, 60, 120, 240, 480, 720 and 1440 minutes.
Change setting to save proc stats every 10 minutes like this…
UPDATE core.setting SET setting_value = '10' WHERE setting_key = 'zys_save_interval'Saving proc interval stats
Sometimes we might temporarily want to save proc interval stats every x minutes, f.e. if we are trying to find if there is extra load at certain hours. Setting zys_save_interval_stats allows us to do this, if it is set then proc interval stats are saved to zys.proc_interval_stats.
Change setting to save proc interval stats like this…
UPDATE core.setting SET setting_value = 'true' WHERE setting_key = 'zys_save_interval_stats'Index Maintenance
zys includes index maintenance procs from Ola Hallengren, see proc zys.dbm_index_optimize.
Settings can be used to control index maintenance…
SELECT * FROM core.setting WHERE setting_key like 'zys%'Index maintenance is now logged in core.event meaning you will see something like this…
Not doing index maintenance every day
By default index maintenance is executed every day via the proc core.run_db_maintenance. That is a little excessive and very normal that some clients want to do that less frequently. Setting zys_index_maintenance_days can be used to change this, it has default value * which means every day but you can change that to comma separated list of day numbers where 1 is monday and 7 is sunday.
Change index maintenance to execute on wednesdays and saturdays like this…
UPDATE core.setting SET setting_value = '3,6' WHERE setting_key = 'zys_index_maintenance_days'Do index maintenance for STG and PROD on different days
It would also be very normal for a client to want to execute index maintenance for STG and PROD on different days, one normal scenario would be to do index maintenance once a week for each database f.e. STG on saturdays and PROD on sundays. The problem here is that proc core.run_db_maintenance executes proc zys.dbm_index_optimize on STG for both databases which means that settings are read from STG and logging is done on STG. So if you want to do index maintenance for STG and PROD on different days you first need to change the core.run_db_maintenance proc and then change index maintenance settings in table core.setting on each database.
First change second call to zys.dbm_index_optimize in proc core.run_db_maintenance like this (simply add agr_prod. to the name)…
EXEC agr_prod.zys.dbm_index_optimize @Databases = @prod_db_name, @use_settings_table=1Then change the setting on each database like this, STG to f.e. saturday and PROD to f.e. sunday…
UPDATE agr_stg.core.setting SET setting_value = '6' WHERE setting_key = 'zys_index_maintenance_days'
UPDATE agr_prod.core.setting SET setting_value = '7' WHERE setting_key = 'zys_index_maintenance_days'System Info Events
A few System Info Events are now logged in core.event meaning you will see something like this…
Hardware Info, Windows Version, SQL Server Version and Database Info is logged once per day and means that we will log if a few important things change f.e. memory, database compatibility level and so on. Database and Transaction Log Size is logged every 10 minutes which is often important data when something goes wild.
SQL to check system info events…
zys.info 'system info events'
SELECT TOP 100 * FROM core.event WHERE event_type_id = 501 ORDER BY event_id DESCDebug Logging
Temporary debug logging inside procs can easily be done using proc zys.debug which logs to table zys.debug_info, examples…
EXEC zys.debug 'Some info text'
EXEC zys.debug 'Some other info text', @int_1 = @@ROWCOUNT, @int_2 = 123SQL to check debug info…
zys.info 'debug'
SELECT TOP 500 * FROM zys.debug_info ORDER BY info_id DESCNote that this is only intended for temporary debug logging meaning it will not be stored in the table for years to come. Everyone is allowed to delete non-recent stuff from the zys.debug_info table without asking for a special permission.
ex views
The purpose of the ex views is to save you from needing to make endless join statements when querying simple data in SQL Studio.
The vision is that for all tables with non-readable id columns you have an ex view to give you text for id’s meaning you simply need to add _ex to the table name to get extra info.
Below you have 3 examples but note that there are a lot more ex views you can try out.
Note that ex views are only for query usage in SQL Studio, ex stands for excessive joins meaning its not built for performance and should not be used in important stored procedures.
Examples
items
Lets do one example with the important items table, here is a simple SELECT statement…
SELECT TOP 3 * FROM items…which gives us this result where we can see that we have location_id, primary_vendor_id and item_group_id which are numbers that do not tell us much…
If we simply add _ex to the table name meaning we now use the ex view we get this simple SELECT statement…
SELECT TOP 3 * FROM items_ex…which gives us this result where we see text info for all of those columns…
mbe_columns
Another example with the important mbe_columns table, here we see that we get text info for columns mbe_table_id, mbe_column_group_id and lookup_table_id…
SELECT * FROM mbe_columns
SELECT * FROM mbe_columns_ex
role_features
Another example with a table that only has non-readable id columns… …
SELECT * FROM role_features
SELECT * FROM role_features_ex