17Oct 2022 by Grant Fritchey 4 Comments

Monitor Cardinality Feedback in SQL Server 2022

SQL Server, You Can't Do That In Profiler
It's possible for you to see new technology at work if you use Extended Events to monitor cardinality feedback. To put it simply, cardinality, the number of rows being returned, is estimated by SQL Server. Sometimes, it gets these estimates right. Sometimes, it gets them wrong. New functionality within SQL Server 2022 uses Query Store to see how well those estimates are working. If they're off, the optimizer can actually change plans to get you different behaviors based on this feedback. There's even more than one way to monitor cardinality feedback. Let's talk about it. Extended Events First up, to really see the full set of behaviors in action, we can use Extended Events: CREATE EVENT SESSION [CardinalityFeedback] ON SERVER ADD EVENT sqlserver.query_ce_feedback_telemetry, ADD EVENT sqlserver.query_feedback_analysis, ADD EVENT sqlserver.query_feedback_validation, ADD…
Read More
19Aug 2019 by Grant Fritchey 19 Comments

Profiler and Trace vs. Extended Events

SQL Server
It's a running joke among the more experienced (read, older) Microsoft Data Platform specialists as to whether you're #teamprofiler or #teamexevents. I'm very much #teamexevents, but I really don't care that you're #teamprofiler. If you want to use the old way of doing things, that's fine. However, I do have a bone to pick. Why on earth would you recommend to new data professionals working on modern systems, let's say at least 2016 or better, who are just getting going on this journey, that they should be using Trace? What are you smoking? Trace vs. Extended Events We're not just talking about where you are comfortable. I get it. You've used Trace/Profiler for 20+ years. Getting it going is all muscle memory. That's fine. As the kids say, you do…
Read More
19Mar 2018 by Grant Fritchey 1 Comment

Query Monitoring and the GDPR

DevOps
I've been reading the General Data Protection Regulation (GDPR) and discussing the ramifications of the beginning of enforcement with lots of people. The implications of it all are fascinating. The real serious issues remain primarily a business problem, with business defined solutions. However, there are technology issues that we need to think about. For example, performance metrics are going to be impacted by the GDPR. Private Data and Monitoring Queries First and foremost, let me say something I've said before. The vast majority of the focus around GDPR has to come from your business. Second, the bulk of your work and focus must be on ensuring core functionality in support of the GDPR. Third, the attack vectors and leaks for GDPR are not going to primarily be around something like…
Read More
07Nov 2017 by Grant Fritchey 19 Comments

Extended Events and Profiler: XE Profiler

SQL Server 2017
There's a war on in the SQL Server world. On the one side is Profiler (although, really, everyone uses Trace Events). On the other, the "new" (they came out in 2008 with a full GUI in 2012, so...) Extended Events. Lots of people have picked sides on this, including Microsoft. New Trace Events There are none. All the new functionality of every sort from Availability Groups to Query Store to R & Python, have Extended Events created for them. Trace Events, and the technologies supporting them in the form of Profiler, are a dead end. Don't fear. While Trace is on the deprecation list, there doesn't appear to be any fear of that technology being removed completely. At least it won't be removed in the foreseeable future. A future which,…
Read More
28Oct 2009 by Grant Fritchey 6 Comments

Snags With Profiler GUI

SQL Server, T-SQL, Tools
Running the Profiler GUI against a production server is not something you should do. I've outlined my research into exactly why in the past. But I hit another little issue with the Profiler GUI as part of work I'm doing on a Microsoft PSS call (more on that in another post). We have a procedure on one of our systems that is erroring out, but only on a particular server and only when called from the application, not when it's called, on the same server, from SQL Server Management Studio. I needed to capture some trace events and do it quickly, so I decided to use the GUI, just this once. I put filters on it so that I would only collect certain data, and the database I was collecting…
Read More