A Fine Slice Of SQL Server
Edwin Sarmiento shows a practical application of Query Store:
In a previous tip on SQL Server 2016 Query Store Introduction, we’ve seen how to enable Query Store and find out the top resource consumers. We’ve experienced high CPU utilization recently and wanted to know the root cause and how to fix it. How can we use Query Store to achieve this?
Read on for the scenario and how Edwin diagnosed and fixed the issue.
Comments closedBen Johnston shares some advice:
An entire post on choosing the correct data types either seems like overkill or much needed and overdue. The perspective might vary based on the databases you’ve worked with recently. I decided to write this after seeing some code with data type decisions that I would classify as questionable. There are many decisions in technology that can be ambiguous, but the correct date type should be based on business rules and a set of technical guidelines. I am going to share my thought process for deciphering the correct type here.
Selecting a data type is an important part of database and table design. The column represents an actual business attribute, is used to support data integrity, or is used for performance considerations. Care should be used when selecting the definition for each column. Choosing the wrong type can impact each of these areas, makes the system difficult to work with, and makes integrations harder than necessary.
Read on for Ben’s selection criteria and further thoughts. Most of it I find quite sensible.
I do, however, strongly disagree on part of Ben’s Unicode character strings recommendation, as I am an NVARCHAR Everywhere kind of guy. My counter-recommendation is always to use NVARCHAR over VARCHAR (I tend to be a bit more flexible about NCHAR vs CHAR, as those are typically for flags versus user-relevant data), and use row-level or page-level compression on indexes wherever it makes sense. If you don’t have any characters outside of your codepage in any row of that data, the size will be the same as with VARCHAR. If you do have the need for special characters, you don’t need to rebuild everything from scratch as part of a half-year (or longer) internationalization program. And if you’re consistent about always using NVARCHAR, you also eliminate implicit conversion risk.
Comments closedHugo Kornelis continues a deep dive into cursors:
One of the things that has always bothered me about the fast forward cursor type is the shocking lack of documentation of what it does exactly. Okay, the name suggests that it is fast. But is it really? When I first looked at cursor performance (granted, a long time ago), I found that a static cursor was actually faster than a fast forward cursor for the same query. So… fastish forward?
The name also suggests that this cursor is forward only. That is indeed the case. At least they got that right in the naming.
Read on to learn more about this cursor property, as well as how it compares to static and dynamic cursors. Hugo ends on a spicy take you won’t want to miss.
Comments closed