Slow SQL Server performance

Eish......

that looks like a parallelism problem, try changing instance settings for the parallelism as per my link above. I think you need a DBA to come in and look at that properly.
 
poffle said:
Eish......

that looks like a parallelism problem, try changing instance settings for the parallelism as per my link above. I think you need a DBA to come in and look at that properly.
Click to expand...

I am starting to think the same thing. :)

Thank you all for the feedback. I will suggest getting someone whom knows this shyte. :D
 
There are also lots written about not making the DB server on the same server as the domain controller. Got to do with IO caching being overridden by the domain controller.
 
SharkBait said:
The best info I can give you, all 3 servers have 8x2TB 7200rpm drives installed and I am going to guess it's in RAID 5, as the free space is being reported as 7.28TB.
Click to expand...

Disk is usually the hardware prime suspect for poor SQL Server performance. It sounds as though you need better than 7.2k Drives and RAID 10 rather than RAID 5. RAID5 has good read but slow write speed and can be appropriate for read heavy SQL databases such as OLAP reporting applications. RAID 10 is what you want for random read/write performance such as transactional databases, including ERP systems and for VM's.

further intelligence to share with accidental DBA's for the greater good:
https://www.brentozar.com/
http://www.red-gate.com/hub/books/#sqldba
https://ola.hallengren.com/

Hope that helps
 
Also this awesome book should be required reading for every admin who has a SQL Server DB in their environment

http://www.red-gate.com/library/sql-server-backup-and-restore

Point in time recovery is freely available in all versions of SQL server through the anti-gravity magic of transaction log backups. Most people who are responsible for a SQL Server do not realise this - including myself until a more enlightened individual saved my ass with the transaction log backups he had set up to run every 20 minutes on my ERP server. Incidently, similar PIT recovery mechanisms are also natively available in mysql, postgresql, Oracle and Sybase RDBMS amongst others.

Apologies for digressing from the matter of SQL performance but I just felt that some reading this thread might benefit from this info.
 
In my experience it is almost never the hardware that is causing the slowdown, but inept/unexperienced programmers thinking they know SQL.

Get a DBA in, you can hire one adhoc from a company that outsources to come evaluate your setup and suggest maintenance procedures as well as optimization paths.
 
InvisibleJim said:
Disk is usually the hardware prime suspect for poor SQL Server performance. It sounds as though you need better than 7.2k Drives and RAID 10 rather than RAID 5. RAID5 has good read but slow write speed and can be appropriate for read heavy SQL databases such as OLAP reporting applications. RAID 10 is what you want for random read/write performance such as transactional databases, including ERP systems and for VM's.

further intelligence to share with accidental DBA's for the greater good:
https://www.brentozar.com/
http://www.red-gate.com/hub/books/#sqldba
https://ola.hallengren.com/

Hope that helps
Click to expand...

Disk is only an issue if you don't have enough RAM, they have enough RAM.
 
I agree with a lot of the earlier comments that this is most likely related to parallelism and this is most likely due to badly written queries. The default settings for parallelism are usually not very good. I would recommend the following:
- For shared SQL environments, MAXDOP on the instance level should be set to no more than 50% of the available cores (so 4 for your example).
- Cost Threshold for Parallelism is set by default to 5 which is actually a very small amount - this should be set to at least 50 although you could try 100 as well. This will stop a lot of smaller queries from paralleling. I'm not going to go into too much detail - here is a link:
https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/

On a more general note, here are some other things you can look out for performance wise:
- The best way to tell if you have enough memory assigned is to monitor the Page Life Expectancy counter - it tells you how long on average data is in the buffer before being refreshed from disk. With 32GB RAM (approximately 28 for SQL buffer cache + 4 for OS), you should have a minimum value of around 2100 seconds. Here are some details on the calculation:
https://blogs.msdn.microsoft.com/mcsukbi/2013/04/11/sql-server-page-life-expectancy/
- For disk performance, there are 2 critical points:
1) Ensure disks are formatted with 64kb allocation units - most of SQL activities occur in 64kb chunks. If you have 4kb allocation unit then a standard read will be 16 commands to the underlying disk while a 64k allocation unit will be 1 - monitor Disk Queue length counter in the OS.
2) As mentioned earlier, you should have separate physical disk pools (that should really be RAID 10) - tempdb, data and logs. This is because SQL can write to this at the exact same time - especially data and logs.
- Tempdb files - There has ben a standard recommendation from Microsoft that tempdb should be assigned multiple files - 1 per core up to 8 cores (I believe this setting is now automatic in SQL 2016 - tempdb files will be created during install). This recommendation is often misunderstood - the reason for it is because each file can only process a single create table / drop table at a time so if you have multiple operations using tempdb at the same time, they may have to queue to create / drop tables (effectively any action in tempdb does this).
 
