April 21, 2009 at 9:00 am
I am trying to figure out SQL usage, but i'm not sure how to go about it. I have the following query but I'm not sure exactly what it tells me. I am also curious about SQL transactions and how I can tell which transaction is being used alot and how long it takes to run. The reason is I am an asp programmer that designs my company's internal intranet. There are occasions where we we have slow downs. I'm trying to rule out SQL from being the cause. Any help or insight would be greatly appreciated.
SELECT sysdatabases.name, sysprocesses.nt_domain, sysprocesses.nt_username, sysprocesses.hostname, sysprocesses.loginame,
sysprocesses.program_name, sysprocesses.cpu, sysprocesses.physical_io, sysprocesses.memusage
FROM sysprocesses INNER JOIN
sysdatabases ON sysprocesses.dbid = sysdatabases.dbid
ORDER BY sysdatabases.name, sysprocesses.cpu desc
April 21, 2009 at 9:14 am
If you're trying to determine if there are slow running queries, you'd be better off running queries against sys.dm_exec_query_stats. That will show you aggregate information about queries that are currently in the system's cache. To see actively execution queries look to the sys.dm_exec_requests, but that will just show you what's executing and for how long. Performance data is best collected from the other dynamic management view (DMV). Look it up in the books online for details around it's use. It should be pretty self-explanatory.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2009 at 9:22 am
You might want to use Profiler and let that run for awhile, grabbing the queries or stored procedures that are being executed.
you can load these up, then do some summing/grouping/totaling to see what is being run often. From there you want to use some of the DMVs Grant has mentioned to tune those items.
I'd also look for missing indexes.
April 21, 2009 at 10:47 am
currently we are using SQL 2000, where would i find sys.dm_exec_query_stats.
April 21, 2009 at 10:51 am
That's not available in 2000. You posted in a 2005 forum, so you got answers for that.
I'll move this to 2000.
You likely need to use Profiler to find queries. Not a lot of system information in 2000.
April 21, 2009 at 10:53 am
oops thanks
April 21, 2009 at 11:32 am
Sorry about that. The forum drove the answer. Steve's right. Profiler is your best, really your only, option.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply