A Fine Slice Of SQL Server
Sakshi Gupta shows how we can combine Prometheus and Grafana to keep track of our Kafka clusters:
Comments closedIn the previous post, we have monitored our Kafka matrices using Prometheus and visualize the health of Kafka over Grafana. Now we will set an alert, so whenever any of Kafka broker is down, we’ll receive a notification.
As I said, this pretty much depends on the business requirements and after discussing that in length with the users, the solution is usually to simply add an “Others” row that sums up all values which are not part of the TopN items. For regular business users this requirement sounds really trivial because in Excel the could just add a new row and subtract the values of the TopN items from the Grand Total.
However, they usually will not understand the complexity behind this requirement for Power BI. In Power BI we cannot simply add a new “Others” row on the fly. It has to be part of the data model and as the TopN calculations is already dynamic, also the calculation for “Others” has to be dynamic. As you probably expected, also this has been covered already:
Oraylis – Show TopN and rest in Power BI
Power BI community – Dynamic Top N and Others category
This is a pain point that ideally I’d like the Power BI team to address. Gerhard does a good job showing how to do it, but Tableau has that (and more) built in.
Comments closedNed Otter shows how to configure SQL Server to use memory-optimized metadata tables in TempDB:
Like other changes to TempDB, in order for the new memory-optimization to take effect a restart of the SQL Server service is required. Once the service is restarted, system tables in TempDB are now memory-optimized (it should be that way for RTM, but in CTP 3.0, it could be the case that not all system tables have been converted to Hekaton). You can reverse this setting with the following command, and again restarting the SQL Server service:
There are a couple of important notes that Ned gives us around accessing these metadata tables in scripts.
Comments closedErin Stellato gives us a simple demonstration of why parameterization is important for performance:
From this screenshot you can see that we have about 3GB total dedicated to the plan cache, and of that 1.7GB is for the plans of over 158,000 adhoc queries. Of that 1.7GB, approximately 500MB is used for 125,000 plans that execute ONE time only. About 1GB of the plan cache is for prepared and procedure plans, and they only take up about 300MB worth of space. But note the average use count – well over 1 million for procedures. In looking at this output, I would categorize this workload as mixed – some parameterized queries, some adhoc.
Kimberly’s blog post discusses options for managing a plan cache filled with a lot of adhoc queries. Plan cache bloat is just one problem you have to contend with when you have an adhoc workload, and in this post I want to explore the effect it can have on CPU as a result of all the compilations that have to occur. When a query executes in SQL Server, it goes through compilation and optimization, and there is overhead associated with this process, which frequently manifests as CPU cost. Once a query plan is in cache, it can be re-used. Queries that are parameterized can end up re-using a plan that’s already in cache, because the query text is exactly the same. When an adhoc query executes it will only re-use the plan in cache if it has the exact same text and input value(s).
Read on to see an example of how long it takes a set of ad hoc queries to finish versus their parameterized equivalents. Erin’s test is at the behavioral extreme (100% parameterized versus 100% ad hoc) so real-world results won’t be quite this good.
Comments closedBrent Ozar notes something nice in the SQL Server 2019 CTP 3.0 setup:
Just a short note – in SQL Server 2019, the setup process has a new MAXDOP tab:
It’s right next to the TempDB tab. I like this idea a lot, as MAXDOP is one of those things you always change right after installation, so letting us change it before install makes it easier to remember and we can include it in the setup script.
Comments closed