Category: Back to Basics

DMV’s for the Beginner

  By ,

I give performance presentations at many different events to all levels of SQL Server professionals. Over time I’ve noticed that some DBAs and developers have never looked at the dynamic management views (dmv’s)  that are available within SQL Server starting with SQL Server 2005. These DMVs are useful means to determine what is going on with a particular SQL Server instance. So, in this post I am going to list a few of my very favorite DMVs as a reference for those who may be new to them. Performance Tuning These dynamic management views are what I use first when… Continue Reading

What is Implicit Conversion?

  By ,

Another quick post of simple changes you can make to your code to create more optimal execution plans. This one is on implicit conversions. An implicit conversion is when SQL Server must automatically convert a data type from one type to another when comparing values, moving data or combining values with other values. When these values are converted, during the query process, it adds additional overhead and impacts performance. Here is a great chart by Microsoft that shows you conversions and which will cause an implicit or explicit conversion. In this post I will not go into explicit, just know… Continue Reading

What’s a Key Lookup?

  By ,

One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index seek against a specific table and that index does not have all of the columns needed to fulfill the result set. SQL Server is forced to go back to the clustered index using the Primary Key and retrieve the remaining columns it needs to satisfy the request. A RID lookup is the same operation but is performed on a table with no clustered index, otherwise known as a heap.  It uses… Continue Reading

Enable Mail Profile – Back to Basics

  By

I’ve seen many people go through the trouble of setting up database mail and configuring SQL Agent Alerts only to realize it’s not working. The reason in the cases I’ve seen is because they have simply neglected to assign a mail profile to SQL Agent. This is way more common than you would think, thus I wrote this quick blog. This check box and drop down can sometimes lead to hours of troubleshooting if you don’t know where to look. In order to receive the alerts, you must enable a mail profile. This would be the profile  created during the… Continue Reading

Scheduling things to Run in SQL Server

  By

A key part of the SQL Server Agent is the ability to schedule jobs. While you can create one schedule for each agent job, frequently with applications like Reporting Services, users use Shared Schedules across multiple jobs. For instance, you can set a schedule to run at 8 am on week days or run every 2 hours or pick from a predefined list of schedules that already exist in the MSDB database. These are very convenient. However, if you choose to use these be sure you are keeping track of what is running for each of these shared schedules. You… Continue Reading

Cycle SQL Server Error Logs

  By , ,

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… Continue Reading

Careful with your SQL Server Max Memory settings

  By , ,

Quite often I see database administrators set SQL Server max server memory thinking everything related to SQL Server uses this shared memory pool. This is a mistake. There are many things that rely on memory that are not part of SQL Server. Best practices state that you should leave memory allotted for the operating system. However, did you know that if you are running services like SSIS, SSAS or SSRS on the same server as the database engine that it does not use the same memory you have allocated for SQL Server? If the Max Memory setting is not configured… Continue Reading

Live, Learn, and Grow

  By ,

Ever look back at old work and wonder what was I thinking when I did that? Or even better you mock and get disgusted over some code you stumble upon and then realize you wrote it? Today is one of those days. Lucky for me one of my clients is a company I used to work for. It’s awesome to be able to go back and work in an environment you are familiar with. However, with that, you realize when you worked there you made some decisions or wrote some code that you now shake your head at. On the… Continue Reading

Template Explorer Gold Mine in SQL Server Management Studio

  By

Sometimes as a newbie to SQL Server using SQL Server Management Studio (SSMS) you don’t know about the little gold mines that are within the product that help you be a better DBA or Developer. Template Explorer is one of those. To get to it go to VIEW from the taskbar then Template Explorer. Alternatively, you can reach it with keyboard shortcut Control+Alt+T. When you click on it, another side window will appear showing you a list of all templates available within Management Studio. A basic example would be creating a new stored procedure, this template provides an easy starting… Continue Reading

SQL Server Updates Tricked Me for Years

  By ,

When applying a new SQL Server cumulative update, hot fix, or upgrade SQL Server doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you had already stay at their pre-upgrade compatibility level, which means they run under the older set of optimizer rules. Additionally, many optimizer fixes are not turned on. The reason for this is that while they may improve overall query performance, they may have negative impact to some queries. Microsoft actively avoids making breaking changes to its software. To avoid any negative performance impacts, Microsoft has hidden optimizer fixes… Continue Reading