Re: Is SQL Server ‘keeping up’ with MySQL?

I came across this article in my DBA readings: Is SQL Server keeping up with MySQL?

Since I’ve got experience on both sides of the fence, I’ll chime in and try to not be biased.

When did MySQL finally add stored procedures?

Version 5.0. From my experience, they are young/buggy. For instance no variables for LIMITs or default parameter values.

How about transaction support, SSIS-like capabilities, a scheduled job management system, and a relatively easy-to-use consolidated GUI management tool?

Version 4.1. The rest of the question is a bit loaded.

MySQL as a query engine has lots of nice features and performs well. You should always use the platform that will meet your needs.

Simply put, MySQL’s administration tools don’t cut it.

“LIMIT” notwithstanding, if SQL Server’s functional specs weren’t already light years beyond MySQL with SQL Server 2005, it will certainly leave them in the dust when SQL Server 2008 hits the streets.

I don’t know about light years. Especially for the query engine.

SQL Server’s query engine is still greatly ahead of MySQL and the administrative tools are light years ahead.

MySQL EXPLAIN Visualization with mktools-visual-explain

EXPLAIN statement visualization is something I love about Aqua Data Studio (we use it a lot with DB2) and SQL Server Management Studio. It takes the result of your EXPLAIN and displays it in an easier to understand diagram. Here’s example output from SQL Server:

visual-explain-ssms.png

Aqua Data Studio doesn’t support MySQL Visual Explains (yet). The only way I’ve been able to find EXPLAIN visualization is with maatkit and mk-visual-explain.

Here’s an explain from one of the queries in a project (columns have changed names)

Here’s the output with mk-visual-explain

Which one is easier to understand?

SQL Server: KILL Processes using a database

If you need to do some operations on a database, sometimes you’re required to be the only user. Typically you will execute this procedure:

exec sp_who

Then you’ll get the spid‘s of each one related to your database and KILL them. The problem is there’s no ability to put a WHERE clause with sp_who.

use master;
select 'kill ', spid from sysprocesses p
	inner join sysdatabases d
	on p.dbid = d.dbid
WHERE d.name = 'DBNAME'

Say spid’s 4, 8, 15, 16, 23, and 42 are using the database ‘DBNAME’. Now you can just copy/paste your results window as a set of new queries:

kill 4
kill 8
kill 15
kill 16
kill 23
kill 42