Expectations of SQL DBA's

Last Post 29 Jun 2012 08:03 AM by Timothy Hiller. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dstoltz
New Member
New Member

--
29 Mar 2012 07:09 AM
I work at an organization that does not have a "real" SQL DBA....they have me. I've been working with MS SQL since version 6.5, but I don't consider myself a DBA. I can design, maintain, backup, restore databases, but I'm not at a level where I would need to be to be considered a "real" dba. That said, I support many databases I've created for web applications, but I also support many SQL databases for vendor (purchased) applications. If we buy a vendor product that runs on SQL, it goes on my server. Typically, aside from setup, I don't touch these databases, maintenance or otherwise. I only back them up. If there is a problem, the vendor is involved, and I assist where needed. Now my question: In the real world, do real DBA's automatically "handle" all aspects of all databases, even vendor databases? Like setting up index rebuilds, etc...????? I have a vendor telling me I should set up indexes on their database. They don't give me specifics on which indexes, if they should be rebuilt or reorganized, etc... I feel I'm being put in a position to support a database that I'm not familiar with to support. I don't know their schema, and the potential issues I can introduce by just "slapping on some indexes and rebuilds"... What advice can anyone give me? Thanks in advance! Any info on how real DBA's function day to day would also be helpful - I'm considering going down that road to "specialize" in SQL DBA.
russellb
New Member
New Member

--
29 Mar 2012 12:36 PM
Let me start with the MOST IMPORTANT function of a DBA is to make sure s/he has good backups.

A production DBA will typically, among other things:
- monitor performance, and make performance tweaks/enhancements as required.
- troubleshoot performance problems
- monitor SQL Agent jobs. Handle and troubleshoot issues
- Yes, create and rebuild indexes. Even on vendor supplied databases. Index tuning is an ongoing process. Indexes that are just fine today, may be inadequate, or worse, cause problems tomorrow.
- Monitor resource utilization (I/O, Disk Space, RAM, CPU) and make recommendations for when more hardware is required or can be optimized.
- Optimize stored procedures. I'll pick a stored procedure from time to time to work on, based on relative cost in terms of performance.
- handle many user requests. Meaning creating logins, modifying permissions, running scripts etc.
russellb
New Member
New Member

--
29 Mar 2012 12:41 PM
Knowing what to index isn't difficult -- even when you don't know the schema. Identify the most often executed and biggest resource consumers and examine their execution plans. SSMS will suggest missing indexes.

Be sure to look at sys.dm_db_missing_index_details and the other index related DMVs.

Now in terms of query cost, I will multiply the number of executions in a given period by the number of reads the query performs. Also by the CPU ticks consumed.

A query that performs 100,000 reads twice a day is not as expensive as a query that performs 1,000 ten times a second.
russellb
New Member
New Member

--
29 Mar 2012 12:43 PM
Also, don't forget about SQL Server built-in reports. Top Queries by I/O, Top Queries by CPU etc.
dstoltz
New Member
New Member

--
30 Mar 2012 03:34 AM
Thanks Russell
russellb
New Member
New Member

--
30 Mar 2012 04:43 PM
You're welcome.
PaulMcKibben
New Member
New Member

--
04 Apr 2012 08:07 AM
The only thing that I would add to Russell's replies is that if you index a vendor's database, it is a good practice to both share the index with the vendor and keep a copy of it in a source control tool.

This avoids the potential issue when the vendor sends you an update to the database of lossing the work that you did.
gunneyk
New Member
New Member

--
04 Apr 2012 08:38 AM
I would also use the sys.dm_exec_query_stats DMV as a source to look for statements that can use an index. Hre is an example query but look at the DMV in BOL to see all that is available in terms of metrics reported. You can sort it however you wish to see high numbers of executions or high reads etc. I usually start with high reads to see what may benefit most but as Russell points out you have to weigh how often it is called as well.
SELECT t.[text] AS [Adhoc Batch or Object Call],
SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
- qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
, qs.[execution_count] AS [Counts]
, qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] / qs.[execution_count]) AS [Avg Worker Time]
, qs.[total_physical_reads] AS [Total Physical Reads], (qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]
, qs.[total_logical_writes] AS [Total Logical Writes], (qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]
, qs.[total_logical_reads] AS [Total Logical Reads], (qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]
, qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] / qs.[execution_count]) AS [Avg CLR Time]
, qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time] / qs.[execution_count]) AS [Avg Elapsed Time]
, qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
-- ORDER BY [Total Worker Time] DESC
-- ORDER BY [Total Physical Reads] DESC
-- ORDER BY [Total Logical Writes] DESC
ORDER BY [Total Logical Reads] DESC
-- ORDER BY [Total CLR Time] DESC
-- ORDER BY [Total Elapsed Time] DESC
-- ORDER BY [Counts] DESC
dstoltz
New Member
New Member

--
05 Apr 2012 05:17 AM
Thanks all for the info...

I just hesitate messing with vendor's databases - some vendors specifically state that their systems should not be touched or you could run into issues with them supporting it (if you messed it up for instance).
Timothy Hiller
New Member
New Member

--
29 Jun 2012 08:03 AM
dstoltz - you're in a great position to advance your career. I would highly reccommend purchasing the WROX 'Performance Tuning' and 'Internals and Troubleshooting' books. The Wizard of OZAR is a major contributor, as well as other highly skilled SQL professionals. I read parts of them almost each week, as there is a lot to consume. Good Luck.
You are not authorized to post a reply.

Acceptable Use Policy