Last updated on July 29th, 2022 at 08:09 pm
This is one of those questions where the answer should seem obvious to everyone. But it is surprising how many ConfigMgr environments don’t have SQL Server indexing enabled nor the ConfigMgr Rebuild Indexes task in use.
Let’s start by defining SQL Server indexing.
In its simplest form, indexes are look-up tables. This is very similar to a book’s table of contents or an index at the back of a book. When enabled, SQL Server indexes will quickly find data anytime the database is queried.
What’s wrong with not having SQL Server index configured? SQL Server’s performance is not as effective.
For example, if there are 10,000 computers (2,000 Windows XP, 2,000 Windows Vista, 2,000 Windows 7, 2,000 Windows 8, and 2,000 Windows 10). And management asks for a summary of all software installed (Add-Remove Program (ARP)) on the Windows XP computers, having SQL Server indexing enabled will give you this information much faster than a non-indexed query.
In a previous blog post, I demonstrated that there are an average of 186 ARP titles per computer. This means that there are 1,860,000 rows of data for 10,000 computers; 372,000 rows of data for only the XP computers. Without an index, your query will need to look at every row. This means ~2 million rows! With an index, your query would only need to look at ~400,000 rows. This means that an indexed query is about 5 times faster than a non-indexed query.
Going back to my book’s table of contents (TOC) example. If I was to cross off a few computers and add a few computers to the list, I would eventually need to start using the margin to add computers to the TOC. After a while, I would have to add a new page to the TOC as it would get so messy that I couldn’t even read it! At some point, I would completely re-write the TOC leaving space to add new computers to the TOC. This task of re-writing the TOC would make my lookups faster (again) as I would quickly see what I need to find. This is what the ConfigMgr Rebuild Indexes task does.
Now, in any ConfigMgr environment you will add and remove computers from the database as needed. But in order to minimize the impact of your queries, you will need to keep your indexes up-to-date. So don’t forget to also enable the ConfigMgr Rebuild Indexes task (look for a blog post that is coming soon). By doing so, your data will always be up-to-date in the indexes. You don’t want to miss any new computers, or include decommissioned PCs in your data. This in turn will help make your queries run faster and the results will be accurate. Additionally, this will help the ConfigMgr console to be faster too.
If you have any questions, please feel free to contact me @GarthMJ.
