If there’s one thing every Scooby-Doo episode teaches us, it’s that solving mysteries is a team skill. The gang doesn’t stumble onto answers by luck (well… except when Scooby and Shaggy fall into secret passages). They solve the case because they know how to spot the clues and put them together. Performance tuning in SQL Server works the same way. If you don’t know where to look—or how to interpret the trail of breadcrumbs—you’ll spend more time running from shadows than unmasking the real villain. I see this practically every day. SQL Server Always Leaves Clues Just like Velma dropping and leaving her glasses behind, SQL Server leaves clues all…
-
-
Quick Tip Remove CONVERT/CAST from your WHERE clauses and JOINS when comparing to variables of different data types. Set their data types to match your table definitions before using them as a filter. Optimizing your queries this way will greatly reduce the amount of CPU time, reads, and I/O generated in your queries and allow your code to take better advantage of indexes. Example We are going to create a very simple stored procedure called ConvertExample. In this procedure we will see two things. One, the first procedure we create will declare two variables as VARCHAR( MAX) data types, then in the WHERE clause it will convert a table column…
-
I saw this again recently and see it too often in environments so wanted to take a second to remind everyone to cycle their error logs on a regular basis. SQL Server keeps error logs and when you reboot or restart SQL Server services the logs are cycled and a new one is created. Depending on how many logs you have configured for SQL Server to have this may include removal of the oldest log as well. Since many of pride ourselves on keeping our SQL Servers up and running, reboots may be few and far between thus our logs get large in size. When they grow out of control…
-
Are you using your Model Database to its full potential? I am finding more and more that Database Admins are not using the Model database to its fullest potential and some not at all. What is that Model Database for? The model database is basically the default setup (template) for all other databases created on a SQL Server instance. All databases created after install will inherit the properties of this database. Why Configure It? Using the model can insure consistency within your environment and is a quick way to automate your database setups. Below is a list of things I’ve used in my environments and others. Top (in no particular…
-
We have all made mistakes in our careers, I thought I’d share one of mine as a quick tip to others so that you don’t make the same one. Everyone has their SQL Alerts setup right? If not, I have included the script below and here is the MSDN link to find out more (https://msdn.microsoft.com/en-us/library/ms180982.aspx). For those who have setup their alerts, how many of you have remembered to set the DELAY BETWEEN RESPONSES setting? When I worked at the Port of Virginia, I was a little less experienced in SQL and didn’t notice this lovely little option. I of course failed to set it. Can anyone guess what happened?…
-
I think sometimes those of us that have been doing database administration/development for a while take it for granted that everyone knows the basics. One such basic is parameterizing stored procedures. This allows us to potentially consolidate multiple stored procedures into a single procedure. It’s as simple thing to do that many don’t. I try to parameterize as many stored procedures as possible. This not only minimizes the amount of procedures I need to maintain, it in my opinion is a much cleaner way to code. It disturbs me when I see multiple stored procedures that pull the exact same data, but may have slight differences between them. Whether it…
-
Wonder if I Do Things Differently? I am always wondering what other DBA’s do and if I am doing things differently. One such thing is my initial server setups, basically, what I configure for each of my new servers. So, why not blog about it and see what others chime in with after they read this. Keeping in mind that everyone has different requirements and different ways that they like to do the actual configurations. For now, I am not going to go into what each one of these configurations do and why I choose the value I do. That’s for another time. If you want that information you can…
