The DBA Routine

  • Comments posted to this topic are about the item The DBA Routine

  • 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.

  • 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!

  • Andy,

    What a great post! It pretty much outlines how I spend my day when working as a DBA....

    Mark

  • My first time posting but I really loved the article.

  • 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?

  • 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".

  • 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

  • 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 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.

  • 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?

  • 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

  • #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

  • 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.

  • 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