Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to monitor SQL server performance using codes
- Thread starter ALO73
- Start date
mrdenny
Programmer
- May 27, 2002
- 11,595
sp_who2 will give you some basic information. As bborissoc said SQL Profiler is also a good tool, but you will need sysadmin rights to run Profiler.
By looking in the CPU and Disk IO columns of sp_who2 this will give you a basic idea of what processes have been using a lot of disk or CPU. This can be deciving however. These numbers are for the life of the connection, not for the specific command. So if you stay connected for 3 days running lots of small commands you could end up with a large CPU and disk number at the end of the three days. To keep your sanity also look at the login_time column of the master.dbo.sysprocesses table. This will tell you when the user logged into the SQL Server. If the user logged in recently and has high disk or cpu, then something may be wrong there.
You can also use the LastBatch column of the sp_who2 output. If the command is active, but the LastBatch was a while ago that's a long running query, and should be looked into.
These are all things that your DBA should know. If they don't an upgrade of your DBA may be in order.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
By looking in the CPU and Disk IO columns of sp_who2 this will give you a basic idea of what processes have been using a lot of disk or CPU. This can be deciving however. These numbers are for the life of the connection, not for the specific command. So if you stay connected for 3 days running lots of small commands you could end up with a large CPU and disk number at the end of the three days. To keep your sanity also look at the login_time column of the master.dbo.sysprocesses table. This will tell you when the user logged into the SQL Server. If the user logged in recently and has high disk or cpu, then something may be wrong there.
You can also use the LastBatch column of the sp_who2 output. If the command is active, but the LastBatch was a while ago that's a long running query, and should be looked into.
These are all things that your DBA should know. If they don't an upgrade of your DBA may be in order.
Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)
--Anything is possible. All it takes is a little research. (Me)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
