In an earlier post, I described how query plans could be saved as .sqlplan file, shared, and loaded again in SQL Server Management Studio (SSMS). It’s also possible to extract them out of SQL Server Profiler or Extended Events Profiler.
This is useful, but the same applies to deadlock graphs. SQL Server 2005 added Deadlock graph as a type of event in SQL Server Profiler. (It’s also part of Extended Events Profiler).
2018-12-27
I’m often wondering about how large different objects in my databases are. There are reports to provide that info, but I often want it in a programmatic form. One of our free SDU Tools for developers and DBAs does just that. It’s ListUserTableAndIndexSizes.
You can see how to execute it in the main image above. The procedure takes these parameters:
@DatabaseName sysname - This is the database to process @SchemasToList nvarchar(max) -> ‘ALL’ or comma-delimited list of schemas to list @TablesToList nvarchar(max) -> ‘ALL’ or comma-delimited list of tables to list @ExcludeEmptyIndexes bit -> 0 for list all, 1 for don’t list empty objects @ExcludeTableStructure bit -> 0 for list all, 1 for don’t list base table (clustered index or heap) @IsOutputOrderedBySize bit -> 0 for alphabetical, 1 for size descending
2018-12-26
Hi folks,
It’s been a big year again and the number of readers just keeps increasing quite fast. So pleased to have you all here. You make it all worthwhile. Thanks to so many who reached out during the year.
Just wanted to wish you all the best for the holiday and Christmas season, from here down under.
Greg
2018-12-25
A pet dislike of mine is application developers creating connection strings, but without Application Names. That’s painful because when you view the processes in SQL Server Activity Monitor, or look at commands in traces from SQL Server Profiler or Extended Events Profiler, you see this:
If your connection just says .Net SqlClient Data Provider, no-one has any idea what application that is. If you are using another connection library, it might say a different name but the concept is the same. You are making management of the server much harder than it needs to be.
2018-12-24
System configurations are becoming more complex all the time. Each and every server, container, and key application and service today has log files that tell you a wealth about what’s going on under the covers. But how accessible are those log files in your organization?
If you aren’t using a log management tool, you probably should be.
Here are a few easy ones to get started with:
One thing that I do find frustrating with Microsoft tooling at present is the constant churn of product names. A while back, we had Application Insights that could collect details of what was happening within an app. The data for that was stored in a tool called Log Analytics, and it could also collect operating system logs and more. Agents were provided for on-premises systems.
2018-12-20
Currently, SQL Server query plans are stored as XML. You can see what they look like by right-clicking in any query plan in SQL Server Management Studio (SSMS), and clicking Show Execution Plan XML:
That will return a whole bunch of XML like this:
It’s important to understand that when SSMS is showing a graphical execution plan, it’s just graphically rendering some XML like the plan above.
The Properties window in SSMS is also showing details extracted from that same XML.
2018-12-20
Hi Folks, whenever a new version of SQL Server is starting to appear, I try to have a series of podcasts that discuss what’s happening with it. Now, SQL Server 2019 isn’t that far off so I thought it’s time to start discussing the features in it, in depth.
First up, it’s Argenis Fernandez (@DBArgenis) from the SQL Server team. Argenis provides an awesome discussion on the use of non-volatile memory systems and how he sees them as a game changer.
2018-12-19
SQL Server databases are often littered with indexes that aren’t needed. In many cases, people won’t even remember why they were created in the first place. So I’m often checking out indexes that might be candidates for removal. One of our free SDU Tools for developers and DBAs does just that. It’s ListUnusedIndexes.
You can see how to execute it in the main image above. The procedure takes just one parameter:
2018-12-19
It’s that time of year again when people look philosophically back at the current year, and start to think about how they’ll improve their situation in the new year. But for many, this is a very hard time of the year.
I’ve had a few friends this year who seem to have almost lost hope when they’ve ended up in poor situations.
I just wanted to make a short post to encourage you all to never lose hope. Alexander Pope said:
2018-12-18
One of my colleagues Orin Thomas is a prolific writer. I’ve lost count of how many detailed books he’s written, and I’ve no doubt he’s lost count as well. If you’ve worked in Microsoft-related IT for any length of time, I’m sure you’ll have read one of his books, particularly if you’ve been involved in certification. You can see a partial list of his books here.
But the other thing that amazes me about Orin is that he’s also a prolific reader. I can’t believe how many books he gets through, and he’s inspired me to get through way more. I have not the slightest doubt that being a good reader is a prerequisite for being a good writer. Every time I meet with Orin, he mentions books that I should read. I note them down, and slowly make my way through many of them.
2018-12-14
