Comparison of Date functions between SQL Server and MySQL

In our support life we all are working of multiple RDBMS at same. It's tough to remember all function available in different RDBMS. We try to run same query which was running in another RDBMS :). Here i am comparing the date function available in MS SQL Server and MySQL. Hope this will help.

SQL SERVER	MYSQL
DATEADD(day, 1, GETDATE())	DATE_ADD(NOW(), INTERVAL 1 DAY)
DATEDIFF(day, GETDATE(), GETDATE()-1)	DATEDIFF(NOW(), NOW() – INTERVAL 1 DAY)
DATENAME(month, GETDATE())	DATE_FORMAT(NOW(), ‘%M’) MONTHNAME(NOW())
DATENAME(weekday, GETDATE())	DATE_FORMAT(NOW(), ‘%W’) DAYNAME(NOW())
DATEPART(month, GETDATE())	DATE_FORMAT(NOW(), ‘%m’)
DAY(GETDATE())	DATE_FORMAT(NOW(), ‘%d’) DAY(NOW())) DAYOFMONTH(NOW())
GETDATE()	NOW() SYSDATE() CURRENT_TIMESTAMP CURRENT_TIMESTAMP()
GETDATE() + 1	NOW() + INTERVAL 1 DAY CURRENT_TIMESTAMP + INTERVAL 1 DAY
GETUTCDATE()	UTC_TIMESTAMP()
MONTH(GETDATE())	MONTH(NOW())
YEAR(GETDATE())	YEAR(NOW())

Some Useful SQL Server Commands

From last some time i am working with one of my SQL DBA friend. In this time i have learned something about SQL Server Also, 

Here are some of useful SQL DBA Commands. There all queries are using SQL Server DMV.

Get the information about CPU Consuming Queries

SELECT TOP ( 10 )
SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1)
AS statement_text ,
execution_count ,
total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count
AS avg_worker_time_ms ,
total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count
AS avg_elapsed_time_ms ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC

Find Out Number of Logical CPU in System using SQL Server

SELECT cpu_countFROM sys.dm_os_sys_info
GO-- Identify Virtual Processors in for SQL Server 2000CREATE TABLE #TempTable([Index] VARCHAR(2000),[Name] VARCHAR(2000),[Internal_Value] VARCHAR(2000),[Character_Value] VARCHAR(2000)) ;INSERT INTO #TempTableEXEC xp_msver;SELECT Internal_Value AS VirtualCPUCountFROM #TempTableWHERE Name = 'ProcessorCount';DROP TABLE #TempTableGO

Recently Executed Query

SELECT
deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Query To Find Out Full Details of Databases 

