02Nov 2020 by Grant Fritchey 2 Comments

Database Fundamentals #29: Create Foreign Keys With Table Designer

Uncategorized
The purpose of a foreign key is to ensure data integrity by making sure that data added to a child table actually exists in the parent table and preventing data from being removed in the parent table if it’s in the child table. The rules for these relationships are not terribly complex: The columns in the two tables must be the same data type, although, if SQL Server can automatically, and correctly, convert the data you can get away with different data types. But don’t do that. It’s begging for an issue. Keep them the same and you won’t have any problems.The child values can be nullable, which means that any child data is unknown.The child data can also be required, meaning that you have to have the relationship, no…
Read More
12Oct 2020 by Grant Fritchey 2 Comments

Most Costly Statement in a Stored Procedure

SQL Server
A lot of stored procedures have multiple statements and determining the most costly statement in a given proc is a very common task. After all, you want to focus your time and efforts on fixing the things that cause you the most pain. You simply don't have the time to tune every single statement in every single procedure. So, identifying the most costly statement is vital. Happily, Extended Events are here to help. Finding a Costly Statement Query tuning is initially an act of discovery. Which queries, batches, procedures are inflicting the most pain on us. That pain could be measured a bunch of ways. The three most common, in particular order, are: Frequency with with a given query/batch/procedure is called.Resources used by that query.Length of time that it takes…
Read More
05Oct 2020 by Grant Fritchey 10 Comments

Assuming Good Intentions

Professional Development
With all the stuff going on around the world over the last year, maintaining some degree of mental stability can be quite a challenge. Speaking only for myself, while my family and I have been fine through all this, the changes to my situation, while mostly benign, have still had their challenges. Again, speaking only for myself, I've found those challenges do lead to negativity on my part. Further, that negativity can bleed into my communication with others, hurting that communication. I have seen others suffering from this as well. Whatever stresses they're going through clearly leads to negativity in their communications. Now, when we're looking at this situation, I'm negative, someone else is negative, we're going to communicate really poorly. So, how to address this? Well, there are a…
Read More
28Sep 2020 by Grant Fritchey 4 Comments

Capture Execution Plans Only For Long Running Queries

SQL Server
I love questions. Most of all, I love questions I can answer. I spotted this question recently: How can I use Profiler to capture execution plans for queries over a certain duration? Oh, that's easy. You don't use Profiler. You use Extended Events. Query_post_execution_showplan Extended events are just better than Profiler. Period. One of many things that is superior is the way in which the events are configured. Take for example query_post_execution_showplan. Here are the fields it captures: This event will capture execution plans plus runtime metrics. It can easily be filtered on any of the fields listed, and you can even add the database_name field if you want. So, to filter by duration is pretty simple: CREATE EVENT SESSION ExecPlansDuration ON SERVER ADD EVENT sqlserver.query_post_execution_showplan (WHERE ([duration] > (1000000)))…
Read More
23Sep 2020 by Grant Fritchey 7 Comments

Review: Stellar Repair for SQL Server

SQL Server
I was contacted by Stellar Info quite a while ago. They asked me to try out their software. I said yes, but I was really bad about getting it done. Well, I finally got off my bottom and did the job. So, let's talk about Stellar Repair for SQL Server. Stellar Repair Let's start with the most important piece of information you need: it works. The software itself is really simple to use and just does what you need, repairs your corrupted SQL Server instance. On that alone, I can recommend the tool. However, there are a few gotchas I ran into along the way. Mostly, little stuff. It's things a little polish in the UI and some clean up around language could help out. Don't get me wrong, I'm…
Read More
24Aug 2020 by Grant Fritchey 2 Comments

Extended Events: Filter on Stored Procedure Name

SQL Server, You Can't Do That In Profiler
I just received a question about Extended Events: What about filtering on the stored procedure name. You know I love writing and talking about Extended Events. The answer is, well, sure, we can do that. However, as with all things, there may be wrinkles worth being aware of. Let's examine this. Filter on Stored Procedure Name Let's create an Extended Event session that captures rpc_starting and rpc_completed: CREATE EVENT SESSION StoredProcedureName ON SERVER ADD EVENT sqlserver.rpc_completed (ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ), ADD EVENT sqlserver.rpc_starting (SET collect_statement = (1) ACTION ( sqlserver.database_name ) WHERE (object_name = N'AddressByCity') ); I've added a WHERE clause to the Extended Event to capture only those procedures that have an object_name equal to 'AddressByCity'. If we look at the output from these…
Read More
17Aug 2020 by Grant Fritchey 1 Comment

Azure SQL Database Automatic Tuning

Azure
I really like the automatic tuning aspect of Azure SQL Database. This post is not a full explanation and demo. Instead, I want to point out a small point of management in the portal and your ability to configure Automatic Tuning. Configure Automatic Tuning You can configure Automatic Tuning within the Azure portal one of two ways. First, you can configure it with your server: You can inherit the values from Azure, or manually define them. Regardless of your choice, if you look at the server, you can see that your automatic tuning is configured: This is true on the server, whether I make changes to the configuration, or, just take the defaults as I did. My confusion is on the databases. Let's look at one: If we look at…
Read More
10Aug 2020 by Grant Fritchey No Comments

My First Hand-built AWS CodePipeline

AWS, Deployment Pipelines
I've just completed my first, successful, AWS CodePipeline. I'm sorry to say it took me weeks to figure out just how simple this was. Frankly, it's embarrassing. I already posted how I used the example code supplied by AWS to build a full blown CI/CD process. However, I went back into all the tools and tore it all apart so that I could build it, myself, by hand, in order to better understand it. Code All The Things Let's first talk about the tools I used. I have my code sourced in CodeCommit. It's Git. I'm using AWS CodePipeline to manage the flow control. It's triggered by a commit to the main branch. It sets up an Ubuntu instance that I can use to do things with (you can use…
Read More
03Aug 2020 by Grant Fritchey 2 Comments

Capturing Queries Can Be a Pain

Uncategorized
The moment you decide you want to monitor your SQL Server instance for query behavior, you're going to be forced to make a bunch of choices. Further, all these choices lead to problems that you're going to have to deal with. Some of the problems are obvious. For example, you decided to capture all the statements run against the system. That's a lot of data you'll have to be prepared to manage. However, other problems are extremely subtle and can really be a pain. For example, how do you differentiate between a query run by a person in SQL Server Management Studio and the queries that SSMS itself runs against the system? Queries From SSMS Let's say I'm interested in capturing both the batch completed event and the statement completed…
Read More
30Jul 2020 by Grant Fritchey No Comments

Announcing: AWS Community Builders

AWS
I'm excited to be able to announce that the AWS Community Builders, a new program for those who help out with AWS technologies, has been launched. You can read the details here. I'm also excited to announce that I'm one of the first set of Builders in this new community. Building AWS Community Builders As I've worked to add AWS to my toolbox, both in the DevOps area and in the data management area, I've been impressed with the technology. I've also been impressed with the help you can get on it out on the interwebs. However, there wasn't any kind of organizing force behind the community. Different people were posting various things, but it was unclear how to tell who had a real finger on the pulse. AWS reached…
Read More