Now, you have a meaningful list of wait statistics that will tell you exactly why, if not where, your server is running slow. Unfortunately, these waits still need to be interpreted. If you read further on Paul’s blog, you’ll see he has a number of waits and their causes documented. That’s your best bet to start understanding what’s happening on your system (although, I hear, Paul might be creating a more complete database of wait stats. I’ll update this blog post should that become available).
Wait stats are fantastic tools for figuring out your server resource limitations given the server’s historic query workload. They’re the first place to look when experiencing server pains.
IT professionals (and amateurs), it’s time we had a chat. It’s time to stop dragging and dropping (or copying and pasting) files between servers and/or workstations.
It’s clumsy. It’s childish. It uses memory on the server.
Oh, and there’s a really easy tool to copy files built into Windows – Robocopy.
The syntax is pretty easy and robocopy handles small files well. Check out Nic Cain’s comment, though, if you’re going to copy large files in production.
This month I challenged the blogging community to share their own creations in Power BI. We got a ton of great entries this month, thank you everyone who participated! My overarching goal for this month’s topic was to get folks who may not normally play in the BI space to use this fantastic solution and maybe get some ideas flowing on how they may be able to apply it in their everyday work.
The part I like most about T-SQL Tuesday is that it introduces you to a whole new set of bloggers and a whole new set of perspectives on any particular topic.
Only want to use in specific scenarios. Basically, from various sources, I’ve always heard that these explicit query hints should typically be designed for edge cases or specific scenarios that are tested, documented, and known to scale appropriately. Anytime you introduce hints, you are taking control from the query analyzer and indicating you know best…. This might be the case, but test test test!
OPTION(RECOMPILE) is like dynamite: use it to blow up big problems, but understand beforehand what’s going to happen.
It was Greg, who suggested that we form a book reading club. Our first book was one I had heard about, but never read – The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling By Ralph Kimball. As a business analyst, I had leaned heavily on Excel, along with a mishmash of other technologies. Data warehouse and data modeling didn’t seem like topics that would be relevant to me; more for an IT/BI team perhaps. But I figured, it couldn’t hurt to learn something new.
Our book club meetings looked more as if, class was in session. We brought in our questions, and Greg patiently answered them, helping us realize the importance of the topics, and trade-offs involved in various choices. As things go, our reading club was disbanded before we were even halfway through the book. But the knowledge that I had gained, helped me grow by leaps and bounds in my Power Pivot and Power BI journey.
Kimball-style fact-dimensional modeling remains a brilliant solution.
This post investigated two potential workarounds to either buy you time before changing your existing
IDENTITYcolumn, or abandoning
IDENTITYaltogether right now in favor of a
SEQUENCE. If neither of these workarounds are acceptable to you, please watch for part 4, where we’ll tackle this problem head-on.
This is your weekly reminder to plan for appropriate data sizes.
For my contribution to this contest I’ve decided to share with you a work in progress. If you know me, I’m a huge lover of Policy-Based Management. In fact, I’m actually part of the Enterprise Policy Management Framework (EPMF) project on Codeplex. T-SQL Tuesday event is normally a DBA-centric event so I figured I’d help the DBA crowd wrap their heads around how a BI solution can help them in their day to day.
What I did to kick start this effort was to create this Power BI report that allows you to explore the database repository that contains the EPMF policy evaluation results. The current EPMF project uses Reporting Services to deliver its reports. This won’t change. If anything I’ll be exploring new capabilities with SQL Server 2016 and R-integration. Here’s a screenshot of what the SSRS dashboard report looks like:
I like this post because most Power BI examples tend to be personal (Fitbit stats, etc.) or business-y. This is a good example of a use of Power BI for back-office database administrators.
Medians as a concept are simple enough. If you have a large number of values, like a range of statistical values, you want to pick the middle one. The median, as opposed to the average is useful for a number of reasons, one of them that you can reduce the effect of so-called outlier values.
The fact that SQL Server doesn’t have a fast, built-in median function surprises me, to be honest. The best alternative I’ve found was a CLR function in SQL#.
In a recent blog post entitled Is Logical Data Modeling Dead?, Karen Lopez (b | t) comments on the trends in the data modeling discipline and shares her own processes and preferences for logical data modeling (LDM). Her key point is that LDMs are on the decline primarily because they (and their creators) have failed to adapt to changing development processes and trends.
I love all things data modeling. I found data models to be a soothing and reassuring roadmap that underpinned the requirements analysis and spec writing of the Dev team, as well as a supremely informative artifact of the Dev process which I would constantly refer to when writing new T-SQL code and performing maintenance. However, as time has passed, I have been surprised by how far it has fallen out of favor.
This is an interesting discussion. I’m not sure I’ve ever created a true logical data model. I’ve worked with systems which could potentially take advantage of them, but they never hit the top of the priority list.
SQL Server offers two flavors of optimistic locking for traditional disk-based tables: Read Committed Snapshot Isolation (RCSI), and Snapshot Isolation. They are each great tools to reduce blocking and make applications faster, particularly in databases that have lots of tiny reads and writes that need to be quick, or mixed-use patterns (lots of little reads and writes + larger reporting queries).
Both isolation levels are available in SQL Server Standard Edition and Web Edition as well as Enterprise. That’s right, they’re cheap and easy. Each of them are controlled by database level settings, and default to disabled when you install SQL Server.
The moral of the story: both of these are awesome, both have potential drawbacks, and both need testing. I’ve had good experiences with RCSI, but even then, maybe about 1% of procedures need specific locking hints (either NOLOCK or an explicit lock) to maintain previous application behavior and to deal with the problem Kendra brought up. Moral of the story: test, test, test.