November 28, 2007 at 11:40 pm
Comments posted to this topic are about the item The DBA Routine
November 29, 2007 at 1:24 am
Useful seeing it listed out like that.
#10 - Whack a Mole. I Like to do something similar but add to the equation total time of queries. Saving 2 seconds off a 3 second query that runs a thousand times a day is better than 10 seconds off a 15 second query that runs only two or three times a day.
November 29, 2007 at 6:07 am
I agree with that, but wanted to keep it simple. In my view its more important to adopt the philosophy of spending some time on performance each week than to use a specific implementation. I suspect that using your system or mine in a year we'd have systems that ran substantially smoother!
November 29, 2007 at 6:24 am
Andy,
What a great post! It pretty much outlines how I spend my day when working as a DBA....
Mark
November 29, 2007 at 6:27 am
My first time posting but I really loved the article.
November 29, 2007 at 6:29 am
Good article and it definitely provides a way to find out if you are doing what needs to be done.
As far as #10, how do you deal with a situation where the application is a 3rd party app and you can't make performance improvements?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 29, 2007 at 7:07 am
As far as #10, how do you deal with a situation where the application is a 3rd party app and you can't make performance improvements?
You have evidence to give to the developers. Any decent developer would be quite happy to have that level of information returned and file it under "Constructive criticism".
November 29, 2007 at 7:07 am
I agree that this is a great article. However, I'd like a little help. About a year ago, my school district told me congratulations, your the new SQL guy. So I delved deeply into Sql Development and am ok, not great at that. As I've read postings on this site, I've realized there's a whole world on the DBA side I've been neglecting. This brings me to my help request.
I need a little more on the "how" of this article. For instance, #10, I looked into the profiler and found trace templates, but I'm not sure which ones contain the data points discussed. Similarly, # 6. Is there a good beginning DB tuning/monitoring site or book that somebody could recommend that would help me get started? Regrettably, at this stage I need some "Click File>Open>Trace...." level help.
Thanks
Rick
November 29, 2007 at 7:09 am
Good post, especially for me - I'm the backup of the DBA when he is not around, really a developer that happens to know enough to be a shoe in to the DBA once in a full moon.
It would be helpful to me to see some sources of "all the published 'best practices' we hear so much about". Would you care to recommend books, articles or even training that will focus on those?
Thanks!
November 29, 2007 at 7:30 am
I need a little more on the "how" of this article. For instance, #10, I looked into the profiler and found trace templates, but I'm not sure which ones contain the data points discussed.
Try http://www.sql-server-performance.com/articles/per/performance_audit_part8_p1.aspx as a start or there is probably a few articles here.
November 29, 2007 at 7:39 am
As far as #10, how do you deal with a situation where the application is a 3rd party app and you can't make performance improvements?
SQL Server is a machine of a thousand (or more!) levers. You can still pull many of them even when hosting a 3rd party application.
If you have full access to the underlying database, you still have many tuning options available. Even if you can't rewrite queries or alter stored procedures, you can at least create or alter indexes, maintain statistics, and address index fragmentation.
Further, you can monitor for memory, CPU and I/O bottlenecks to justify improvements to the physical architecture. Moving log files to separate spindles, changing RAID configurations, adding memory (or even CPUs) should still be in your control.
Archiving old data to a warehouse and making the application database smaller almost always results in faster performance, too!
Hope this helps!
Carter
But boss, why must the urgent always take precedence over the important?
November 29, 2007 at 7:42 am
Carlos E Souza Lopes (11/29/2007)
Good post, especially for me - I'm the backup of the DBA when he is not around, really a developer that happens to know enough to be a shoe in to the DBA once in a full moon.It would be helpful to me to see some sources of "all the published 'best practices' we hear so much about". Would you care to recommend books, articles or even training that will focus on those?
Thanks!
I would be interested as well in a few recommendations like this.
Marc
November 29, 2007 at 7:44 am
#10 hit home with me. Below is my gereral query after importing a standard template profile into a table and selecting out queries for a user1, user2, or user3. bblack
==================================================
SELECT rtrim(substring(TextData, 1, 50)) AS SQL_Code, --' ',
substring(LoginName, 1, 10) as ' Who Ran ',
-- Cast(Round(Duration/1000, 1) as char(10)) as 'Seconds_Used', StartTime
Cast(Round(Duration, 5) as char(10)) as 'Millisecs', StartTime
--FROM phs_2004_07_28
--FROM pro_2004_09_08
--FROM phs_2004_10_04
--FROM phs_2004_11_17
--FROM phs_2004_11_22
--FROM phs_2004_12_14
--FROM phs_2005_02_01
--FROM phs_2005_07_21
--from phs_2006_03_03
--FROM PHS_090407
from isbatch_1012
--WHERE (SQLUserName = 'rptuser') AND (TextData IS NOT NULL)
--WHERE rtrim(substring(TextData, 1, 14)) = 'dbo.is_phr063s'
WHERE (TextData IS NOT NULL)
AND Duration > 299
AND (substring(TextData, 1, 2) <> '--')
-- AND (substring(TextData, 1, 13)) = 'dbo.is_phr009'
AND Round(Duration/1000, 1) is NOT null
AND (substring(LoginName, 1, 10) = 'user1' OR
substring(LoginName, 1, 10) = 'user2' OR
substring(LoginName, 1, 10) = 'user3' )
--ORDER BY Duration DESC
--ORDER by StartTime
ORDER by SQL_Code
--select * from sysprocesses
--ORDER by Seconds_Used DESC
November 29, 2007 at 7:49 am
cburleigh (11/29/2007)
As far as #10, how do you deal with a situation where the application is a 3rd party app and you can't make performance improvements?
SQL Server is a machine of a thousand (or more!) levers. You can still pull many of them even when hosting a 3rd party application.
If you have full access to the underlying database, you still have many tuning options available. Even if you can't rewrite queries or alter stored procedures, you can at least create or alter indexes, maintain statistics, and address index fragmentation.
Further, you can monitor for memory, CPU and I/O bottlenecks to justify improvements to the physical architecture. Moving log files to separate spindles, changing RAID configurations, adding memory (or even CPUs) should still be in your control.
Archiving old data to a warehouse and making the application database smaller almost always results in faster performance, too!
Hope this helps!
Carter
Right, I guess I was coming from the perspective of performance tuning the queries themselves. Although, from experience, even changing/adding indexes can be problematic as when you get an update from the 3rd party your changes/improvements get overwritten. Of course the answer to this is DOCUMENTATION.
You have evidence to give to the developers. Any decent developer would be quite happy to have that level of information returned and file it under "Constructive criticism".
Unfortunately Simon, I guess I have not had the opportunity to work with decent developers. I have had instances where I have not only made suggestions, based on best practices, but sent improved code with test results and the developer would not acknowledge the problem.
But, of course, do what you can, where you can, when you can is all you can do.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 29, 2007 at 8:53 am
As a former student of Andy's at End To End Training, I have implemented several of these pieces into my daily routine. Although the word "Routine" evokes for some a negative connotation, as a DBA, this “Routine” will ultimately improve your skills, and your environment. You won’t feel like your flying by the seat of your pants. You’ll actually feel confident that you are aware of what is happening in your environment today, what happened in the past, and perhaps have a notion of what might become a problem in the future. Proactive DBA? Good stuff.
“... the training is as much a ‘health check’ as it is a learning experience.” So true.
For some reason I feel an affinity with #3 – Best Practices.
BPH
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply