December 3, 2016 at 8:35 am
I'm following the discussion from its start.
The performance related with the hardware and the server configuration is not the big problem after you get the matters settled on. Adding faster HW resources won't help you much. And based on your analysis the environment is far from maxed-out utilized.
I think the time for the code optimisation had come there. With efficient code (t-sql, EF, Linq, ...) you can do wanders. That's what I've learned and experienced in the past several years. However, it's a long process, .. which requires staff and expertise.
Igor Micev,My blog: www.igormicev.com
December 3, 2016 at 10:49 am
I have to take exception with one thing you said Igor:
With efficient code (t-sql, EF, Linq, ...) you can do wonders.
EF, as in Entity Framework, has been involved in some of the worst applications I have ever seen coded. It does horrible things by design/default and if you do things right then you are losing some of the benefits of it which is coding speed. It also makes it difficult to tune, difficult to even find where poor queries are being generated from, writes poor query constructs in some cases, plan cache bloat, etc, etc, etc. I actually just presented a session today at the SQL Saturday in Prague titled "Know What Your Code is Doing to SQL Server" covering just a few of the issues with EF (and ORMs in general).
And hopefully you don't mean LINQ-to-SQL. I think that has been put out to pasture so probably not.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 3, 2016 at 11:27 am
TheSQLGuru (12/3/2016)
I have to take exception with one thing you said Igor:With efficient code (t-sql, EF, Linq, ...) you can do wonders.
EF, as in Entity Framework, has been involved in some of the worst applications I have ever seen coded. It does horrible things by design/default and if you do things right then you are losing some of the benefits of it which is coding speed. It also makes it difficult to tune, difficult to even find where poor queries are being generated from, writes poor query constructs in some cases, plan cache bloat, etc, etc, etc. I actually just presented a session today at the SQL Saturday in Prague titled "Know What Your Code is Doing to SQL Server" covering just a few of the issues with EF (and ORMs in general).
And hopefully you don't mean LINQ-to-SQL. I think that has been put out to pasture so probably not.
Heh.... to err is human... to really screw things up, you need an ORM. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2016 at 12:35 pm
Jeff Moden (12/3/2016)
TheSQLGuru (12/3/2016)
I have to take exception with one thing you said Igor:With efficient code (t-sql, EF, Linq, ...) you can do wonders.
EF, as in Entity Framework, has been involved in some of the worst applications I have ever seen coded. It does horrible things by design/default and if you do things right then you are losing some of the benefits of it which is coding speed. It also makes it difficult to tune, difficult to even find where poor queries are being generated from, writes poor query constructs in some cases, plan cache bloat, etc, etc, etc. I actually just presented a session today at the SQL Saturday in Prague titled "Know What Your Code is Doing to SQL Server" covering just a few of the issues with EF (and ORMs in general).
And hopefully you don't mean LINQ-to-SQL. I think that has been put out to pasture so probably not.
Heh.... to err is human... to really screw things up, you need an ORM. 😛
In the words of Wayne Sheffield...
"An ORM is the fastest way to slow down a database."
December 4, 2016 at 1:59 am
Ed Wagner (12/3/2016)
Jeff Moden (12/3/2016)
TheSQLGuru (12/3/2016)
I have to take exception with one thing you said Igor:With efficient code (t-sql, EF, Linq, ...) you can do wonders.
EF, as in Entity Framework, has been involved in some of the worst applications I have ever seen coded. It does horrible things by design/default and if you do things right then you are losing some of the benefits of it which is coding speed. It also makes it difficult to tune, difficult to even find where poor queries are being generated from, writes poor query constructs in some cases, plan cache bloat, etc, etc, etc. I actually just presented a session today at the SQL Saturday in Prague titled "Know What Your Code is Doing to SQL Server" covering just a few of the issues with EF (and ORMs in general).
And hopefully you don't mean LINQ-to-SQL. I think that has been put out to pasture so probably not.
Heh.... to err is human... to really screw things up, you need an ORM. 😛
In the words of Wayne Sheffield...
"An ORM is the fastest way to slow down a database."
Yes, EF is usually "bad" code of the application. It can be corrected or better replaced with t-sql as we're doing it in the past 2-3 years. And the results are fantastic.
"An ORM is the fastest way to slow down a database." - very true.
Igor Micev,My blog: www.igormicev.com
December 5, 2016 at 3:29 pm
We currently use https://www.softperfect.com/products/ramdisk/ to host the Windows \Temp folder and tempdb. It should also be possible to create a RAM Drive natively within W2012 and above with syntax like New-IscsiVirtualDisk –Path ramdisk:test.vhdx –Size 20MB
If you do set up a RAM drive for tempdb, then you will need to create the appropriate folder structure before the SQL Server service starts. SoftPerfect provides a way to do this by automatically running a .bat or .ps1 file after the drive has been created at boot time. If you use the native Windows RAM drive you will need to do your own trigger, such as a scheduled task that runs at boot time.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 6, 2016 at 11:02 am
Gail Wanabee (12/2/2016)
Michael, I responded to part of your suggestions in another post, want to complete it here.The vendor won't tell us a lot about their hyper-convergence solution; it's patented, and I don't think their support people actually know the under-the-hood details, other than it's a black box with a really smart processor for deduping.
Their focus is on write efficiency and that, they have achieved. Watching PERFMON, using a 100 second interval, I rarely see the "Average Disk Seconds/Write" metric exceed 3 msecs, even under the highest I/O loads, using primarily 7200 rpm disk drives with a small amount of SSD and some very high speed RAM for caching, thrown in.
[highlight="#ffff11"]Reads are the only problem, and primarily on 3 files (2 application database data files, and the TempDB log file).
[/highlight]
<< snipped >>
--------
Thanks to all of you for taking the time and energy to respond.
Just verifying that you're stalling on reads for templog.ldf? That would be unusual indeed. Certainly looks like IO contention but figuring out why is the fun part. Do you know what's causing high reads on tempdb log? The app doing a lot of rollbacks on large temp tables?
December 6, 2016 at 12:49 pm
The vendor won't tell us a lot about their hyper-convergence solution
Have you looked at what you can do with Windows 2016. The Windows side of hyper-convergence capability has been available for a while, and there are now some chassis that allow W2016 to deliver performance rivaling vendor packages at potentially much lower cost.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 16, 2017 at 2:15 pm
Re-visiting this thread, based upon doing some more evaluating of various hardware and VM's
How many SCSI controllers are configured in this machine? And, what type are they?
We had very poor tempdb latency numbers, and when we switched to the different SCSI controllers, and added 4 of them, the latency went below 20 ms.
Also, and this may have been asked and answered, have you looked into creating virtual flash disks in VMWare?
This blog spells it out.
https://blogs.vmware.com/vsphere/2014/02/vscsi-controller-choose-performance.html
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 20, 2017 at 10:46 pm
Gail Wanabee - Wednesday, November 30, 2016 3:05 PMRAMDisk used to exist in Windows NT. I used it and it worked really well. Then, it was eliminated in Windows Server 2003, as I recall. You would block off a certain amount of RAM, proclaim it to be a disk drive, then access it like was a disk drive with folders and files. It worked well. Context: My company's main server has a bottleneck issue associated with the TempDB. Perfect storm, lots of little tables with small amounts of data, being created and dropped frequently. Already implemented T1118, to no avail. If we can eliminate the disk drive for the TempDB and create it in RAM to run at RAM speeds, it might go a long way to eliminating this server bottleneck. Does anyone know of a reliable RAMDisk implementation, that could be invoked from a stored procedure on SQL Server startup?
All are interesting facts..but why cant you use extended buffer poll feature from SQL Server.
@JayMunnangi
August 21, 2017 at 12:20 am
JakDBA - Sunday, August 20, 2017 10:46 PMGail Wanabee - Wednesday, November 30, 2016 3:05 PMRAMDisk used to exist in Windows NT. I used it and it worked really well. Then, it was eliminated in Windows Server 2003, as I recall. You would block off a certain amount of RAM, proclaim it to be a disk drive, then access it like was a disk drive with folders and files. It worked well. Context: My company's main server has a bottleneck issue associated with the TempDB. Perfect storm, lots of little tables with small amounts of data, being created and dropped frequently. Already implemented T1118, to no avail. If we can eliminate the disk drive for the TempDB and create it in RAM to run at RAM speeds, it might go a long way to eliminating this server bottleneck. Does anyone know of a reliable RAMDisk implementation, that could be invoked from a stored procedure on SQL Server startup?All are interesting facts..but why cant you use extended buffer poll feature from SQL Server.
As you say, all interesting facts including the extended buffer poll but you'll get a much bigger bang for your buck if the code were refactored to operate more efficiently. I have a couple of databases that exceed the 1.2TB mark that have some very hefty bulk load and batch operations running on them throughout the day but haven't had any problems with TempDB contention nor have any of the 8 files that make up TempDB grow to larger than 2GB each because of the way the code has been written.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2017 at 12:47 am
JakDBA - Sunday, August 20, 2017 10:46 PMAll are interesting facts..but why cant you use extended buffer poll feature from SQL Server.
Because buffer pool extensions are only for unchanged data pages, and TempDB's pages are not going to be unchanged, by the very nature of that DB.
A local SSD with TempDB on would be more beneficial than buffer pool extensions.
However, if you read the OP's responses, they can't add SSDs to the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply