asked on

update records taking several hours in the table.

Hi,

We have a production Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) and RTM version. Out of 4 GB ram sql server assigned 3 GB RAM. User complained about they are having some performance issues while they update their records and it takes several hours.

What i found that they kept their all data, log, tempdb and backup in a same drive. Each databases memory usage are very less. I asked them we need to separate drives to keep data and log file separately.

But SAN Admin replied that, Since we only have one slow SAN I'm not sure moving this data to different drive letters is going to matter if they are still on the same spindles on the same SAN. It may be time to buy a physical for this if better performance is needed.

Please advise me what else i can check and give them suggestions.

Thanks

Zahid

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Deepak Chauhan

However for best performance , DATA, LOG, and TempDB should not be on same drive. Initially you can start by looking into Blocking , disk utilization and IO. Also you can capture perfmon data to analyze disk counters using PERFMon tool. There is a dynamic SQL DMF " Sys.dm_IO_Virtual_File_Stats" to look into individual sql MDF\LDF file IO latency.

SOLUTION

Andrew Hancock (VMware vExpert PRO / EE Fellow/British Beekeeper)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zahid Ahamed

ASKER

Hi Deepak,

Here is my result . Please tell me how do i know which one is bad here:--

ReadLatency      WriteLatency      Latency      AvgBPerRead      AvgBPerWrite      AvgBPerTransfer      Drive      DB      physical_name
4      62      33      63043      62464      62749      D:      tempdb      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\tempdb.mdf
5      17      17      11377      54305      54148      D:      tempdb      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\templog.ldf
12      15      14      14843      11620      12768      D:      Perfection      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\Perfection_Data.mdf
11      11      11      40507      11367      38765      D:      FailureFinder      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\FailureFinder_Data.mdf
16      7      16      14198      9684      14079      D:      msdb      D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf
15      7      15      14820      8986      14581      D:      master      D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf
32      6      6      59160      2328      2354      D:      msdb      D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf
16      4      15      201843      8412      191545      D:      dbmasops      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\dbmasops_Data.mdf
14      4      4      9803      1024      1039      D:      Astria_Host      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\Astria_Host_log.ldf
15      4      14      63503      36355      60558      D:      AAAA0001      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\AAAA0001.mdf
21      3      16      14807      9067      13169      D:      Astria_Host      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\Astria_Host.mdf
30      2      2      10606      6652      6662      D:      AAAA0001      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\AAAA0001_log.ldf
11      2      11      17429      8192      16927      D:      EclipseSignalRBackplane      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\EclipseSignalRBackplane.mdf
8      2      7      31340      10510      30132      D:      otg      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\otg_Data.mdf
17      2      17      259281      11091      258470      D:      TDI      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\TDI_Data.mdf
20      2      5      154992      32950      54025      D:      FailureFinder      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\FailureFinder_Log.ldf
15      2      14      162346      14814      154205      D:      Mandata      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\ManData_Data.mdf
15      1      1      15826      3003      3178      D:      Mandata      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\ManData_Log.ldf
10      1      8      17516      8192      15519      D:      MES-CM      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\MES-CM_Data.mdf
12      1      4      2357      3289      3059      D:      MES-CM      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\MES-CM_Log.ldf
21      1      17      19721      8192      17531      D:      Fincerts      D:\MSSQL\MSSQL12.MSSQLSERVER\DBdata\fincerts_Data.mdf
12      1      3      2735      3275      3142      D:      Fincerts      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\fincerts_Log.ldf
18      1      2      15203      1304      2149      D:      dbmasops      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\dbmasops_Log.ldf
17      1      14      17595      8192      15558      D:      model      D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\model.mdf
13      1      4      2234      3269      3017      D:      model      D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\modellog.ldf
18      1      4      5304      2719      3195      D:      otg      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\otg_Log.ldf
22      1      1      16529      587      618      D:      master      D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
21      1      4      13889      3264      5057      D:      EclipseSignalRBackplane      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\EclipseSignalRBackplane_log.ldf
21      0      1      82383      1584      3025      D:      TDI      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\TDI_Log.ldf
36      0      1      1014246      3052      6087      D:      Perfection      D:\MSSQL\MSSQL12.MSSQLSERVER\DBlogs\Perfection_Log.ldf

Deepak Chauhan

In the first row we can see latency =33 (TempDb file) which is not a good number. Good value is < =10 ms. and > 10 and < 15 is sometime acceptable but > 15 will be consider as poor and need to work on it. Try to capture data at time interval here is very good script to capture data at 30 minutes interval. There are multiple reason of IO latency like improper indexes ( lead to read latency) , Outdated statistics, slow IO subsystem, memory pressure.
SCRIPT :: https://www.sqlskills.com/blogs/paul/capturing-io-latencies-period-time/

Zahid Ahamed

ASKER

Hi,

Here i have came across couple more things.

1. Definitely i will split data and log files separately
2. Apply Latest SP since it is SQL Server 2014 RTM
3. Few of dbs are still 2008 compatibility label i will switch them 2014 compatibility label
4. Increase memory
5. Ola Hallen's for Index maintenance Plan, since i have observed the current index maintenance skipped few dbs pages are greater than 1000.
6. Lastly what i see there are two dbs settings data file auto growth settings 1 MB to unlimited. and log file settings are 10 percent autogrowth settings.

Please confirm and advise about what will be best practice db auto growth settings

Thanks

Zahid

ASKER CERTIFIED SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zahid Ahamed

ASKER

Deepak,

The databases sizes are very small 288.44 MB and another one is 11.00 MB. Backup sizes are accordingly 24.282 MB and 0.895. I am going to reset this auto growth size.

Thanks

Zahid Ahamed

ASKER

Thanks everyone for your valuable comments!