Here’s a simple way to determine the date/time a database was created in MySQL:
SELECT create_time FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DB_NAME'
mysql> select create_time from information_schema.tables where table_schema='foo'; +---------------------+ | create_time | +---------------------+ | 2013-03-01 10:42:34 |
I was working with a mail archive MySQL database today and was twiddling my thumbs waiting for simple queries to complete. The database has about 12 million rows and is on a 2x2GHz 2GB Linux Server x64. I wanted to try to optimize the database and found this little gem from Lee at SoftLayer blog post.
SELECT
TABLE_SCHEMA,
TABLE_NAME,
CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA,
CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB') FREE
from
information_schema.TABLES
where
TABLE_SCHEMA NOT IN ('information_schema','mysql')
and
Data_free > 0
After you run the SQL on your database you can use optimize table table_name to optimize it.
My performance increased enough to be noticeable. =)
Here is a list of Windows’ performance counters to use in monitoring performance of an SQL server.
Create Performance Collection rules targeted to a SQL Server computer group for the following performance counters:
LogicalDisk(*)Avg Disk sec/ReadLogicalDisk(*)Avg Disk sec/WriteLogicalDisk(*)Disk Read Bytes/secLogicalDisk(*)Disk Reads/secLogicalDisk(*)Disk Write Bytes/secLogicalDisk(*)Disk Writes/secMSSQL:Buffer MangerBuffer cache hit ratioMSSQL:Buffer MangerPage Lookups/secMSSQL:Buffer MangerPage reads/secMSSQL:Buffer MangerPage writes/secFrom Paul Randal’s survey of highest wait on server TSQL.
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO
From Sunil’s blog to identify the top 10 queries generating most IO.
SELECT TOP 10
(total_logical_reads/execution_count) AS
avg_logical_reads,
(total_logical_writes/execution_count) AS
avg_logical_writes,
(total_physical_reads/execution_count)
AS avg_phys_reads,
execution_count,
statement_start_offset as stmt_start_offset,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
plan_handle
FROM sys.dm_exec_query_stats
ORDER BY
(total_logical_reads + total_logical_writes) DESC