SELECT database_id,CONVERT(VARCHAR(25), DB.name) AS dbName,CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],user_access_desc AS [User access],recovery_model_desc AS [Recovery model],CASE compatibility_levelWHEN 60 THEN '60 (SQL Server 6.0)'WHEN 65 THEN '65 (SQL Server 6.5)'WHEN 70 THEN '70 (SQL Server 7.0)'WHEN 80 THEN '80 (SQL Server 2000)'WHEN 90 THEN '90 (SQL Server 2005)'WHEN 100 THEN '100 (SQL Server 2008)'END AS [compatibility level],CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],-- last backupISNULL((SELECT TOP 1CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +' (' + CAST(DATEDIFF(second, BK.backup_start_date,BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],page_verify_option_desc AS [page verify option],CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]FROM sys.databases DBORDER BY dbName, [Last backup] DESC, NAME

Hope these will also help you. :)

 

Useful MySQL command line Shortcuts and commands

In our daily MySQL Process we need to do some task repeatedly. Below are some useful  command line shortcut to work with MySQL. 

\? = help 

\c = clear command, It Will cancel last executed command. 

\C = switch to another charset, It will help if you want to use another character set. Make sure you are choosing correct charcter Set.

\d = set delimiter (use something else after a statement instead of ;), It will help whenever you are  creating function and procedure.

\g = send command to server , it will help to execute command , Will work as delimiter

\G = send command to server, Will work as delimiter , It will show resultset in grid format.

With \g and \G you can generate "vertical" output with each column value on a separate line. This makes large outputs far more readable !

\h = help, to See the help content and commands

\p = print current command

\q = quit MySQL
\r = reconnect (or see which database you're in)

\R = change your mysql prompt (personalize your prompt, which can be funny)

\s = show status (which can be very informative)

\t = don't write to into outfile
\T = write into outfile
\u = use database

\w = don't show warnings after every statement
\W = show warnings after every statement
\# = rebuild completion hash
\. = execute an SQL scriptfile

select database()= To know current database.

Enjoy Short cuts :)

meminfo To know detail Memory information on *nix server

Meminfo provides information about distribution and utilization of memory.  This utilization varies on architecture of server.  All fields may not be there is all server output.

$cat /proc/meminfo

MemTotal:        8062872 kB

MemFree:          927352 kB

Buffers:          225360 kB

Cached:          6142440 kB

SwapCached:            0 kB

Active:           949748 kB

Inactive:        5816904 kB

Active(anon):     330948 kB

Inactive(anon):    68100 kB

Active(file):     618800 kB

Inactive(file):  5748804 kB

Unevictable:           0 kB

Mlocked:               0 kB

SwapTotal:       4194296 kB

SwapFree:        4194296 kB

Dirty:                44 kB

Writeback:             0 kB

AnonPages:        398812 kB

Mapped:            61616 kB

Shmem:               192 kB

Slab:             253172 kB

SReclaimable:     221144 kB

SUnreclaim:        32028 kB

KernelStack:        1936 kB

PageTables:         7340 kB

NFS_Unstable:          0 kB

Bounce:                0 kB

WritebackTmp:          0 kB

CommitLimit:     8225732 kB

Committed_AS:     862100 kB

VmallocTotal:   34359738367 kB

VmallocUsed:      289592 kB

VmallocChunk:   34359440136 kB

HardwareCorrupted:     0 kB

AnonHugePages:    342016 kB

HugePages_Total:       0

HugePages_Free:        0

HugePages_Rsvd:        0

HugePages_Surp:        0

Hugepagesize:       2048 kB

DirectMap4k:       10240 kB

DirectMap2M:     8378368 kB

Here is the detail about some of important parameters

 MemTotal: Total usable RAM (i.e. physical ram minus a few reserved bits and the kernel binary code)
 MemFree:  Free RAM portioan
 Buffers:     Temporary storage for raw disk blocks
Cached:      In-memory cache for files read
SwapCached: Memory that once was swapped out, is swapped back in but still also is in the swapfile (if memory is needed it doesn't need to be swapped out AGAIN because it is already in the swapfile. This saves I/O)
 Active: Memory that has been used more recently and usually not reclaimed unless absolutely necessary.
 Inactive: Memory which has been less recently used.  It is more eligible to be reclaimed for other purposes

SwapTotal: total amount of swap space available
SwapFree: Memory which has been evicted from RAM, and is temporarily on the disk
Dirty: Memory which is waiting to get written back to the disk
Writeback: Memory which is actively being written back to the disk
AnonPages: Non-file backed pages mapped into userspace page tables
Mapped: files which have been mmaped, such as libraries
Slab: in-kernel data structures cache
SReclaimable: Part of Slab, that might be reclaimed, such as caches
SUnreclaim: Part of Slab, that cannot be reclaimed on memory pressure
PageTables: Amount of memory dedicated to the lowest level of page tables.
NFS_Unstable: NFS pages sent to the server, but not yet committed to stable storage
Bounce: Memory used for block device "bounce buffers"
WritebackTmp: Memory used by FUSE for temporary writeback buffers
VmallocTotal: total size of vmalloc memory area
VmallocUsed: amount of vmalloc area which is used
VmallocChunk: largest contiguous block of vmalloc area which is free

Troubleshooting MySQL database performance

Here are some cool ways, how you can monitor the database performance of your MySQL installation.

Monitoring of any database/Application is always an iterative and continuous process. You need to check which graph is ok for your Application/Database. You need to measure which values are the signs of Alert or DR and what may cause it.

Below are the main items you can use to monitor your system:

1. MySQL error log

2.  mysqladmin processlist

3.  mysqladmin extended (absolute values)
4.  mysqladmin extended -i10 -r (relative values)
5.  mysql -e “show innodb status”
6.  OS data. vmstat/iostat

1) MySQL error log
-The error log is great source of information in case of there is any error.  Nothing should write to the error log, after the MySQL server has completed its initialization sequence, so everything written in error log after initialization should check immediately.

 

2) mysqladmin processlist or “SHOW FULL PROCESSLIST” command
It will return number of threads connected and running by using other statistics, but this is a good way to check how long queries that are running take. If there are some very long-running queries  the admin/Application team should be informed.

