One data type that’s often been quite controversial is the bit data type. So a bit is where we have an individual value, which is either a zero or one. They’re the only values allowed unless it’s NULL.
Always keep in mind when you’re working with bits in SQL server that they’re aren’t just 2 potential states, there are three. It can be zero, or one, or it can be NULL. That is, it can have no value at all.
2018-12-31
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
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
I’ve mentioned before that SQL Server Management Studio (SSMS) is a good tool for analyzing queries, as much as for executing them.
In SQL Server 2005, query plans had missing index details added. When a query plan was created, SQL Server recorded that it thought it could have executed the query better, if only you’d provided it with appropriate indexes. But at that point, the suggestions weren’t very good, and the tools didn’t show them.
2018-12-13
Years ago, it was common to need to print checks (cheques) from computer systems. Fortunately checks have now pretty much disappeared in most countries. One of the challenges with printing a check, was that you needed to convert an amount of money into words. Even though checks are on the wane, it’s still important to do that on some contracts, etc. In our free SDU Tools for developers and DBAs, we added a function NumberAsText to do just that.
2018-12-12
