Good hardware-slow performance. Possibly misconfigured.

Question

Good hardware-slow performance. Possibly misconfigured.

RVO

SSCertifiable

Points: 5495
More actions
October 6, 2016 at 11:49 am

#400844

Company migrated to new infrastructure
a few months ago. Since then performance is slow compared to previous
system.
Old system was 32bit SQL Server 2005.
Physical server,
SAN storage.
Now it's
64-bit SQL Server 2014,
Virtual machine,
16 Cores,
128 GB RAM
SAN Storage (Nimble system) is used
Main focus is on SCEKE database performance.
What changed compared to old environment - they added High Availability setup.
And unfortunatly Secondary server is heavily used...
What looks strange is:
SCEKE database has 24-28 Filegroups.
Mostly one Filegroup has one Data File.
Log file is on L:\ drive
But one huge 563 GB data file 'Scene_Data2' also lives in L:\ drive.
Probably interfering with Log file.. or it doesn't matter with SAN storage..?
On PRIMARY filegroup they have
2 TB 'ProvidianClassic1_Data' file and that
563 GB 'Scene_Data2' file
Other data files are 20-30 GB all.
Three of them are empty...
They have one huge 2TB table "Poink" table.
It' son PRIMARY filegroup.
I think it's stored in that 2TB 'ProvidianClassic1_Data' data file.
Another strange thing
when I go to Disk Usage Report in SSMS
File Growth events sometimes occur 6 times a per minute.
For example on 10/3/2016
Log File Auto GrowthProvidianClassic1_Log
happened 6 times within 5 minutes window...
Disk Usage and File Growth stats are attached in Excel below.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Mostly one Filegroup has one Data File.
Log file is on L:\ drive
But one huge 563 GB data file 'Scene_Data2' also lives in L:\ drive.
Probably interfering with Log file.. or it doesn't matter with SAN storage..?

It matters with SAN storage and this will likely slow things down. You'll have the data and log files competing for I/O. I would take a closer look at your IO usage and keep an eye out for PAGEIOLATCH_SH events.

when I go to Disk Usage Report in SSMS
File Growth events sometimes occur 6 times a per minute.
For example on 10/3/2016
Log File Auto GrowthProvidianClassic1_Log
happened 6 times within 5 minutes window..

.

This is a huge problem; data and log file auto growth is a killer. I have an application with queries that finish in milliseconds. Once auto growth begins, the same queries can take one or more minutes. If you have the room, I'd make these files that are auto-growing so much bigger and increase the size of the autogrow.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

RVO SSCertifiable Points: 5495 More actions · Answer 2

Thanks Allan.B

Ignore File Growth part.

Apparently they were doing something crazy on that day (October 3).

Preparing for partitioning Poink table.

Since October 3 there is only 1 Log File Grow per day...

RVO SSCertifiable Points: 5495 More actions · Answer 3

24 Partitions mystery is clear now.

I talked to DBA.

They are planning to partition {Pink} table

and created these partitions as placeholders.

They partitioned {Poink_parttioned} tables will be moving data to {Poink} soon.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

RVO (10/6/2016)
24 Partitions mystery is clear now.
I talked to DBA.
They are planning to partition {Pink} table
and created these partitions as placeholders.
They partitioned {Poink_parttioned} tables will be moving data to {Poink} soon.

You have a DBA... have you talked with him/her about the apparent difference in performance? Provide them with some examples so they don't scoff at you for providing only anecdotal evidence.

--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)

RVO SSCertifiable Points: 5495 More actions · Answer 5

He's a junior DBA..company is looking for Senior DBA now..

In the meantime they asked me if I can find any bottlenecks in

database performance and the causes.

I talked to DBA.

He doesn't know what's causing the slow performance.

And he's way overwhelmed with

tens of servers maintenance...

He barely has time to keep the servers up and running..

By the way they have SOLARWINDS monitoring tool

but even that doesn't give them clear answers.

andrew gothard SSChampion Points: 12314 More actions · Answer 6

16 cores on a VM looks like a lot to me. If you over provision cores on VM's running SQL Server this can lead to shocking performance problems. Have a look at what your actual use of the cores is.

Vendors often just spec VM's as the same as their physical spec for servers. The physical spec is usually just what do you get in a 2 grand blade. You need to look at what your actual use is, and your CPU ready time.

The CPU ready time is the time your VM is waiting to have it's cores allocated to it. Bear in mind that even if your VM only wants to do 1/2 a core's work, the visor will only allocate cores when it can allocate all 16, which on a multi VM host is going to be harder to grant than 8 or 4 (on VMWare, anyway, I believe this is also the casae in HyperV), so if you're maxing out at under 50% or under 25% or your CPU resource cutting the number of cores (this requires a restart) *should* provide you with a major performance boost. Counterintuitive as this may seem,

https://www.sqlskills.com/blogs/jonathan/cpu-ready-time-in-vmware-and-how-to-interpret-its-real-meaning/

gives a good explanation of CPU ready time and te issues

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.