July 26, 2016 at 1:29 pm
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.
July 26, 2016 at 4:58 pm
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
July 26, 2016 at 7:31 pm
+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/
July 26, 2016 at 10:46 pm
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.
😎
July 26, 2016 at 11:18 pm
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.
July 27, 2016 at 10:11 am
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".
July 27, 2016 at 12:43 pm
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:
July 31, 2016 at 6:23 pm
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
Change is inevitable... Change for the better is not.
August 3, 2016 at 12:40 am
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