3) mysqladmin extended

 

It’s a client utility available with MySQL. You can monitor MySQL status using this utility. You need to check below parameter.

# Slave_running: If replication is configured on system, it will be provide slave status. This command should be executed on slave server.
#Threads_connected: The number of clients currently connected to MySQL server. This should be less than configured value (max_connection).

# Threads_running: If the database is overloaded you’ll get an increased number of queries running. That also should be less than some preset value . It is OK to have values over the limit for very short times. Then you can monitor some other values, when the Threads_running was more than the preset value and when it did not fall back in 5 seconds.

4) mysqladmin extended

The idea is that you store the performance counter value and compute the difference with the new values. The interval between the recordings should be more than 10 seconds. The following values are good candidates for checking:

* Aborted_clients: The number of clients that were aborted (because they did not properly close the connection to the MySQL server). For some applications this can be OK, but for some other applications you might want to track the value, as aborted connects may indicate some sort of application failure.
* Questions: Number of queries you get per second. Also, it’s total queries, not number per second. To get number per second, you must divide Questions by Uptime.
* Handler_*: If you want to monitor low-level database load, these are good values to track. If the value of Handler_read_rnd_next is abnormal relative to the value that you normally would expect, it may indicate some optimization or index problems. Handler_rollback will show the number of queries that have been rolled back. You might want to wish to investigate them.
* Opened_tables: Number of table cache misses. If the value is large, you probably need to increase table_cache. Typically you would want this to be less than 1 or 2 opened tables per second.
* Select_full_join: Joins performed without keys. This should be zero. This is a good way to catch development errors, as just a few such queries can degrease the system’s performance.
* Select_scan: Number of queries that performed a full table scan. In some cases these are OK but their ratio to all queries should be constant. if you have the value growing it can be a problem with the optimizer, lack of indexes or some other problem
* Slow_queries: Number of queries longer than –long-query-time or that are not using indexes. These should be a small fraction of all queries. If it grows, the system will have performance problems.
* Threads_created: This should be low. Higher values may mean that you need to increase the value of thread_cache or you have the amount of connections increasing, which also indicates a potential problem.


5) “SHOW INNODB STATUS”
This statement produces a great deal of information, from which you should extract the parts in which you are interested. The first thing you need to check is: “Per second averages calculated from the last xx seconds”. InnoDB rounds stats each minute.

* Pending normal aio reads: These are InnoDB IO request queue sizes. If they are bigger than 10-20 you might have some bottleneck.
* reads/s, avg bytes/read, writes/s, fsyncs/s: These are IO statistics. Large values for reads/writes means the IO subsystem is being loaded. Proper values for these depend on your system configuration.
* Buffer pool hit rate: The hit rate also depends a lot on your application. Check your hit rate, when there are problems.
* inserts/s, updates/s, deletes/s, reads/s: These are low level row operations that InnoDB does. You might use these to check your load if it is in expected range.

 

The detail information this output you can get here.

6) OS Data. Good tools to see the system status are vmstat/iostat/mpstat.

 

#iostat will provide information about io on *nix systems.