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.
- SQL Server DBA “Interview Questions And Answers”
- SQL Azure Interview Questions and Answers Part – 1
- SQL Azure Interview Questions and Answers Part – 2
- 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
CREATE TABLE spwho2 (SPID VARCHAR(50)
INSERT INTO spwho2
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
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
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 :).
Online SQL/Powershell Classes @ http://tuitionaffordable.webstarts.com