Month: January 2018

SQL 101: Two places to start troubleshooting

Getting started

Someone calls you at 1 AM on Saturday and says, “Is there an issue with SQL?” First, breath and take a look at your logs. This post is about two places you can take a look to see if there’s an issue, quick, so you can get back to sleep. Especially when I was new to SQL Server, I thought fixing an issue would involve querying the server to see what was there. However, both of these locations are available through the GUI and are easy to get to. The two places I’d start looking are the Windows Server Application Log, and the SQL Server Log.

What to look for

In both of these locations, look for the last date time when you knew SQL was working. Then look messages about errors from SQL in between then and now.

1: Windows Server Application Log

My favorite way to read this log is with the app Event Viewer. You can search for this app in Windows or you can go to the file directory: C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Administrative Tools

Start up this app. By default, it shows the logs of your local computer. To connect remotely, go to the top left of the app, click Action -> Connect to another computer

Once you’ve got the app running, go to Windows Logs and click on Application. It’s as easy as that. If you have multiple SQL Server instances running on the same server, you can look at the “Source” column to see what instance wrote to the log.

Here’s what you should see:

2: SQL Server Error Log

Another useful log. To find this one, you can go to the file location listed in the screenshot above. That’s for my local instance, yours will be slightly different since my instance is named “FOURTEENTWO” (SQL Server 2014, instance #2 on this server).

Or

You can open SQL Server Management Studio (SSMS). Connect to your server, in Object Explorer go to Management -> SQL Server Logs

Guaranteed?

No. I can’t guarantee that this will help you fix your issue. But I always forget it! It’s saved me a lot. Here’s a list of things that are logged in these locations, that I’ve needed to find:

  • When SQL Server started
  • When SQL Server crashed and where the log dump is stored from that crash
  • Changes in compatibility level of databases

 

SQL Saturday Nashville #698

Why pick my session?

Hi, I’m Arthur Daniels. I’m presenting Reading Execution Plans Successfully at SQL Saturday Nashville. If you’re considering attending my session, I’d really like to see you there! I deal with execution plans daily. They can be really gnarly, and it takes a lot of time to work through the larger execution plans. I wrote this session as a foundation for performance tuning. Some of the things I’ll be talking about are:

  • What an execution plan is, behind the picture in management studio
  • The execution plan cache and what it can be used for
  • Finding where to start reading an execution plan
  • Interpreting a lot of the information provided on an Index Seek
  • When to use Query Store, and when the execution plan cache could be better

Ask questions

I think the best place to ask a question is in person. I might not have the answer you’re looking for, but I know a lot of great places and great people that we can ask. If you’re stumped (especially on performance), I encourage you to come ask.

Who am I?

My name is Arthur Daniels. I work primarily on performance for SQL Servers at SAP Fieldglass. I spend a lot of my time finding ways to improve and rewrite queries, and occasionally adjust or add indexes. If you want to talk about something I covered or didn’t cover in my session, please feel free to catch me after my session!

Read the slides

If you want the latest version I’ve used, here is the SQL Saturday Minnesota where I last gave my presentation. I also provide my slides on my website, here‘s a copy of the slides when I first gave this presentation. I’ve been spending the time between presentations improving.

 

 

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close