A Fine Slice Of SQL Server
Michael J. Swart explains why we can’t have nice things:
I spend a large amount of time translating software requirements into schema and queries. These requirements are sometimes easy to implement but are often difficult. I want to talk about UI design choices that lead to data access patterns that are awkward to implement using SQL Server.
Read on for three such examples, including sorting, paging, and search.
Comments closedMonica Rathbun shows us how to use DBCC INPUTBUFFER:
A command I like to use when performance tuning is DBCC INPUTBUFFER. If you have ever run sp_whoisactive or sp_who2 to find out what sessions are executing when CPU is high for instance this can be a real quick life saver. At times, for me, those two options do not return enough information for what I’m looking for which is the associated stored procedure or object. Using this little helper along with the session id can easily get you that information.
Let’s take a look.
Let’s.
Comments closedMelissa Coates has a new document:
For the most part it’s targeted to orgs who have Power BI deployed to a certain extent, yet know there’s room for improvement. We focus mostly on the harder things that are more difficult to manage than the technology itself.
Although I did the writing and diagram creation, I did so with Matthew Roche’s direction, advice, and guidance. That man is a wealth of knowledge. If you’re reading this post, then you’re probably familiar with his series on building a data culture. If you haven’t reviewed that series thoroughly, please do. You’ll recognize a lot of common themes from his content in this new adoption roadmap.
Click through for some Q&A and information on where you can get the roadmap.
Comments closedJared Poche shares a few queries with us:
The QDS Toolbox is set of tools that can help you review and store the performance related data in Query Store. This was released by ChannelAdvisor last September thanks to the hard work of a number of my coworkers.
If you aren’t experienced with Query Store, this can provide a good starting point for getting familiar with data that is available and what you can do with it. If you are experienced with Query Store, this may give you an easy way to set up customizable reports that help you find issues and see trends.
The QDS Toolbox has several components, and I intend to post about each in turn. Two new components were added to this recently by @sqlozano (https://www.sqlozano.com/), bringing the current total to eight.
Click through for a deeper dive into the Server Top Queries report.
Comments closedHugo Kornelis wraps up a discussion of temporal tables with miscellany:
Of course, it will be a quite common requirement to query products and their suppliers. When querying the present, you can just access the Suppliers and Products table without the “FOR SYSTEM_TIME” keyword, and the execution plan will unsurprisingly show that a regular join of the two “current” tables is used, with no reference to the history table. Nothing special. We already saw in the earlier parts that querying the present simply ignores the history table; joining does not make that different.
But what if, for instance, we want to show all data as it was valid on June 3 at noon? Well, that is also simple. We already know that we can use FOR SYSTEM_TIME AS OF” to get the rows from a single temporal table as they were at a specific time. We can use that syntax for both tables, to get the data we need:
Read on as Hugo dives into some messy problems. Temporal table queries can expand out in complexity very quickly, as this post shows.
Comments closedGilbert Quevauvilliers continues a series on moving to Power BI Premium Per User:
In this blog post I am looking at how to load or reload historical data in AAS and PPU and compare the differences.
It should already be noted that I am only going to compare tables where I have partitions created and enabled. The reason being for dimension tables it is typically quick and easy to reload the data by re-processing the data for the table.
Read on for the details.
Comments closedPhil Factor has a bit of Powershell for us:
How do you list all the objects and values within a PowerShell object, investigate an object or explore its structure? There must be a simple way. I used to use ConvertTo-JSON. This is fine up to a point but what if you just wish to search for strings or look for objects with a cartain name or value? What if you need their path so you can reference them? It isn’t plain-sailing. In the ISE, the intellisense will help you a lot but I want more and I want to do it in script rather than the IDE
Read on to check it out and get a copy of the cmdlet.
Comments closedAndy Mallon says it’s time to give up a couple of procedures:
We recently ran into some performance problems with our login sync, which is based on
sp_hexadecimalandsp_help_revlogin, the documented & recommended approach by Microsoft.I’ve been installing & using these two procedures since I started working with SQL Server, back at the turn of the century. In the nearly two decades since, I’ve blindly installed & used these procedures, first on SQL Server 2000, and then on every version since… just because that’s the way I’ve always done it. But our recent performance problems made me rethink that, and dive in to take a look at the two procedures to see if I could do better, which made me realize, OHBOY! WE CAN DO BETTER!!
Read on to understand how.
Comments closedGrant Fritchey reviews minimum requirements:
I was recently asked what permissions were needed to force plans in query store. I’m sure I knew at one point, but at the moment I was asked, I couldn’t remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here’s what I found.
Click through for the disappointing answer.
Comments closedCamila Henrique explains the importance of knowing SQL:
I wanted to talk about this because I see a lot of doubts and lack of direction from people who are either beginning now in IT land or thinking about switching careers. The short answer to “do I need to know SQL for a data job?” , is yes. In the next few paragraphs I explain why I think so.
It’s easy to get caught up in all the fancy programming languages and methodologies for projects that sometimes the basics… are just not there. I believe having a good foundation opens paths to other doors that you could not see before. And I’m certain that SQL is one hell of a foundation to have in the data land.
This is also a good time to slip in a reminder of Feasel’s Law.
Comments closed