September 30, 2009 at 2:34 pm
Hi,
1-what could be the right answere if some one ask you in interview that users are complaining about slow performance?
I can say you check if there is any blocking,indexes need to create,memory bottlenecks.If i say i run the profiler to see which sql is taking long time then what counters i should select?
but is there any thing else and step by step to check please?
2-
If you have E drive with extra space and d drive which is having .mdf file is going out of space after few hours and you cant shrink .mdf file and cant bring the database down then what is the best way?
I said you can add more space to d drive with windows guys help or you can archive the tables to some where else server and delete the data from those tables.
3-
If developer want to run the sql server profiler what level of rights you need to give him,I said we DBAs run profiler and if necessary then sysadmin but this much more rights i know?
Please reply with your thoughs.
September 30, 2009 at 3:34 pm
1. I always start with a Profiler trace to see if I can see any pain points. Normally I just select the Default Trace Profile and add dbid and HostName, then sort by Duration.
If I am not seeing anything there (or sometimes even if I am), I start a logman (perfmon) session with the following counters set. These counters are what MS has recommended that I provide them when opening a Gold support case (overkill, perhaps, but thorough):
;Memory Utilization
\Memory\Available MBytes
\Memory\Free System Page Table Entries
\Memory\Pages Input/Sec
\Memory\Pages/Sec
;Paging File
\Paging File\% Usage
\Paging File\% Usage Peak
;CPU Utilization
\Processor(*)\% Privileged Time
\Processor(_Total)\% Processor Time
\Process(sqlservr)\% Privileged Time
\Process(sqlservr)\% Processor Time
;System Utilization
\System\Context Switches/sec
\System\Processor Queue Length
;Disk Utilization
\PhysicalDisk(_Total)\Avg. Disk sec/Read
\PhysicalDisk(_Total)\Avg. Disk sec/Write
;SQL Utilization
\SQLServer:Access Methods\Forwarded Records/sec
\SQLServer:Access Methods\FreeSpace Scans/sec
\SQLServer:Access Methods\Full Scans/sec
\SQLServer:Access Methods\Index Searches/sec
\SQLServer:Access Methods\Mixed page allocations/sec
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:Access Methods\Range scans/sec
\SQLServer:Access Methods\Scan Point Revalidations/sec
\SQLServer:Access Methods\Workfiles Created/sec
\SQLServer:Access Methods\Worktables Created/sec
\SQLServer:Access Methods\Worktables From Cache Ratio
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:Buffer Manager\Free pages
\SQLServer:Buffer Manager\Lazy writes/sec
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:Buffer Manager\Page lookups/sec
\SQLServer:Buffer Manager\Page reads/sec
\SQLServer:Buffer Manager\Page writes/sec
\SQLServer:General Statistics\Processes blocked
\SQLServer:General Statistics\Temp Tables Creation Rate
\SQLServer:General Statistics\Temp Tables For Destruction
\SQLServer:General Statistics\Logins/sec
\SQLServer:General Statistics\Logouts/sec
\SQLServer:Latches\Latch Waits/sec
\SQLServer:Latches\Total Latch Wait Time (ms)
\SQLServer:Locks\Lock Requests/sec
\SQLServer:Locks\Lock Waits/sec
\SQLServer:Locks\Lock Wait Time (ms)
\SQLServer:Locks\Lock Timeouts (timeout > 0)/sec
\SQLServer:Locks\Number of Deadlocks/sec
\SQLServer:Memory Manager\Memory Grants Pending
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:Wait Statistics\Lock waits
\SQLServer:Wait Statistics\Log write waits
\SQLServer:Wait Statistics\Network IO waits
\SQLServer:Wait Statistics\Non-Page latch waits
\SQLServer:Wait Statistics\Page IO latch waits
\SQLServer:Wait Statistics\Page latch waits
\SQLServer:Wait Statistics\Wait for the worker
\SQLServer:Databases(*)\Transactions/sec
2. The answer I would give depends - is it an interview question, or is it happening in production? If it were in production, to get us "over the hump", I would perhaps add Filegroups to the database on another disk until more primary disk can be obtained. In either scenario I would recommend getting more disk space (obviously) and then project growth going forward to avoid this issue and get enough room going forward.
Lots of other factors come into play here, though. Is there a large, stagnant database with data files on the disk, too? If so, let's move the files to the E:\ drive until more space on D:\ can be freed up. What exactly is taking up the space? Are there backups being stored there too?
3. I never give devs sysadmin rights, even on centralized dev servers. Why? I don't necessarily mind them having sa access on dev, conceptually, but if it's granted then it's possible to put something crazy like an xp_cmdshell call in a new application. Then what when it moves to production?
I came up as a dev so I have made most of those mistakes myself and have learned from bitter experience. 😉
What I have done if they want or need Profiler info, is I set up the trace they want, and pump the results into a table in their database they can in turn query.
Hope this helps, and I look forward to seeing others' input.
MJM
October 1, 2009 at 7:47 am
All these Int. questions are very tricky and having multiple answers. It's all about matching the wavelength with the interviewrs. There are many int. I have faced in which I have performed extreemly well and not got selected. And also a few in which I feel I have not done well but got selected. 😛
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 1, 2009 at 7:57 am
Mark,Thank you very much for answeres.May be i dont have clear concept about filegroup so please can you tell me is it possible to add filegroup while database is live?and after creating filegroup how you will specify that the existing tables from primary filegroup whould move to 2nd filegroup which we will place in E drive to solve space issue?
October 1, 2009 at 8:34 am
You can ask them few more questions like:
Is the application runnning slow for all the users or a only set of user(s)?
Is the complete application slow or only a part of the application?
Is it throughout the day or only for some period?
Its an open question, you have different variables ...it can be network glitch, it can be some scheduled operations running in background...some ppl have antivirus s/w on the m/c ...and then check the perfmon, profiler,sql tuning etc..!You need to identify the bottlenecks and solve the problem.PT is not something which can be done just like that....it takes time!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply