Very generic question

  • Hi all, I have a very generic question which may sound stupid but very important to me. I'm going to join new team as a database developer and all I heard is that there was no dedicated database developer previously and there are some performance issues which have to be fixed. I'm trying to prepare a checklist of what all information I need to gather in the new system so as to start working on it. As per the new members, it is an OLTP system.

  • Sqlenthu,

    A simple answer for you is to check the SQL instance out right away.

    Brent Ozar has a great, free tool for this: spBlitz which can be downloaded here: [/url]

    You just have to run the spBlitz code to create the stored procedure and the run it in SQL. It will tell you most everything that is wrong in the environment.

    Then you can start getting more in depth with fine tuning the performance.

    Hope this helps!

    Jon

  • +1 for sp_Blitz.

    This will give you things to look at. I've written some thoughts on the tool from our databases, though I'm not sure what the change are since I wrote these items:

    http://www.sqlservercentral.com/articles/SQLServerCentral/95693/

  • Piling on, sp_Blitz is good but one must thoroughly validate all settings and configurations on the servers in such a situation. Far too often have I seen simple mistakes like file locations, memory configuration, tempdb configuration etc. muffle even the beefiest of servers. Another must is to do a static code analysis, tells you a lot about how things have been done and what needs correcting.

    😎

  • Thank you guys. i'll certainly take help of his proc. Also I've till now worked on OLAP environment and this will be first time i'll be working on OLTP. Is there any documentation on t-sql best practices for OLTP environment. I burned my midnight oil to search a lot but couldn't get any.

  • Some things to check, roughly in order:

    1) Verify that a valid and reasonable "max memory" value is set in SQL Server.

    2) Verify that IFI is turned on in Windows to allow SQL to use it.

    3) Check the disk latency. The query below will give you an overview by drive. In general, you want values around 15ms or less up to say 25ms. Anything over 30ms could noticeably slow SQL.

    SELECT

    LEFT(physical_name, 1) AS drive,

    CAST(SUM(io_stall_read_ms) / (1.0 + SUM(num_of_reads)) AS NUMERIC(10,1)) AS avg_read_disk_latency_ms,

    CAST(SUM(io_stall_write_ms) / (1.0 + SUM(num_of_writes) ) AS NUMERIC(10,1)) AS avg_write_disk_latency_ms,

    CAST((SUM(io_stall)) / (1.0 + SUM(num_of_reads + num_of_writes)) AS NUMERIC(10,1)) AS 'avg_disk_latency_ms'

    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs

    INNER JOIN sys.master_files AS mf ON

    mf.database_id = divfs.database_id AND

    mf.file_id = divfs.file_id

    GROUP BY LEFT(physical_name, 1)

    ORDER BY avg_disk_latency_ms DESC;

    4) Check for any autogrow file growth setting/amount that is: (1) too small or (2) too large or (3) a %, such as 10%, rather than a fixed value, such as 20MB.

    5) Use SQL's built-in, standard reports to list the "TOP 10" most IO queries, avg and total.

    6) Check for log files with too many VLFs, correct them if found.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • SeniorITGuy (7/26/2016)


    Sqlenthu,

    A simple answer for you is to check the SQL instance out right away.

    Brent Ozar has a great, free tool for this: spBlitz which can be downloaded here: [/url]

    You just have to run the spBlitz code to create the stored procedure and the run it in SQL. It will tell you most everything that is wrong in the environment.

    Then you can start getting more in depth with fine tuning the performance.

    Hope this helps!

    Jon

    Making the linkusable:

    https://www.brentozar.com/blitz/

  • sqlenthu 89358 (7/26/2016)


    Hi all, I have a very generic question which may sound stupid but very important to me. I'm going to join new team as a database developer and all I heard is that there was no dedicated database developer previously and there are some performance issues which have to be fixed. I'm trying to prepare a checklist of what all information I need to gather in the new system so as to start working on it. As per the new members, it is an OLTP system.

    Is there a DBA?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes. There is a DBA team.

    Jeff, I would be thankful if you can tell me any links proving performance tuning and optimization in OLTP environment.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply