{ "metadata": { "kernelspec": { "name": "SQL", "display_name": "SQL", "language": "sql" }, "language_info": { "name": "sql", "version": "" } }, "nbformat_minor": 2, "nbformat": 4, "cells": [ { "cell_type": "markdown", "source": [ "# SQL ConstantCare® Population Report, Spring 2020\n", "May 30, Brent Ozar - [Get the latest version free here](https://www.brentozar.com/archive/category/sql-constantcare/sql-server-population-report/)\n", "\n", "\n", "\n", "[Our SQL ConstantCare® service](https://www.brentozar.com/sql-constantcare/) lets users send data in about their SQL Servers and get health & performance advice via email. Users can opt in to share their anonymized data to get better recommendations overall.\n", "\n", "**This data isn't a full picture of the SQL Server ecosystem.** Lemme be really clear: this is by no means a random sample of the full population. It's just a picture of the kinds of servers where people want an inexpensive second opinion. It doesn't include giant corporations, hospitals with PII, or tiny businesses who just shove a SQL Server in the corner and then never take a second glance at it (and if my experience is any indication, never back it up.)\n", "\n", "Even though it isn't a full picture, with 3,878 database servers sharing data as of May 30 (up from 3,570 in the last report), there's still a lot of useful information in here for the community. Understanding what servers are out there will help you put context around where your own servers rank, and helps the community better build training material that's relevant for most users.\n", "\n", "This population report is just my attempt to help clear up the fog of war. Microsoft simply doesn't give us any adoption information (even though [SQL Server phones home by default](https://www.brentozar.com/archive/2016/06/sql-server-2016-internet-forced-updates-phoning-home/).) If you believed what you saw onstage at conferences and in Microsoft's changing certification programs, you'd think no one still bothers with self-hosted SQL Server, but the few who do have already migrated to SQL Server 2019 running on Kubernetes and Linux. This report is my humble attempt to keep Microsoft honest.\n", "\n", "Let's start with a simple question:\n", "\n", "## What Versions are the Most Popular?" ], "metadata": { "azdata_cell_guid": "817155ad-8d76-4a84-b826-fe228117c503" } }, { "cell_type": "code", "source": [ "SELECT \n", " CASE WHEN props.engine_edition = 5 THEN 'Azure SQL DB'\n", " WHEN props.engine_edition = 6 THEN 'Azure Synapse Analytics'\n", " WHEN props.engine_edition = 8 THEN 'Azure SQL DB MI'\n", " WHEN props.engine_edition = 9 THEN 'Azure SQL Edge'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 10 AND SPLIT_PART(product_version, '.', 2)::INTEGER = 0 THEN '2008'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 10 AND SPLIT_PART(product_version, '.', 2)::INTEGER = 50 THEN '2008 R2'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 11 THEN '2012'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 12 THEN '2014'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 13 THEN '2016'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 14 THEN '2017'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 15 THEN '2019'\n", " ELSE 'SQL Server Futurama' END AS SQL_Server_Version,\n", " SUM(1) AS Servers\n", "FROM collection_server vcs\n", "INNER JOIN registered_user u ON vcs.user_id = u.user_id\n", "INNER JOIN server_property props ON vcs.connection_id = props.connection_id\n", "LEFT OUTER JOIN collection_server vcsParent ON vcs.connection_id = vcsParent.prev_connection_id\n", "WHERE vcsParent.run_id IS NULL /* No subsequent collections */\n", " AND u.email NOT LIKE '%brentozar.com'\n", " AND vcs.collection_date > (now() - interval '4 week') /* Only get collections from the last month */\n", "GROUP BY 1\n", "ORDER BY 1\n", ";" ], "metadata": { "azdata_cell_guid": "24fa0a63-4445-47bb-9407-bdc5e02e995a", "tags": [ "hide_input" ] }, "outputs": [ { "output_type": "display_data", "data": { "text/html": "(9 row(s) affected)" }, "metadata": {} }, { "output_type": "display_data", "data": { "text/html": "Total execution time: 00:00:00.783" }, "metadata": {} }, { "output_type": "execute_result", "metadata": { "azdata_chartOptions": { "type": "bar", "dataDirection": "horizontal", "columnsAsLabels": true, "labelFirstColumn": true, "legendPosition": "bottom" } }, "execution_count": 35, "data": { "application/vnd.dataresource+json": { "schema": { "fields": [ { "name": "sql_server_version" }, { "name": "servers" } ] }, "data": [ { "0": "2008", "1": "68" }, { "0": "2008 R2", "1": "255" }, { "0": "2012", "1": "627" }, { "0": "2014", "1": "694" }, { "0": "2016", "1": "1293" }, { "0": "2017", "1": "786" }, { "0": "2019", "1": "102" }, { "0": "Azure SQL DB", "1": "51" }, { "0": "Azure SQL DB MI", "1": "4" } ] }, "text/html": [ "", "", "", "", "", "", "", "", "", "", "", "
sql_server_versionservers
200868
2008 R2255
2012627
2014694
20161293
2017786
2019102
Azure SQL DB51
Azure SQL DB MI4
" ] } } ], "execution_count": 35 }, { "cell_type": "markdown", "source": [ "Woohoo! This quarter, SQL Server 2019 adoption outpaced SQL Server 2008!\n", "\n", "Thoughts on reading that chart:\n", "\n", "* SQL Server 2019 still hasn't caught up to 2008R2.\n", "* If you add together 2008 & 2008R2, almost 10% of the servers out there are no longer under support. (That number is really slightly higher since we don't monitor 2000 or 2005, and every time I talk versions in front of a group, folks talk about the 2000 and 2005 instance they still have cowering in shame in a closet somewhere.\n", "* SQL Server 2017 adoption continues to rise, but frankly, 2016 is still the complete juggernaut: 1 in 3 servers is 2016.\n", "\n", "I'm going to continue publishing the numbers in this format (only showing the current population) for the first year of the survey, but on the first year's anniversary, I'll switch to graphs that show trending and year-over-year changes.\n", "\n", "How about development? Are people maybe testing their apps on newer versions?\n", "\n", "" ], "metadata": { "azdata_cell_guid": "ada9577d-3ae4-4d48-bbdb-2fa2ddfdcd37" } }, { "cell_type": "markdown", "source": [ "## What Versions are People Using in Development?\n", "" ], "metadata": { "azdata_cell_guid": "a9efd907-8364-4a4a-8af5-7c27137257ef" } }, { "cell_type": "code", "source": [ "SELECT \n", " CASE WHEN props.engine_edition = 5 THEN 'Azure SQL DB'\n", " WHEN props.engine_edition = 6 THEN 'Azure Synapse Analytics'\n", " WHEN props.engine_edition = 8 THEN 'Azure SQL DB MI'\n", " WHEN props.engine_edition = 9 THEN 'Azure SQL Edge'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 10 AND SPLIT_PART(product_version, '.', 2)::INTEGER = 0 THEN '2008'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 10 AND SPLIT_PART(product_version, '.', 2)::INTEGER = 50 THEN '2008 R2'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 11 THEN '2012'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 12 THEN '2014'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 13 THEN '2016'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 14 THEN '2017'\n", " WHEN SPLIT_PART(product_version, '.', 1)::INTEGER = 15 THEN '2019'\n", " ELSE 'SQL Server Futurama' END AS SQL_Server_Version,\n", " SUM(1) AS Servers\n", "FROM collection_server vcs\n", "INNER JOIN registered_user u ON vcs.user_id = u.user_id\n", "INNER JOIN server_property props ON vcs.connection_id = props.connection_id\n", "LEFT OUTER JOIN collection_server vcsParent ON vcs.connection_id = vcsParent.prev_connection_id\n", "WHERE vcsParent.run_id IS NULL /* No subsequent collections */\n", " AND u.email NOT LIKE '%brentozar.com'\n", " AND vcs.collection_date > (now() - interval '4 week') /* Only get collections from the last month */\n", " AND props.edition LIKE 'Developer%'\n", "GROUP BY 1\n", "ORDER BY 1\n", ";" ], "metadata": { "azdata_cell_guid": "26b13a03-51fb-4529-bd8c-d8bf7447d515", "tags": [ "hide_input" ] }, "outputs": [ { "output_type": "display_data", "data": { "text/html": "(7 row(s) affected)" }, "metadata": {} }, { "output_type": "display_data", "data": { "text/html": "Total execution time: 00:00:00.779" }, "metadata": {} }, { "output_type": "execute_result", "metadata": { "azdata_chartOptions": { "type": "bar", "dataDirection": "horizontal", "columnsAsLabels": true, "labelFirstColumn": true, "legendPosition": "bottom" } }, "execution_count": 37, "data": { "application/vnd.dataresource+json": { "schema": { "fields": [ { "name": "sql_server_version" }, { "name": "servers" } ] }, "data": [ { "0": "2008", "1": "2" }, { "0": "2008 R2", "1": "10" }, { "0": "2012", "1": "39" }, { "0": "2014", "1": "38" }, { "0": "2016", "1": "96" }, { "0": "2017", "1": "47" }, { "0": "2019", "1": "33" } ] }, "text/html": [ "", "", "", "", "", "", "", "", "", "
sql_server_versionservers
20082
2008 R210
201239
201438
201696
201747
201933
" ] } } ], "execution_count": 37 }, { "cell_type": "markdown", "source": [ "SQL Server 2019's adoption rate for development servers doubled from the last quarter, and that's a great sign. People are starting to develop more against 2019. However, 2017's growth wasn't as strong - I think it basically means people are using 2019 as their default new dev box, but it doesn't bode well for a rapid adoption of 2019 in production over the next couple of quarters. People just aren't testing on it yet, and testing is utterly critical for 2019 given its radical differences in execution plans (batch mode on rowstore, inlined scalar functions, etc.)\n", "\n", "I think about this chart a lot when I'm designing training. When is the right time to start building developer training material for SQL Server 2019? It's a tricky timing act: you need to be skilled-up before you adopt the new version, and I need to have the training material ready to go before then.\n", "\n", "However, if I focus on building 2019-specific training material right now, it could be 6-12-18 months before it really starts to pay off. Judging by this chart, I'd be better off building 2017-specific training material since there are a lot of people who still haven't adopted it yet. I use 2019 in my class lab VMs, but I still focus on 2017 compat mode (and only require the bring-your-own-VM students to use 2017 minimum, not 2019.)\n", "\n", "For the rest of the census report, I'm going to focus on just the production servers." ], "metadata": { "azdata_cell_guid": "a374777d-1177-4280-b1dc-277a3ced0eb3" } }, { "cell_type": "markdown", "source": [ "## How Much Data do Servers Have?\n", "\n", "For quick, easy reporting (and because accuracy here has relatively limited value), I'm going to use user database data file sizes, not contents, and not log files." ], "metadata": { "azdata_cell_guid": "9785ff76-a621-4772-b065-caa646b53bd1" } }, { "cell_type": "code", "source": [ "WITH IndividualServers AS (\n", " SELECT vcs.connection_id,\n", " CASE WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) < 25 THEN '<25'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 125 THEN '25-125'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 225 THEN '125-225'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 325 THEN '225-325'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 425 THEN '325-425'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 525 THEN '425-525'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 625 THEN '525-625'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 725 THEN '625-725'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 825 THEN '725-825'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 925 THEN '825-925'\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 1025 THEN '925-1025'\n", " ELSE '>1025' END AS Size_Category,\n", "\n", " CASE WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) < 25 THEN 1\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 125 THEN 2\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 225 THEN 3\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 325 THEN 4\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 425 THEN 5\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 525 THEN 6\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 625 THEN 7\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 725 THEN 8\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 825 THEN 9\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 925 THEN 10\n", " WHEN ROUND(SUM(pcData.cntr_value / 1024.0 / 1024 )::numeric, 3) <= 1025 THEN 11\n", " ELSE 12 END AS Size_Category_Id\n", "\n", "\n", " FROM collection_server vcs\n", " INNER JOIN registered_user u ON vcs.user_id = u.user_id\n", " INNER JOIN server_property props ON vcs.connection_id = props.connection_id\n", " INNER JOIN sys_dm_os_performance_counters pcData ON pcData.connection_id = vcs.connection_id\n", " AND pcData.counter_name = 'Data File(s) Size (KB)'\n", " AND pcData.instance_name <> '_Total'\n", " AND pcData.instance_name <> 'master'\n", " AND pcData.instance_name <> 'model'\n", " AND pcData.instance_name <> 'msdb'\n", " AND pcData.instance_name <> 'tempdb'\n", " LEFT OUTER JOIN collection_server vcsParent ON vcs.connection_id = vcsParent.prev_connection_id\n", " WHERE vcsParent.run_id IS NULL /* No subsequent collections */\n", " AND u.email NOT LIKE '%brentozar.com'\n", " AND vcs.collection_date > (now() - interval '4 week') /* Only get collections from the last month */\n", " AND props.edition NOT LIKE 'Developer%'\n", " GROUP BY vcs.connection_id\n", ")\n", "SELECT Size_Category AS Data_File_Size_GB, SUM(1) AS Servers\n", "FROM IndividualServers\n", "GROUP BY Size_Category, Size_Category_Id\n", "ORDER BY Size_Category_Id;" ], "metadata": { "azdata_cell_guid": "6635df4e-5b32-48b7-b2b8-60e7b139f71b", "tags": [ "hide_input" ] }, "outputs": [ { "output_type": "display_data", "data": { "text/html": "(12 row(s) affected)" }, "metadata": {} }, { "output_type": "display_data", "data": { "text/html": "Total execution time: 00:00:01.152" }, "metadata": {} }, { "output_type": "execute_result", "metadata": { "azdata_chartOptions": { "type": "bar", "dataDirection": "horizontal", "columnsAsLabels": true, "labelFirstColumn": true, "legendPosition": "bottom" } }, "execution_count": 39, "data": { "application/vnd.dataresource+json": { "schema": { "fields": [ { "name": "data_file_size_gb" }, { "name": "servers" } ] }, "data": [ { "0": "<25", "1": "1049" }, { "0": "25-125", "1": "795" }, { "0": "125-225", "1": "348" }, { "0": "225-325", "1": "218" }, { "0": "325-425", "1": "175" }, { "0": "425-525", "1": "116" }, { "0": "525-625", "1": "80" }, { "0": "625-725", "1": "65" }, { "0": "725-825", "1": "62" }, { "0": "825-925", "1": "41" }, { "0": "925-1025", "1": "33" }, { "0": ">1025", "1": "569" } ] }, "text/html": [ "", "", "", "", "", "", "", "", "", "", "", "", "", "", "
data_file_size_gbservers
<251049
25-125795
125-225348
225-325218
325-425175
425-525116
525-62580
625-72565
725-82562
825-92541
925-102533
>1025569
" ] } } ], "execution_count": 39 }, { "cell_type": "markdown", "source": [ "Keep in mind that this is the total data file size for all user databases on the server, and these numbers really help to keep the world in perspective.\n", "\n", "Over half of all servers host less than 225GB data.\n", "\n", "Over 2/3 of servers are smaller than the Stack Overflow demo database, and that doesn't even have nonclustered indexes. That's why I love using that database for training: it challenges a very good chunk of the audience.\n", "\n", "Consistent with the last population report, about 15% have a terabyte or more of data, with some of them having dozens of terabytes. That's useful because it gives me over 500 servers' worth of metadata to help guide folks in larger tiers. Performance advice is dramatically different at that scale: it's so much harder and more expensive to throw memory and indexes at problems. Heck, just creating an index on a 1TB table can represent a big challenge for some shops." ], "metadata": { "azdata_cell_guid": "928ab64d-a929-4f92-a459-9e7e605dc652" } }, { "cell_type": "markdown", "source": [ "# How Much Hardware Do Production Servers Get?" ], "metadata": { "azdata_cell_guid": "84b94e27-45b4-40c5-bc7d-2cde15e1076c" } }, { "cell_type": "code", "source": [ "WITH IndividualServers AS (\n", " SELECT vcs.connection_id,\n", " CASE WHEN SUM(1) <= 4 THEN '1-4'\n", " WHEN SUM(1) <= 8 THEN '5-8'\n", " WHEN SUM(1) <= 12 THEN '9-12'\n", " WHEN SUM(1) <= 16 THEN '13-16'\n", " WHEN SUM(1) <= 20 THEN '17-20'\n", " WHEN SUM(1) <= 24 THEN '21-24'\n", " WHEN SUM(1) <= 28 THEN '25-28'\n", " WHEN SUM(1) <= 32 THEN '29-32'\n", " ELSE '>=33' END AS Size_Category,\n", "\n", " CASE WHEN SUM(1) <= 4 THEN 1\n", " WHEN SUM(1) <= 8 THEN 2\n", " WHEN SUM(1) <= 12 THEN 3\n", " WHEN SUM(1) <= 16 THEN 4\n", " WHEN SUM(1) <= 20 THEN 5\n", " WHEN SUM(1) <= 24 THEN 6\n", " WHEN SUM(1) <= 28 THEN 7\n", " WHEN SUM(1) <= 32 THEN 8\n", " ELSE 9 END AS Size_Category_Id\n", "\n", " FROM collection_server vcs\n", " INNER JOIN registered_user u ON vcs.user_id = u.user_id\n", " INNER JOIN server_property props ON vcs.connection_id = props.connection_id\n", " INNER JOIN sys_dm_os_schedulers cpu ON vcs.connection_id = cpu.connection_id\n", " AND cpu.status IN('VISIBLE ONLINE','VISIBLE OFFLINE')\n", " LEFT OUTER JOIN collection_server vcsParent ON vcs.connection_id = vcsParent.prev_connection_id\n", " WHERE vcsParent.run_id IS NULL /* No subsequent collections */\n", " AND u.email NOT LIKE '%brentozar.com'\n", " AND vcs.collection_date > (now() - interval '4 week') /* Only get collections from the last month */\n", " AND props.edition NOT LIKE 'Developer%'\n", " GROUP BY vcs.connection_id\n", ")\n", "SELECT Size_Category AS CPU_Cores, SUM(1) AS Servers\n", "FROM IndividualServers\n", "GROUP BY Size_Category, Size_Category_Id\n", "ORDER BY Size_Category_Id;" ], "metadata": { "azdata_cell_guid": "c4034ff2-4adb-40fa-a11f-ba60e0b8fa3e", "tags": [ "hide_input" ] }, "outputs": [ { "output_type": "display_data", "data": { "text/html": "(9 row(s) affected)" }, "metadata": {} }, { "output_type": "display_data", "data": { "text/html": "Total execution time: 00:00:00.712" }, "metadata": {} }, { "output_type": "execute_result", "metadata": { "azdata_chartOptions": { "type": "bar", "dataDirection": "horizontal", "columnsAsLabels": true, "labelFirstColumn": true, "legendPosition": "bottom" } }, "execution_count": 40, "data": { "application/vnd.dataresource+json": { "schema": { "fields": [ { "name": "cpu_cores" }, { "name": "servers" } ] }, "data": [ { "0": "1-4", "1": "1687" }, { "0": "5-8", "1": "786" }, { "0": "9-12", "1": "187" }, { "0": "13-16", "1": "278" }, { "0": "17-20", "1": "92" }, { "0": "21-24", "1": "134" }, { "0": "25-28", "1": "1" }, { "0": "29-32", "1": "306" }, { "0": ">=33", "1": "91" } ] }, "text/html": [ "", "", "", "", "", "", "", "", "", "", "", "
cpu_coresservers
1-41687
5-8786
9-12187
13-16278
17-2092
21-24134
25-281
29-32306
>=3391
" ] } } ], "execution_count": 40 }, { "cell_type": "markdown", "source": [ "Again, really helps set expectations: the majority of servers out there are pretty doggone small. You can go a really, really long way with just a few CPU cores, especially if you're diligent about query tuning. At $2,000 per core for Standard Edition, you wanna avoid throwing cores at a problem if you can avoid it.\n", "\n", "About 2/3 of the servers have less CPU power than a modern pro-grade laptop. That's not a number to be ashamed of - that's a number to celebrate! You can do a lot with a little. As with last quarter's report, only about 10% of servers have over 24 cores." ], "metadata": { "azdata_cell_guid": "133ab188-20c8-45c6-b462-2ec2cee23dc8" } }, { "cell_type": "markdown", "source": [ "## How Much Memory Does the Server OS Have?\n", "\n", "Memory isn't licensed - although Standard Edition has a ~128GB cap - so how much memory do folks have at the OS level:" ], "metadata": { "azdata_cell_guid": "2e8e35b7-0503-4426-b78b-972c43a7ff6a" } }, { "cell_type": "code", "source": [ "WITH IndividualServers AS (\n", " SELECT vcs.connection_id,\n", " CASE WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 15 THEN '0-15'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 31 THEN '16-31'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 47 THEN '32-47'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 63 THEN '48-63'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 79 THEN '64-79'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 95 THEN '80-95'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 111 THEN '96-111'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 127 THEN '112-127'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 143 THEN '128-143'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 159 THEN '144-159'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 175 THEN '160-175'\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 192 THEN '176-192'\n", " ELSE '>192' END AS Size_Category,\n", "\n", " CASE WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 15 THEN 0\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 31 THEN 1\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 47 THEN 2\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 63 THEN 3\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 79 THEN 4\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 95 THEN 5\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 111 THEN 6\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 127 THEN 7\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 143 THEN 8\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 159 THEN 9\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 175 THEN 10\n", " WHEN ROUND((os.total_physical_memory_kb / 1024.0 / 1024)::numeric,0) <= 192 THEN 11\n", " ELSE 12 END AS Size_Category_Id\n", "\n", " FROM collection_server vcs\n", " INNER JOIN registered_user u ON vcs.user_id = u.user_id\n", " INNER JOIN server_property props ON vcs.connection_id = props.connection_id\n", " INNER JOIN sys_dm_os_sys_memory os ON vcs.connection_id = os.connection_id\n", " LEFT OUTER JOIN collection_server vcsParent ON vcs.connection_id = vcsParent.prev_connection_id\n", " WHERE vcsParent.run_id IS NULL /* No subsequent collections */\n", " AND u.email NOT LIKE '%brentozar.com'\n", " AND vcs.collection_date > (now() - interval '4 week') /* Only get collections from the last month */\n", " AND props.edition NOT LIKE 'Developer%'\n", ")\n", "SELECT Size_Category AS Memory_OS_GB, SUM(1) AS Servers\n", "FROM IndividualServers\n", "GROUP BY Size_Category, Size_Category_Id\n", "ORDER BY Size_Category_Id;" ], "metadata": { "azdata_cell_guid": "c0de224b-7520-4ee6-9636-f23e129647b8", "tags": [ "hide_input" ] }, "outputs": [ { "output_type": "display_data", "data": { "text/html": "(13 row(s) affected)" }, "metadata": {} }, { "output_type": "display_data", "data": { "text/html": "Total execution time: 00:00:00.407" }, "metadata": {} }, { "output_type": "execute_result", "metadata": { "azdata_chartOptions": { "type": "bar", "dataDirection": "horizontal", "columnsAsLabels": true, "labelFirstColumn": true, "legendPosition": "bottom" } }, "execution_count": 41, "data": { "application/vnd.dataresource+json": { "schema": { "fields": [ { "name": "memory_os_gb" }, { "name": "servers" } ] }, "data": [ { "0": "0-15", "1": "701" }, { "0": "16-31", "1": "726" }, { "0": "32-47", "1": "456" }, { "0": "48-63", "1": "223" }, { "0": "64-79", "1": "345" }, { "0": "80-95", "1": "48" }, { "0": "96-111", "1": "111" }, { "0": "112-127", "1": "40" }, { "0": "128-143", "1": "233" }, { "0": "144-159", "1": "46" }, { "0": "160-175", "1": "68" }, { "0": "176-192", "1": "51" }, { "0": ">192", "1": "549" } ] }, "text/html": [ "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "
memory_os_gbservers
0-15701
16-31726
32-47456
48-63223
64-79345
80-9548
96-111111
112-12740
128-143233
144-15946
160-17568
176-19251
>192549
" ] } } ], "execution_count": 41 }, { "cell_type": "markdown", "source": [ "I'm specifically looking at memory for the entire server OS here, like how much Windows sees.\n", "\n", "There are several ways you can look at SQL Server memory, like target (how much it wants to use) or total (how much it's currently using.) I kept it simple and I'm just looking at OS memory because I want to get a feel for the hardware population overall, not how people are configuring SQL Server, or [whether their workloads are causing total memory to rise to the target counter](https://www.brentozar.com/archive/2018/05/how-to-tell-if-your-sql-server-has-too-much-memory/).\n", "\n", "When you think about these metrics, remember that over half of all production servers host <225GB data files. If you handed me a server with, say, 200GB of data and asked me how much memory to assign it, I think 32-48GB is actually a pretty realistic number. I might even call it generous - keeping in mind that RAM is still ridiculously overpriced in the cloud, and it's tied to core counts.\n", "\n", "Now, look at the metrics: half of the servers host <225GB of data, and half of servers have <48GB RAM. That makes decent sense. 0-15GB RAM sounds terrifying, but remember that 1,049 servers are hosting <25GB of data.\n", "\n", "When you're at these really low database sizes, I can see why people would think they'd be interested in a container-based solution: the overhead of 1,049 servers running Windows is pretty ugly. Containers might remove some of that overhead." ], "metadata": { "azdata_cell_guid": "f86fe653-3830-408b-977c-2fbbcf275692" } }, { "cell_type": "markdown", "source": [ "## Bonus: What Unusual Wait Types are Showing Up?\n", "\n", "One of the fun things with gathering performance metrics on thousands of servers is that we get to see some pretty nifty edge cases. Here are wait stats that have only been seen by a handful of people in the last 30 days:" ], "metadata": { "azdata_cell_guid": "87f177e4-8855-4022-a49d-e2815643712b" } }, { "cell_type": "code", "source": [ "SELECT wait_type, COUNT(DISTINCT user_id) AS users_who_saw_it, SUM(waiting_tasks_count) AS waiting_tasks,\n", " SUM(wait_time_ms / 1000.0 / 60) AS wait_time_minutes, AVG(avg_wait_time_ms * 1.0) AS avg_wait_time_ms\n", "FROM wait_stats w\n", "GROUP BY wait_type\n", "HAVING COUNT(DISTINCT user_id) <= 5\n", "ORDER BY avg_wait_time_ms DESC;" ], "metadata": { "azdata_cell_guid": "dd19fb25-a75b-4a89-8e78-577f1279fcb8", "tags": [ "hide_input" ] }, "outputs": [ { "output_type": "display_data", "data": { "text/html": "(110 row(s) affected)" }, "metadata": {} }, { "output_type": "display_data", "data": { "text/html": "Total execution time: 00:00:11.501" }, "metadata": {} }, { "output_type": "execute_result", "metadata": {}, "execution_count": 48, "data": { "application/vnd.dataresource+json": { "schema": { "fields": [ { "name": "wait_type" }, { "name": "users_who_saw_it" }, { "name": "waiting_tasks" }, { "name": "wait_time_minutes" }, { "name": "avg_wait_time_ms" } ] }, "data": [ { "0": "HADR_FABRIC_CALLBACK", "1": "2", "2": "10597", "3": "125280.8398166666666668", "4": "818345.263888888889" }, { "0": "PVS_PREALLOCATE", "1": "2", "2": "36506", "3": "125334.7800833333333335", "4": "498484.319444444444" }, { "0": "HADR_SEEDING_LIMIT_BACKUPS", "1": "2", "2": "36", "3": "256.21445000000000003333", "4": "368789.333333333333" }, { "0": "XTP_TRUNCATION_LSN", "1": "3", "2": "14", "3": "140.985399999999999966656667", "4": "352466.583333333333" }, { "0": "PWAIT_ALL_COMPONENTS_INITIALIZED", "1": "2", "2": "39", "3": "54.63303333333333280005", "4": "84104.684210526316" }, { "0": "RESTORE_SERVICE_CALL", "1": "1", "2": "20", "3": "12.97506666666666670000", "4": "52959.285714285714" }, { "0": "CLR_JOIN", "1": "5", "2": "18676", "3": "471.89568333333333310000", "4": "23027.379629629630" }, { "0": "SEMPLAT_DSI_BUILD", "1": "1", "2": "1", "3": "0.17093333333333333333", "4": "10256.0000000000000000" }, { "0": "SLEEP_MSDBSTARTUP", "1": "1", "2": "1", "3": "0.09305000000000000000", "4": "5583.0000000000000000" }, { "0": "SATELLITE_SERVICE_SETUP", "1": "4", "2": "7081", "3": "54.37826666666666656668", "4": "2478.0714285714285714" }, { "0": "SERVER_IDLE_CHECK", "1": "1", "2": "4", "3": "0.13675000000000000000", "4": "2051.0000000000000000" }, { "0": "HADR_RECOVERY_WAIT_FOR_CONNECTION", "1": "3", "2": "1066", "3": "33.18470000000000000001", "4": "1839.2500000000000000" }, { "0": "SHRINK_CLEANER_SYNC", "1": "3", "2": "37", "3": "1.23466666666666663333", "4": "1057.0000000000000000" }, { "0": "SLEEP_MASTERDBREADY", "1": "1", "2": "1", "3": "0.01585000000000000000", "4": "951.0000000000000000" }, { "0": "WAIT_XTP_CKPT_CLOSE", "1": "1", "2": "154", "3": "1.28838333333333330000", "4": "508.2857142857142857" }, { "0": "SLEEP_DCOMSTARTUP", "1": "5", "2": "8", "3": "0.06535000000000000001", "4": "490.1250000000000000" }, { "0": "CLR_RWLOCK_READER", "1": "5", "2": "619", "3": "0.089766666666666666660000", "4": "396.5384615384615385" }, { "0": "HADR_SEEDING_CANCELLATION", "1": "4", "2": "136", "3": "2.306050000000000033360000", "4": "359.7368421052631579" }, { "0": "REPLICA_WRITES", "1": "2", "2": "2", "3": "0.00946666666666666667", "4": "284.0000000000000000" }, { "0": "SLEEP_RETRY_VIRTUALALLOC", "1": "2", "2": "26", "3": "0.19825000000000000001", "4": "280.2500000000000000" }, { "0": "RESTORE_MSDA_THREAD_BARRIER", "1": "2", "2": "4685", "3": "19.72643333333333326668", "4": "263.1785714285714286" }, { "0": "ASYNC_OP_COMPLETION", "1": "1", "2": "8", "3": "0.02881666666666666667", "4": "216.0000000000000000" }, { "0": "HADR_TRANSPORT_FLOW_CONTROL", "1": "4", "2": "266894", "3": "2097.96828333333333333332", "4": "187.9333333333333333" }, { "0": "EXTERNAL_SCRIPT_NETWORK_IO", "1": "5", "2": "9897", "3": "58.662583333333333533336667", "4": "139.3547008547008547" }, { "0": "HADR_TDS_LISTENER_SYNC_PROCESSING", "1": "1", "2": "3", "3": "0.00481666666666666667", "4": "131.0000000000000000" }, { "0": "HADR_DB_OP_COMPLETION_SYNC", "1": "4", "2": "6", "3": "0.010266666666666666666667", "4": "122.4000000000000000" }, { "0": "SOS_VIRTUALMEMORY_LOW", "1": "1", "2": "74", "3": "0.17383333333333333334", "4": "103.5000000000000000" }, { "0": "RESOURCE_SEMAPHORE_SMALL_QUERY", "1": "1", "2": "1", "3": "0.00160000000000000000", "4": "96.0000000000000000" }, { "0": "TERMINATE_LISTENER", "1": "4", "2": "7", "3": "0.011166666666666666673334", "4": "95.7142857142857143" }, { "0": "STARTUP_DEPENDENCY_MANAGER", "1": "4", "2": "41", "3": "0.06708333333333333334", "4": "92.7857142857142857" }, { "0": "FFT_NSO_FCB_FIND", "1": "5", "2": "546703", "3": "208.57738333333333346664", "4": "84.1789473684210526" }, { "0": "PREEMPTIVE_OLEDB_JOINTRANSACTION", "1": "5", "2": "17301", "3": "2.70510000000000000004", "4": "74.8739495798319328" }, { "0": "BUILTIN_HASHKEY_MUTEX", "1": "2", "2": "3", "3": "0.00378333333333333333", "4": "59.0000000000000000" }, { "0": "CREATE_DATINISERVICE", "1": "3", "2": "7", "3": "0.011949999999999999996666", "4": "58.6000000000000000" }, { "0": "PREEMPTIVE_XHTTP", "1": "2", "2": "1015971", "3": "1135.23396666666666669998", "4": "54.2500000000000000" }, { "0": "BROKER_TRANSMISSION_OBJECT", "1": "3", "2": "41", "3": "0.035233333333333333336669", "4": "53.0000000000000000" }, { "0": "TRAN_MARKLATCH_EX", "1": "1", "2": "1", "3": "0.00088333333333333333", "4": "53.0000000000000000" }, { "0": "PREEMPTIVE_OS_CLUSTEROPS", "1": "2", "2": "4", "3": "0.00280000000000000000", "4": "42.0000000000000000" }, { "0": "EE_SPECPROC_MAP_INIT", "1": "1", "2": "26", "3": "0.01715000000000000000", "4": "40.0000000000000000" }, { "0": "RESOURCE_QUEUE", "1": "1", "2": "36", "3": "0.02158333333333333333", "4": "36.0000000000000000" }, { "0": "TRAN_MARKLATCH_SH", "1": "4", "2": "1311", "3": "1.063083333333333333340000", "4": "35.7878787878787879" }, { "0": "HADR_SEEDING_READY_FOR_RESTORE_STREAM", "1": "1", "2": "1", "3": "0.00058333333333333333", "4": "35.0000000000000000" }, { "0": "IO_RETRY", "1": "1", "2": "1", "3": "0.00040000000000000000", "4": "24.0000000000000000" }, { "0": "BROKER_ENDPOINT_STATE_MUTEX", "1": "2", "2": "2", "3": "0.00080000000000000000", "4": "24.0000000000000000" }, { "0": "LCK_M_SCH_M_ABORT_BLOCKERS", "1": "1", "2": "2", "3": "0.00073333333333333333", "4": "22.0000000000000000" }, { "0": "ERROR_REPORTING_MANAGER", "1": "4", "2": "44", "3": "0.015599999999999999999999", "4": "21.2727272727272727" }, { "0": "QDS_EXCLUSIVE_ACCESS", "1": "1", "2": "2", "3": "0.00070000000000000000", "4": "21.0000000000000000" }, { "0": "INST_CERT", "1": "1", "2": "49", "3": "0.01220000000000000000", "4": "15.0000000000000000" }, { "0": "HADR_FILESTREAM_PREPROC", "1": "3", "2": "3525", "3": "1.482000000000000000030002", "4": "13.4523809523809524" }, { "0": "PWAIT_HADR_OFFLINE_COMPLETED", "1": "1", "2": "1", "3": "0.00021666666666666667", "4": "13.0000000000000000" }, { "0": "LOGMGR", "1": "4", "2": "25", "3": "0.00526666666666666668", "4": "12.5555555555555556" }, { "0": "PREEMPTIVE_COM_DELETEROWS", "1": "5", "2": "2351233", "3": "312.96901666666666670001", "4": "11.9690721649484536" }, { "0": "SLEEP_WORKSPACE_ALLOCATEPAGE", "1": "2", "2": "165266", "3": "35.17478333333333336666", "4": "11.5333333333333333" }, { "0": "PREEMPTIVE_OS_SETNAMEDSECURITYINFO", "1": "4", "2": "41", "3": "0.008100000000000000010000", "4": "11.0869565217391304" }, { "0": "QDS_TASK_START", "1": "1", "2": "1", "3": "0.00016666666666666667", "4": "10.0000000000000000" }, { "0": "XTPPROC_CACHE_ACCESS", "1": "1", "2": "26", "3": "0.003966666666666666663333", "4": "9.1538461538461538" }, { "0": "FFT_NSO_FCB_PARENT", "1": "5", "2": "413149", "3": "30.622033333333333300026667", "4": "9.0585106382978723" }, { "0": "FS_LOGTRUNC_RWLOCK", "1": "1", "2": "1", "3": "0.00015000000000000000", "4": "9.0000000000000000" }, { "0": "SQLSORT_SORTMUTEX", "1": "2", "2": "4", "3": "0.000550000000000000000000", "4": "8.5000000000000000" }, { "0": "FABRIC_REPLICA_CONTROLLER_STATE_AND_CONFIG", "1": "1", "2": "5", "3": "0.000800000000000000000000", "4": "8.2500000000000000" }, { "0": "INSTANCE_LOG_RATE_GOVERNOR", "1": "2", "2": "11021467", "3": "1652.26524999999999950000", "4": "7.6944444444444444" }, { "0": "FILE_VALIDATION_THREADS", "1": "5", "2": "1107", "3": "0.044266666666666666673332", "4": "7.6206896551724138" }, { "0": "LOG_RATE_GOVERNOR", "1": "1", "2": "706231", "3": "77.64969999999999996667", "4": "6.7272727272727273" }, { "0": "SQLTRACE_PENDING_BUFFER_WRITERS", "1": "5", "2": "103", "3": "0.011116666666666666673332", "4": "6.3970588235294118" }, { "0": "CHECK_TABLES_SINGLE_SCAN", "1": "1", "2": "432", "3": "0.04076666666666666667", "4": "6.0000000000000000" }, { "0": "RESOURCE_GOVERNOR_IDLE", "1": "4", "2": "800286835", "3": "32556.264983333333666466660000", "4": "5.6190476190476190" }, { "0": "SOS_OBJECT_STORE_DESTROY_MUTEX", "1": "1", "2": "125", "3": "0.01103333333333333333", "4": "5.0000000000000000" }, { "0": "EC", "1": "3", "2": "745301663", "3": "4710.46086666666666653332", "4": "4.9481481481481481" }, { "0": "DUMP_LOG_COORDINATOR_QUEUE", "1": "1", "2": "321919572", "3": "14646.14511666666666673333", "4": "4.2916666666666667" }, { "0": "REPL_CACHE_ACCESS", "1": "1", "2": "1", "3": "0.000066666666666666666667", "4": "4.0000000000000000" }, { "0": "PREEMPTIVE_OS_WINSOCKOPS", "1": "1", "2": "1", "3": "0.000066666666666666666667", "4": "4.0000000000000000" }, { "0": "FS_FC_RWLOCK", "1": "2", "2": "2", "3": "0.000133333333333333333333", "4": "4.0000000000000000" }, { "0": "PREEMPTIVE_COM_SETDATAFAILURE", "1": "2", "2": "29", "3": "0.002266666666666666666667", "4": "4.0000000000000000" }, { "0": "FS_HEADER_RWLOCK", "1": "3", "2": "17", "3": "0.001216666666666666663335", "4": "3.8888888888888889" }, { "0": "PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS", "1": "3", "2": "421", "3": "0.047866666666666666650000", "4": "3.3898305084745763" }, { "0": "METADATA_LAZYCACHE_RWLOCK", "1": "4", "2": "238", "3": "0.010416666666666666663334", "4": "3.0000000000000000" }, { "0": "PWAIT_REPLICA_ONLINE_INIT_MUTEX", "1": "1", "2": "1", "3": "0.000050000000000000000000", "4": "3.0000000000000000" }, { "0": "HADR_DB_OP_START_SYNC", "1": "1", "2": "1", "3": "0.000050000000000000000000", "4": "3.0000000000000000" }, { "0": "FFT_STORE_DB", "1": "4", "2": "10798", "3": "0.032283333333333333336673", "4": "2.7358490566037736" }, { "0": "PREEMPTIVE_FILE_MAPPING", "1": "1", "2": "40", "3": "0.000633333333333333340001", "4": "2.5714285714285714" }, { "0": "HADR_TDS_LISTENER_SYNC", "1": "2", "2": "3", "3": "0.000116666666666666666667", "4": "2.3333333333333333" }, { "0": "WAIT_XTP_TRAN_DEPENDENCY", "1": "1", "2": "3", "3": "0.000100000000000000000000", "4": "2.0000000000000000" }, { "0": "SNI_TASK_COMPLETION", "1": "3", "2": "13", "3": "0.000533333333333333333337", "4": "1.8333333333333333" }, { "0": "DISPATCHER_JOIN", "1": "2", "2": "3079521", "3": "104.49953333333333346667", "4": "1.6666666666666667" }, { "0": "FFT_STORE_TABLE", "1": "4", "2": "18889", "3": "0.256533333333333333330006", "4": "1.5200000000000000" }, { "0": "AUDIT_GROUPCACHE_LOCK", "1": "3", "2": "7683", "3": "0.163133333333333333370000", "4": "1.2948717948717949" }, { "0": "PREEMPTIVE_OS_NETGROUPGETUSERS", "1": "1", "2": "126", "3": "0.002766666666666666670000", "4": "1.2941176470588235" }, { "0": "PWAIT_COOP_SCAN", "1": "2", "2": "8648", "3": "0.28431666666666666667", "4": "1.2800000000000000" }, { "0": "PREEMPTIVE_RG_RESPONSEFROMSERVER", "1": "2", "2": "25004", "3": "0.56228333333333333333", "4": "1.2083333333333333" }, { "0": "DUMP_LOG_COORDINATOR", "1": "1", "2": "85270", "3": "1.29351666666666666668", "4": "1.1304347826086957" }, { "0": "HADR_SEEDING_SYNC_COMPLETION", "1": "1", "2": "1", "3": "0.000016666666666666666667", "4": "1.00000000000000000000" }, { "0": "XE_CALLBACK_LIST", "1": "1", "2": "1", "3": "0.000016666666666666666667", "4": "1.00000000000000000000" }, { "0": "SECURITY_CNG_PROVIDER_MUTEX", "1": "1", "2": "1", "3": "0.000016666666666666666667", "4": "1.00000000000000000000" }, { "0": "PWAIT_HADR_CLUSTER_INTEGRATION", "1": "1", "2": "5", "3": "0.000066666666666666666668", "4": "1.00000000000000000000" }, { "0": "PREEMPTIVE_OS_NCRYPTIMPORTKEY", "1": "1", "2": "24558", "3": "0.092550000000000000000001", "4": "1.00000000000000000000" }, { "0": "PREEMPTIVE_OS_SQLCLROPS", "1": "4", "2": "14844", "3": "0.117016666666666666650001", "4": "0.92857142857142857143" }, { "0": "WINFAB_API_CALL", "1": "2", "2": "22050", "3": "0.28968333333333333335", "4": "0.81944444444444444444" }, { "0": "PWAIT_MD_SERVER_CACHE", "1": "2", "2": "354", "3": "0.000283333333333333330001", "4": "0.75000000000000000000" }, { "0": "FFT_NSO_DB_LIST", "1": "5", "2": "748", "3": "0.004433333333333333336670", "4": "0.41379310344827586207" }, { "0": "LOGPOOL_MGRSET", "1": "5", "2": "3587", "3": "0.007649999999999999990003", "4": "0.28888888888888888889" }, { "0": "PWAIT_MD_UPGRADE_CONFIG", "1": "3", "2": "247", "3": "0.001316666666666666666679", "4": "0.18181818181818181818" }, { "0": "PREEMPTIVE_OS_SETENDOFFILE", "1": "5", "2": "410806", "3": "1.526516666666666666670001", "4": "0.14893617021276595745" }, { "0": "XIO_LEASE_RENEW_MGR_RWLOCK", "1": "2", "2": "11771", "3": "0.006133333333333333340001", "4": "0.01492537313432835821" }, { "0": "ROWGROUP_VERSION", "1": "1", "2": "13444", "3": "0.002883333333333333330000", "4": "0E-20" }, { "0": "QE_WARN_LIST_SYNC", "1": "1", "2": "21", "3": "0.000033333333333333333334", "4": "0E-20" }, { "0": "XIO_IOSTATS_BLOBLIST_RWLOCK", "1": "2", "2": "194270", "3": "0.21399999999999999999", "4": "0E-20" }, { "0": "SLOG_TRUNCATION", "1": "1", "2": "24", "3": "0.000050000000000000000001", "4": "0E-20" }, { "0": "SOSHOST_TRACELOCK", "1": "1", "2": "18917", "3": "0.03186666666666666667", "4": "0E-20" }, { "0": "PREEMPTIVE_COM_LBSTAT", "1": "1", "2": "82064", "3": "0.000050000000000000000000", "4": "0E-20" }, { "0": "PREEMPTIVE_COM_LBREADAT", "1": "2", "2": "83630", "3": "0.000166666666666666670000", "4": "0E-20" } ] }, "text/html": [ "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "
wait_typeusers_who_saw_itwaiting_taskswait_time_minutesavg_wait_time_ms
HADR_FABRIC_CALLBACK210597125280.8398166666666668818345.263888888889
PVS_PREALLOCATE236506125334.7800833333333335498484.319444444444
HADR_SEEDING_LIMIT_BACKUPS236256.21445000000000003333368789.333333333333
XTP_TRUNCATION_LSN314140.985399999999999966656667352466.583333333333
PWAIT_ALL_COMPONENTS_INITIALIZED23954.6330333333333328000584104.684210526316
RESTORE_SERVICE_CALL12012.9750666666666667000052959.285714285714
CLR_JOIN518676471.8956833333333331000023027.379629629630
SEMPLAT_DSI_BUILD110.1709333333333333333310256.0000000000000000
SLEEP_MSDBSTARTUP110.093050000000000000005583.0000000000000000
SATELLITE_SERVICE_SETUP4708154.378266666666666566682478.0714285714285714
SERVER_IDLE_CHECK140.136750000000000000002051.0000000000000000
HADR_RECOVERY_WAIT_FOR_CONNECTION3106633.184700000000000000011839.2500000000000000
SHRINK_CLEANER_SYNC3371.234666666666666633331057.0000000000000000
SLEEP_MASTERDBREADY110.01585000000000000000951.0000000000000000
WAIT_XTP_CKPT_CLOSE11541.28838333333333330000508.2857142857142857
SLEEP_DCOMSTARTUP580.06535000000000000001490.1250000000000000
CLR_RWLOCK_READER56190.089766666666666666660000396.5384615384615385
HADR_SEEDING_CANCELLATION41362.306050000000000033360000359.7368421052631579
REPLICA_WRITES220.00946666666666666667284.0000000000000000
SLEEP_RETRY_VIRTUALALLOC2260.19825000000000000001280.2500000000000000
RESTORE_MSDA_THREAD_BARRIER2468519.72643333333333326668263.1785714285714286
ASYNC_OP_COMPLETION180.02881666666666666667216.0000000000000000
HADR_TRANSPORT_FLOW_CONTROL42668942097.96828333333333333332187.9333333333333333
EXTERNAL_SCRIPT_NETWORK_IO5989758.662583333333333533336667139.3547008547008547
HADR_TDS_LISTENER_SYNC_PROCESSING130.00481666666666666667131.0000000000000000
HADR_DB_OP_COMPLETION_SYNC460.010266666666666666666667122.4000000000000000
SOS_VIRTUALMEMORY_LOW1740.17383333333333333334103.5000000000000000
RESOURCE_SEMAPHORE_SMALL_QUERY110.0016000000000000000096.0000000000000000
TERMINATE_LISTENER470.01116666666666666667333495.7142857142857143
STARTUP_DEPENDENCY_MANAGER4410.0670833333333333333492.7857142857142857
FFT_NSO_FCB_FIND5546703208.5773833333333334666484.1789473684210526
PREEMPTIVE_OLEDB_JOINTRANSACTION5173012.7051000000000000000474.8739495798319328
BUILTIN_HASHKEY_MUTEX230.0037833333333333333359.0000000000000000
CREATE_DATINISERVICE370.01194999999999999999666658.6000000000000000
PREEMPTIVE_XHTTP210159711135.2339666666666666999854.2500000000000000
BROKER_TRANSMISSION_OBJECT3410.03523333333333333333666953.0000000000000000
TRAN_MARKLATCH_EX110.0008833333333333333353.0000000000000000
PREEMPTIVE_OS_CLUSTEROPS240.0028000000000000000042.0000000000000000
EE_SPECPROC_MAP_INIT1260.0171500000000000000040.0000000000000000
RESOURCE_QUEUE1360.0215833333333333333336.0000000000000000
TRAN_MARKLATCH_SH413111.06308333333333333334000035.7878787878787879
HADR_SEEDING_READY_FOR_RESTORE_STREAM110.0005833333333333333335.0000000000000000
IO_RETRY110.0004000000000000000024.0000000000000000
BROKER_ENDPOINT_STATE_MUTEX220.0008000000000000000024.0000000000000000
LCK_M_SCH_M_ABORT_BLOCKERS120.0007333333333333333322.0000000000000000
ERROR_REPORTING_MANAGER4440.01559999999999999999999921.2727272727272727
QDS_EXCLUSIVE_ACCESS120.0007000000000000000021.0000000000000000
INST_CERT1490.0122000000000000000015.0000000000000000
HADR_FILESTREAM_PREPROC335251.48200000000000000003000213.4523809523809524
PWAIT_HADR_OFFLINE_COMPLETED110.0002166666666666666713.0000000000000000
LOGMGR4250.0052666666666666666812.5555555555555556
PREEMPTIVE_COM_DELETEROWS52351233312.9690166666666667000111.9690721649484536
SLEEP_WORKSPACE_ALLOCATEPAGE216526635.1747833333333333666611.5333333333333333
PREEMPTIVE_OS_SETNAMEDSECURITYINFO4410.00810000000000000001000011.0869565217391304
QDS_TASK_START110.0001666666666666666710.0000000000000000
XTPPROC_CACHE_ACCESS1260.0039666666666666666633339.1538461538461538
FFT_NSO_FCB_PARENT541314930.6220333333333333000266679.0585106382978723
FS_LOGTRUNC_RWLOCK110.000150000000000000009.0000000000000000
SQLSORT_SORTMUTEX240.0005500000000000000000008.5000000000000000
FABRIC_REPLICA_CONTROLLER_STATE_AND_CONFIG150.0008000000000000000000008.2500000000000000
INSTANCE_LOG_RATE_GOVERNOR2110214671652.265249999999999500007.6944444444444444
FILE_VALIDATION_THREADS511070.0442666666666666666733327.6206896551724138
LOG_RATE_GOVERNOR170623177.649699999999999966676.7272727272727273
SQLTRACE_PENDING_BUFFER_WRITERS51030.0111166666666666666733326.3970588235294118
CHECK_TABLES_SINGLE_SCAN14320.040766666666666666676.0000000000000000
RESOURCE_GOVERNOR_IDLE480028683532556.2649833333336664666600005.6190476190476190
SOS_OBJECT_STORE_DESTROY_MUTEX11250.011033333333333333335.0000000000000000
EC37453016634710.460866666666666533324.9481481481481481
DUMP_LOG_COORDINATOR_QUEUE132191957214646.145116666666666733334.2916666666666667
REPL_CACHE_ACCESS110.0000666666666666666666674.0000000000000000
PREEMPTIVE_OS_WINSOCKOPS110.0000666666666666666666674.0000000000000000
FS_FC_RWLOCK220.0001333333333333333333334.0000000000000000
PREEMPTIVE_COM_SETDATAFAILURE2290.0022666666666666666666674.0000000000000000
FS_HEADER_RWLOCK3170.0012166666666666666633353.8888888888888889
PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS34210.0478666666666666666500003.3898305084745763
METADATA_LAZYCACHE_RWLOCK42380.0104166666666666666633343.0000000000000000
PWAIT_REPLICA_ONLINE_INIT_MUTEX110.0000500000000000000000003.0000000000000000
HADR_DB_OP_START_SYNC110.0000500000000000000000003.0000000000000000
FFT_STORE_DB4107980.0322833333333333333366732.7358490566037736
PREEMPTIVE_FILE_MAPPING1400.0006333333333333333400012.5714285714285714
HADR_TDS_LISTENER_SYNC230.0001166666666666666666672.3333333333333333
WAIT_XTP_TRAN_DEPENDENCY130.0001000000000000000000002.0000000000000000
SNI_TASK_COMPLETION3130.0005333333333333333333371.8333333333333333
DISPATCHER_JOIN23079521104.499533333333333466671.6666666666666667
FFT_STORE_TABLE4188890.2565333333333333333300061.5200000000000000
AUDIT_GROUPCACHE_LOCK376830.1631333333333333333700001.2948717948717949
PREEMPTIVE_OS_NETGROUPGETUSERS11260.0027666666666666666700001.2941176470588235
PWAIT_COOP_SCAN286480.284316666666666666671.2800000000000000
PREEMPTIVE_RG_RESPONSEFROMSERVER2250040.562283333333333333331.2083333333333333
DUMP_LOG_COORDINATOR1852701.293516666666666666681.1304347826086957
HADR_SEEDING_SYNC_COMPLETION110.0000166666666666666666671.00000000000000000000
XE_CALLBACK_LIST110.0000166666666666666666671.00000000000000000000
SECURITY_CNG_PROVIDER_MUTEX110.0000166666666666666666671.00000000000000000000
PWAIT_HADR_CLUSTER_INTEGRATION150.0000666666666666666666681.00000000000000000000
PREEMPTIVE_OS_NCRYPTIMPORTKEY1245580.0925500000000000000000011.00000000000000000000
PREEMPTIVE_OS_SQLCLROPS4148440.1170166666666666666500010.92857142857142857143
WINFAB_API_CALL2220500.289683333333333333350.81944444444444444444
PWAIT_MD_SERVER_CACHE23540.0002833333333333333300010.75000000000000000000
FFT_NSO_DB_LIST57480.0044333333333333333366700.41379310344827586207
LOGPOOL_MGRSET535870.0076499999999999999900030.28888888888888888889
PWAIT_MD_UPGRADE_CONFIG32470.0013166666666666666666790.18181818181818181818
PREEMPTIVE_OS_SETENDOFFILE54108061.5265166666666666666700010.14893617021276595745
XIO_LEASE_RENEW_MGR_RWLOCK2117710.0061333333333333333400010.01492537313432835821
ROWGROUP_VERSION1134440.0028833333333333333300000E-20
QE_WARN_LIST_SYNC1210.0000333333333333333333340E-20
XIO_IOSTATS_BLOBLIST_RWLOCK21942700.213999999999999999990E-20
SLOG_TRUNCATION1240.0000500000000000000000010E-20
SOSHOST_TRACELOCK1189170.031866666666666666670E-20
PREEMPTIVE_COM_LBSTAT1820640.0000500000000000000000000E-20
PREEMPTIVE_COM_LBREADAT2836300.0001666666666666666700000E-20
" ] } } ], "execution_count": 48 } ] }