The strangest SQL Server Agent problem you will ever meet!

I came across this issue recently and I found it to be logged nowhere. It was a nightmare to troubleshoot and thus I would like its resolution to stay publicly available – because frankly there is 0% chance anyone will be able to solve it on his own. I was lucky to be helped by a…

2

How to do a quick SQL Server healthcheck

There is sometimes the need to do a quick SQL Server healthcheck. Usually this is needed before a maintenance or a migration takes place or after a problem has been resolved. The time given to you for this typical healthcheck is usually short, so let’s see how you can do this in a quick and efficient…

1

How to use Extended Events to proactively monitor your SQL Server for Deadlock issues

Usually after a deadlock occurs, we don’t have the needed information to troubleshoot the deadlock issue. This is because in order to troubleshoot a deadlock issue we need verbose information, so we need to have certain trace flags enabled (e.g. 1204, 1222). This is usually a problem because after setting up these trace flags, you…

2

SQL Server default settings that you might want to change

  One very common complaint I receive from SQL Server administrators is: “Why is not SQLServer performing fine out of the box, with the default settings?”   My answer is that SQL Server is like a car. With the default settings it will run fine for most people. But if you want a racecar you…

0

Specialized Performance Troubleshooting (Part 3: How to identify storage issues at a SQL Server box)

This week I will explain how to detect and analyze storage performance issues. The first step is to collect a Perfmon trace and check these two counters for each disk:Physical Disk: Avg. Disk Sec/ReadPhysical Disk: Avg. Disk Sec/Read The recorded values should be less than 20 msec for attaining good performance. They will have to…

0

Specialized Performance Troubleshooting (Part 1: How to troubleshoot Forwarded Records)

This week I will discuss how to troubleshoot Forwarded Records issues.   What are Forwarded Records? If you have some time, you can read the full story at Paul Randal’s blog post: http://www.sqlskills.com/blogs/paul/forwarding-and-forwarded-records-and-the-back-pointer-size/ If not, I will tell you the short story: “They are a performance overhead that can occur when you insert rows into…

0

How to troubleshoot SQL Server performance issues with simple tools (Part 3: The Profiler)

  This week we will show how you can use another simple tool to capture useful data for doing a performance analysis of SQL Server. This tool is included in the SQL Server installation and it is called the Profiler tool. It is very useful in troubleshooting Performance issues, because you can identify which queries…

0

How to troubleshoot SQL Server performance issues with simple tools (Part 2: How to analyze the Perfmon trace and detect SQL Server performance issues)

We have already shown how we can detect IO bottlenecks inside a Perfmon trace. Now it is time to see how we can detect SQL Server issues inside the same trace. We will use these Objects/Counters:   – SQLServer:Access Methods: Forwarded Records/sec These should be below 10% of the Batch Requests/Sec. If not, this means…

2

How to troubleshoot SQL Server performance issues with simple tools (Part 1: How to analyze the Perfmon trace and detect IO bottlenecks)

So you have finally collected the Perfmon trace. Good job! Now, how can you analyze and evaluate this data? It is easier than it seems:   1. Open Perfmon and go to the Performance Monitor tab. 2. Right click on the graph and choose to Remove All Counters 3. Right click on the graph and choose…

0