Using Sql profiler to check for performance issue .. continue

To use the server side sql tracing, we can use the following

DECLARE @TraceID int

Declare @tracefilename varchar(50)

set @tracefilename = 'C:\Temp\ServerSide_Trace' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '')

EXEC CreateTrace

@tracefilename,

@OutputTraceID = @TraceID OUT

-- stored procedures

EXEC AddEvent

@TraceID,

'SP:Completed',

'TextData, Duration, Reads ,Writes, CPU, ObjectID, ObjectName, ApplicationName, NTUserName, TargetLoginName, ClientHostName, StartTime , EndTime'

--- sqls

EXEC AddEvent

@TraceID,

'SQL:StmtCompleted',

'TextData, Duration, Reads ,Writes, CPU, ObjectID, ObjectName, ApplicationName, NTUserName, TargetLoginName, ClientHostName, StartTime , EndTime'

-- only include procedures that has run for more than 10 seconds.

EXEC AddFilter

@TraceID,

'Duration',

10000,

'>='

--- exclude system run ned stored procedures and sqls

EXEC AddFilter

@TraceID,

'ObjectID',

100,

'>='

EXEC StartTrace @TraceID

GO


To clear the server side tracing,

EXEC StopTrace 1

EXEC ClearTrace 1

Using Sql profiler to check for performance issue

If you are intending to check for sql server 2000 performance issue, check out this How to identify SQL Server performance issues, by analyzing Profiler output http://vyaskn.tripod.com/analyzing_profiler_output.htm .

It gives you a good template to trace the performance and find the top running stored procedures , high cpu usage , procedure cache usage information.

mysql common command

mysql> show databases;
+-------------+
| Database |
+-------------+
| mysql |
| servicedesk |
| test |
+-------------+

mysql> use servicedesk;

mysql> show tables;


mysql>
mysql> show privileges;
+-------------------------+--------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+--------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases and tables |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Shutdown | Server Admin | To shutdown the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+--------------------------+-------------------------------------------------------+
21 rows in set (0.00 sec)



mysql> show status;
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Aborted_clients | 4 |
| Aborted_connects | 6 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 170957189 |
| Bytes_sent | 672760406 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 256 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 175896 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 80 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 463 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 246 |
| Com_savepoint | 0 |
| Com_select | 489861 |
| Com_set_option | 1120 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 10 |
| Com_show_databases | 2 |
| Com_show_errors | 0 |
| Com_show_fields | 11 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 20 |
| Com_show_logs | 2 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 1 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 1 |
| Com_show_tables | 21 |
| Com_show_variables | 254 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 73349 |
| Com_update_multi | 0 |
| Connections | 267 |
| Created_tmp_disk_tables | 199 |
| Created_tmp_files | 4 |
| Created_tmp_tables | 282 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 175954 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_read_first | 189002 |
| Handler_read_key | 16998410 |
| Handler_read_next | 23482498 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 31315 |
| Handler_read_rnd_next | 6107226 |
| Handler_rollback | 499 |
| Handler_update | 772 |
| Handler_write | 30380 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 7173 |
| Key_blocks_used | 36 |
| Key_read_requests | 17488 |
| Key_reads | 263 |
| Key_write_requests | 4642 |
| Key_writes | 0 |
| Max_used_connections | 4 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 0 |
| Open_streams | 0 |
| Open_tables | 14 |
| Opened_tables | 27168 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 741859 |
| Rpl_status | NULL |
| Select_full_join | 544 |
| Select_full_range_join | 0 |
| Select_range | 373 |
| Select_range_check | 0 |
| Select_scan | 154713 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 202632 |
| Sort_rows | 855337 |
| Sort_scan | 2217 |
| Table_locks_immediate | 664883 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_connected | 3 |
| Threads_created | 266 |
| Threads_running | 1 |
| Uptime | 2415211 |
+----------------------------+-----------+
163 rows in set (0.00 sec)

Restore database (or database table) from backup.
[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql


mysql> show global variables;



mysql> show global variables like '%LOG%';
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| expire_logs_days | 0 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | OFF |
| log_error | .\SGUATSUPPORT01.err |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_relay_log_size | 0 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| sync_binlog | 0 |
+--------------------------------+----------------------+
25 rows in set (0.00 sec)