A Fine Slice Of SQL Server
Erik Darling might have found the Bermuda Triangle:
While helping a client out with weird performance issues, we isolated part of the code that was producing a whole bunch of bad plans.
At the intersection of bad ideas, there was a cursor looping over a table gathering some data points with a local variable in the where clause.
Click through to understand why this isn’t a great idea.
Comments closedJoe Celko has a moment of zen:
Human beings are not very good at handling nothing. The printing press didn’t just lead to civilization as we know it, but it also changed our mindset about text. When we wrote text manually on paper, a blank or space was not seen as a character. It was just the background upon which characters were written.
It was centuries before the zero was accepted as a number. After all, it represents the absence of a quantity or magnitude or position; how could it possibly be a number? Before it was accepted as a number, it was considered a symbol or mark in a positional notation to indicate that there was nothing in that position.
It’s an interesting riff, so check it out.
Comments closedJames Rowland-Jones has an update for us:
The importance of R was first recognized by the SQL Server team back in 2016 with the launch of SQL ML Services and R Server. Over the years we have added Python to SQL ML Services in 2017 and Java support through our language extensions in 2019. Earlier this year we also announced the general availability of SQL ML Services into Azure SQL Managed Instance. SparkR, sparklyr, and PySpark are also available as part of SQL Server Big Data Clusters. We remain committed to R.
With that said, much has changed in the world of data science and analytics since 2016. Microsoft’s approach to open-source software has undergone a similar transformation in the same period. It is therefore time for us to share how we, in Azure SQL and SQL Server, are changing to meet the needs of our users and the R community moving forward.
I never used ML Server (but have used SQL Server ML Services a lot), so that part of the announcement doesn’t affect me and I’m not sure how many organizations it does affect. Switching to CRAN R is a good idea and I appreciate that they’re open-sourcing the RevoScaleR and revoscalepy code bases. The one thing I’d really like to see in vNext’s Machine Learning Services is an easy way to update the version of R
1 CommentForrest McDaniel wants a zippier queue in SQL Server:
I recently had the pleasure of investigating a procedure that pulled from a queue. Normally it was fast, but occasionally runtime would spike. The spooky thing was the query was using an ordered index scan that should only read one row, but during the spikes it was reading thousands.
Surely there’s a rational explanation…
Spoilers: there was. And Forrest a’int afraid of no ghosts.
(sotto voce – I’m so glad that Forrest didn’t sneak in any Ghostbusters references so that I could do that here and be original.)
Comments closedDevin Knight has a video for us:
Power Virtual Agents empowers subject matter experts to build intelligent conversational bots, using a guided, no-code graphical interface. In this video you will learn how to monitor how successful your chatbots are at answering your users questions. Using the monitoring capability you will uncover areas of your chatbot that can be improved.
If I were familiar enough with Latin, I’d try a play on “Quis custodiet ipsos custodes?” with this.
Comments closedRandolph West dives into the archives:
Ten years of hindsight (and being able to read the wrap-up post with all the responses) gives me an advantage in this retrospective, I admit, but I didn’t find the thing I was going to write about anyway even though one or two people had a similar idea. And that, dear reader, means that I can write about one of my favourite performance secret weapons: the indexed view. It’s essentially a regular view with an index (or indexes) attached to it. Oracle calls them materialized views. Unlike a regular view which is simply a query definition, the indexed view persists the results, making it a lot more efficient to query that data:
Read on for more information.
Comments closedAaron Bertrand has some advice for us:
I’ve recently discussed a few features that Microsoft advises against using, and that I think you should forget exist, too. There was the case where a colleague constantly promoted the deprecated backward compatibility view
sys.sysprocessesinstead of newer dynamic management views (DMVs), and another case where a different colleague took down a production server using SQL Server Profiler.My latest run-in with things best forgotten is a new stored procedure with an
ntextparameter. I checked and, sure enough, the data type matches the schema for the underlying table. My mind started racing about these older data types to explain why we really shouldn’t be using them anymore:
I can’t remember the last time I saw new SQL Server tables created with ntext or other deprecated types, but apparently it happens.
If you want to troubleshoot a slow SQL Server Restore or just find out more about all the restore steps , use these techniques to give you an insight
Click through for the extended event session as well as a script to shred the resultant XML and a sample output.
Comments closedEric Cobb has a success story:
This image is an actual screen shot of one of our production SQL Server’s CPU usage, taken from our SQL Sentry monitoring system. Obviously this server was under some strain. Users were complaining that queries were taking 30+ seconds to run when they normally returned data in milliseconds. It eventually reached a point to where applications were timing out because queries were taking so long. After doing some analysis in SQL Sentry, we were able to determine what was causing the CPU spike, and came up with 3 nonclustered indexes to add to help alleviate the pressure. The screen shot you see here is what happened after I added the first index.
Click through to see the image as well as the major difference in CPU utilization from a single change.
Comments closedAlmost 18 months ago I compared Azure Analysis Services and Power BI Premium for large datasets. At that time, Azure Analysis Services was a clear choice, but it is almost time to update that post with a longer article. Because of time constraints, I just want to quickly review what changed so far, promising a longer and more detailed update in a few months.
Read on to see Marco’s synopsis of what has happened since then. For my money, Power BI Premium Per User is already at a place where I’d prefer it to Azure Analysis Services.
Comments closed