Fix Windows 10 - 100% Disk Usage in Task Manager

A colleague directed me to the URl below with a list of tips to fix the 100% disk usage problem I was having with Windows 10.   I also disabled all the Google Chrome Sync "on - custom settings"

You can find the site here:  Windows 10 100% disk usage in Task Manager.

I only needed to do a few steps:

1. disable windows search
2. disable SuperFetch service  (see registry adjustments below)
3. disable all Google Chrome:  Sync "on - custom settings"

A failing SAS hard drive on my RAID :

I've setup my primary workstation with RAID 1 using an LSI SAS 3000 series (1068E) controller.  When disk usage started to spike again, the problem was with the hard drive.  The RAID controller was struggling to maintain consistency with the failing SAS drive. Once the hard drive was replaced, and the RAID rebuilt, disk usage for the RAID partition was back to normal.

Other notes:

I eventually, changed the registry settings for memory management:

Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\PrefetchParameters\

• EnablePrefetcher:   change from 3 to 0
• EnableSuperfetch:  change from 3 to 0

Background Intelligent Transfer Service:  I have it set to 'Manual' and is typically not running

AI and the demise of the DBA

Are DBA's soon to be the way of the buggy whip?

I've long held that the need for a full time DBA to tune a database was past its prime. In an age where database vendors like Microsoft hire brilliant database theorists, it makes far more sense for them to work out the algorithms to manage the database than having a full-time database mechanic (DBA) on staff to monitor and adjust the system.

With the release of SQL Server 2017, a new feature, called Automatic Tuning (SQL Server 2017) has been introduced.

While SQL Server has made progress in this area, the open source world has made advances as well. OtterTune has made some initial advances tuning and optimizing two open source database flatforms:  MySQL and Postgres.

Here are key links to learn more:

AI topics - OtterTune

A detailed look into OtterTune by Adrian Colyer

Research Paper - Automatic Database Management System Tuning Through Large-scale Machine Learning

Index usage stats for SQL Server

Need information on if your indexes are being used?  Greg Larsen has posted a query utilizing the DMV sys.dm_db_index_usage_stats.  You can find it here:  Which Indexes are not Used? - Greg Larsen

Heaps - would it be faster than a table with a clustered index?

In design, we almost always automatically setup a clustered index. But should we?

The site SQLPerformance decided to take the challenge and find out if there are times when a heap just might be faster than a table with a clustered index. You can find all the details here:  Is a RID Lookup faster than a Key Lookup? [SQL Performance].

Query Optimization - Limit Tables in Join

Years ago, I was called in to stabilize the SQL Server databases for a large internet platform. Among the problems discovered was that there was really no concern for limiting how many tables should be included in a query. It was just assumed that the query optimizer would automagically work, For one table, two tables, or dozens of tables.

Digging through the internal statistics and various DMV's, a few queries were found to be causing most of the problems.  One query in particular was found to have 192 tables as part of the join. Given that a join with 10 tables can have over one million possible join orders, 192 tables was most likely astronomical.  On his blog, Benjamin Nevarez has written about the number of possible permutations as the number of tables in the join increases.  You can find it here:  Optimizing Join Orders.

If you just want a quick overview,
here is a reprint of the table posted in the article by Benjamin Nevarez:

Tables	Left-Deep Trees	Bushy Trees
1	1	1
2	2	2
3	6	12
4	24	120
5	120	1,680
6	720	30,240
7	5,040	665,280
8	40,320	17,297,280
9	362,880	518,918,400
10	3,628,800	17,643,225,600
11	39,916,800	670,442,572,800
12	479,001,600	28,158,588,057,600