A Fine Slice Of SQL Server
Kenneth Fisher has a script for us:
It’s been a while since I worked with compression and the other day I needed to check which of my indexes were compressed and which weren’t. Now, I knew the information wasn’t going to be in sys.tables and I couldn’t find it in sys.indexes or INDEXPROPERTY(). I’ll be honest it had me stumped for a little bit. Until I remembered something!
Read on for Kenneth’s remembered insight and a script to get the job done.
Comments closedAlways Encrypted protects our data both at rest and in transit. To accomplish that, SQL only stores the encrypted data and cannot decrypt it; all the work is done by the client. In our case the client is SSRS and it is the account running the SSRS service that will need the certificate to decrypt data. Note that it is not the account running the report.
Click through for the solution.
Comments closedStephanie Bruno has updated a useful tool:
If you’re like me, building a data model in Power BI is an iterative process. Sometimes you have to try out different ways of writing measures before you hit on the one that’s right. You end up with temporary measures that don’t actually end up being used in visuals. You may also pull in more columns than you might end up needing, just in case. When you’ve finally finished your masterpiece with measures and visuals, there are probably quite a few that you don’t need. Two problems with this are that having extraneous columns and measures (1) can slow down your model and (2) can make it more difficult to maintain. You may also want to know where on your report a change to a measure will have an impact.
Click through for a demonstration of the solution.
Comments closedErik Darling talks about key lookups:
I go back and forth when it comes to Lookups.
On the one hand, I don’t think the optimizer uses them enough. There are times when hinting a nonclustered index, or re-writing a query to get it to use a nonclustered index can really help performance.
On the other hand, they can really exacerbate parameter sniffing problems, and can even lead to read queries blocking write queries. And quite often, they lead to people creating very wide indexes to make sure particular queries are covered.
Read on for one scenario around deadlocking due to key lookups.
Comments closed
Wolfgang Strasser checks out a small but helpful addition to Azure Synapse Analytics:
Good morning, day, afternoon or night – wherever and whenever you read this blog post! My day started with a nice surprise when I connected to one of our Azure Synapse workspaces …
Sometimes, it’s those little things that make (development) life easier – you can now add folders to structure the list of development artefacts in Synapse:
Read on to see how, including how you can bring order to the chaos of existing Synapse Analytics workspaces.
Comments closedMike Cisneros takes us through cases when waterfall charts are useful:
In our workshops, we often put a grid of a dozen charts up on the screen, and say to the participants, “Most of the charts you’ll need to communicate effectively in business are right here on the screen. 99% of the time, one of the visuals you see here will get your message across effectively. And as you can see there aren’t any really unusual charts here. You’ve probably seen all of these before.”
If, at this point, somebody in the room says, “Actually, I’ve never heard of a ______ chart before,” you can almost always fill in the blank with the word “waterfall.”
Waterfall charts are really useful in a few scenarios, but I see them get misused far too frequently.
Comments closedKathi Kellenberger has a follow-up of some questions after a PASS Virtual Summit session:
Last week, I presented a session on Intelligent Query Processing for the first ever Virtual PASS Summit. This summit had a mix of live and pre-recorded session. During the pre-recorded sessions, the speaker could hang out with the attendees in the discussion room and join a virtual chat room at the end. My session was live, so I answered questions a few questions during the session. There were a couple of questions that I couldn’t answer fully during the session, but all the questions were interesting, so I’ll go through them here.
Click through for the questions and answers.
Comments closedOn Thursday, I presented a session at PASS Summit entitled The Curated Data Platform. You can grab slides and links to additional information on my website. Thank you to everyone who attended the session.
During and after the session, I had a few questions come in from the audience, and I wanted to cover them here.
Most of the questions were around document databases, so check them out.
Comments closedJack Vamvas is looking for love files in all the wrong places:
I’m migrating some SQL Server databases to Openshift Containers. The SQL Server is set up with persistent disk , with a dedicated persistent disk partition for the SQL Server defaultbackup directory. I don’t have access to the underlying files via command line and can only use command line. How can I get the physical disk device , which will then allow me to create a RESTORE DATABASE statement pointing to the device?
Read on for the answer, including a T-SQL script to find where these files live.
Comments closedMatt Allington wants to trim the fat:
I have a saying in Power BI. Load every column you need, and nothing that you don’t need. The reason for this advice is that columns can make your data model bigger and less performant. You will of course need some columns in your data model for different purposes. Some are used for defining measures and some are used for slicing, dicing and summarising your data in the various visuals. But it is very common for people to load everything from the source, meaning that some of the columns are likely to be loaded but not used. Once the data model is ready and the reporting is done, it can be beneficial to remove the columns that are not being used and are not likely to be used for ad hoc reporting in the near future. The question is – how do you find the columns not being used? This is where Imke’s Power BI Cleaner tool comes in; I will show you how to use it below.
Read on for Seven Minute Abs for your Power BI data model.
Comments closed