Author: volt
-
Monitoring Wait Events of a single Session or Query in SQL Server
Using sys.dm_os_wait_stat is not useful for deep troubleshooting because this view contains wait events for ALL processes/queries running on your instance since last restart. Using command “DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR)” we can reset this view with no restart but on big and stressed instances with many processes and applications running this isn’t a real good…
-
How to script logins with the original password HASH and original SID
A quick tip useful to avoid problems during migrations. This query allow to script instance users with orginal SID and password HASH. This can be very useful to transfer logins to a new or different instance. All informations come from master database catalog. You have to be member of sysadmin role to run correctly this…
-
How to improve mysql performance using CACHING
A deep analisys of a performance bottleneck could be long and is based on different kind of data (OS metrics, IO performance tests, DB wait stats and so on…). Before starting with this (or simply after a new delivery) some simple checks could help you to correct rapidly some of the common (and bigger) bottlenecks…
-
Massive SQL Server Database Moving Using Detach – Attach: The Complete Procedure
This is a complete configurable metascript, prepared to create detach statements, file move statement and re-attach statements for every database in your instance. You have only to configure the final destination for datafiles and the database list you want to migrate. The final generated script for every database selected will be something like this (remembar…
-
Anayze SQL default trace to investigate instance events
Quering default trace is the best way to investigate unusual or critical events heppened in SQL server and not logged in errorlog files. It’s not difficult to find useful informations there but the trace is full of codes to translate to make it more readable and useful. This is my query, based on sys.fn_trace_gettable function…
-
Queries to see rapidly what your MySql is doing NOW
Those queries are useful to admin MySQL instances using mysql client. 1) Check active processes/connections running mysql> show processlist; the previous statement doesn’t show all queries text running. To see the complete statements running use: mysql> show processlist; –or– mysql> show full processlist G; the running queries statements could bee quite long. Use the “G”…
-
SQL Jobs Monitoring: check last run datetime and duration
A simple query to check rapidly your job’s status and duration. Useful for fast monitoring on many instances. No more thing s to say: this is the code based on msdb..sysjobs and msdb..sysjobhistory. It’s easy if necessary filtering a single job id or jobs durations too long. select job_id, job_name, run_datetime, SUBSTRING(run_duration, 1, 2) +…
-
How to make your databases smaller and faster: find unused indexes
It’s a boring job but sometimes a good DBA has to do it. Applications change and you have to understand what become unuseful in your databases: we are talking about unused indexes. In any SQL server database indexes take up a lot of space and have to be updated every time an application runs an…
-
Analyze SQL Server database historical growth: MONTLY size changes
The most simple way to analyze database historical growth is quering database backup catalog. SQL Server catalog stores informations about every single database backup in msdb..backupset. If you don’t have other instruments to collect historical database size this is a good point to start for a capacity planning This time we take the max size…
-
Analyze SQL Server database historical growth: DAILY size changes
The most simple way to analyze database historical growth is quering database backup catalog. SQL Server catalog stores informations about every single database backup in msdb..backupset. If you don’t have other instruments to collect historical database size this is a good point to start for a capacity planning. Remembar only this: – msdb..backupset stores historical…