Last edited:
PsYTraNc3 said:
Since drive performance is in question, I would recommend checking the page file location and move to a different disk if you can.
Also check if the page file size is big enough.
The article below give calculations you could use for the size.
https://blogs.technet.microsoft.com/motiba/2015/10/15/page-file-the-definitive-guide/
I just stick to the old RAM*1.5
Click to expand...

This is a big misconception - If your SQL server is using your page file for it's buffer cache, you are in serious trouble. If you have configured your server correctly, a SQL server should not really use the page file at all (just checked one of our servers - 16GB RAM, system managed pagefile is at 2.3GB of which only 26% is being used). SQL specifically uses RAM to cache data so it doesn't need to read from disk. If this is paged out, reading from the cache effectively reads from disk in a way that is often slower than if SQL were to used it's own optimised processes from reading from the database file.
If you are running a single instance environment with no other software on the server (not even SSIS), you can let SQL server manage its own memory, but as soon as you add any other software, you should be configuring SQL server max server memory on every instance to ensure you leave enough headroom for everything else.
Also on high performance servers, always ensure there is at least about 2GB memory available to be used by the Windows cache otherwise windows won't be able to cache IO activities, etc.
 
halfmoonforever said:
In my experience it is almost never the hardware that is causing the slowdown, but inept/unexperienced programmers thinking they know SQL.

Get a DBA in, you can hire one adhoc from a company that outsources to come evaluate your setup and suggest maintenance procedures as well as optimization paths.
Click to expand...

+500

And the most common cause of poorly performing queries are sub-optimal indexes, resulting in additional lookups.
Depending on the use cases, if reads need to be optimized over writes, then covering indexes for the most frequent queries will significantly speed up performance.

I'm not a qualified DBA, but I spent a shyte load of time googling to fix 6 DB machines, all of which were consistently 90+% CPU usage, all of which were dog slow, one of which was crashing weekly and needed to be rebuilt (I kid you not) with the other 5 going the same way.

A big contributor to the improvement was just getting proper indexes in place, and keeping stats up to date.

When I was done with my optimization, things were running so smoothly that I was able to merge some of our DB machines together, thus freeing up (at the time) R15k pm on cloud hardware.

EDIT: In the above environment, I was one of the "inept/unexperienced programmers thinking they know SQL". But boy did I learn!
 
Last edited:
Yes, get a DBA in but based on what I can see here you need to look at ad hoc queries.

The io on your temp DB is a problem, so an educated guess is that its DB statistics that are out or too many ad hoc queries that are not using the indexes.



Lets see what Most expensive transaction is.
 
Copy one of the queries you use in excel that take a long time to run.
run them in enterprise manager
include the actual execution plan in the results
see if it recommends an index to be created.
create the index
Only do this if its a query that you need to run often, don`t create unnecessary indexes.

defrag your indexes, i`ve got this one running every week on a sunday.
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
 
Thank you for all the replies. Most of you were right, it was SQL queries, or more specifically, the amount of data that needed to be searched. They removed old row entries and it's a lot better now.

I still think things can be improved with a server upgrade, but since we will be moving to MS Dynamics 365, I am not too worried about it now.
 
I stopped reading after "*Windows* Server 2012 R2 is installed and SQL 2014"
 
SharkBait said:
Thank you for all the replies. Most of you were right, it was SQL queries, or more specifically, the amount of data that needed to be searched. They removed old row entries and it's a lot better now.

I still think things can be improved with a server upgrade, but since we will be moving to MS Dynamics 365, I am not too worried about it now.
Click to expand...

Too much data should never be the issue - its a db after all. The problem is indexing and searching on those indexes. Hopefully there was a re-index after the cleanup.
 
SharkBait said:
Thank you for all the replies. Most of you were right, it was SQL queries, or more specifically, the amount of data that needed to be searched. They removed old row entries and it's a lot better now.

I still think things can be improved with a server upgrade, but since we will be moving to MS Dynamics 365, I am not too worried about it now.
Click to expand...

As @Nerfherder said, "too much data" is not a valid cause of slowness. You need to look at indexes (possibly create covering indexes to support certain high-use queries) and update table statistics.

Additionally, table partitioning can also help significantly (e.g. if the relevant index for a particular query no longer fits into memory, then it's definitely worthwhile partitioning).

Beyond that you'd have to look at sharding/clustering.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X