A Fine Slice Of SQL Server
Michael Swart notes that cursor performance can be hidden from monitoring tools:
The
OPENstatement is missing fromsys.dm_exec_query_stats. I want to demonstrate that.
There are a few workarounds, and Michael even provides us a handy table, so read the whole thing.
Comments closedWhy would you want to do this? In my case I have a PowerShell function to accept an input object, convert it to a DataSet (a .NET representation of tables and the links between them), and bulk copy the data into SQL Server. However because the input objects can vary slightly with additional tables or additional columns I use the flexibility of SMO to create the tables and columns on the fly.
There’s a lot of power in programmatically defining objects. I’m not sure that’s a good default scenario, but there are times in which it can come in handy.
Comments closedRobert Davis hosted T-SQL Tuesday, and he has a nice roundup of posts:
Thanks to everybody that participated in this month’s T-SQL Tuesday. A big thanks to everyone who wrote a participating blog post, and a really huge thanks to everyone who read the posts shared by this month’s participants. If you follow one of the links on this round-up page, I will kindly ask that you leave a comment on a blog post that you read if it teaches you something, gives you a new perspective, or makes you think. A blog post is just a bunch of words until somebody reads it, and blog writers love it when they hear that their post resonated with someone on some level.
Thanks to Robert for hosting T-SQL Tuesday #74.
Comments closedChrissy LeMaire ran into a trust issue one one node of a cluster:
You know what’s scary as hell? When one node of an important cluster loses its trust relationship with the domain and you see the error “the trust relationship between this workstation and the primary domain failed”. That happened to me late last year with one of my SQL Server 2008 R2 nodes. The scary part was that I just didn’t know what to expect. The fix could be simple, or it could require a node rebuild.
Trust no one.
Comments closedGail Shaw warns against using Task Manager to figure out how much memory SQL Server is using:
Err, what? SQL Server’s not even using 100MB? I’ve just read 30GB of data and SQL Server’s not even using 1% of it’s allowed memory!!!
Or is it?
Spoilers: it is. Read the whole thing.
Comments closedTom Norman talks about setting up a data scrubbing server:
With that said, when I setup a Scrub server, it is in a very secure area where the data access is very, very limited. For example, in my current company, the server is in a separate domain from Production and QA/Dev. Only DBAs are allowed to access this server. If you have multiple DBAs at your location, you may want to even limit which DBAs have access to this server. Our goal is to automate the entire scrubbing process so no one has to access the data including copying backup files from Production and to a shared scrub location for QA/Dev to retrieve.
Scrub servers are a way of stripping personally identifiable or sensitive information from production data so developers can safely use the data in lower, less secure environments.
Comments closedGinger Grant talks about Personal and Enterprise Power BI Gateways:
The Personal Gateway takes the data and imports it into Power BI. If you want to extract data from a variety of different places such as an Oracle Database, and Excel Spreadsheets, the Personal Gateway will support this, and the Enterprise Gateway won’t. Remember the Enterprise Gateway only connects to three different data sources, and Excel and Oracle are not on that list. If you want to manage connection and refresh of the data as the administrator or provide access to the data to everyone who needs it, use the Personal Gateway.
It sounds like these are currently different enough that “both” might be the correct option within an organization, at least until Enterprise Gateway adds the missing features.
Comments closedSince then, I’ve wondered if ColumnStore indexes (both clustered and non-clustered) might help any of these scenarios. TL;DR: Based on this experiment in isolation, the answer to the title of this post is a resounding NO. If you don’t want to see the test setup, code, execution plans, or graphs, feel free to skip to my summary, keeping in mind that my analysis is based on a very specific use case.
I actually would have been surprised to find the answer here to be “yes.” Columnstore is designed with aggregation in mind, rather than pulling out a fairly small subset of the data.
Comments closedKenneth Fisher is looking for the database page tied to a particular row:
It’s not one of those things you have to do frequently but every now and again you need to know what page a particular row is on. It’s not terribly difficult. There is a virtual column called %%physloc%%, but unfortunately it’s not terribly useful on it’s own.
Finding a record’s page probably isn’t something you want to do every day; this is more one of those “once in a great while” activities which can help with troubleshooting.
Comments closedAaron Bertrand talks about naming standards:
I’ve long been a proponent of not caring about which naming standards you use, but I do find it very important that your standards follow these three basic rules:
The conventions make sense. You should be able to argue why the chosen convention is better than an alternative, and it can’t just be because you like it better. This doesn’t mean you have to win that argument, just that you should be arguing for something tangible.
The entire team is on board. You should all agree on a standard before implementation, and any changes you make over time should be by committee.
You don’t make exceptions. Even if you’re a one-person team, if you’re going to bother having a standard, it needs to be used consistently. It’s amazing how quickly exceptions can become the new rules.
If you want to talk subjectivity, I disagree with the idea that tables should be plural, as I tend to think terms of an entity (e.g., Person) which contains attributes, rather than the collection of entities which contain a specific combination of attributes. Regardless, “set a standard and stick to it” is important advice.
Comments closed