Very generic question

Question

Post reply

Very generic question

sqlenthu 89358

Hall of Fame

Points: 3069
More actions
July 26, 2016 at 1:29 pm

#328665

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.

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

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

SeniorITGuy SSC Eights! Points: 864 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

+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/

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

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.

😎

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 4

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.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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/

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

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)

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 8

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.