Today, I am going to start writing another topic thread for Database Administration (DBA). I do alot of DBA work among many other things at my current job. I am going to cheat a little by copying a list from a renown SQL Server MVP named Paul Randal. This list was published in a TechNet Magazine article in November, 2010.
To be a succesful database administrator, you should start working your way through the following tasks (secrets) to manage choas at your organization.
|
1 2 3 4 5 6 7 8 9 10 |
10. Take Inventory 9. Standardize Configurations 8. Understand the I/O Subsystem 7. Create a Customized Maintenance Plan 6. Ensure the Security of Your System 5. Get on Good Terms with Your Developers 4. Develop a Comprehensive Disaster Recovery Strategy 3. Take and Test Regular Backups 2. Monitor and Maintain Performance 1. Know Where to Find Information |
In the coming weeks, I am going to write articles on how I choose to execute some of these tasks.
For today, I want to start the discusion with a quick performance tool. There are many times in which I want to figure out what is causing issues with a database. I go into SQL Server Management Studio to pull up the activity monitor but nothing comes back since the CPU usage is extremely high.
What do you do when that happens?
There is a nice system stored procedures called sp_who and sp_who2 that will return information on blocking and CPU usage; However, on a large database that has hundreds of processes running (SPID’s), it is very difficult to track down the exact entries you are looking for.
I wrote a user defined stored procedure called usp_who2 which I installed in the msdb database. Basically, this procedure creates a table in the tempdb given the users login. Since I am logged in under the dba account, that is the name it will use for the table.
The output of sp_who2 is piped to this table. A simple SELECT statement with a WHERE clause can narrow down the process that your are looking for in seconds. Please note, spids under 50 are usually system processes.
The following stored procedure is for your usage. Like many shops, I have a couple SQL Server 2000 systems. Microsoft’s transition from 2000 to 2005 made changes to this system stored procedure. Below are the two variants.
SQL SERVER 2000
SQL SERVER 2005+
A sample call to the procedure with verbose on tells you the name of the temporary table.
|
1 2 3 4 5 6 7 8 |
-- Call proc with verbose on msdb.dbo.usp_who2 1 -- Check the following table for output [usp_who2] - please select from table [tempdb].[dbo].[dba_login] for your output. -- Track down the issue by user computer select * from [tempdb].[dbo].[dba_login] a where a.host_name LIKE '%the_user_computer%' |
The table created by this procedure stays in the system until tempdb is recreated upon server startup. I found this procedure very handy when trashing on the database system does not allow the activity monitor to load data.
Super article, I will be checking back again frequent to watch out for up-grades.
Whenever I discovered this particular article, I had my questions answered and the information was great. You’ve got a good way of conveying your thoughts.
Very interesting topic, appreciate it for posting.
There is evidently a bundle to identify about this. I think you made some nice points in features also.
Wow! Thank you! I constantly wanted to write on my blog something like that. Can I take a fragment of your post to my website?
Enjoyed looking through this, very good stuff, regards .
Keep up the wonderful work , I read few blog posts on this site and I think that your site is real interesting and has got circles of wonderful information.