SQL Server Admin First day.

Well this was my first day as a DBA some six years back. I can still feel how my first day started. This blog not only talks about my feelings but a simple script also which I could utilize in getting appropriate data which every DBA needs to have on her/his fingertips.

INTERVIEW PREPARATION IN 30 MINs – Read Following wires for a quick interview preparation.

  1. SQL Server DBA “Interview Questions And Answers”
  2. SQL Azure Interview Questions and Answers Part – 1
  3. SQL Azure Interview Questions and Answers Part – 2
  4. Powershell Interview Questions and Answers

The day starts with a small meeting. I was introduced to big chaps rather I introduced myself :). I wanted to relax with a cup of Starbucks so found myself in a corner sipping coffee all alone just after meeting was over. I was sure that first day (or fist week ;)) in any project should be relaxing. I was more interested in social networking on that day rather sitting down and working on project tasks. But this was not going to happen.

My TL, a tall guy, told me from behind his system without looking at me that my perms are working. Only one question struck my relaxing grey matter, “WHY??”. All of a sudden ‘social networking plans’ collapsed with a thud and I was against my system working as a submissive fresher. I was in the mid of planning for an early lunch when my TL asked me to find out the blocking in the server. “Oh chap!! That is a small thing”, I murmured. The data was ready with sp_who2 ‘Active’. But tall guy wanted more out of that. He wanted a list of session ids Vs %CPUTime and Vs %DiskIO. Wasn’t he cruel on the first day itself? Yes, this wasn not about finding why CPU was not performing perfectly L1, L2 and L3 latch on intel CPU?? or why query is slow query-running-slow-significance-of-non-clustered-index, but for a fresher, this could be a wild goose chase.

It took me whole one and half hours completing my first script in my first project while ‘early lunch plan’ evaporating on the back of my mind.

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N’spwho2′) AND type = (N’U’))
DROP TABLE spwho2
GO
CREATE TABLE spwho2 (SPID VARCHAR(50)
,Status VARCHAR(50)
,Login VARCHAR(50)
,HostName VARCHAR(50)
,BlkBy VARCHAR(50)
,DBName VARCHAR(100)
,Command VARCHAR(50)
,CPUTime VARCHAR(50)
,DiskIO VARCHAR(50)
,LastBatch VARCHAR(50)
,ProgramName VARCHAR(500)
,SPIDS VARCHAR(50)
,REQUESTID BIGINT)

INSERT INTO spwho2
exec sp_who2

DECLARE @currentsession BIGINT
SELECT @currentsession = @@SPID

DECLARE @TotalCPUTime BIGINT
SELECT @TotalCPUTime = SUM(CAST(CPUTime AS BIGINT)) FROM spwho2

SELECT CAST(CPUTime AS DECIMAL(10,4))/@TotalCPUTime * 100 AS [CPU%], SPID, Command, DiskIO
FROM spwho2
WHERE SPID @currentsession
AND CAST(CPUTime AS DECIMAL(10,4))/@TotalCPUTime*100 > 1
ORDER BY CAST(CPUTime AS DECIMAL(10,4))/@TotalCPUTime DESC

DECLARE @TotalDiskIO BIGINT
SELECT @TotalDiskIO = SUM(CAST(DiskIO AS BIGINT)) FROM spwho2

SELECT CAST(DiskIO AS DECIMAL(10,4))/@TotalDiskIO * 100 AS [DiskIO%], SPID, Command, CPUTime
FROM spwho2
WHERE SPID @currentsession
AND CAST(DiskIO AS DECIMAL(10,4))/@TotalDiskIO*100 > 1
ORDER BY CAST(DiskIO AS DECIMAL(10,4))/@TotalDiskIO DESC

I was late for the lunch grabbing almost finished lunch which I devoured with a smile on my face. I would like the reader to use this script to save her/his precious one and half hours and find herself/himself on lunch table before this finishes :).

Regards,
Online SQL/Powershell Classes @ http://tuitionaffordable.webstarts.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s