In a post I made a few months back I was going to research three avenues of optimizing a database. The first was a custom script created to monitor blocking, and the second was running a SQL Profiler trace to monitor high usage hours. The second was screwed a little and I blogged about creating a SQL Profiler trace to look for internal application queries that take over a minute to run. Granted there are several traces that can be run to monitor your database during high usage hours. The one I wrote about, I believe to be a very useful solution. The third, well that’s not very SQL Lumberjack of me. It was Research SQL Monitor (RedGate Tool) to monitor my database. The third is going to cost my department money. Which (Old Adage in a monotone voice) in these economic times anyway to save your department money and still exceed expectations will take you far in your career. My director asked me a question that I will never forget, in fact I will remember this for the rest of my career. I wanted two more physical drives for a new server. She asked me “If this was your money would you buy the drives?” My immediate response “I WOULD NOT HAVE BOUGHT THE SERVER!” Therefore, to stay true to form I will continue to write about being a Lumberjack without an axe, i.e saving the department money using your default tools. I’m going to explore some other methods of using SQL Profiler to troubleshoot database performance problems.
There are several ways to troubleshoot different areas of SQL Server. Using SQL Profiler is another way to be a SQL Lumberjack. I would like to explore all of these areas over the next few months. First, lets dive into the ever popular “How to Identify Memory Problems”
In my research I have found there are a few key types of events that can help identify memory leaks. I would recommend reading each one before kicking off your trace. They are: (I also included the path to each event in SQL Profiler)
- Hash Warnings Events > Errors and Warnings > Hash Warning
- Sort Warnings Events > Errors and Warnings > Sort Warnings
- Execution Warnings Events > Errors and Warnings > Executing Warnings
- Server Memory Changes Events > Server > Server Memory Changes
It’s a good idea to run this trace over a 24 hour period. If your trace finds memory leaks, keep in mind it may take several traces to find any. If you ran a trace and in 10, 20, 30 minutes and you hit several events that is a cause for concern. It’s also a good idea to run a secondary trace with the following additional events. Again I would recommend reading up on each event before you run a trace. You have to know what your looking for.
Now that your trace has completed what are we looking at. The big thing you want to look for is Execution Warnings and Server Memory Changes. The others are for the most part normal operation but we still need to be mindful of them. The Execution Warnings can be considered normal. If you see a few scattered around in a 24 hour period that is normal. In fact it’s a sign that your application is being used, and it’s operating sufficiently. When you see an execution warning you need to look at the duration column. If it says 30000 that means that operation had to wait 30 seconds for memory to free up before it could execute. Time is an interesting thing is’t it? While 30 seconds to one person may not seem that long, to an IT person it’s a waste of your life. That’s why we are in the position we are in. Your users should not have to wait 30 seconds for an operation to execute. You can set a duration in the trace to only populate if the duration exceeds 20 seconds (20000 Ms). Otherwise 5, 10, 15 seconds is not to bad if, BIG IF they are scattered around the trace. If they are happening consecutively within (This is arguable based on project expectations) 10, 20, 30 minute time frame you may want to consider increasing the memory on the server. Worst case scenario, you will get time out errors in your trace which is a red flag that memory is a problem. Server Memory Changes is SQL Server and the OS playing tug-of-war. This is common when a server is restarted and the application immediately starts running events such as queries or stored procedures. The two will be fighting for RAM as they try to execute processes or events. Once one finishes it gives some RAM back and so on and so forth. This will create Server Memory Changes in your trace. In the event subclass you will see 1 – increase or 2 – decrease. This is common when the server starts up and should not cause a concern unless you get time out errors. When you see a lot of these during normal business hours that could mean more memory is needed.
What!! The SQL Lumberjack is telling me to go to my directory and ask them to spend money on memory. C’mon now, would I do that? In some cases yes, yes I would. This maybe one of them. Ultimately you need to satisify your project expectionations. However, you can tell SQL Server to only utilize ‘X’ amount of server memory. See Server Memory Options for more details. This will help increase the performance of the server. On the flip side it will increase the amount of Execution Warnings on the trace. This really boils down to.. do you ask to spend money, or do you ask the project to be mindful of the server limitations. Ya know…sometimes it’s both
So until next week…. Tomato, fruit or vegetable?