Tuesday, January 25, 2011
T-SQL Viewing current processes and queries in SQL 2000
One of my customers recently inquired with me if there was a way to include the current query with the results of master.dbo.sysprocesses in SQL 2000. Yes, there are folks out there still running production applications on SQL 2000! After I finished cleaning the cobwebs out of my head I came up with this. It's probably not the most elegant solution, but it gets the job done. This made me appreciate the DMVs a little more. By the way, this uses a function ( fn_get_sql) that was introduced in SQL 2000 SP3 so you'll need that before running this. Also, to improve the matches, you would need to implement trace flag 2861 which allows SQL Server to keep zero cost plans in cache, which SQL Server would typically not cache. This flag was also introduced in SQL 2000 SP3. The downside is you’d have a reduced data cache but the upside might be worthwhile. Either way, it’s a measurable change if you were to implement it. The code I wrote is below, royalty free.